Hacker News Reader

Comment by YorickPeterse | Hacker News Reader

> Disagree. SQL is a declarative language that is both clear and universal. Feel free to generate the migrations however you’d like, but I want to see the SQL.

This works until you need something beyond the trivial "UPDATE table SET column1 = column2", such as "Update the values in column X with the contents of file Y that exists in Amazon S3", or really anything else you can't express in SQL.

> Another bonus (and I’m sure I’ll be told I’m wrong) is that you don’t need to write tests for the migration, because it’s declarative.

This indeed is wrong, and essentially comes down to "It looks simple so it's correct". A SQL query "DELETE FROM users" might be correct, but if you meant for it to be "DELETE FROM users WHERE id IN (...)" it's going to cause problems.

In other words, at least for data migrations you absolutely have to write tests or you will run into problems.

View on HN

These are not migrations in the traditional sense. I was referring to DDL. Nevertheless:

> "Update the values in column X with the contents of file Y that exists in Amazon S3"

You actually can do that in SQL, assuming it’s hosted on AWS (or for Postgres, you’ve installed the correct extension). It would be a bit convoluted (I think to handle the UPDATE, you’d first dump from S3 into a temp table, then update from that), but it would work.

> A SQL query "DELETE FROM users" might be correct, but if you meant for it to be "DELETE FROM users WHERE id IN (...)" it's going to cause problems.

If someone doesn’t notice this issue in SQL, they’re not going to notice it in an ORM, either. It’s also possible (and a good idea) to enforce predicates for DELETE at a server configuration level, such that the DB will refuse to execute them. And really, if you actually want to delete everything in a table, you should be using TRUNCATE anyway.

You can express it in SQL; you would need to create a table with "the contents of file Y" from S3, or use something like PostgreSQL foreign data wrappers to that effect: we're using pg_analytics[1] to query CSV/Parquet data, and the same applies to most databases these days.

The limitation you're implying—is not that of SQL, but your data model.

[1] https://github.com/paradedb/pg_analytics

Both you and sgarland are missing the point here: the example I gave is literally just that: an example. There could be many more complex cases, some relying on other business logic provided by your application, some more isolated. Some of those cases could be handled using some random PG extension, but for many that won't be sufficient.

In contrast, a migration written in an actual programming language can handle all such cases. Depending on the amount of abstractions applied, it can also look close enough to a declarative language (in other words, it doesn't have to be verbose).

So yes, the limitation I'm implying very much is a limitation of SQL (or really any declarative query language for that matter). It has nothing to do with the data model as it applies equally well to using e.g. MongoDB, Redis, or really anything else that stores a pile of data you may want to transform in complex ways.

What can I say, use a shitty schema—win shitty prizes. This is why we never allow coders to touch the schema: you _will_ screw it up given the tiniest chance. Suddenly, my data model has to be aware of "all such cases." Thank you please no good bye.

Unfortunately what you wind up with, IME, is devs “owning” their DB (sometimes it’s a schema on a shared instance, sometimes it’s its own instance), until such time that the poor decisions catch up. Then it’s an incident, at which point the DBRE / SRE team asks WTF, nurse the thing back to life, and then are summarily ignored for future advice. Rinse and repeat.

I’m hoping my current job breaks this trend.

True. Although to be fair, I think you're a bit naive to assume that this is a trend, if anything. IMO there's no escaping Ownership issues: if your culture is to "move fast break things", it would be inevitable. The ownership philosophy is such that some gaps in ownership are inevitable.

I think you’re missing the point that what you’re describing is not a migration as a DB{A,RE} would call it. That is what I’m pushing back on.

If you’re shifting or otherwise manipulating tuples, then yes, you probably want to handle that logic in a more general-purpose language (though it isn’t _required_, annoying though it might be otherwise).

But for DDL? No, I don’t want anything in the way. The state should be stored in VCS, of course, but I don’t want some abstraction trying to be clever about ALTER TABLE foo. Just run it how I specified.

Reply on news.ycombinator.com