What I have learned over the years is that the only way to properly use ORM is as a fancy query tool. Build the query, fetch/update data, MOVE THE DATA to separate business objects. Don't leave ORM entities shared across the sea of objects!
Phew, thanks, I got that off my chest.
its sql in elixir syntax with a bunch of QOL improvements.
for one thing, I can seperate my subqueries into separate variables
``` sub_q = from(l in Like) |> where([l], l.user_id == ^user_id) |> select([l], %{ user_id: l.user_id, likes_count: count(l.id) }) |> group_by([l], l.user_id)
main_query = from(u in User) |> join(:left, [u], likes_count in ^subquery(sub_q), on: likes_count.user_id == u.id, as: :likes_count) |> select([u, likes_count: l], %{ name: u.name, likes: l.likes_count, }) |> where([u], u.id == ^user_id)
user = main_query |> Repo.one()
```
Being able to think directly in sql lets you perform optimal queries once you understand sql. and imho, this much cleaner than tha equivalen sql to write. it also takes care of input sanatization and bindings.
Then I learned Ecto/Phoenix and this is truly the best way. Ecto is so close and translateable to raw SQL that there's little to no friction added by it, but it handles all the stuff you don't want to have to do by hand (like query building, parameterization, etc). Ecto is a real breath of fresh air and I find myself writing quick scripts that hit a database in Elixir just so I can use Ecto! I also love how easy Ecto makes it to model database tables that were originally defined by another language/framework or even by hand. Trying to do that with ActiveRecord or another ORM is usually a recipe for extreme pain, but with Ecto it's so easy.
It’s so elegant and the Lego blocks (query, schema, change set, repo) can be mixed and matched in different ways.
I’ve even used schemas and change sets to validate API requests and trivially provide very nice, clean, and specific errors, while getting perfectly typed structs when things are validated.
[edit] for instance, I have a case where I use a few dozen custom queries on timers to trawl through massive live data and reduce it into a separate analytics DB. Using everything from window functions to cron timers to janky PHP code that just slams results from separate DBs together to provide relatively accurate real-time results. At the end from that drastically reduced set in the analytics DB... sure, I'm happy to let the client summarize whatever they want with Metabase. But those things just couldn't be done with an ORM, and why would I want to?
This is compatible with the OC suggestion of using ORMs as a "fancy query builder" and nothing more, which I strongly support.
(I would say that it is conceptually perverse for a client of a system to have authority over it. Specifically, for a database client to define its schema.)
This is where migrations also become sort of annoying. Because if you use them. Then it is harder to fix the mistakes since you can’t just change your DB without using the ORM or you’ll typically break your migration stream or at least run into a lot of troubles with it.
And what is the plus side of having a code-first DB really? You can fairly easily store those “alter table” changes as you go along and have full availability of history in a very readable way that anyone, including people not using C#, Java, Python.
Which is the other issue with ORMs. If you have multiple consumers of your data. Then an ORM most likely won’t consider that as it alters your “models”.
For a lot of projects this is a non-issue, especially at first. Then 10 years down the line, it becomes a full blown nightmare and you eventually stop using the ORM. After spending a lot of resources cleaning up your technical debt.
The benefits should be obvious if you've used ORMs. They are an object that represents your database data in code rather than in a table where you can't touch it. If you have code that brings data from a database into code, congratulations, you've implemented part of an ORM. Having the data model defined "in code" treats the code as first-class instead of the SQL, which makes sense from an ergonomics perspective, you will spend much more time with the code objects than you will the SQL schemas. Either way, you will have two versions: a SQL version and a code version. You might as well get both from writing one.
If you can read alter table in SQL, you can probably read migrations.AddField in Python, and whatever the equivalent is in the other languages. I still am waiting with bated breath for the problems with much maligned (by some) ORMs to arrive.
I’m not really against ORMs as such. I’m not a fan of code-first databases for anything serious, but as far as CRUD operations goes I don’t see why you wouldn’t use an ORM until it fails you, which it won’t in most cases, and in those cases where it does… well similar to what I said earlier you just wouldn’t have build it to scale from the beginning anyway, and if you had and it turned out it didn’t need to scale then you probably wasted a lot of developer resources to do so.
- Proper DB design first. You should be able to remove the ORM and DB should still function as intended. This means application-side cascade operations or application-side inheritance is banned.
- No entities with magical collections pointing to each other. In other words, no n to n relations handled by ORM layer. Create in-between table, for gods sake. Otherwise it becomes incredibly confusing and barely maintainable.
- Prefer fetching data in a way that does not populate collections. In other words, fetch the most fine-grained entity and join related data. Best if you craft special record entities to fetch data into (easy with EF or Doctrine).
- Most ORMs allow you to inspect what kind of queries you create. Use it as query building tool. Inspect queries often, don't do insane join chains and other silly stuff.
I would use ORM in one kind of app: where I would work with data that shares records that might need to be either inserted or updated, and there is several nesting levels of this kind of fun. You know, you need to either insert or update entity, if it exists, you should update, and then assign related entities to it, if it does not, then you should insert, and assign related entities to the newly created id. The ORM can easily deal with that, and on top of that it can do efficient batched queries, which would be really annoying and error-prone to hand-craft.
If the app does not require this kind of database with these kind of relations, I would not use ORM.
So, I have a database that looks like this. My method was to lay out the database myself, by hand, and then use EF's facility to generate EF code from an existing database. The bridge table was recognized as being nothing but the embodiment of a many-to-many relation and the EF code autogenerated the collections you don't like.
Is this a problem? If you do things the other way around, the ORM creates the same table and it's still there in the database. It isn't possible not to create the bridge table. Why is that case different?
I just fail to see what else would you do, besides implementing a bug-ridden, half-ORM yourself.
1. Very often we need generated SQL because writing SQL for primitive CRUD operations is hell tedious and error-prone (as well as writing UI forms connected to these CRUD endpoints, so I prefer to generate them too).
2. Structured Query Language being very poorly structured is indeed a huge resource drain when developing and maintaining complex queries. PRQL and the like try to address this, but that's an entirely different level of abstraction.
3. Unfortunately, when efficiency matters we have to resort to writing hand-optimized SQL. And this usually happens exactly when we terribly need a well-composing query language.
But that comes to the second problem. Most devs I meet seem to be deathly allergic to SQL. :)
One project I had a dev come to me asking me to look at a bug in the thing. Having never seen that particular ORM before I was able to diagnose what was wrong. Because MS ORMs have the same issues over and over (going back to the 90s). You better read those docs! Because whatever they did in this stack will be in their next one when they abandon it in place 3 years from now.
Depends on the ORM.
I have noticed that typically, 'unit of work' type ORMs (EFCore and Hibernate/NHibernate as examples) prevent being 'true to the ORM' but 'efficient'.
i.e. Hibernate and EFCore (pre 7 or 8.0ish) cannot do a 'single pass update'. You have to first pull the entities in, and it does a per-entity-id update statement.
> I just fail to see what else would you do, besides implementing a bug-ridden, half-ORM yourself.
Eh, you can do 'basic' active-record style builders on top of dapper as an afternoon kata, if you keep feature set simple, shouldn't have bugs.
That said, I prefer micro-ORMs that at most provide a DSL for the SQL layer. less surprises and more concise code.
Traditionally, though, SQL generation was known as query building. The query was executed via database engine or database driver, depending on the particulars. ORM, as the name originally implied, was the step that converted the relations into structured objects (and vice versa). So, yes, technically if you maintain your data as tuples end to end you are not utilizing ORM. Lastly, there once was what was known as the active record pattern that tried to combine all of these distinct features into some kind of unified feature set.
But we're in a new age. Tradition has gone out the window. Computing terms have no consistency to speak of, and not just when it comes to databases. Indeed, most people will call any kind of database-related code ORM these days. It's just funny that ORM no longer means object-relational mapping.
Object-relational mapping was originally coined in the Smalltalk world, so objects were in front of mind, but it was really about type conversion. I am not sure that functions or methods are significant. It may be reasonable to say that a struct is an object, for all intents and purposes.
A pendant might say that what flimsy definition Kay did give for object-oriented programming was just a laundry list of Smalltalk features, meaning that Smalltalk is (probably) the only object-oriented language out there, and therefore ORM can only exist within the Smalltalk ecosystem. But I'm not sure tradition ever latched onto that, perhaps in large part because Kay didn't do a good job of articulating himself.
ORMs create this false equivalence (and in this sense, so does Django's admin interface despite using tuples instead of classes). I can see the sense of this, vaguely, for an admin interface, but it's still a false equivalence.
Simple: because if I don't, I'm going to spend the rest of my career explaining why I didn't to people extremely skeptical of that decision. Meanwhile even people like me tend to just shrug and quietly go "oh, an ORM? Well, that's the price of doing the job."
Also, ORMs are an endless source of well-paid jobs for people who actually learned relational algebra at some point in their lives, and that's not a compliment to ORMs.
On top of that most ORMs have migrations, connection management, transaction management, schema management and type-generation built-in.
Some ORMs have inherently bad design choices though, like lazy loading or implicit transaction sharing between different parts of the code. Most modern ORMs don't really have that stuff anymore.
But in .Net, EF is still the most common way of accessing data (I have heard, because I stopped using it over a decade ago).
I hate EF and everything it stands for. :)
As an alternative to query-by-example (QBE)?