Don't use your ORM entities for everything – embrace the SQL(blackparrotlabs.io)
blackparrotlabs.io
Don't use your ORM entities for everything – embrace the SQL
https://www.blackparrotlabs.io/post/architecture-pitfalls-dont-use-your-orm-entities-for-everything
47 comments
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.
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.
[0]: https://kysely.dev/
[0]: https://kysely.dev/
Kysely is excellent! Another library that complements it is pgTyped[0]. It compiles .sql files into functions with strongly typed parameters and return values
[0] https://pgtyped.dev/
[0] https://pgtyped.dev/
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.
But if I can I use raw SQL where possible.
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.
But if I can I use raw SQL where possible.
Django’s ORM is also widely popular in the Python world. It’s just not standalone.
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.
> resort to raw SQL
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/
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/
Those tend to be great until two words come into play:
Dynamic SQL.
Dynamic SQL.
What’s your issue with dynamic SQL in this case?
I’ve written aiosql functions that use dynamic SQL (via plpgsql) just fine.
I’ve written aiosql functions that use dynamic SQL (via plpgsql) just fine.
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.
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.
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.
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.
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.
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.
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.
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.
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'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.
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.
Indeed—never heard proper normalization described as "over engineering" before.
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.
Whilst true from a performance level, do appreciate there is a complexity cost with raw SQL given how it allows for inconsistent ways of working.
Coming from a Rails legacy app, these raw SQL "clever ideas" are where I found the most issues.
Coming from a Rails legacy app, these raw SQL "clever ideas" are where I found the most issues.
In the 1980's we had C/C++/Ada/Pascal/Fortran/Assembly/Lisp. Since the 1980's the languages have exploded.
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.
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.
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.
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.
Are they worse? The popularity of them has exploded like programming languages from the 80's to now.
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.
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.
> 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'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.
Not even that works painlessly. You commonly run into incompatibilities so an ORM won't necessarily make your code database agnostic.
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.
> It's not like the ORMs exist without SQL.
That's because TINA: There is No Alternative.
That's because TINA: There is No Alternative.
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?
Why is there still a C:\ drive in personal computers, and an invisible device called PRN in every directory?
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.
- 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.
Drizzle [1] comes pretty close the last time I checked.
[1]: https://orm.drizzle.team
[1]: https://orm.drizzle.team
Looks promising, thanks!
> 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."
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."
Why not run Pg in a container if that's what's in prod?
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.
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.
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.
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.
This has to be one of the long lasting topics on HN. I remember commenting on "vietnam of cs" blogpost like two decades ago.
Have you seen Pure ORM? It's an ORM that purely does object relational mapping.
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
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
JOOQ is awesome, doesn't try to do too much. Also tried to use Exposed from Kotlin, but it's way too young, support only simple data types.
Couldn’t agree more. There’s huge benefits to ORMs but when the fast path is needed hand written and optimized SQL is my go to.
[deleted]
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.