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”.
Last I heard the "yes" and "no" factions formed an alliance to fight against their common enemy, the "it depends" faction. I expect that sooner or later one of the factions will barbarossa the other though.
This is pretty much the idea behind sqlc, with some extra steps of course. You write your SQL queries in file, and it generates type safe bolierplate for you.
I've found that the problem with not using an ORM is that you can end up writing a lot of repetitive CRUD queries. Every model ends up with the same basic read, insert, update, etc. function, but because you're writing untested, un-typechecked SQL queries, they still need to be carefully reviewed, tested, etc. You can write some helper functions to abstract over the repetitive parts, but then you start creeping into home-grown ORM territory, which is much worse than using a standard battle-tested ORM. If I'm using a ORM, I can be pretty confident that the method to read my model using an ID is going to work without fuss.
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.
So much of workaday SQL stuff is handled nicely by ORMs it's hard to pass on the convenience.
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.
At a deeper level ORMs exist because SQL isn't what people want most of the time.
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.
>At a deeper level ORMs exist because SQL isn't what people want most of the time.
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.
I think SQL is good at what it was meant to do back in the 1980s. Kind of the same way C was for systems programming, and is still good for many things today.
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.
No. Classical fallacy: not using an ORM does NOT mean writing raw SQL strings. You can still use a library that helps will all the SQL specifics and even checks your schema for you at compile-time and help you abstract over common logic/operations.
This. In golang gorm has been nicely covering this area. It still feels like sql so you don't start abusing it as a nosql store but has good abstractions to apply common operations across several models instead of depending on too much magic!
No, usually linq is used in combination with an OEM (e.g. entity Framework) to avoid writing raw sql. From the parent comment, I thought he meant something more like sqlx for rust
Plus some ORMs have nice schema migration tools. Engineers should still verify what the auto-generated migration is doing, but that's much better than writing the migration by hand
Well, even when you use ORM, you still need to write tests to check that you use it correctly: e.g., you didn't omit any needed fields, excluded all unneeded fields, etc.
After working with ORMs for a long time, all I really want is a nice API for building queries (that actually supports all underlying database features) and automatic mapping of the results to whatever objects/structs and primitives the language supports.
Everything else is IMO a bad layer of abstraction that will eventually bite you when you need to get closer to SQL.
> all I really want is a nice API for building queries (that actually supports all underlying database features) and automatic mapping of the results to whatever objects/structs and primitives the language supports.
The only part of ORMs I liked was the ability to dynamically build queries. You’re right that a query would have to return the same columns (for common downstream processing), but a query builder can make adding conditions much easier.
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.
A query builder is much more flexible. For example, in some cases there might be an additional condition you want to add to the WHERE clause. You can't do that with parameterized queries, so you'd have to make a new query for every possible variation. That may be fine in some cases, but it could become a maintenance headache in large project.
Yeah I built one of these for .NET specifically for this, with DB-specific drivers to extend the API, especially for Postgres. It just tries to be a dead simple representation of the query, then provides a command API to do mapping and execution, some unit of work support. Really happy with it so far, especially compared to an ORM.
It has separate, stackable layers for query building, data mapping, ORM, and ActiveRecord-ish declarative models. You can pick the parts you want and ignore the rest.
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.
That's true. I guess what I have in mind are ORMs like Active Record, which adds all sorts of magical layers on top the query building, but still somehow gets the some of the basics wrong, like being able to select arbitrary values easily, or making OR queries an intuitive mess.
Post focuses on the "easy" part (building queries), but leaves as an exercise to the reader how to do the M in ORM: so, once I submit my SQL query and get one or more rows: how do I map them into my (potential) complex set of objects? Sure thing, I can do that manually (it's easy), but that's the whole point of using ORMs: to not to have to do it manually.
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.
I’m inclined to agree. The article brushes over the type-safety provided by Prisma as being merely a ‘useful feature’ — but this is huge, especially working on a large project with lots of other developers. The discipline imposed by strict types is indispensable to my mind, how ever much I agree with the author that writing SQL directly feels good.
That's one thing I like about Rail's ActiveRecord: it covers the most common queries with its own DSL, but also makes it easy to insert snippets of plain SQL while still returning ActiveRecord models.
The mapping part is definitely the part of an ORM I like the most. I like both (although unrelated from my understanding) sqlx for Go and sqlx for Rust, which both handle the mapping problem given raw SQL.
I find that the best of both worlds is to use something like sqlx for rust [1], which does compile time checking (using postgres explain analyze!) of the queries and macros for static typing, but still let you write the query raw (and can be turned off completely by removing the trailing macro `!` as an escape hatch)
We use it extensively for Windmill (e.g: [2]) and couldn't be happier.
Maybe I'm holding it wrong, but my experience with sqlx is a bit more lukewarm. It's really great that compilation success means that you didn't fuck up any queries, but... there's just SO much boilerplate. I find that due to Rust's extremely strict type system, I have to create dedicated record structs for all of my domain models representing how a model is stored in the database. So once everything's up and running it almost always implies there's essentially no bugs, but man is getting there a drag.
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.
I mainly checked out sqlx as it seemed like compile-time checked queries gave some of the advantages of sqlc in Go. However, I was a bit disappointed. Compile time checking requires you to connect to a database at compile time, with the schema already set up. Sqlc doesn't even require this at codegen time in Go, at the cost of needing to emulate some of the underlying database behaviors. I'm sure it's not a deal breaker to everyone, but I find the premise of needing a database to make queries against for every single build to be way, way, way too much baggage.
The idea of checking the queries against a schema is great. Even better if you can use it to infer arguments and return types.
I can see where this would be inconvenient. I write the migrations first so it doesn't bother me, but for prototyping it sure seems cooler to not even require the schema to exist.
The reasons you want an ORM or query builder: Reuseability and composeability. In Django for example, you can compose querysets and reuse them in Forms, Admin, Templates, wherever. You can also generate Forms and Admin out of nothing but the model class.
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.
At work, I’ve been using Django exclusively for almost a decade. I’ve just taken note of how much of a funk I’m in and have been branching out, especially in the JS space. I’m amazed at how far ahead Django’s ORM is. Hell, I’m amazed at how far ahead 5-years-ago Django’s ORM is. I’ve been enjoying playing with some of the typed JS ORMs / query builders out there. I’m certainly more skeptical of ORMs than I’ve been in previous years, but if an ORM is what you want, I have an increased appreciation of Django’s execution.
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.
I am forced to use dotnet at work after having done Django for a long time. In ASP.NET with CQRS Pattern I need to have about three classes for every endpoint, while with DRF I can get 5 endpoints with about 3 classes including the model and that's about 80-90% of what I'll ever need of a resource. DRF allows you to infer what a sensible API would look like and lets you override pretty much everything on top of that.
You can achieve this without an ORM, but having composable query bits and abstractable querying to the programming language and not directly SQL is really nice. Objects map fine to relational hierarchies so I am not so worried about design constraint.
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.
I would not roll it myself. That was my point about some ORM frameworks offering a middle ground, which is often appealing when adapting to an existing database schema.
This is why Language Integrated Query (LINQ) is so powerful in .NET. You never need to learn any ORM specific query language or syntax since you've probably learned to use LINQ with objects already and then you just continue to use it with a database.
True in the sense that you do not need to learn anything extra to build queries using an ORM. But it is not the complete picture: whenever you use an ORM, you should really know how the ORM works and what it does. For example, even if you use LINQ, you have to realize that the expressions you use in the C# code cannot always be translated to SQL. So, you need to know the ORM and its limitations to be aware of what is possible and what is not. An ORM cannot magically translate the code behind a derived property into SQL.
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.
Yes, and LINQPad lets you write very powerful ad-hoc queries with results that are significantly easier to navigate. It's much better than using SQL management studio to view your database.
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.
Please elaborate on the limitations and paths! I'm doing technology selection research and EF Core is currently on the table, would love to hear what you encountered.
One of the most significant for our use case is that composite primary keys do not work with inheritance, which makes it impossible to setup constrained polymorphic relationships.
In general, if you use your database domain (classes) in your business logic, you will have pitfalls when working with your database. This either leads to the N+1 problem (when you use lazy loading), or data structures where a relationship will be null when you try to traverse it.
(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.
I'd be really interested to know some recommended methods of realizing having separate db and domain classes. I'm actually in the early phases of a project where I have been using the Memento pattern to save and restore domain entities. I'm about ready to rip it out and switch back to persisting the domain classes directly in the db because it's so tedious. (using EF Core 7 and C#)
This feels like an ad. The author created a tool that makes it easy to ditch ORMs. The tool is referenced 10 times in the article, including the article's first sentence and the last sentence. The article also includes examples on how to use the tool.
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.
I agree that this blog post is part "orm" and part "lead gen for my saas app which helps you write sql queries". I think a blog title which reflects this, ORM and AiHelperBot, would be more helpful.
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.
I’ve done about ten years of multi-hat development without knowing SQL beyond the basics because an ORM fits my use case well: CRUD of a few million records across a dozen tables utilized internally only be a few hundred humans and thousands of robots, the entire backend written and maintained by 5% of one engineer.
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.
Learning SQL is a software dev superpower at the moment. So many devs don't know SQL and it causes such pain. But mostly invisible pain because if you don't know SQL then you don't understand the horrible things your ORM is doing.
Exactly! Devs should learn SQL and should learn how their ORM works, what it is doing "behind the scenes".
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.
I don't mind an ORM per se, what I don't like is when the ORM bleeds into the whole system (like in Django). If there is an ORM between the database layer or not doesn't matter, but if you're passing fat objects around where touching any property accidentally does a query, or it's impossible to change a table because the details have been solidified in code all over the place, it's not very nice.
For me it would be a red flag if the ORM objects exist in any context outside the views and serializers. It would suggest that I'm mixing business logic and database/API logic.
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.
This approach is definitely swimming against the tide with Django. I’ve spent a lot of time with Django, and a lot of time trying to work out how to best build complex systems with it. I don’t think that for Django’s case the answer is anything close to being as simple as “don’t let the models go anywhere”, unfortunately. Though I am really intrigued to try it and see how it goes.
I've in some apps tried to use a pattern where only certain files are allowed to touch the model classes (selectors.py, services.py). Something queried from the selector is mapped to a dumb pydantic object before being returned. To change something you need to call a function in services, can no longer modify a value on the object and call save.
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
This. There is no easy way to track down performance killers anymore, and someone will inevitably tank performance with a "this change shouldn't have affected that query" change. Huge problem in every Rails codebase I've worked in over the years, and it's not like it's anyone's fault the framework encourages this.
The main problem with ORM is the "object" part. I feel object-oriented programming had its chance and it failed.
Both event sourcing and relational models (="table modelling") are better models for programming systems.
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."
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).
I think the "O" in ORM doesn't really share all that much relation to OOP. Many can be called TRM ("Type-relational mapping") as well. "Object" is one of those words that's so overloaded with different meanings I try to avoid using it.
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.
I don't disagree with anything of what you say really.
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.
When I said development of tooling of 30 years, I didn't mean a bunch of scripts or libraries, it would include things such as databases and programming languages. Certainly things like automating database migrations is things can be fixed in that timespan on tooling -- and probably WILL be fixed in coming 30 years.
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).
You mention the issue being with OOP, but wouldn't you run into the same issue with any nested structure? I think the "impedance mismatch" as it's called is that in programming languages parent elements point to their children, but in relational models, it's the children that point back to their parent (foreign keys).
Hmn. Good point. I feel a more important impedance mismatch is one of transactions/concurrency.
If your model is ORM OOP such as:
user := LoadUser(db, uid)
user.SetName("Alice")
user.Save()
the impedance mismatch is really that the "user" you have in the memory in the backend actually isn't the real user. After doing SetName() it isn't committed to the database, other processes will still see the old name and so on.
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.
I’m curious - how do you use “relational models” as a programming model? I get it for data storage and retrieval, but you eventually also need functions/methods to operate on that data. So, how would you organize that code… relationally?
I just find that when organizing code it makes most sense to organize it after what it does. Example:
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)
Okay, I think I get what you're trying to say now. I hate... hate... hate... ORMs where you alter objects and then let the ORM magically persist the changes to the database. That is just a level of abstraction that I'm not comfortable with. I think it pushes the developer too far away from the data layer.
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).
I guess I worded things a bit sloppily, OOP can mean a lot of different things too..
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.
Cross posted from a discussion on a different post but I thought it was relevant here as well:
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.
In the olden days we’d distinguish between an ORM and other kinds of data access abstractions.
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).
From my experience ORMs are very good for fast MVPs and getting something done, but then quite some effort to scale (= replace with hand crafted SQL). Also when scaling, transaction problems show up b/c you misunderstood the ORM and it was not an issue with 100 users/day, but it is now with 10.000 users/day.
I've never fully trusted ORMs (building mostly multi-tenant apps where I want to enforce the tenant ID in every table) and the other issue is that my ORM of choice, Doctrine, has abandoned its API which is forcing re-writes of a decade of code. Would have been better with my own abstraction.
I'm a current ORM user, specifically Prisma. And it's nice that it gives me type checking but the CRUD actions and the programmatic interface are not the real reason I use it. I use it as a tool for declarative schema management, handling schema version control and migrations. If someone has a better solution to this that does not involve xml, I'm all ears. I'd personally much rather use https://pgtyped.vercel.app/ than prisma.
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.
ORMs do not exist because good developers can't write SQL, they exist to prevent Jr developers, offshore experts and general buffonery running amuck with security issues, database design and proper techniques.
Hm - I've found that the security and performance issues are worse with ORMs than with direct SQL because the developers don't understand (and are actually separated from) what's actually going on.
Performance, maybe. Security…I strongly disagree. The 2000s taught us what happens when you don’t make it really really really hard to ‘accidentally’ not parametrise your queries. You don’t need an ORM to accomplish that goal, sure, but every ORM I’ve used will stop you from doing that, pretty much inherently. My experience is that any half decent ORM’s query API will be at least as readable as SQL when it comes to looking for unintentional data access/manipulation.
ORM have their on inherent security issues. Accidentally bringing back all related tables to the user because of referential integrity settings, for example.
I won't argue why they exist but I would argue that they help with security, database design, or proper techniques.
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.
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.
The first part is correct, but if you do it properly (translate a proper db design into ORM) it's fine.
You can just as well design a catastrophic data model with pure sql.
>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.
> 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).
Kysely[1] and zapatos[2] are excellent solutions for type-safe typescript query builders. It’s hard to go back to the days of spending 20-30% of your time in the object mapping layer.
I've been using Vert.x a lot and one of the great things is just throwing away the whole Spring magic and going back to basics with a sql query and processing the results without an ORM. Basically ignoring all the Java cruft that gives Java its bad reputation. Its so refreshing I love it.
I’ve built a product doing exactly that. That being said, the Vert.x path has some bumps along the way, but boy, oh boy, I’m so happy I’m not doing any Spring related stuff.
ORM until you don't need to, then SQL. IF your green fielding. If you already have the SQL, just work with it. If all you're doing is building Crud, though maybe consider Hasura or something that gives you a performant API instead.
Android bolted views and updates directly to the SQLite database classes. It was an elegant design for an OS meant to run (at first) on 200Mhz small memory configurations. It didn't need an ORM and it enabled apps to persist data asap.
Yeah, now imagine a system at the scale of Gitlab, it's essentially unmanageable without an ORM, which helps a big time figuring out issues, types, migrating schemas, rewriting and so on. Messing with SQL is suitable for manual query optimization or the simplest databases.
If you're happy with your ORM (and, ahem, your users are happy with your app's speed), then you can disregard this post.
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.
> 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.
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!