ORM or Native SQL?

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

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

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.

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.

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.

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.

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.

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.

Any ORM as long as its ActiveRecord.