You might not need an ORM
sometechblog.com
sometechblog.com
Please organize yourselves in two equal-height piles around “yes” and “no”, and then a third minuscule one around “the hell do I know, it’s your use case and your code”.
See you again in a few weeks!
I can write something like this (pseudocode):
username = '31337'
result = db.query('SELECT username, email FROM users WHERE username = 1?', username)
result?: {
username: string,
email?: string
}I'd ask about tabs vs spaces, but that seems settled now ...
For a small service with just a few basic queries, skipping the ORM and going straight to SQL is pretty nice. And there are situations where you need custom SQL, like complex reports or dashboards. But for typical CRUD in a team setting, I've found that ORMs can be worth the overhead.
I've found that using the ORM in conjunction with stored procedures works well. For the basic stuff, the ORM handles it well enough. As soon as the ORM request starts to look like spaghetti, it's time to think about making a stored procedure and using it.
Years ago I also went down the road of "use stored procedures for everything," which also works, but you have to get deep into the gnarly SQL weeds. And, if DB portability is one of your goals, you more or less have to give up on your dreams. It makes a lot of sense if your project has many fingers in the pie and you have access to a talented-if-grumpy DBA (beard optional). I liked it for the small project I did, but I will admit that it was more work than was optimal.
I think it's ironic that with databases and hardware description languages, we have been stuck in the 1990s with just one or two selections. In regular software development we have maybe 500 languages that large systems have been built from.
ORM's are language abstractions that compiles down to SQL. With HDL's we have VHDL and Verilog. The other HDL's that are built on Python/Scala compile down to VHDL and Verilog, just like the ORM's compile down to SQL.
Definitely true. ORMs allow an on-rails experience for, I dunno, 80%? 85%? of what people want a DB for. The RDBMS is mostly treated as a durable storage system.
The real magic of an RDBMS only comes through when you get really, really complicated, and then you need SQL to manage these complex relationships. ORMs try to make that easier, but beyond a certain level it isn't less complex, just differently complex.
The ORM written in your preferred language means less context switching during development. Great idea, and a great tool.
In those two instances I mentioned, there's this weird ideology usually in the form of "just learn SQL/Verilog/thing here" -- while ignoring basic inefficiencies of those languages.
No one bothers to say "Just learn C and all your problems will be solved" because I think everyone understands all the inefficiencies that C has. Though there are other proponents of other languages/frameworks that take this tack.
Everything else is IMO a bad layer of abstraction that will eventually bite you when you need to get closer to SQL.
Honestly asking - I'm not an expert in this space: is this not just parameterized queries?
It seems like anything else would have to have exactly the same "shape" in order to support all underlying database features...
For example, if you’re adding filtering to a product list. The query will always return the product id, name, image url and description. But you might have multiple filters (in stock, blue, etc). The query builder can make that process easier and less error prone.
Same with pagination, depending on how you do it.
You just described ORMs. The implementation details can vary but ORM is pretty much an API for building queries on top of a DB.
For Java based solutions, check out https://www.jooq.org/ or http://querydsl.com/
The query builder makes composable SQL syntax trees from raw text or Python objects. Executing those as queries gives you rows of tuples.
If you teach it the shapes of your data with the data mapper, you can query in terms of your tables (instead of ad-hoc column names) and get rows of populated custom objects.
On top of the mapper, the ORM defines your application-level data model, manages relationships and loading strategies, and powers up the query builder with the usual stuff like automatic joins (if you want them). You can still build composable queries, now against your application objects, but you can also work directly with the model like a standard ORM.
If you know you want the whole thing from the start, Declarative will let you do the mapping and the ORMing all at once by defining a class.
I also like the idea of using plain SQL and do all the additional manual work myself (that's what I do in personal projects); I like the feeling that I'm in control. But when it comes to professional work, and there's a team behind, then handling raw SQL is a recipe for disaster. ORMs bring consistency to the table, ORMs are not the best technical solution, but are the best solution when it comes to working with people.
We use it extensively for Windmill (e.g: [2]) and couldn't be happier.
[1]: https://github.com/launchbadge/sqlx [2]: https://github.com/windmill-labs/windmill/blob/d60a770eb710e...
The idea of checking the queries against a schema is great. Even better if you can use it to infer arguments and return types.
> Enables building the macros in offline mode when a live database is not available (such as CI) [0]
[0] https://github.com/launchbadge/sqlx/blob/76ae2865005cc79d569...
Is this not a problem in the rust implementation ?
Also, testing is quite a drag. If you're using sqlite, you can use in-memory mode and that's quite nice. But for other databases you basically have only two options: 1. setup and teardown an actual testing db 2. wrap your sqlx interfacing code in something vaguely resembling the repository pattern and create an in-memory implementation for testing. I'd call both of these options less than ideal.
At work we predominately use TypeOrm, which for all its (many, many) faults is really ergonomic and succinct. So maybe my views of ORMs is just warped.
Most frameworks are way behind Django/Django Rest Framework in that regard. I'm not saying there aren't any others doing something similar, NestJs for example. But I'm not aware on other solutions.
Rust Diesel can go between pure ORM and a more composable query layer. SQLAlchemy also exposes such functionality when you don’t want to fully embrace ORM, but also don’t want to throw out some reuse and composability.
As you pointed out most ORMs have a query builder layer anyway and usually a raw sql layer too. Why not use an ORM for the easy bits?
I’ve honestly got no idea if DRF is comparatively better than what you can do with other frameworks. I’ve recently come out the other side of some work that’s led me to believe that DRF has some deep rooted design flaws that it’ll never be able to dig itself out of. I’m excited to see how the new generation of API authoring tools shakes this all up. None I’d this is to say that it’s any better anywhere else. I don’t think that things like FastAPI are the sort of like-for-like replacement that I’m interested in.
(Basically, if you use lazy loading, your code will be slow and may require major refactors late in the project life. If you use aggressive loading, your code may have bugs when expected relationships aren't populated.)
I should point out that this isn't purely an EF core limitation! The "correct" approach is to always copy objects from database domain (classes) to business logic domain (classes); but this is often more effort than it's worth.
FWIW: I shipped a C# product with a very simple SQLite database. When the application started LINQtoSQL wasn't available (we used Mono,) so we just wrote our own queries.
Best decision ever. We didn't need to do the various futzing and learning curve that comes with learning an ORM. Granted, it only worked because we had a handful of tables, very basic CRUD, and infrequent schema changes.
Still, if you fall into the "I must use an ORM/hand SQL" camps, you're probably limiting yourself due to your biases.
ORM should definitely not be regarded as a technology that allows you to use SQL and RDBMS without knowing those technologies. ORM should be regarded as a technology that can make you much more efficient when using SQL and RDBMS, when you already have good knowledge on SQL and RDBMS. An ORM should help you with all the 'routine' SQL stuff. And a good ORM will support you using straight SQL whenever you want to do something that the ORM does not support well.
I love LINQ, but I've noticed that there are people that think they know LINQ, and then think they can use an ORM because of that. And in the end, they do not really know LINQ, they do not really know how an ORM works, and their code generates SQL queries that have really poor performance. And then the obvious conclusion is that ORMs are bad. Well.... no, they are not, they are very powerful tools, but it requires knowledge to use them well.
I'd be fine if it was a "show hn" post. But I thought this is an objective article about ORMs and their pros and cons; this is not the case.
That being said - the faq's on https://aihelperbot.com/ don't answer the main questions I have:
1) I saw a banner somewhere, can't find it now, which said "your schema is only stored in the browser". While that is nice, is my schema transmitted over the wire to your cloud service's servers?
2) If your servers get it, do you send my schema off to OpenAI's ChatGPT? Or some other AI as a service?
3) Do I own the intellectual property of all sql queries written by your tool?
4) How do you verify the "validness" of a query? For example, do you take my schema, load it into a small postgres/etc database and have your code run the query your AI generates? Do you parse/lint the query? Or is the AI good enough that it always generates valid sql?
5) I'm also interested in the "correctness" of a query, from the perspective of "user with the name of john", will the resulting sql returns results with name = "John", "john", " John ", "Johnny", etc - but I am fine with that being left up to the user.
6) Is snowflake/duckdb/clickhouse on your roadmap? Rather than replacing ORM's, helping business users write analytics/BI queries seems like a home run use-case.
My lessons are:
- you might not need an ORM, but it’s also fine if it’s the right tool. (Though I wonder if it should be inverted: you might not need SQL)
- I wish I learned more SQL just to know it better.
- don’t early optimize, but do read up on how you do optimizations with your ORM. That way you can identify when to get ahead of performance woes.
- set up a profiling system asap. For example, Django Silk.
In the case where you don't want to separate Database/API from your business logic, and you just want to use SQL to directly manipulate the database, then yeah, SQL seems fine. While a lot of things make me nervous about that prospect, it probably makes sense in certain contexts where the alternative feels far too heavy too soon.
One key problem I have with using an ORM that way is that the ORM gives you an opinionated representation of of the database schema. And the shape of your database is rarely the ideal shape of your data structures for use in business logic. I'll inevitably wish I had a view layer in the middle.
Some drawbacks is that it can cost you a few queries when you want to select related. Or you need to make a specific function for selecting the relation, but then have a different pydantic return type now with that value prefetched. So a bit more boilerplate, but no longer any surprises
I had a phase years back of chasing performance issues because your ORM does stuff like thousands of related queries for data you don’t actually need.
It is also a good idea to keep a sql profiler open when working with the application to see which queries are generated. When you do that regularly, you can often fix issues before they really become a problem. This is also useful even if you do not use an ORM. For example, an action might execute a loop with a query on the inside of the loop. It might be more performant to retrieve the data using one query and then loop over the results of that query.
So by using an ORM, you are (at least in the name, and as traditionally done) taking a better programming model (relational models) and mapping it to an inferior one (OOP) -- also getting a ton of leaky abstractions involved too.
I wish there were more "ORMs" that were made with the assumption that Object-Oriented Programming is BAD, and started out with assumptions of wanting to program either in an event sourcing model and/or a relational model, and see where that would take things.
Related:
Fred Brooks, The Mythical Man Month (1975): "Show me your flowchart and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowchart; it'll be obvious."
Out of the tar pit paper: https://curtclifton.net/papers/MoseleyMarks06a.pdf
--
My take on the software industry today is that OOP failed to produce good and maintainable code, but instead of fixing that, one is adding the idea of micro-services to try to contain the damage done by OOP.
If more people moved to event sourcing, functional code and focusing on data over code, there would be much less need for micro-services etc (or, they just become less relevant).
mind elaborating why OOPS is BAD and failure?
The main thing is that working with state is hard. "Objects" is often about representing something stateful and encourages thinking in terms of doing actions to state. It embraces a stateful world-view..
Functional programming I guess is the one popular alternative; but a lot of the benefits of functional programming can also be had in imperative languages, if one e.g. thinks in terms of event sourcing.
My definitions; If your object is named UserNameChanged, and you write code to process such events and so on, that is event sourcing. While if you structure your code to say that a "User" has a "Name" property, that is OOP.
Focusing on the events, state transitions etc as the first class thing gets you closer to a "stateless" programming model.
The Out of the tar pit paper I linked to describes the problems of working with state. And I think Event Sourcing is a more real-world, pragmatic approach to the same problems described in that paper.
E.g., an enrollment flow may be setting the user's name for one reason, and a database import job fetch the user's name for a second reason, and an internal support tool the set the user's name for a third reason.
In either case the job of the backend is to "set the name", to take an action. (Either by inserting a row to UserNameChangedInSupportTool, or by doing an UPDATE to User.Name, depending on whether you are doing event sourcing or not).
Typically the "set the name" is a very small detail in a larger context (enrollment flow; support tool use; import job). So in this example I would likely organize the code after those tasks done / problems solved.
My point is really that in these 3 functions I find it doesn't lead to cleaner code to have some sort of mirror (in principle out-of-date the moment you load it) of the "User object" in the memory, and modify it, and "persist" it
Just focus on what you need to do (like, "change the name of the user") and execute that towards the database from the Function you are performing. The User lives in the database, not in an object in your backend.
I.e., "update User set Name = @newName where Id = @uid and Name = @oldName" (action to change the user's name, if no-one raced us), instead of "load user", "set user name", "save user" (OOP, with an impedance mismatch because there is one version in your backend and another in the DB)
I still think that the object model does have utility, but like everything, when taken too the logical ends, you end up with ORM managed objects.
Instead I've always liked using a Service pattern, where you have data objects that are (largely) immutable. Then you have Service objects that contain the methods that operate on the data objects.
I'm just not sure I'd have called it a relational model (hence my confusion).
If your model is ORM OOP such as:
user := LoadUser(db, uid)
user.SetName("Alice")
user.Save()
I mean, just at the point you have loaded the user there is (I feel..) an impedance mismatch if you think about the "user" object as "the user", instead of simply "a query result of userInfo that may already be stale".
If you drop the ORM and just execute "update User set Name = @newName where Id = @uid and Name = @oldName" there is no impedance mismatch. Either you do the update or someone raced you and you did not. There is no stale data anywhere to worry about.
Perhaps OOP isn't the sole reason for the "fetch/modify/save" programming style, but I think the OOP way of thinking of the world quickly lead to that kind of solution approach.
A database supports "query" and "apply a transaction to move from one version of the data to the next version".
OOP-focused use of ORM thinks more about "loading" and "saving". That is where the impedance mismatch is I feel.
OOP => there is such things as "objects" => objects must be "loaded" and "saved" => mismatch.
If one doesn't need to fetch/modify/save, but instead things in terms of queries and transactions, then you also don't need to care about "nested structure" nearly as often. I don't use ORMs and it just doesn't come up that much -- why would I load the nested structures to the backend?..
Although I agree with what you say that there is a fundamental mismatch between the efficient way to store nested structures in backend vs DB.
Event sourcing is pretty complex; I'm hesitant to use it unless I have a clear and specific reason for it. It's not mutually exclusive with a ORM either; I've seen people use an ORM to query and even build their projections for example.
But I think perhaps that "event sourcing is pretty complex" may be due to programming languages and ORM having the object-oriented mindset as the "default" usage pattern. If we had 30 years of tooling evolution and education exposure for event sourcing I do not think it would be "complex".
(Also I mean event sourcing as in "how does one model data in the SQL database", not whether one is doing event-driven architectures with distribtion, async, event brokers etc -- that IS adding a lot of complexity but is something else..)
1. You have a table with records; to change something it's one "update record".
2. You have an append-only log of everything that happens, and a table of records which represents the current state after every log item is played back for efficient querying. To change something you do "insert log + update projection_record".
It seems to me the second item is fundamentally more complex, no matter what you do. You can abstract some of that away with good tooling, but things like migrations will probably forever be a right pain with event sourcing.
Sometimes all of that is worth it, but often it's not. That's probably why it's not the default way to write applications.
The thing about "update projection_record" is that any business logic in there can be done fully declarative/functional, making it easier to develop, reduce bugs, evolve it etc etc.
Sure if you actually have to program "insert log + update projection_record" it's more fragile and complex. But you are basically doing things the compiler+database should have been doing for you transparently if it was the kind of event sourced tooling I describe.
If you can just say in one part of the program "insert log"..
..and independently of that say "I need to know this to make a decision, it can be computed from the logs like this" -- and change those questions as you like, and it is the job of some combination of DSL/declarations/compiler/database to make sure that the right projections are maintained to efficiently evaluate the expression, then I don't think that is more complex.
This is the main idea in the Out of the Tar Pit paper I linked to. Also e.g. the Materialize database has some ideas like this (it can't be used in many situations, just an example of a database that has this kind of idea).
I mostly do work on the Python side of things and SQLAlchemy is the de facto ORM there. I hate it. It’s heavy. Opinionated. The happy path seems to be doing things in sessions where joins foreign keys and things are evaluated in the app instead of at the DB level (one can define relationships in models for the ORM to understand but not define explicit foreign key relationships at the DB table level, wtf? Thereby doing joins based on common columns…) and yet I can’t fault ORMs for their ability to get you going fast and help you iterate very quickly. I recently had the chance to start a new side project and trying to go the pure SQL route which I love was so slow in terms of productivity. When I could just model the tables via SQLAlchemy I was able to get to the meat of the thing I was making much quicker. What I didn’t like was all the additional cognitive overhead but I gained DB agnostic ways of querying my data and could use say SQLite for testing and then say Postgres for staging or production by just changing a config whereas if I write pure SQL I might get into issues where the dialects are different such that that flexibility is not there. In the end I am very conflicted. Oh, some context I began my professional life as a DBA and my first love was SQL. I like writing queries and optimizing them and knowing exactly what they’re doing etc.
With sqlc, you write queries and get fully typed functions you can call. It's not completely perfect, but it's pretty good.
As a concept, I think this is great. In practice, I've only been able to use it for Go projects using PostgreSQL so far. Definitely worth it though.
An orm has a specific job, handling the impedance mismatch between oo and relational paradigms. I almost never use an orm because I think it’s almost always better to avoid mapping objects to relational stores.
But! I’d almost never get rid of some of the other things data access abstractions can provide (eg security, typed sql, etc).
edit: Ok, I actually read the article... He briefly mentions the migration tool. Author: please go into more detail about how you do schema migrations.
Security - You don't need an ORM to prevent SQL injections. Plenty of libraries handle safe string SQL formatting.
Database design - I feel depending on an ORM for this instead of actually thinking about the solution would lead to just as many mistakes.
Proper Techniques - It is proper until it isn't I guess. Trying to get the ORM to do things it doesn't support usually leads to unexpected side effects.
Here's a recent error I found, and of course, this is a PHP issue, someone used if(empty()) and the value was a string "0" . This evaluated to true, causing a function, that builds up inserts from an associative array (functionally equivelant to say, eloquent's Model::create($data)->save()), to insert NULL instead of "0".
Now, we can say that's a php ism, that's just a bug or mistake, but I have found repeated issues like this, especially with legacy PHP code.
Going out the gate with something like Eloquent in Laravel, and showing people patterns, really does help. It helps isolate their mistakes, it gives them rails to ride on.
What does an ORM have to do with security?
Or database design?
Or proper techniques?
In reality, the ORM leads to poor DB design and even poorer techniques.
This may be true of the code-to-db type of mapping (where your table definitions are in code and then used to forward-generate the actual database tables) but there is also the possibility of generating an ORM from your database and its relations. I think the real benefit of an ORM is that it's reducing the amount of boilerplate code needed to be manually written in a project.
I agree with this, but when people think of an ORM they aren't thinking of sqlc, which I quite like.
The most-used ORMs perform square-peg-round-hole hammering to save the developer doing anything in SQL, they aren't simply saving the effort of writing the boilerplate (like sqlc).
How does an ORM help with that in any way? Are there any ORMs that will normalize your data model and write apropriate constraints? If so I _have_ been under a rock.
[1] https://github.com/koskimas/kysely [2] https://github.com/jawj/zapatos
... and if it goes to hell, it's just sqla-core under the hood, so we're all good!
An ORM is an abstraction over what already is a huge abstraction, which is SQL. Therefore it would feel to me like driving a bus by remote control, or something like that.
SQL isn't a procedural language, like C, Fortran, Cobol, Java, JavaScript, Python, Ruby, etc. It is a "query language", I guess, where you tell it what you want, and it decides how it's actually going to find, filter, sort, etc., that data. It's also the only game in town. Is there any widespread alternative to SQL, at least for querying tabular data?
The problem with it being so abstract doesn't rear its head with simple SELECT statements. They all seem to go fast enough. It isn't until you're joining tables together, or no longer getting data row for row but instead aggregating, summing, averaging, etc. Suddenly sometimes the whole thing can slow to a crawl.
Thankfully databases like Postgres let you prepend a command called EXPLAIN to the problematic query, so that you can diagnose the slowness (if you can understand the output of EXPLAIN). But it was a long time before I got good at reading the EXPLAIN output and finding a way to get it to run faster. Even though SQL is so abstract, more just a description of what you want, there is more than one way to write it --- and the difference in speed can be hundreds or thousands of times.
I have had the luxury of working mainly with Postgres for 17 years, and toward the end of it I finally feel supremely confident working directly with it, just using the psql command-line client, and hand-typed SQL in text files, to get exactly what I want, and as fast as I want it (which usually is less than a second). Mind you, I don't work with Big Data, just a variety of CRUD apps but they often have mindbending reports requirements.
It should not have taken me years and years to master SQL (nor should it you). I think the reason is partly distraction, for those of us that are "full-stack developers", and also that there is so little education about how to master SQL. I mean, the books and posts are out there, but the attention to them from the community at large is small.
But SQL is really not harder than the other languages and techniques you know so well. If developers spent as much time on SQL as they do on the ins and outs of React, containerization, CI/CD pipelines, or the quirks of their favorite programming language, I think they would find it easier than most of those things. And it pays rich dividends, because SQL is cross-platform, you can carry that knowledge with you from job to job for the rest of your life, and your inefficient database queries are making your app use quadruple the hardware and 10 times the latency than if you truly mastered SQL.
Well said! Even if you use an ORM, you definitely should still learn SQL.