Hacker News Reader

Comment by akprasad | Hacker News Reader

If writing SQL directly, what process do you use to update your queries during schema changes? Do you rely on a test suite to catch errors then update queries by hand? Are you using compile-time checks through libraries like sqlx [1]?

[1]: https://github.com/launchbadge/sqlx

View on HN

Finally something that bucks my instinct that integration tests are a waste of time. If I’m deploying a SQL query, everything that gets in or out gets thorough automated testing. Regardless of your attitude on the subject, directly interfacing a database is business logic. Test it like you would anything else that interacts with an external dependency. If you made a schema change and it broke something, you’ll either get test failures, or learn where your coverage is incomplete, or learn where you don’t care about a particular failure mode.

Normally I’d prefer testing more isolated units, but if it’s your own data access, just write the integration tests. They’re at least implicitly part of your “unit” anyway.

I recommend taking a step back before making a backward-incompatible schema change. Do you really need this? Chances are, it's just faffing about out of a sense of neatness and orderliness and you don't actually need to break the schema.

Sorry to butt heads there, but what ORM does automatically handle schema changes? I have so many teams with highly abstracted ORMs telling me that no-downtime schema changes are impossible, no matter how trivial the changes are, or would be in e.g. Hibernate.

And the only team capable of zero-downtime schema changes uses a minimal DSL to SQL lib.

There are two parts to the schema change, the table changes (migrations) and the code changes. I think the person you were replying to was asking about the code part. Every ORM I've used or looked at[0] does that automatically in the sense that you make a change to a model and all queries generated by that model will now have that change. Depending on the change they could break, but the point is the changes propagate. If you're not using an ORM and are just using string templates, this won't happen. There are query builders, too, which are sort of in between.

[0] Django, SQLAlchemy, Active Record, Piccolo, Tortoise, SQLModel, peewee

^yes, that's indeed what I had in mind, and you've articulated the position very clearly.

Thanks, I’m glad! It’s a topic that pops up for me a lot. Related: https://news.ycombinator.com/item?id=26905647

I don't know of any ORM that handles schema changes automatically (edit: for the most strict definition of "automatically" -- I've used ORMs that generate migration files and am generally happy with them).

My experience here is quite limited, so I'm inclined to defer to yours. With that caveat, I find that ORMs play more nicely with my IDE (e.g. I can rename a column name and automatically update all call sites) and often raise early warnings if their declarative classes are out of sync with the underlying table schema.

The persistent[0] library can automatically generate and run migrations to match schema changes.

[0]: https://hackage.haskell.org/package/persistent-2.14.5.0/docs...

This problem seems foreign to me. Are your teaming not using Laravel? If not maybe it's time to upgrade. https://laravel.com/docs/10.x/eloquent

This is exactly why I find no solution fits all here. For me, I use an ORM as a catch-all and then for certain applications I manually write the queries. It's best of both worlds, and I know what components in my app have custom queries so I can test against them.

Reply on news.ycombinator.com