6 ms·

So much of what is bad about tooling comes from this one assumption: Database schema changes are hard. If changing the schema is hard, then you come up with s

So much of what is bad about tooling comes from this one assumption: Database schema changes are hard. If changing the schema is hard, then you come up with silly rules about when the schema can change and who can do it. You make migration tools in other languages to avoid writing the line of SQL that would change the schema. You use 3rd party tools to compare two databases and spit out change scripts automatically (and keep two databases versions up to date just for that purpose). You adopt entire schemaless databases so that you never need to change the schema. But that's silly. Because Database Schema Changes are Not Hard. You do the thing the author is scared of: SQL Change Scripts. Insert column, massage the data, flip on the null constraint, add relationships. It's all really basic stuff, and if you don't know the SQL syntax for it you can just ask the db tool you're using to make the change. It'll have a little "Script This Out" button next to the Save button. If you do that, then you get to live in a world where Database Schema Changes are Easy. You get to have a build that just runs new change scripts in order rather than involving Ruby or some wacky 3rd party tool. And you can move as fast as you like.

YouAreARetard 8y ago
Database schema changes are not hard in terms of complexity, but if you make a mistake it can be very costly. You seem to be a complete idiot who has no experience in anything.
djrobstep 8y ago
I'm not sure you read the talk, which is literally about creating SQL change scripts?
- jasonkester 8y ago
  I did. Its main piece of advice is to stop writing sequential change scripts, and to instead only keep "empty", "dev", "live" schema scripts or similar, and to rely on a tool to sort out changes between them. ... which introduces all the problems that everybody here is talking about. Thus my comment above advocating not doing that.
  - djrobstep 8y ago
    I have no idea what document you just read, but the linked talk is literally about preparing sequential change scripts.
bartread 8y ago
Even if you do know SQL syntax using the tool to make the change script for you can save quite a bit of time, especially if you've made a bunch of changes which can happen if you've added new functionality. DISCLAIMER: I worked at Redgate, who make a tool called SQL Compare that works with SQL Server, for nearly a decade. Since this article is about PostgreSQL you should check out Postgres Compare, built by my friend Neil: https://www.postgrescompare.com/ https://www.postgrescompare.com/. He also used to work at Redgate.
- postgrescompare 8y ago
  Thanks Bart! I know for PostgresCompare a lot of its utility is in finding the differences. Providing clarity into what has changed while one was building a feature. A lot of users still write the SQL themselves. They let the tool figure out the easy stuff, get the dependencies correct, and they make a few edits here or there.
Existenceblinks 8y ago
> But that's silly. Because Database Schema Changes are Not Hard. Live database schema changes are hard. Usually people just let server down a few seconds if schema changes and data are not huge.
- vbezhenar 8y ago
  Why is it hard? Most of changes are atomical and either in background or instant. What change require downtime? I would argue that software change without downtime is harder and basically not solved problem for most environments unless you're using cluster of machines.
  - deleted 8y ago
    [deleted]
  - gnud 8y ago
    Live changes are hard because they requires precise planning, and must often be done in several steps. Imagine you're extracting some data to a new table in order to support 1:N instead of 1:1 of something. For example, introducing an address table, instead of storing the address directly in the customer table. If you can have downtime, and have a small database, this can be done with one change and one deploy - that's not very hard. If you have a big database (100s of GB), and can't have downtime, you can't grab a full table lock to rewrite all the records in one go. You'll have to do something like 1) create the new table (easy) 2) create a marker column on the old table saying 'data moved to new table', set to false on all records 3) deploy version of application that can use both schemas 4) migrate all data to the new table, flipping the marker as you go. 5) nce all records are migrated, deploy new version of application that only uses new schema 6) drop marker and old columns (there are multiple ways to do this, of course - this is one approach) Now imagine that you have 4 or 5 applications using this database. (Some reporting application, some integration with an accounting system, some integration with a marketing tool, etc). And you must do a live migration. That is _hard_.
    - vinceguidry 8y ago
chousuke 8y ago
I don't really understand what is it about that makes RDBMS schema changes particularly harder in people's minds. All schema changes to your data can be hard or easy depending on what you do, regardless of whether that schema is implicit in the application or enforced by a database. If the schema change is additive, it's usually easy. On the other hand, if you drop data items that older versions of applications expect, or change the semantics of existing data, you will need to proceed carefully. Whether you're using a relational database or something else doesn't really matter. The tool you use to execute those changes doesn't really matter either if your data model changes at the whim of the developers without actual planning behind it to deal with the impact on upgrades it will have.
- speedplane 8y ago
  In an RDMS, a change in schema is hard because changing one table can affect many others. In a NOSQL database, there are far fewer connections between tables (or "objects"). There is less to wrap your head around.
  - chousuke 8y ago
    That's just up to how you model your data. there's nothing that prevents you from using a single table with a JSON blob in an RDBMS either, but it's throwing away most of the benefits. A NoSQL database might seem to make migrations easier, but I'm not convinced that it actually does.
    - speedplane 8y ago
      > That's just up to how you model your data. Well, a NoSQL database forces you to reduce or eliminate relations, whereas an RDMS allows and encourages them. Sure, theoretically, you can make an RDMS database without relations, but it goes against the standard design methodology. You can be sure that if you implement an RDMS in a large organization, relationships between tables will seep into your schema. With a NoSQL database, that's pretty much impossible.
  - deleted 8y ago
    [deleted]
sametmax 8y ago
That's assuming changing the schema is just about changing the layout of the database, which is definitly not true. You have your code depending on it, documentation, constrainsts, replication, caching, db load, deployement systems, different envs and versions... The SQL is the easy part and barely registers as an issue. I see the same problem with SQL lovers rejecting ORMs as a dumb way to avoid using the right tool for the job. But using an ORM is not about avoiding to write SQL. It's about all the rest: code introspection, data validation, documententation, having a common api, tooling, etc
- pytester 8y ago
  ORM are frameworks that exist mainly as a way to avoid writing and maintaining thousands of lines of boilerplate. Like most frameworks (i.e. packages that force you to write your code to conform to their patterns) a really good one confers a massive boost in productivity while a really bad one is horrifying to work - it fails in bizarre unexplainable ways and straitjackets your development. IME, passionate rejection of ORMs predominantly comes from people who have been scarred by experience with really bad ORMs.
  - ken 8y ago
    Blaming it all in “really bad ORMs” without naming any sounds suspect to me. Can you name some good ones?
    - l33tman 8y ago
      Django's ORM has always served me well, it handles migrations and I've had almost zero SQL-hacks that needed to be done.
    - pytester 8y ago
      For example JPOX was an ORM that almost put me off the entire idea. It was horrendous. Recently I've used Django ORM and SQLAlchemy - both are warty but generally decent and better than not using any ORM. This is somewhat language dependent - certain language qualities inhibit the creation of decent ORMs (e.g. it's not really possible to create a decent ORM in golang due to its design).
ajessup 8y ago
The author's point is not that schema changes are hard - but rather that versioning schema changes are hard.

YouAreARetard 8y ago

Database schema changes are not hard in terms of complexity, but if you make a mistake it can be very costly. You seem to be a complete idiot who has no experience in anything.

djrobstep 8y ago

I'm not sure you read the talk, which is literally about creating SQL change scripts?

jasonkester 8y ago
I did. Its main piece of advice is to stop writing sequential change scripts, and to instead only keep "empty", "dev", "live" schema scripts or similar, and to rely on a tool to sort out changes between them. ... which introduces all the problems that everybody here is talking about. Thus my comment above advocating not doing that.
- djrobstep 8y ago
  I have no idea what document you just read, but the linked talk is literally about preparing sequential change scripts.

bartread 8y ago

Even if you do know SQL syntax using the tool to make the change script for you can save quite a bit of time, especially if you've made a bunch of changes which can happen if you've added new functionality. DISCLAIMER: I worked at Redgate, who make a tool called SQL Compare that works with SQL Server, for nearly a decade. Since this article is about PostgreSQL you should check out Postgres Compare, built by my friend Neil: https://www.postgrescompare.com/ https://www.postgrescompare.com/. He also used to work at Redgate.

postgrescompare 8y ago
Thanks Bart! I know for PostgresCompare a lot of its utility is in finding the differences. Providing clarity into what has changed while one was building a feature. A lot of users still write the SQL themselves. They let the tool figure out the easy stuff, get the dependencies correct, and they make a few edits here or there.

Existenceblinks 8y ago

> But that's silly. Because Database Schema Changes are Not Hard. Live database schema changes are hard. Usually people just let server down a few seconds if schema changes and data are not huge.

vbezhenar 8y ago
Why is it hard? Most of changes are atomical and either in background or instant. What change require downtime? I would argue that software change without downtime is harder and basically not solved problem for most environments unless you're using cluster of machines.
- deleted 8y ago
  [deleted]
- gnud 8y ago
  Live changes are hard because they requires precise planning, and must often be done in several steps. Imagine you're extracting some data to a new table in order to support 1:N instead of 1:1 of something. For example, introducing an address table, instead of storing the address directly in the customer table. If you can have downtime, and have a small database, this can be done with one change and one deploy - that's not very hard. If you have a big database (100s of GB), and can't have downtime, you can't grab a full table lock to rewrite all the records in one go. You'll have to do something like 1) create the new table (easy) 2) create a marker column on the old table saying 'data moved to new table', set to false on all records 3) deploy version of application that can use both schemas 4) migrate all data to the new table, flipping the marker as you go. 5) nce all records are migrated, deploy new version of application that only uses new schema 6) drop marker and old columns (there are multiple ways to do this, of course - this is one approach) Now imagine that you have 4 or 5 applications using this database. (Some reporting application, some integration with an accounting system, some integration with a marketing tool, etc). And you must do a live migration. That is _hard_.
  - vinceguidry 8y ago

chousuke 8y ago

I don't really understand what is it about that makes RDBMS schema changes particularly harder in people's minds. All schema changes to your data can be hard or easy depending on what you do, regardless of whether that schema is implicit in the application or enforced by a database. If the schema change is additive, it's usually easy. On the other hand, if you drop data items that older versions of applications expect, or change the semantics of existing data, you will need to proceed carefully. Whether you're using a relational database or something else doesn't really matter. The tool you use to execute those changes doesn't really matter either if your data model changes at the whim of the developers without actual planning behind it to deal with the impact on upgrades it will have.

speedplane 8y ago
In an RDMS, a change in schema is hard because changing one table can affect many others. In a NOSQL database, there are far fewer connections between tables (or "objects"). There is less to wrap your head around.
- chousuke 8y ago
  That's just up to how you model your data. there's nothing that prevents you from using a single table with a JSON blob in an RDBMS either, but it's throwing away most of the benefits. A NoSQL database might seem to make migrations easier, but I'm not convinced that it actually does.
  - speedplane 8y ago
    > That's just up to how you model your data. Well, a NoSQL database forces you to reduce or eliminate relations, whereas an RDMS allows and encourages them. Sure, theoretically, you can make an RDMS database without relations, but it goes against the standard design methodology. You can be sure that if you implement an RDMS in a large organization, relationships between tables will seep into your schema. With a NoSQL database, that's pretty much impossible.
- deleted 8y ago
  [deleted]

sametmax 8y ago

That's assuming changing the schema is just about changing the layout of the database, which is definitly not true. You have your code depending on it, documentation, constrainsts, replication, caching, db load, deployement systems, different envs and versions... The SQL is the easy part and barely registers as an issue. I see the same problem with SQL lovers rejecting ORMs as a dumb way to avoid using the right tool for the job. But using an ORM is not about avoiding to write SQL. It's about all the rest: code introspection, data validation, documententation, having a common api, tooling, etc

pytester 8y ago
ORM are frameworks that exist mainly as a way to avoid writing and maintaining thousands of lines of boilerplate. Like most frameworks (i.e. packages that force you to write your code to conform to their patterns) a really good one confers a massive boost in productivity while a really bad one is horrifying to work - it fails in bizarre unexplainable ways and straitjackets your development. IME, passionate rejection of ORMs predominantly comes from people who have been scarred by experience with really bad ORMs.
- ken 8y ago
  Blaming it all in “really bad ORMs” without naming any sounds suspect to me. Can you name some good ones?
  - l33tman 8y ago
    Django's ORM has always served me well, it handles migrations and I've had almost zero SQL-hacks that needed to be done.
  - pytester 8y ago
    For example JPOX was an ORM that almost put me off the entire idea. It was horrendous. Recently I've used Django ORM and SQLAlchemy - both are warty but generally decent and better than not using any ORM. This is somewhat language dependent - certain language qualities inhibit the creation of decent ORMs (e.g. it's not really possible to create a decent ORM in golang due to its design).

ajessup 8y ago

The author's point is not that schema changes are hard - but rather that versioning schema changes are hard.