I think the correct approach is to understand the SQL that your ORM is using. If you can't have it create the correct query than roll your own. I think most of the trouble people get in with ORMs is not taking the time to understand the SQL that it creates and using it incorrectly as a result.
Imo software engineers look to the ORM to abstract the declarative set-theory, blah blah of SQL back into the object model they’re familiar with. IMO that’s a dereliction of duty as a developer but it happens and ORMs have gotten pretty good of late. In the Java space the JOOQ team is doing really good work.
In my space of Python SQLalchemy has been the dominate ORM of choice and I hated it but without putting out a better product I have just decided to work around it or adapt.
The article mentions database portability as an advantage of ORMs, and I agree - to me that is basically the only real advantage to ORMs. The article also mentions that database portability is not a very compelling advantage, since lots of applications don't actually need that, which I also agree with.
Personally I just pick either PostgreSQL or SQLite depending on my use case (and they're different enough that there is always one obvious choice) and just interface with them directly. Hasn't served me wrong yet.
Postgres is the one time I embrace lock-in, if the topic of switching to mysql or something else comes up it's a good thing if there'd be a prohibitive amount of work involved.
I still don't understand why people believe that abstracting away something as important as your data store is a good idea. They quickly run into performance problems that are quite difficult to fix.
It's funny that the same developers that try to avoid vendor lock-in don't realize they've locked themselves into an ORM forever.
Plus, SQL isn't that hard. And as a backend person, you should be able to visualize your data model anyway. A SQL datatbase is just a bunch of planes, for the most part, and the queries are how you intersect them...more or less.
We use Kysely[0], which is a nice balance. The code you write in Kysely translates 1:1 into SQL, which means there's very little perf left on the table, but we get an interface that's automatically typed, type safe, and works well with our js language tools.
Kysely is excellent! Another library that complements it is pgTyped[0]. It compiles .sql files into functions with strongly typed parameters and return values
My simple rule for databases is that if you are not actively querying on it, it doesn't need to be a separate table or column. Use the YAGNI rule here and you'll be better off. The classic example here is persons that have addresses and phone numbers. Most applications have no requirements to query on most of that: street name, postal code, phone number, etc. Less tables and columns mean simpler joins (or better, no joins at all). Any structured data that you don't query, just store it in json form in a blob and simplify your schema. Anything that actually requires complex querying, consider using a proper search engine. Or extract it to a dedicated field with some index on it that actually helps you querying effectively. The golden rule here is that if that query is connected to user input you probably need some notion of ranking, fuzzy searches, etc.
Either way, everything gets easier with a simple schema. Faster query and insert performance, easier to reason about when doing transactions, easier to maintain, etc.
Maybe, but not sure you’re getting much from that trade. If you’re not querying often, then performance shouldn’t be an issue. Modern hardware/dbms can definitely handle a couple of joins without blinking. So you’ve mostly lost flexibility.
Also having to redo later will wipe out the time savings of many, many simplifications. Not to mention doc and teaching reqs you’ve added to new devs.
I think they mean that adding a bunch of uncommon columns to your user table is just gonna decrease performance of common queries. In those cases I like offloading uncommon columns to separate tables that are joined only when needed.
I've fixed a few systems by simplifying their schemas. Over-engineering at the database level can lead to a lot of issues beyond just performance. The extra complexity causes a lot of overhead with technical debt, lots of ceremony around changes, etc. Performance issues are usually a good indicator of a team that is a bit out of their depth. Usually goes hand in hand with over engineered data models being mapped 1 to 1 to a table structure.
And I've fixed a few systems by properly engineering their schema on the database level.
Every single system I've encountered that had issues with scaling, performance, tech debt, etc have all been due to badly denormalized (and broken) database schemas, with no one understanding who did what why or what piece of code owns what part of the schema. You start getting arcane knowledge and little fiefdom silos and grumpy grey-beards with inflated savior complexes that are the "goto person" despite the whole thing being ridiculously simple.
> Will you actually need to change between fundamentally different database technologies?
Yes, use sqlite locally for development and run PG in prod. Unit tests can now use the db and finish in milliseconds. You get unit tests that have the power of integration tests and don't have to ever stub out your db. I use Redislite for the same thing.
I'm of the opinion that SQLite is the musl of the SQL world. By deciding that you'll support it first-class you'll avoid the sharp edges of database specific behavior and extension hell and write better more maintainable code.
"Sorry we can't actually put that logic the database, SQLite doesn't support spooky action at a distance."
Comments here make me wonder if I've just been spoiled by ActiveRecord. Not that I use it for all queries but 1) it's rare that I have to resort to raw SQL and 2) it kindly gets out of the way when I do.
I'm the opposite, I would rather write SQL than "resorting to" ORM queries, which is why my favourite libraries are aiosql[1] in Python, Hugsql[2] in Clojure and similar: write the queries as SQL in .sql files, which then get exposed as functions to your code.
I’ve been using aiosql and Hugsql for years and have never had any problems. In fact, Hugsql has a “snippets” feature that allows you to compose bits of SQL dynamically.
But the vast majority of SQL I’ve worked with simply doesn’t need it. But even when you do, the situation has never been any worse than it is with ORM’s for me.
I guess I failed to set the context correctly given that you presented solutions for Clojure and Python, where it isn't as much of a problem since from the start the language fails to provide compiler guarantees you usually come to expect out of a SQL driver wrapper in typed languages (even though Clojure macros are probably powerful enough to allow this).
As a comparison, DX-wise this is no safer and is indeed very similar to the usual idiom in Go for example, where you just concatenate (pre-interpolated) SQL strings. But when you actually want the compiler to prove the correctness of your queries even in a rudimentary way, these .sql file solutions usually (if not, everytime) fail to provide the necessary external checker that processes templates and uses an accurate model of your database and SQL to verify that all used combinations make sense.
The closest thing to a proper take on this I've seen is https://github.com/andywer/squid with https://github.com/andywer/postguard which, although the SQL is inlined in the code, it uses the right approach for verifying correctness as far as I could tell in the little time I experimented with it.
ActiveRecord seems great until it isn't--though I still think in SQL first and foremost. Common problems are (1) instantiation of many preloaded associated AR models that lead to memory pressure and GC time, (2) N+1 queries--yes you can preload, but there aren't easy ways of avoiding them on mutating a batch of AR models (validations and other callbacks on them don't batch).
Activerecord uses an enormous amount of resources that the databasr is way better equipped to handle, unfortunately.
There is also a mindset problem that's pretty serious in the ruby world, developers would use activerecord and load the universe, rather than running a select query carefully narrowed to the specific needs.
on top of this, often there are entities that are the result of joining tables, but these are never surfaced with ORMs (activerecord specifically), since the concept of an entity from a query joined with multiple tables doesn't exist except in the limited fashion of a view.
Except ORMs are just worse? Unless you mean they are the symptom of longing for other languages where there are none.
I would also love to see a new language for writing declarative queries. I also feel people don't like sql not for the language itself, but the lack of tooling.
> One might say that if SQL was so great there would be no ORMs
It's not like the ORMs exist without SQL. Their primary purpose is to provide a consistent interface between different flavors of SQL so the application developer doesn't need to care what database they're using.
For example, delimiting object names in Mysql uses ` and in Postgres uses ". The ORM will ostensibly have different adapters which take care of these differences without changing the application code.
It lessens the pain, still. It's rare that you'd use common ORM methods and run into something that's supported for one RDBMS and not another. Doing stuff that's less common and, of course, writing raw SQL will still be pain points but they'll be fewer than if one was doing everything in raw SQL.
We have also cloned Unix entirely since 1980, and extended and improved it. Yet, we are booting the systems using shell scripts where an unquoted variable stops the show. Why is that?
Why is there still a C:\ drive in personal computers, and an invisible device called PRN in every directory?
The times I've run into people who've written a library to handle something built into Postgres... everyone likes to reinvent timestamp and range handling for some reason.
I always admired micro ORMs like Dapper. The hard (or at least time wasting or error prone) tasks are:
- mapping results to app entities or projections
- input sanitization
- dynamic query building
- connection pooling and transaction management
- supporting multiple SQL dialects with one code base
After many years of experience with Hibernate, NHibernate, Entity Framework, and a plethora of other full ORMs, I absolutely do not want lazy/eager loading, opaque query DSLs and criteria APIs, and dealing with leaky abstractions for queryables (looking at you, LINQ and JPA), and I’m pretty wary anymore over cascading persistence. These things all look great in tutorials and are nearly magical in your PoC or simple projects, but in my experience, performance problems, unexpected and sometimes hard to diagnose bugs, and lots of ugly yak shaving are inevitable for moderate complexity apps over time.
Of all of them, I enjoyed JOOQ’s SQL builder with or without codegen the most if I need to support multiple dialects or complex dynamic queries.
Speaking of JOOQ, anyone have recommendations for something similar on nodejs? I haven’t been particularly excited by prisma, typeorm, or sequelize.
You can learn a lot about a system merely by looking at the database schema and data transformations to new consistent data. How exactly these processes do what they do is an implementation detail. Think in invariants, not mental simulation of incidentally complex procedures. In this light knowing SQL is a superpower that applies to all systems built with any RDBMS.
47 comments
[ 3.8 ms ] story [ 96.3 ms ] threadIn my space of Python SQLalchemy has been the dominate ORM of choice and I hated it but without putting out a better product I have just decided to work around it or adapt.
But if I can I use raw SQL where possible.
Personally I just pick either PostgreSQL or SQLite depending on my use case (and they're different enough that there is always one obvious choice) and just interface with them directly. Hasn't served me wrong yet.
It's funny that the same developers that try to avoid vendor lock-in don't realize they've locked themselves into an ORM forever.
Plus, SQL isn't that hard. And as a backend person, you should be able to visualize your data model anyway. A SQL datatbase is just a bunch of planes, for the most part, and the queries are how you intersect them...more or less.
[0]: https://kysely.dev/
[0] https://pgtyped.dev/
You write SQL but instead of getting back flat and potentially-collided data, you get back pure objects which are properly structured.
https://github.com/craigmichaelmartin/pure-orm
Coming from a Rails legacy app, these raw SQL "clever ideas" are where I found the most issues.
Either way, everything gets easier with a simple schema. Faster query and insert performance, easier to reason about when doing transactions, easier to maintain, etc.
Also having to redo later will wipe out the time savings of many, many simplifications. Not to mention doc and teaching reqs you’ve added to new devs.
Every single system I've encountered that had issues with scaling, performance, tech debt, etc have all been due to badly denormalized (and broken) database schemas, with no one understanding who did what why or what piece of code owns what part of the schema. You start getting arcane knowledge and little fiefdom silos and grumpy grey-beards with inflated savior complexes that are the "goto person" despite the whole thing being ridiculously simple.
Yes, use sqlite locally for development and run PG in prod. Unit tests can now use the db and finish in milliseconds. You get unit tests that have the power of integration tests and don't have to ever stub out your db. I use Redislite for the same thing.
I'm of the opinion that SQLite is the musl of the SQL world. By deciding that you'll support it first-class you'll avoid the sharp edges of database specific behavior and extension hell and write better more maintainable code.
"Sorry we can't actually put that logic the database, SQLite doesn't support spooky action at a distance."
Most IDEs provide intellisense/validation of ORM entities, vs treating SQL like a raw string.
ORM entities also make refactoring and impact analysis slightly easier.
Despite those benefits, I generally find ORMs a pain for anything besides the most basic queries.
I'm the opposite, I would rather write SQL than "resorting to" ORM queries, which is why my favourite libraries are aiosql[1] in Python, Hugsql[2] in Clojure and similar: write the queries as SQL in .sql files, which then get exposed as functions to your code.
[1] https://nackjicholson.github.io/aiosql/
[2] https://www.hugsql.org/
Dynamic SQL.
I’ve written aiosql functions that use dynamic SQL (via plpgsql) just fine.
https://www.hugsql.org/using-hugsql/composability/snippets
But the vast majority of SQL I’ve worked with simply doesn’t need it. But even when you do, the situation has never been any worse than it is with ORM’s for me.
As a comparison, DX-wise this is no safer and is indeed very similar to the usual idiom in Go for example, where you just concatenate (pre-interpolated) SQL strings. But when you actually want the compiler to prove the correctness of your queries even in a rudimentary way, these .sql file solutions usually (if not, everytime) fail to provide the necessary external checker that processes templates and uses an accurate model of your database and SQL to verify that all used combinations make sense.
The closest thing to a proper take on this I've seen is https://github.com/andywer/squid with https://github.com/andywer/postguard which, although the SQL is inlined in the code, it uses the right approach for verifying correctness as far as I could tell in the little time I experimented with it.
There is also a mindset problem that's pretty serious in the ruby world, developers would use activerecord and load the universe, rather than running a select query carefully narrowed to the specific needs. on top of this, often there are entities that are the result of joining tables, but these are never surfaced with ORMs (activerecord specifically), since the concept of an entity from a query joined with multiple tables doesn't exist except in the limited fashion of a view.
But we still only have one language for querying a database. And a kinda not very good one at that. Why is that?
https://www.holistics.io/blog/quel-vs-sql/
One could argue that ORM's are the alternative language people are looking for.
I would also love to see a new language for writing declarative queries. I also feel people don't like sql not for the language itself, but the lack of tooling.
One might say that if SQL was so great there would be no ORMs -- because there would be no need.
The ORMs, if anything, are a symptom of the larger problem that is SQL.
It's not like the ORMs exist without SQL. Their primary purpose is to provide a consistent interface between different flavors of SQL so the application developer doesn't need to care what database they're using.
For example, delimiting object names in Mysql uses ` and in Postgres uses ". The ORM will ostensibly have different adapters which take care of these differences without changing the application code.
That's because TINA: There is No Alternative.
Why is there still a C:\ drive in personal computers, and an invisible device called PRN in every directory?
- mapping results to app entities or projections
- input sanitization
- dynamic query building
- connection pooling and transaction management
- supporting multiple SQL dialects with one code base
After many years of experience with Hibernate, NHibernate, Entity Framework, and a plethora of other full ORMs, I absolutely do not want lazy/eager loading, opaque query DSLs and criteria APIs, and dealing with leaky abstractions for queryables (looking at you, LINQ and JPA), and I’m pretty wary anymore over cascading persistence. These things all look great in tutorials and are nearly magical in your PoC or simple projects, but in my experience, performance problems, unexpected and sometimes hard to diagnose bugs, and lots of ugly yak shaving are inevitable for moderate complexity apps over time.
Of all of them, I enjoyed JOOQ’s SQL builder with or without codegen the most if I need to support multiple dialects or complex dynamic queries.
Speaking of JOOQ, anyone have recommendations for something similar on nodejs? I haven’t been particularly excited by prisma, typeorm, or sequelize.
[1]: https://orm.drizzle.team