As someone who always prefers native SQL over ORM queries, I'll add a couple of counterpoints.<p>Most IDEs provide intellisense/validation of ORM entities, vs treating SQL like a raw string.<p>ORM entities also make refactoring and impact analysis slightly easier.<p>Despite those benefits, I generally find ORMs a pain for anything besides the most basic queries.
I still don't understand why people believe that abstracting away something as important as your data store is a good idea. They quickly run into performance problems that are quite difficult to fix.<p>It's funny that the same developers that try to avoid vendor lock-in don't realize they've locked themselves into an ORM forever.<p>Plus, SQL isn't that hard. And as a backend person, you should be able to visualize your data model anyway. A SQL datatbase is just a bunch of planes, for the most part, and the queries are how you intersect them...more or less.
I think the correct approach is to understand the SQL that your ORM is using. If you can't have it create the correct query than roll your own. I think most of the trouble people get in with ORMs is not taking the time to understand the SQL that it creates and using it incorrectly as a result.
Comments here make me wonder if I've just been spoiled by ActiveRecord. Not that I use it for all queries but 1) it's rare that I have to resort to raw SQL and 2) it kindly gets out of the way when I do.
My simple rule for databases is that if you are not actively querying on it, it doesn't need to be a separate table or column. Use the YAGNI rule here and you'll be better off. The classic example here is persons that have addresses and phone numbers. Most applications have no requirements to query on most of that: street name, postal code, phone number, etc. Less tables and columns mean simpler joins (or better, no joins at all). Any structured data that you don't query, just store it in json form in a blob and simplify your schema. Anything that actually requires complex querying, consider using a proper search engine. Or extract it to a dedicated field with some index on it that actually helps you querying effectively. The golden rule here is that if that query is connected to user input you probably need some notion of ranking, fuzzy searches, etc.<p>Either way, everything gets easier with a simple schema. Faster query and insert performance, easier to reason about when doing transactions, easier to maintain, etc.
Whilst true from a performance level, do appreciate there is a complexity cost with raw SQL given how it allows for inconsistent ways of working.<p>Coming from a Rails legacy app, these raw SQL "clever ideas" are where I found the most issues.
In the 1980's we had C/C++/Ada/Pascal/Fortran/Assembly/Lisp. Since the 1980's the languages have exploded.<p>But we still only have one language for querying a database. And a kinda not very good one at that. Why is that?<p><a href="https://www.holistics.io/blog/quel-vs-sql/" rel="nofollow">https://www.holistics.io/blog/quel-vs-sql/</a><p>One could argue that ORM's are the alternative language people are looking for.
I always admired micro ORMs like Dapper. The hard (or at least time wasting or error prone) tasks are:<p>- mapping results to app entities or projections<p>- input sanitization<p>- dynamic query building<p>- connection pooling and transaction management<p>- supporting multiple SQL dialects with one code base<p>After many years of experience with Hibernate, NHibernate, Entity Framework, and a plethora of other full ORMs, I absolutely do not want lazy/eager loading, opaque query DSLs and criteria APIs, and dealing with leaky abstractions for queryables (looking at you, LINQ and JPA), and I’m pretty wary anymore over cascading persistence. These things all look great in tutorials and are nearly magical in your PoC or simple projects, but in my experience, performance problems, unexpected and sometimes hard to diagnose bugs, and lots of ugly yak shaving are inevitable for moderate complexity apps over time.<p>Of all of them, I enjoyed JOOQ’s SQL builder with or without codegen the most if I need to support multiple dialects or complex dynamic queries.<p>Speaking of JOOQ, anyone have recommendations for something similar on nodejs? I haven’t been particularly excited by prisma, typeorm, or sequelize.
> Will you actually need to change between fundamentally different database technologies?<p>Yes, use sqlite locally for development and run PG in prod. Unit tests can now use the db and finish in milliseconds. You get unit tests that have the power of integration tests and don't have to ever stub out your db. I use Redislite for the same thing.<p>I'm of the opinion that SQLite is the musl of the SQL world. By deciding that you'll support it first-class you'll avoid the sharp edges of database specific behavior and extension hell and write better more maintainable code.<p>"Sorry we can't actually put that logic the database, SQLite doesn't support spooky action at a distance."
The article mentions database portability as an advantage of ORMs, and I agree - to me that is basically the only real advantage to ORMs. The article also mentions that database portability is not a very compelling advantage, since lots of applications don't actually need that, which I also agree with.<p>Personally I just pick either PostgreSQL or SQLite depending on my use case (and they're different enough that there is always one obvious choice) and just interface with them directly. Hasn't served me wrong yet.
The times I've run into people who've written a library to handle something built into Postgres... everyone likes to reinvent timestamp and range handling for some reason.
You can learn a lot about a system merely by looking at the database schema and data transformations to new consistent data. How exactly these processes do what they do is an implementation detail. <i>Think in invariants</i>, not <i>mental simulation</i> of incidentally complex procedures. In this light knowing SQL is a superpower that applies to all systems built with any RDBMS.
Have you seen Pure ORM? It's an ORM that <i>purely</i> does object relational mapping.<p>You write SQL but instead of getting back flat and potentially-collided data, you get back pure objects which are properly structured.<p><a href="https://github.com/craigmichaelmartin/pure-orm">https://github.com/craigmichaelmartin/pure-orm</a>