Ask HN: ORM or Native SQL? | Hacker News Reader

Ask HN: ORM or Native SQL?

20 pointssh_tomer48 comments

Do you use ORM or native SQL, or both? which ORM are you using and how did you choose it?

Both. StackOverflow official blog mentions they are mixing both Entity Framework and Dapper. [1] [2]

Specially with larger projects, I generally find that ORMs can be good enough for 90% of your code. It also enforces strongly typed models, and makes it easier for refacorting, and finding where the models and variables are being used.

The 10% critical section of the code, where performance is more sensitive, SQL is acceptable, and needs to be carefully looked at/reviewed.

[1] https://stackoverflow.blog/2022/07/04/how-stack-overflow-is-... [2] https://www.reddit.com/r/dotnet/comments/9mg7v6/with_stack_o...

The best solution I've ever seen is this Rust library https://github.com/cornucopia-rs/cornucopia

You write plain SQL for you schema (just a schema.sql is enough) and plain SQL functions for your queries. Then it generates Rust types and Rust functions from from that. If you don't use Rust, maybe there's a library like that for your favorite language.

Optionally, pair it with https://github.com/bikeshedder/tusker or https://github.com/blainehansen/postgres_migrator (both are based off https://github.com/djrobstep/migra) to generate migrations by diffing your schema.sql files, and https://github.com/rust-db/refinery to perform those migrations.

Now, if you have simple crud needs, you should probably use https://postgrest.org/en/stable/ and not an ORM. There are packages like https://www.npmjs.com/package/@supabase/postgrest-js (for JS / typescript) and probably for other languages too.

If you insist on an ORM, the best of the bunch is prisma https://www.prisma.io/ - outside of the typescript/javascript ecosystem it has ports for some other languages (with varying degrees of completion), the one I know about is the Rust one https://prisma.brendonovich.dev/introduction

Cornucopia is neat. I wrote a similar library in Go [1] so I'm very interested in comparing design decisions.

The pros of the generated code per query approach:

- App code is coupled to query outputs and inputs (an API of sorts), not database tables. Therefore, you can refactor your DB without changing app code.

- Real SQL with the full breadth of DB features.

- Real type-checking with what the DB supports.

The cons:

- Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs.

- Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to.

[1]: https://github.com/jschaf/pggen

> Therefore, you can refactor your DB without changing app code.

Indeed, that's incredible! I tend to think that the actual layout of db tables a low level concern that is mainly driven by query performance and simplicity.

> - Type mapping is surprisingly hard to get right, especially with composite types and arrays and custom type converters. For example, a query might return multiple jsonb columns but the app code wants to parse them into different structs.

In Rust there's serde for converting json into structs (or enums if the shape of the json is complicated enough). Doesn't Go have a similar serialization/deserialization library?

> - Dynamic queries don't work with prepared statements. Prepared statements only support values, not identifiers or scalar SQL sub-queries, so the codegen layer needs a mechanism to template SQL. I haven't built this out yet but would like to.

In this case, what about stored procedures?

But, generally, instead of a template language for SQL, I would like to have a real compile-to-SQL higher level language, that could also do dynamic queries. The trouble with templating is that dynamic queries are hard to do in a type-safe manner, and it's hard to prevent generating invalid SQL when you have a bad template substitution (and then you get bad error messages from the db). There's a few languages like this do this https://github.com/ajnsit/languages-that-compile-to-sql but none fits the bill

> Doesn't Go have a similar serialization/deserialization library?

There isn't anything as flexible or widespread as serde.

> In this case, what about stored procedures?

That works but moves logic out of the query and into the DB. Keeping logic in the query is nice to keep server releases independent. The lifecycle of app (and query) code is easier to manage than DB migrations.

> The trouble with templating is that dynamic queries are hard to do in a type-safe manner.

Where-clauses and order-by-clauses are straightforward since the structure of those clauses doesn't affect the structure of the query results. Group-by and table clauses are troublesome since a query might work during codegen but fail at runtime.

> Where-clauses and order-by-clauses are straightforward since the structure of those clauses doesn't affect the structure of the query results. Group-by and table clauses are troublesome since a query might work during codegen but fail at runtime.

If you do this kind of analysis (to reject malformed dynamic queries before sending them to the db) I think one can argue you created your own programming language (a DSL but still)

My absolute most horrid work experiences have all involved Hibernate / JPA. It slows everything to a grinding crawl, works somewhat 99.9 % of the time and creates inexplicable un-debuggable errors for the remaining 0.1%

On top of that you have to ditch referential integrity to make it at least limp along

Give me plain SQL or something lowlevel like sql2o any day

Hibernate is masochitic

How to turn a 1x developer into a 10x developer? Stop using Hibernate.

I might still have psychological trauma from it.

I completely agree. I pretty much stopped using Spring/Boot because of it, even though it could be used without Hibernate/JPA.

I tried sql2o and later switched to jdbi and Javalin for a lightweight framework. I started making a typesafe library[0] that maps bottom-up like SQL expressions but development as stalled as I haven't been doing much side-project work to use it.

[0] https://github.com/karmakaze/safeql

I normally dislike ORMs, but Elixir's Ecto has changed my mind on it. It's a DSL that is very close/similar to SQL, enough so that it feels to me like writing SQL. IMHO it's the best of both worlds because it's close to SQL but it returns rich Elixir Structs and provides exceptions/error handling in an idiomatic way for the language.

Ecto is more than a typical ORM. You can map queries into arbitrary structures, which permits arbitrary joins and selects. It also provides better mechanisms for validation for insert/update that go beyond enforcing type annotations

I've used both repeatedly, and I will never use an ORM again. It's never gone well. Conversely, direct SQL read/write works great for a variety of applications if you do it right. No complaints about that. Pretty convinced the only appeal of ORMs is for people who want OOP everywhere or just don't want to learn SQL, but it's worth learning SQL and the basics of a normalized DB schema.

Sometimes it's supposed to be about abstracting away the DBMS. But the interface between your code and the DBMS is usually very "wide," plus you might be working around performance concerns. You can try to abstract it away with an ORM, but the abstraction will be just as wide, and now you have another layer introducing limitations and performance issues. So it seems to go against basic software design, but you really have to accept that your backend code is going to be married to your DBMS. It's either that or marry the ORM.

Forgive the title, but I also agree with the points here: https://blog.codinghorror.com/object-relational-mapping-is-t...

And if your ORM isn't a super well-supported thing, good luck. When I started my new job years ago, they said they were starting to use a custom DB/ORM-as-a-service thing maintained by a partner team. That team had great engineers, but it was simply too big a project for them to essentially build a DBMS on top of a DBMS, so it was a mess. I tried to act like this was ok until one day I showed a skip-manager some unfixable race conditions that broke our whole application. Our team got new devs who also complained about it. It also happened to not comply with some unrelated department mandate, which gave me the justification to take the reigns on our team bailing us out of that. Took a full codebase rewrite where I just gave them a regular SQL database. I'd say it cost us an entire year of development in the end.

Both, but I find myself having to learn a new ORM practically every time I switch languages and platforms, or just every 5 years or so because new "better" ones come out. Also, I often do not feel I've every really "learned" the ORM. It doesn't act like my understanding of it, all of the time. Either my laziness, poor documentation, etc.

So I keep coming back to SQL, even if it means rewriting code I've already written. At least with SQL I know exactly what's going on, probably because I have a lot of experience with it. With SQL I can try code out against the database in parallel if needed. It always works exactly as expected. I get the paradigm so I don't have trouble writing it correctly. I don't have confusing bugs related to saving state vs. leaving it in memory. YMMV.

Off the top, ORM all the way. I don't like SQL syntax, it makes my head hurt. Boooo.

I recently switched from RethinkDB to EdgeDB, and it uses SQL under the hood. I love it, it's awesome. I don't need raw SQL but I think EdgeDB allows you to write queries if you want.

How do you host your edgedb server? I’ve entertained it but didn’t like the idea of having to host 3 servers just to have my app, edgedb, and a database. Strictly speaking toy projects here. Db can be a managed free tier. I guess I can make edgedb run on the same server as my app but in another process.

I do! I wrote a nameserver in Deno and have it sitting on the same server as EdgeDB. The baremetal instructions are missing a bit when I was attempting this setup last month but to the devs' credit, they updated the documentation to address my issues.

The EdgeDB team is going to release a managed service sometime "soon".

Guess I’ll have to try it now. Just saw they released a .net client too. Neat.

No ORM. Sucks having to learn a new ORM every project.

Even worse when you need to do something in a new ORM one step more advance than the tutorials show you.

> No ORM. Sucks having to learn a new ORM every project.

I don't quite follow your logic. ORMs provide a lot of critical features for free right out of the box which take a lot of time and effort to roll out by hand. Database migrations are perhaps the hardest to get right, for example. Why do you believe that the trivial job of onboarding onto a ORM is too much to handle, but manually rolling out and maintaining and debugging hand-written code is somehow an acceptable loss of productivity?

Learning the ORM is easy. On the other hand, learning the ORM bugs...

It's never a trivial job. And in my experience it's rarely a single ORM. You will have one for the js code, on for the .net code, one for php, on for ruby, etc, etc.

And your developers are going to be sitting read docs for hours to do many things beyond a trivial query.

JPA + EclipseLink. It's pretty simple:

em.getTransaction().begin();

customer.setName("smith");

::bunch of other atomic actions here::

em.getTransaction().commit():

JPA offers caching, cache eviction, and falls back to automatic Version columns (update customer where name = bob and version = 1 set name = smith, version = 2). We use ActiveMQ to put cache eviction notices onto a Topic and distribute them out the JVMs that advertise interest in the objects.

Cache hits can be obtained by "business key" like "cust-00001" (or primary key internally)... A common use case is not causing database I/O on RESTful URLs: http://example.com/customer/cust-00001

Granted there is some really complex stuff we dip into native SQL and stored procedures, but JPA does 95% of everything and we handle everything else as it comes. Highly recommend.

Neither.

Native SQL syntax is not very uniform, and it prevents parts from composing easily. String manipulation is fiddly, even with nicer (named) parameter substitution.

A typical tutorial ORM approach, with mapping relational data to objects, and then allowing to add data manipulation methods to the objects, and especially to override save(), kills most of the advantages of relational databases. It is particularly bad when you need to reach for parts of rows, or do updates on multiple rows, or even selects with more more complex conditions / joins.

The sweet spot is a library that allows you to easily describe tables, and write composable, terse statements that translate to SQL.

In Python land, for instance, both SQLAlchemy and Django ORM allow that. They allow more but I usually avoid making table models rich.

SQLAlchemy is the most composable, Lego-like of the two. You can, for instance, factor out complex conditions and share, mix and match them between various selects, updates, deletes, as needed.

Django ORM excels at being terse and describing joins in a select without even mentioning them, just referring to other tables via double__underscore, if the relation is already described in the table models.

Both operate with result sets which can be not only fetched but also combined. You can write a select (or .filter in Django) and e.g. give it as a parameter to another select, and the ORM layer will rewrite the result as a single query with appropriate joins or sub-selects.

You can easily issue updates to multiple records without fetching and hydrating a single one of them. It's a massive performance improvement compared to the naive ORM approach where you operate on DB data as if it were objects in RAM: a loop to fetch, update, and save each object.

You can easily limit the result set to the particular set of columns you want at the time, and avoid hydrating result sets into full-fledged objects which may be too large or unnecessarily nested.

You can of course sometimes use "proper" objects formed from relational data, when you need to compute something interesting from their attributes for rendering / reporting. But I try to avoid even that where possible.

I started a project recently using no ORM and having the flexibility to easily merge queries and extract exactly the data I need is very helpful. Converting the rows to objects hasn't been a major concern and I have compile-time type checking against the database schema (for non-dynamically built queries).

I'd say that the only real issue I have is building dynamic queries and some helpers for joins to make it easy to confirm that joins are against the right keys and tables. But that hasn't been much of an issue.

Likely depends on the language. With Go I like using https://sqlc.dev/ - it generates Go-code from a SQL script. You get the benefits of static typing for parameters and return types + it removes much of the boilerplate involved in using plain SQL. And you still get the full expressiveness of SQL. I think this is a middle ground between SQL and ORM.

ORM all the way, I write native SQL when I really need something specific but most of the time, ActiveRecord does the job well.

I'm no database admin but I find myself fighting ORMs, I'd rather use SQL for anything but the most basic queries in any given language that I've worked in. It just feels more natural to me. I think it's an opinion thing though. However for me I learned it once and it's easy to adapt.

I like the Django ORM a lot.

That said, I know SQL, and I will absolutely say if you use an ORM you should know SQL.

Django ORM is good for ~90% of queries, but there's always that pesky query that would be too convoluted to build up with the ORM, so it's nice to be able to jump down to the DB API and `execute()` a custom query.

It has been a long time since I have used it, but the ability to create test fixtures and have them run in an in memory sqlite database was killer feature.

I strongly prefer SQL over any ORM.

RedbeanPHP has been a lifesaver for me.

https://redbeanphp.com/index.php

So easy to set-up and use. Lots of built in PHP security.

Covers 99% of my need. Allows me to use native SQL to cover the remaining 1%.

Simple cases (query a single view/table fully, or get one item, mutate and save): ORM (EF core)

More complex queries or needing only a scalar value: Raw SQL via dapper.

How do you unit test your sql? At least with orms, the data access is tested implicitly via business logic unit tests.

At the repository (data access) application layer.

For a query (e.g. loading an entity):

- Arrange: test database loaded with fixtures

- Act: execute repository method

- Assert: returned data corresponds to fixture

For updating queries: additionally assert the new database state.

Repository tests shouldn't care nor know whether the methods under test internally execute raw SQL or use ORM methods. This allows freely switching and mixing the two.

I really like microORM call Dapper when using C#.You still write native sql but it's typed.

Native sql with sqlc.dev

Query builder or SQL.

native sql with auto generation of code on server side.

Choosing this depends a lot on your language, its ecosystem, and your application's own ecosystem (if your app shares a database, ORMs aren't necessarily out of the question, but have a lot more hurdles to overcome).

In Python using the Django ORM lights up all sorts of great features in the Django Framework because its automated admin and automated CRUD views feed off the ORM's information and it's often silly not to use Django's ORM for how much free productivity it can buy you. But then if you aren't using Django with Python you've got far more choices of ORMs and maybe fewer reasons to use one or another.

In C#, I'm a massive fan of LINQ. I think recent versions of EF (Entity Framework) have been rock solid and do everything that I need to do. (Early versions EF that supported LINQ got some (deserved) bad reputations for its query transformer sometimes doing surprising things. As someone who had to debug many of them, I knew that pain directly. Ever since the "reboot" (EF Core 1.0), EF has had a very good, very "no magic" query transformer that doesn't do anything you don't ask it to do. I do mean it greatly when I say that recent versions have been rock solid.) With LINQ (in either of its forms) I almost never feel like I need to write a query directly in SQL (and most of what is left are fun rare things like MERGE statements) and LINQ offers somethings that writing in SQL can't. Type safety is obviously the big one for why you should want to choose an ORM in the first place. But there are also small things where I think LINQ ergonomics are actually better than their raw SQL equivalents (things like WHERE versus HAVING when writing GROUP BY logic).

I've also worked on massive shared database "microservice architectures" where everything needed to be "Native SQL" and not just "Native SQL" but hand tuned micro-apps written in T-SQL (or equivalents) to insure data integrity and overall performance of the entire ecosystem. I don't really recommend architecting applications that way, but I do know they exist for all sorts of reasons and have had cases where any ORM involvement made things more difficult. (That said, in C# modern EF support for type safe calls of stored procedures and views is pretty great and I'd probably still use that today rather than building hand-managed ADO.NET wrappers like I used to have to in those days even if most of the actual ORM parts and query transformation tools were left unused.)

Ask two developers and you'll sometimes get five different answers. There's a lot of personal preference here, mixed with all sorts of experiences from all sorts of ORMs. There are so many ORMs to choose from out there and most answers about one don't apply to others, and with EF as an obvious example even the same ORM can have massively different reputations in different eras of its lifespan.

I find that the ORM is terrible no matter what language, ecosystem, etc. Or which ORM. But the worst is probably CoreData.

Just because you've had bad experiences with ORMs doesn't mean that there are no good ORMs. That's why I bring up how much language/ecosystem does matter. Bad experiences with ORMs come not from ORMs themselves as a concept (it is an extremely broad concept) but from specific ORMs in specific ecosystems. I thought it useful to give examples of specific ORMs in specific ecosystems that bring joy, because throughout these threads you see plenty of hate for ORMs, just like you thought to bring up in response to my post.

The auto-CRUD of Django is a joy to have for some types of projects.

The type safety and power of C#'s LINQ is easy to underestimate and while there have been times when that power seemed "too magic" and bad versions of EF gave off plenty of bad experiences, there are times of great joy in modern EF.

If you haven't experienced these specific ecosystems for yourself, it might be worth doing in a side project somewhere just to give them a shot. I can't guarantee you would find joy in that, but I can suggest that there is possible joy that I know exists to find in such places if you try them with an open mind.

It's the concept of an ORM itself, which is a mapping from OOP to relational algebra, both concepts that don't change much between implementations. You have a relational DB with a nice powerful interface already. The ORM tries to abstract that way with a not-so-compatible OOP interface that enforces mediocre schema design.

On the practical side, it's common for a system to start needing more impl-specific database optimization eventually, at which point the ORM gets in the way even more. The ORM is ok for small projects, but so is SQL.

CoreData is only especially bad because it's confusing to use on top of that. I've used the Django auto-CRUD too.

Not all databases use relational algebra. Among other things document databases are increasingly common for a number of types of applications and ORMs can provide good experiences for those types of databases as well.

Beyond document databases, I think the "theoretical" mismatch between OOP and Relational is often over-exaggerated/over-stated and most of the mismatch blamed entirely on the wrong direction. For historical reasons, some of which were a love of relational algebra mathematical "purity", relational databases have tended to have problems storing and querying hierarchical and graph data. Hierarchical and graph data are relatively common and somewhat "natural" in OOP: inheritance and polymorphism often rely on hierarchies, and references between objects can easily be complex graphs, including cycles and other complex graph mechanics.

Theoretically "pure" relational algebra cannot accommodate that kind of data, it's what keeps the math simple and pretty. There is a clear OOP/Relational mismatch, but it's a lot more to do with that theory problem, and the problem is entirely on the relational side being unable to express practical, common, complex data. If there's something holding things back and enforcing "mediocre schema design", there's a lot of fingers to point at relational algebra itself (and SQL too, for its own ways of trying to be "pure" to relational algebra).

In practice, of course, there is no such thing as a "pure" relational database that people actually develop software for and most SQL databases have all sorts of "impure" extensions and tools and some dialects of SQL can surely feel quite powerful but still often have real, interesting holes in the types of data they can describe and query. Though some of those extensions offer escape hatches today, with the increase in JSON support especially in the majority of SQL databases.

I know in practice I've anecdotally been seeing an increasing number of cases where ORMs remain relevant and helpful but reliance on SQL-based databases and SQL specifics is diminishing all the more each year. Not that I don't think SQL-based databases are bad at what they do, what they do well they do well, but that there increasingly there's a lot more escape hatches being used to non-SQL data stores (even if just increasing numbers of JSON blobs in an SQL database). There's always more hierarchical and graph data to store and query, and there are better options every year for that kind of data.

From my perspective, the best ORMs often don't "abstract" away the relational underpinnings of a relational database: they do their best to help you simplify (if not dumb down) your application's intended data structures to better fit a relational database. I say that as someone with good SQL performance understanding, a love of the theory of pure relational algebra, and a knowledge that when I need to that I can absolutely beat an ORM at hand tuned SQL and "brilliant" relational database schemas. I'm sure our experiences have been vastly different to this point in our careers. I agree with you that there is an ORM/relational mismatch, but I don't expect you to agree with me that my practical experiences continue to tell me that the fault isn't with ORMs in that mismatch.

The "R" in ORM means relational, so whatever is working with non-relational DBs is not an ORM. Document store DBs can make sense for some use cases, like very hierarchical data as you said, but it really is a niche use case.

You don't often benefit from basing your DB around tall object hierarchies, and you can instead mix in jsonb columns sparingly when they make sense (I'm not a purist about these things). I've been through that ride a few times in different jobs: They start with a relational DB, then someone realizes you could shove everything into a graph where each node can be just about anything, then they do it, then eventually they feel the pain from using what is essentially a weakly-typed DB. Eventually someone whacks everything and goes back to a regular relational schema design, which turns out was a fine way to do it.

To put it another way, you could start with the relational way of doing things and probably never encounter enough pain to warrant going to an ORM.

The web went through something vaguely similar with HTTP methods. It was cool at some point to conform to a standardized CRUD interface of GET/PUT/PATCH/DELETE on objects, but it's never that simple. Nowadays, plenty of APIs just use POST for everything with freeform requests/responses. I see DELETE sometimes, can't remember the last time I saw PATCH or PUT though.

> The "R" in ORM means relational, so whatever is working with non-relational DBs is not an ORM.

It used to mean that. No one that I'm aware of has yet proposed a better name because many of these are the same tools. (EF has some low level support for both SQL-based relational databases and some classes of Document-based databases and keeps exploring ways to improve that.) I think ORM several years back passed the point where the initial acronym was all that important and "ORM" itself is now just the generic name, no acronym at all.

We could probably use a better name as an industry, but as the adage goes naming things is one of the hardest problems in software.

> To put it another way, you could start with the relational way of doing things and probably never encounter enough pain to warrant going to an ORM.

No kidding, and also this is what most of the other threads in this Ask are about and I've never argued against it. What I have argued for, because there aren't as many voices speaking for it, is that the same happens the other way round:

You could start with a good ORM in a nice language/ecosystem doing the ORM's comfy way of doing things and probably never encounter enough pain to warrant going directly to SQL (or whatever).

Not enough people are saying that. That's most of what I wanted to contribute. I'm not sure why you are battling me on semantics or so adamant that I'm so wrong about "ORMs can be nice sometimes" other than yes it sounds like you can show me where an ORM hurt you once, but that's not my point. ORMs hurt lots people. Not every ORM is great, and not every ORM is great for you. That should all go without saying. ORMs can be nice sometimes. In an Ask full of ORM negativity, I thought I'd offer some positivity. I'm sorry?

> The web went through something vaguely similar with HTTP methods. It was cool at some point to conform to a standardized CRUD interface of GET/PUT/PATCH/DELETE on objects, but it's never that simple. Nowadays, plenty of APIs just use POST for everything with freeform requests/responses. I see DELETE sometimes, can't remember the last time I saw PATCH or PUT though.

I'm definitely lost here at this aside. I don't actually know what it has to do with the point you are making.

For what it is worth as an aside: I see plenty of REST APIs still that focus on POST/GET/PUT/DELETE (that's the direct CRUD analog in CRUD order). I haven't seen a "POST only" API since my rough and tumble PHP youth, but we presumably have very different APIs we work with. (ETA: With the obvious exception of GraphQL, which is its own sort of special, and I don't really think of an as HTTP API.)

PATCH is absolutely rare, I'll give you that, but was never a CRUD HTTP verb to begin with. It was always an extension HTTP verb from WebDAV and elsewhere. It is often hard to implement right (PUT is much easier), though increasingly accepted standards like JSON Patch (RFC 6902) and various libraries that support it have started to make it somewhat easier again and I've been seeing PATCH with expected content-type: application/json-patch+json (and only that content type) start to show up again in some APIs.

Any ORM as long as its ActiveRecord.

Reply on news.ycombinator.com