You're coming at it from a slightly wrong angle. You are completely right that for queries, there's really no gain, you just end up having to learn both SQL and whatever your ORMs DSL is.
Where ORMs are useful is once you have your objects. The usefulness of an ORM is being able to say:
Also being able to work on your data in objects or structure native to your programming language means that you can leverage both the strengths of the language and the database server. Some operations are much simpler to do in Python or C#, compared to SQL. In other scenarios you really need to let the database do its thing. Again, you do have a point, because believing that you can skip learning SQL and just rely on the ORM will get you into trouble.
One other feature of ORMs is that they allow you to write code that will run on multiple databases, but at the cost of not being able to use the more advanced database features. For many CRUD applications that is a price worth paying though.
Sure you have to write the updateGroups method and use SQL to do it, but that's trival. On the otherhand when you want to do something more complex this is when ORM's inject all sorts of subtle and dangerous bugs into your code base.
For one, you're creating a hard coupling to a specific flavor of SQL. And that's not too mention the fact that you're taking an otherwise purely data object and embedding persistence logic into it - a horrifying abuse of OO.
> you're creating a hard coupling to a specific flavor of SQL
You're just trading one coupling (specific flavor of SQL) to another (your ORM.)
Assuming your application is layered correctly, when you write your own queries, all of your SQL queries are in a single place and can be updated.
BUT: If you're using an ORM, and you let your data bound objects leak into all layers, the coupling is much much much harder to fix if you chose to change your ORM. IE, if you do things like lazy loading, or construct your queries in business logic, switching ORMs will be extremely painful.
I've done it both ways (write my own SQL and use an ORM) and I would say the single biggest mistake is to assume that you absolutely should (or shouldn't) use an ORM.
Not at all - in most architectures, your data objects know nothing about how they are being stored, instead delegating that to a repository class or something similar.
Adding persistence logic to a data object adds all kinds of bloat - it has to have a connection to the database, which now makes unit testing a pain in the neck and introduces all kinds of weirdness around serialization. Now it has a bunch of CRUD methods, so developers have to know which methods are for business logic versus persistence. Also, data objects with persistence logic aren't really suited to be published in a shared library as clients should not access your database directly.
> Adding persistence logic to a data object adds all kinds of bloat
That's not what you do if you write your own queries. That's what a novice does after learning object oriented programming.
For a more accurate example, look at things like Hibernate / NHibernate / Entity Framework and lazy loading. They inherit from classes at runtime and will transparently run queries as business logic navigates relationships on an object graph. It's "not wrong," but it can lead to all kinds of problems and painful refactors. (But it's totally "worth it" in prototypes, one-offs, and throwaway code.)
If you don't do lazy loading, Entity Framework still requires that you pre-declare which relationships you will traverse. It's intended that your business logic uses Entity Framework APIs to say what part of the object graph it will use. (Thus tightly coupling your application to your ORM, which means your trading tightly coupling to a database to tightly coupling to a framework / library.)
But, keep in mind that the ORMs I mentioned tie the objects to the database connection. It isn't quite as intense as "Adding persistence logic to a data object", but they do track that, if you modify an object, it can only be saved on the transaction that it was loaded from; or if you're using lazy loading, lazy loading only works if the transaction is still open.
If you build a layer around your data access code that fully shields your business logic from your ORM, you've "done it right." But, at that point your ORM's value becomes negligible, because from the perspective of your business logic, it doesn't matter if your data access layer has hand-optimized SQL or an ORM; but you've lost one of the real selling points of an ORM, which is that you can easily do your data access from within your business logic. Which is why I say that the biggest mistake is assuming you should, or shouldn't, use an ORM.
Three things - first, it wasn't my example, I'm just responding to the terrible looking code in your previous comment.
Second, you just tried to argue for manually writing your own SQL rather than using an ORM by suggesting the way NHibernate (an ORM) does it is the right one. Might want to think up a new argument.
Finally, I used NHibernate in the past but it's gone out of favor because of the problematic things it does. Those proxies have to be runtime generated (startup cost + some extra overhead for each query) and are terrible for your data model. You have to mark properties virtual and suddenly GetType doesn't return the expected value. State tracking is actually not a great idea - causes many people to do dumb things like querying just to perform an update.
Entity Framework with state tracking turned off is better all around.
> you're creating a hard coupling to a specific flavor of SQL.
I fail to see why this is a problem. Switching databases is a costly move, and is pretty rare as far as I know. When it does happen, it is usually from one type of db to another type, not between two RDBMSs.
IMO it doesn't, by itself, justify sticking to an ORM rather than raw SQL.
Switching databases can be an easy move depending on how you design schemas, versioning and migration. We have a multi-cloud, multi-db offering - customers choose their DB. Without ORM, this would not be feasible.
Many/most ORMs (the ones that follow the ActiveRecord pattern) do this as well though. I prefer to avoid mixing concerns and use datamapper-based ORMs myself, but what GP wrote is fundamentally not that different than what a lot of ORMs do.
I'd dispute "most", as it does not match my experience across a number of languages. Yes, active record sorta does it but, as best I recall, those methods are all implemented generically as mixins, so not actually a part of the data object. This seems to be advocating for data specific queries to be added to each class.
Ummm... No. See my previous comment - at least in Rails, the queries aren't part of the data object themselves, but are instead mixins or default handlers (don't recall which offhand), so it's really just syntactic sugar that makes it appear as though they are methods on the data class.
Even if they did the same thing you're suggesting, by that logic, we should all use PHP because WordPress does, and it's probably more popular than those two combined.
Take a look at something like Spring or Entity Framework to see other takes on ORM patterns.
And again, you're trying to argue against ORMs, while citing that your suggested alternative is supported by the way ORMs do it.
I’m not the person that suggested handwriting object methods. As I’ve said, I agree with the point you’re making, I’m not arguing with you, or for any specific ORM design.
I don’t see what the nuance is you’re getting at with the mixin vs object method argument though. They both have the same issues around testability and violation of SRP.
Ahh, apologies misread the tree. In terms of the nuance around a mixin vs object - a mixin can be defined and tested in its own separate library independent of your data model, so it's at least cleaner in terms of separation of concerns.
That’s fair. My issue with the mixin approach is that then your app code can hit the database from anywhere you have access to the model object, which makes testing your own code in isolation much more difficult
I mostly agree. I think sql's achilles heel in this regard is where prepared statement parameters are needed but aren't supported, or need a better representation
- during bulk inserts, having to generate a list of values. It'd be wonderful to be able to just supply a single `?`, or use some other symbol to note that it's a value list. Making the user generate a bunch of (?,?),(?,?)... is not at all friendly, and something everyone has to do. and the cherry on top is that there can't be a dangling comma at the end, so it's gotta be chopped off, or omitted.
Not at all a hard problem. It's an annoying problem that I don't understand why it hasn't been solved at the prepared statement level.
- things like database, table, or column identifiers that may be variable based on application context
Basically anywhere that currently winds up getting interpolated should have a way to be parameterized.
without those two, i think it's inevitable to arrive at one of:
- an orm
- a sql query template renderer
- a bunch of functions to do very specific string interpolations
insert into your_table(id, created_at, uri, project_id)
select id
, created_at
, endpoint as uri
, project_id
from jsonb_to_recordset($1)
as x(
id uuid
, created_at timestamptz
, endpoint text
, project_id uuid
)
Absolutely agree with this, particularly the (?,?,?,...) issue. SQL has a lot of little pain points, but generally ORMs feel like they throw the baby out with the bath water.
ORM is orthogonal to SQL. The purpose of ORM is to transform relations (sets of tuples) to object graphs and back again. ORM toolkits provide some kind of declaration method to describe how that mapping should occur to save you the slog of doing it by hand.
SQL is the usual mode for receiving those relations, and so many ORM toolkits also include query builders to help with that level of abstraction, but theoretically an ORM toolkit could require you to write SQL in the raw.
it is so refreshing to see the correct answer stated so succinctly, even though it's buried in the middle of yet another one of these "duh, ORMs suck, write raw SQL" threads (isn't everyone here bored of these discussions yet?). congrats on being one of so very few who gets it.
You would really like sqldelight[1] then. It takes the concept of an ORM and flips it on its head. Instead of mapping function calls to SQL statements, it lets you write SQL statements and then generates classes for you that have methods for those statements.
For instance, you could have a SQL statement like getCardsForFight: select * from fights where cardId = ? and titleFight = ?, and it would generate a class that has a method getCardsForFight(cardId: number, titleFight: number).
My understanding from reading the page was that flyweight translates trivial table access to SQL. For anything beyond that is seems to do exactly what you describe i.e. you write the query and it is creating a TypeScript API for the query. Which saves you the work of defining the interface and ensures some level of correctness of the mapping.
They are indispensable when you need to build queries dynamically. Suppose you have some sort of list UI where users can query for data and filter down on rows they are interested in. They can filter on one of a dozen different columns in any combination. They can also sort on whatever column they want. There's no way you can implement this without building the SQL select statement dynamically, adding to the WHERE clause based on what the user wants to filter things. So you either start appending strings or you reach for an ORM.
I agree that often the full ORM isn't very helpful, I prefer something that's more of a light layer over SQL to enable dynamic query building and that's it.
Having worked with a decade+ old, hand-engineered, framework-less, codebase, for a profitable international business, I'd say they are anything but indispensable.
They are very nice to have, and I'll implement one the first chance I get, but "indispensable" is a stretch, IMO
It's not as if you're expected to use ORMs to do ad hoc querying of a database. Just fire up a db connection and start writing sql.
If you work on a large application with lots of transactional processing, it makes sense. I've worked on apps with 10's of 1000's of lines of Ibatis scripts. And on applications where you're expected to just invoke stored procedures for every little operation. I'll take Hibernate over that situation any day.
People really overstate the pitfalls of ORM's. If you're running reports against a data warehouse, don't use an ORM. If you are trying to optimize a complex query, don't use an ORM. For an app that is write heavy, with very many simple updates to a complicated object graph, it makes sense.
> Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL
I'm with you on that. I use ORM's, yet, being perfectly comfortable with SQL my perspective ends-up in a range between "this complexity isn't worth it" and "why not just write this in SQL?".
One of the arguments for ORM's is being able to move to different db engines. Frankly, I can't remember the last time I had to do that for a mature/released application.
Once again, I'll admit my perspective is biased because SQL isn't a problem for me. When I look at ORM code it looks and feels very detached from the database. I look at SQL and everything is clean and clear. In addition to that, you don't have to create and manage a bunch of objects that take-up memory and slow things down. Adding layers of abstraction isn't always the best idea.
> Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.
I tend to agree, I'm more partial towards Micro-ORMs, ideally with a 'natural' DSL; something that is a good blend of 'SQL'-ness and 'target_language'-ness.
I can think of SlickDb (Scala), Linq2Db (C#), Ecto (Elixir) was good if definitely 'elixir-y'... IDK what other languages have such magical things; I know all of the above lean heavily on certain language features to provide their magic.
In the case of SlickDb, while I don't write Scala I read a lot of it, and could always understand what it was doing. In the case of Linq2Db, it just plain has a DSL that is 'close enough' to SQL. I love it and have saved multiple teams from EF with it. Ecto, as mentioned above... it wasn't -bad- but definitely walked the line of 'just write SQL' to me. (I'll admit however I am at best a good elixir app -maintainer-...)
In general,
- 'object tracking' is bad; I have yet to see a good use case where it doesn't wind up masking scope of modifications in the long run.
- 'overly native' DSLs are usually bad; the closer a DSL is to the 'metal' of SQL, the less likely you are to be surprised by the generated SQL (e.x. the 'surprises' of linq2Db tend to be far fewer than in, say, Entity Framework)
- If your language doesn't have a good DSL-providing MicroORM... try to find a basic MicroORM that just takes SQL, and write tooling around it.
This sort of thing can be very good in the right hands, but it also facilitates the antipattern of select * with no conditions, then doing the real selection of columns and filtering of rows in code.
There is no point in selecting columns here since what we want is objects, complete with all their properties. As for the rows, of course you can filter in your SQL:
Person.objects.raw('SELECT * FROM myapp_person WHERE last_name = %s', ["Doe"])
Should you want to work without your models... well, you still have access to your connection object and can happily fetch whatever column or aggregate you want.
>Confession: something about ORMs has never clicked with me.. none of them ever seem simpler than SQL.
You are not alone on that sentiment. The opaque nature of the translation often result in poor performance, and to me the apparent ergonomics gains are not worth the trouble either (at least in most cases).
Nowadays we still use libraries to abstract over SQL dialects and generate SQL in a typesafe and convenient way, but it's not an ORM in the sense that it maps from the object oriented domain into the relational one and back.
One of the major benefits of ORMs was that you’d write a query once and it would work on any relational database or nosql data store. Imo it’s kind of pointless when an ORM only targets one specific database.
Your example is a bit disingenuous. A SQL query isn’t native in most programming languages, so you’re missing a lot more boilerplate code
That's what I feel like, too. Every ORM that I've worked with is a separate DSL that I need to learn. Also, abstracting away the database is something I don't get - why abstract away something that I explicitly chose because it does something different than the other alternatives?
I've yet to encouter a project where I'd need to switch to a different database. Even if that happens, there is likely some raw SQL that someone wrote because the ORM didn't do something as expected. Or some part of the code uses DB-specific ORM features that don't map to different databases.
The only thing I can imagine where this would be useful is when you don't have control about what DB is being used, for example, when building a product that should be compatible with Postgres and MariaDB (and each is getting used). However, in the age of containerization, this isn't a big problem any more.
In some ORMs, I need to create types that the result of a query containing JOINs is mapped to. Others don't support them _at all_. In TypeORM, there is a query builder which forces you to put in _some_ SQL for things like "WHERE a in (b, c)". Most ORMs I've used have a cumbersome handling of relations, for example when I need to specify which relation should be fetched eagerly.
I created a proof of concept of a different approach: Just embrace SQL and provide static typing based on the query. The return type of a query is whatever that thing is that the query returns in the context of the database schema. It's possible to do in TypeScript, by parsing the SQL query at development time:
One benefit is that it does not need any runtime code, as it's just a type layer over SQL. You don't have to rely on some type-metadata that TypeScript emits. That's why it also works with JavaScript only.
You don't have to fit every result into some type - it just returns an interface that can be used wherever you want. That's especially useful because TS's type system is structural.
One major downside is that it's rather complicate to implement a parser and evaluation of the result type in TypeScript's type annotations. A different story is debugging type-level code; it's basically try-and-error. Providing error messages in case a query is wrong is also something that needs work. That's why it's only a PoC.
>Every ORM that I've worked with is a separate DSL that I need to learn
That is the point of Flyweight. The API is very small, and for everything else you use SQL. The ORM parses the SQL to figure out the types, and when you want it to, it maps the SQL into more complex data structures.
Probably partially because you (like me) know SQL pretty well. I'm dealing now with an application at $currentjob whose employees are really, really good at Ruby on Rails' ActiveRecord, and the Rails/AR code they come up with seems to me INCREDIBLY complex, taking (I think) more lines than the equivalent SQL would. And not really any more readable. But they're very much into do it the Rails way because Rails says you should.
I think it's one reason that I'm leaving at the end of next week.
Sure, this is a simple example -- and you are right, if you are just writing a few selects, you may not find any value.
ORMs (or query-builders, as some like to draw a distinction there) become more valuable as you use them to compose and transform queries. SQL is decidedly not composable.
My experience with ORMs is that they’re a minefield of performance cliffs. It’s easy to accidentally generate suboptimal SQL or introduce additional round trips unless you’re very careful about the code you write, at which point you might as well write SQL directly and be at the mercy of one less black box.
If you compose SQL with subqueries you tend to do less composition in general (because you are restricted in what you can compose), and you are more at the mercy of the query planner, which is its own black box.
I'd rather simply understand how my ORM generates queries, and then use the ORM to get the full power of my language of choice in creating the right SQL queries.
> I'd rather simply understand how my ORM generates queries,
Depending on the ORM this may be be far from trivial. Despite being a C# dev rather than a DBA I can say that I have a better understanding at least one DBMS's query planner than what Entity Framework will generate for certain queries [0].
I'll note that -good- MicroOrms can make 'subqueries' in a fairly composable way.
I think with a manual SQL, you can still make a 'good' mini-DSL with subqueries, but it will take some thought to do well. Actually, with -extremely- thoughtful design it can be far more performant and productive, but IMO the complexity would need to show ROI to be worth it.
But in -either- case, in the long run the restrictions help with perf and maintenance. Whether it's hand-written mini-DSL or a good MicroORM DSL, you have a much better mechanical sympathy for what you're doing to the DB, and it becomes easier to write Table designs that are performant.
> and then use the ORM to get the full power of my language of choice in creating the right SQL queries.
Now you have to learn an ORM, a Query planner, -and- how to make them play nice.
[0] - There has been more than one shop where I was the 'EF Expert'... take that as you will.
Your code example is just wrong. You didn't use parameter binding. You didn't even execute the query. You didn't even store the returned rows in a usable structure.
You are missing the steps to get that select statement into JavaScript (running the database command, passing in the parameters, parsing the result, etc).
I said it once many years ago and I still say it: "ORMs are for people who don't know SQL"
However, I understand the problem it tries to solve: Object-Relational Impedance Mismatch. I only wish there were other idioms, not the convolution of classes and mappers.
I've never said this before but I'll say it now: "'ORMs are for people who don't know SQL' is usually said by people who have never written a complex application."
I agree that ORMs are dangerous and clumsy for a number of use cases, but the query-building aspect is indispensable in many scenarios. Unfortunately, the query builder is usually tightly coupled to the ORM, but _if you do know SQL_ then you can use the ORM sensibly and performantly.
I sustain and refute your claim that I've not written complex apps. They just don't have to be complicated, which is what most ORMs bring, and has been my experience. Complex don't mean complicated.
