4 ms·

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 b

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 https://github.com/launchbadge/sqlx

willio58 4y ago
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.
tetha 4y ago
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.
- ipaddr 4y ago
  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 https://laravel.com/docs/10.x/eloquent
- akprasad 4y ago
  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.
- travisjungroth 4y ago
  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
  - akprasad 4y ago
    ^yes, that's indeed what I had in mind, and you've articulated the position very clearly.
    - travisjungroth 4y ago
      Thanks, I’m glad! It’s a topic that pops up for me a lot. Related: https://news.ycombinator.com/item?id=26905647 https://news.ycombinator.com/item?id=26905647
- yakshaving_jgt 4y ago
  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/Database-Persist.html https://hackage.haskell.org/package/persistent-2.14.5.0/docs...
eyelidlessness 4y ago
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.
phendrenad2 4y ago
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.

willio58 4y ago

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.

tetha 4y ago

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.

ipaddr 4y ago
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 https://laravel.com/docs/10.x/eloquent
akprasad 4y ago
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.
travisjungroth 4y ago
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
- akprasad 4y ago
  ^yes, that's indeed what I had in mind, and you've articulated the position very clearly.
  - travisjungroth 4y ago
    Thanks, I’m glad! It’s a topic that pops up for me a lot. Related: https://news.ycombinator.com/item?id=26905647 https://news.ycombinator.com/item?id=26905647
yakshaving_jgt 4y ago
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/Database-Persist.html https://hackage.haskell.org/package/persistent-2.14.5.0/docs...

eyelidlessness 4y ago

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.

phendrenad2 4y ago

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.