A lot of the stuff I see people writing application code for doesn't require any more complex SQL features than joins, aggregations and filters. i.e the stuff plain old SQL is really good at.
That’s just born of either psychosis or naivety; but even when one does know SQL, there’s more to consider. RDBMS’s are awkward systems to work with, and SQL knowledge isn’t the issue
moving business logic into schema/queries is usually fine, (and perhaps the only sane place for the DB to directly interact with business logic) though schema changes has all the issues described. Queries get to live with your application code, and so can be treated sanely, but you only really get to a good setup with a decent query builder library (ORM-haters smashing strings together like an ape is naturally full of issues, and ORM-lovers get all the fun of table/object model-mismatch and poor query generation). But then you run into the issue that every database library tries to be database-agnostic, and you lose access to all the fancy features your database provides
> but you only really get to a good setup with a decent query builder library (ORM-haters smashing strings together like an ape is naturally full of issues, and ORM-lovers get all the fun of table/object model-mismatch and poor query generation). But then you run into the issue that every database library tries to be database-agnostic, and you lose access to all the fancy features your database provides
I've found that smashing strings together works excellently so long as:
- You use something like JavaScript's template strings so that interpolated fragments are inline
- You have named placeholder support (so that you're not trying to count parameter placements which gets hugely complex for optional parameters).
- You mainly use it for SELECTS and have minimal query builder for INSERTs/UPDATEs/UPSERTs
And you get full access to all of the database's features.
If you’re maintaining a layer between construction and finalization (the query builder), then you’re not really in the string smashing group I was describing. The main thing I was trying to get at there is no one should make the mistake of “ORMs are shit, I can just write SQL strings directly” because that road is plagued with problems (because SQL is not a language that composes well..).
And it’s not just named parameters; with a sane query builder you don’t need to totally rewrite things when dealing with subqueries vs top-level, aggregations, etc.
As long as you don’t go so far as to introduce the horror of an ORM
> The main thing I was trying to get at there is no one should make the mistake of “ORMs are shit, I can just write SQL strings directly”
This has actually been the road I've been heading down on my personal projects. If I were to use this for work I would use something like Kysely so I can have type safety and make sure the application layer is updated to support every migration, but I've taken time to really practice SQL and write queries & migrations manually and avoid ORMs completely. I love writing SQL by hand, however I know that doesn't scale without some automated checking going on.
