- SQL is so easy and so useful that being a professional engineer that can code but can't learn SQL is absurd
- You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd
- If you ever get enough scale to require optimization the ORM will be your enemy
In summation, anything related to ORMs and SQL is immediately invalid, other than questions like "Help! I joined a company where they mandate ORMs and I hate my life!"
(probably) not many are using ORMs because "SQL is hard". You're attacking a strawman. I use ORMs because they make two things incredibly easy:
- saving object graphs: it's trivial to write SQL for a simple CRUD form. It's far more difficult to work with real models where you have to deconstruct the form the user posted into a dozen tables with joins between them, doing insert/update/delete as needed. With a decent ORM you simply construct an object graph by assigning fields (the result can be very complicated), shove that into the database, and the ORM takes care of the rest. It may not be web scale™, but many of us don't need that.
- constructing queries on the fly: if you ever get any exposure to LINQ, I don't think you'll want to go back to SQL after that. It allows you to easily construct queries based on user input (in a type-safe way, unlike some other solutions), which saved me countless hours when building complicated tables with search and filtering on many (sometimes hundreds) of fields.
> And with good ORMs like Entity Framework the underlying queries they generate are predictable.
Right up until they are not and generate crap. I went from "predictable" EF generated queries to handcrafted ones reduced the load on the DB by about 70% while having more queries per second...
I find that ORMs makes it significantly harder to construct queries for anything but the most basic of queries. I routinely end up having to fight the ORM.
ORM is never about "not learning SQL". I am great at writing SQL, but i hate converting my resultset to object graph, converting lazy loading vs eager loading at times, dealing with types and changing types etc.
On "great" side of things, Linq brought massive type safety to .net land, and made queries also compile time checked.
IS it leaky? yes. Every once in a while you'll need to write SQL.
Does it solve a lot of problems? Also yes.
Disclaimer: I was once a contributor to NHibernate.
Alas, it's far from a complete solution. It's too easy to type an expression that compiles but blows up at runtime because the underlying provider doesn't support it or gets confused.
All your arguments are correct, but are unrelated to the main motivations for ORMs. ORMs are about:
- Convenience of using SQL from $LANGUAGE where mapping the result, (re)naming, etc. is handled for you. Do this manually, and eventually you will either make silly mistakes or create your own mini-framework.
- Connection pooling / retrying if desired. Can be done without, but again mini-framework.
- Migrations. Generating a schema from code is neat.
- Making our lives miserable when the ORM inevitably generates horrible SQL somewhere and we need to override it manually. That is, ORMs are NOT a replacement for learning SQL.
I only have experience with Java ORMs, so I'd say you forgot to add that ORMs require entity objects which basically duplicate everything - table structures, relationships, the whole lot is redefined.
In my experience Java domain code written with an ORM is about 4x the LOC compared to using stored procedures to do the same job.
Oh - and that 4x LOC gets you about 1/4 the performance, loosely speaking.
I am a substantially more productive developer since I kicked all those abstractions to the kerb.
It is the database and its structure that is the important part of an application. Building an additional layer of abstraction on top of an already existing abstraction no longer makes sense to me.
We used SQLAlchemy on a Python project which allowed us to use an in-memory SQLite DB in our integration tests. This gave us blazingly fast database tests during development where a database was torn down and recreated, with data, for each test in milliseconds. When the commits were pushed, CI would run the tests against SQLite and MySql.
It worked extremely well and would be a tremendous amount of effort to handwrite SQL for both DBs. I'd do it on ever project if I could.
I know that a lot of people do this but I would have very little faith in my tests if they were run against a different type of database than my production one.
I use pg and test suites run in parallel against their own db. It’s pretty cheap with CREATE DATABASE helpers plus using container means little to no headache.
Switching DB's does happen, but it is generally rare in my experience, and usually a strategic mistake. Those who do it usually underestimate the migration time. It's not economical to spend a lot of time up front to prepare for a scenario that happens once in a blue moon.
The main reason to write mostly generic SQL is so new hire-ees can get up to speed.
I was lead on migrating several systems from traditional deployment to a cloud provider for a major company. Part of the migration was switching a microsoft DB to MySQL. The java ecosystem and ORMs makes this so easy it's barely worth talking about. And that wasn't the only time db migrations have some up.
Not sure if that's enough to justify ORMs, but DB migration is a real use case and there are things you can do ahead of time to greatly simplify the process.
No, you may never have had to switch databases, but I have worked on codebases where we've had to switch databases, and I'm sure others have to. Most likely when dealing with monoliths where you can't do a full re-write and you can't break out a separate service for various reasons. The repository pattern is your friend in these situations. An ORM should ideally be able to read/write to multiple database drivers which makes this easy by dual-writing to both databases from the repository layer.
I've even worked on a number of projects where
a) the unit tests used an in-memory database
b) dev environments used mySQL/postgres/SQLite or similar
c) production used Oracle or SQL server or similar
Good luck doing that with hand-coded SQL.
But for me 99% of the benefit of using an ORM is compile-time checking of queries. I don't see why in principle that couldn't be possible using raw SQL, but I don't know of any good examples of it.
> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd
One of the main reasons that Django has been so popular for so long is the idea of reusable "apps". Eg, Django Admin, which comes bundled with Django but there are also thousands of 3rd party ones that you can use to quickly put together very powerful sets of features out of polished, tested pieces. That whole ecosystem really only works because of the ORM, which means that apps mostly don't have to know/care what database might be used. (I assume some other frameworks have similar stories, but Django's what I'm most familiar with).
As far as scaling, I've developed and supported Django apps that have hundreds of thousands of users and serve thousands of requests per second. I would probably not even put the ORM in the top ten list of problems with achieving scale. I've occasionally found it generating slow queries and had to tweak or bypass it (both extremely easy to do) but honestly, those have been low hanging fruit when it comes to optimizing (and very easy to find thanks to reusable apps like `django-debug-toolbar`, which exists at least in part because of the ORM). Are ORMs unteneble once you're in the millions of users or tens of thousands of requests per second range? Maybe. But that's a very small part of the software landscape and for the vast, vast majority of the rest of us, a good ORM isn't going to be the problem.
One way to have the best of both worlds for situations where you have complicated read queries is to create database views for the queries. You can then define them as an unmanaged table in Django (requires some boilerplate) and query them in a simple way as if they were a normal ORM model.
> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd.
You actually might switch databases at some point, but in my experience this always included overriding some of the ORM-queries with custom ones that the particular RDBMS needed for some reason.
So you have to write SQL after all when switching, might as well do it from the beginning.
Most data I have is not tabular, but tree-like in nature. A course has instances of a course, which have students and assignments. Students have submissions per assignment. You can press this into tables, and then join it back together, but it is a detour. The question for me is not: why use an ORM, but why use an SQL database as the underlying storage.
My reason is that there are no good object databases yet. I would want something that allows you to take your C++ vector or Python list of plain old objects, and stuff it into a data structure:
- with a choice of rowwise (array-of-struct) or columnwise (struct-of-array) storage
- with custom indexes (e.g. not just map<string, MyStruct>, but Database<MyStruct> and tell it to index of propertyA, propertyB, ...)
basically taking performance oriented aspects of relational databases and applying them to an in-language data structure, without leaving the world of trees and objects. Back to the question, an ORM is for me not an alternative to SQL - it is an alternative to dicts and list comprehensions and manual storage.
SQL is not hard, it just sucks. Massively. "So useful" in the same way BASIC was useful for 80's computers.
- You will never switch databases
You might not want, but you might need to
- If you ever get enough scale to require optimization the ORM will be your enemy
BS. Most ORMs do let you drop into SQL, no problem. This is usually one or two queries on a whole project that merit this treatment
If things are slow you can probably optimize stuff at the ORM level itself (or your modeling was done by interns - which would be a problem regardless of ORM or no ORM)
So this is an interesting one. We didn’t switch databases, but deployed the same application in some different infrastructure due to how different companies in our “group” have different setup. So we went from having an API running in Azure connecting to a DB running in MS SQL Server, and had to also deploy that (with some UI changes) to Google Cloud with postgres DB.
It went surprisingly well, but there were a few fiddly bits and I was quite glad I didn’t have to worry about the DB interaction, EF Core handled it for us
Exactly. I'm happy with tsql [0] - template based, with safe sanitation, helper renderers/combinators, used in production for several years, would recommend this approach. It's basically pure sql with safe helpers when you want to use them. You can't use orm if you want to use underlying database beyond common denominator for basics.
There's a reason why the modern approach is parameterized: The old PHP-style templating led to galore SQL injections, and worse performance. Don't see any reason to ever go back.
> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd
I have a number of hobby projects that work with both SQLite and Postgres by virtue of a ORM. SQLite is great for testing and lightweight installs, while Postgres scaled much better.
So yes, there are absolutely use-cases where the ability to switch DBs is very valuable.
I also dislike ORM, mostly because it takes what I like (beautifully normalized relational data) and squishes it into ugly object graphs. No need for ORM if you don't do OOP.
But if I have to do OOP on data from database I much prefer ORM over doing it manually.
My basic complaint about ORM is that it is such a massively leaky abstraction. That the (sometimes, but not always-made) claim that it 'abstracts away the sql' is just plain false. An understanding of SQL is typically necessary. Many ORMS are introduced by saying 'this expression translates to that sql'. Oops, no abstraction there. I like this sketch of objection because it's in some respects just friendler sql syntax.
I agree - I would much rather write SQL. But like others have said in this thread, I hate writing custom code to translate anything but the simplest queries into what I actually need.
I also hate trying to combine slightly different queries with custom string manipulation that turns into 750 lines of code before you know it. Most ORMs handle that use case very well.
I have never ever met anyone who genuinely used ORM claim that in any form of shape. The only ever place where I’ve seen it is threads about “how bad ORMs are”, so it is only a strawmen.
ORMs are first and foremost a tool for OLTP-based workflows, helping mapping between objects and db rows, especially for insertions. They are also very great for CRUD operations on tables. They were never meant to abstract away the SQL model, that is one necessarily has to know the underlying tables, and often write queries with manual joins, etc (many ORMs allow custom queries in a cross-DB way). But even in these cases mapping the result is very convenient with an ORM.
I hate the complaint that everything is a "leaky abstraction".
Can't we just just accept a tool that augments and improves something without caring that it's not a complete and perfect abstraction over another technology? I use an ORM every day and I also use SQL every day. The ORM is such an improvement for what it does that of course I would use it. But I'm not restricted to using it. In fact, most ORMs encourage mixing SQL with their API; that's not a leaking abstraction -- it's bringing the fire hose.
Yes, I totally agree. Something isn't working? Oops, now you still gotta look at the SQL!
Also...
I've seen several anti-pattern usages where someone would query a list of IDs, and then in a loop with an accumulator fetch data from another table, one ID at a time.
If you don't grok joins, and someone tells you "yeah whatever don't bother learning SQL, we have an ORM", this happens.
My problem wit ORMs is that majority of them assumes 1–to-1 correspondence between table-rows and objects. And forces me to build a layer on top which glues pieces of object-graphs into higher level abstraction. I often feel that writing custom mappers which skip this lower level works better. Original DataMapper and DataAccessObject patterns have this flexibility in mind, but are rarely used these days
That's interesting. I use Knex a lot and I agree that it's very easy to end up just writing what is basically an ORM on the spot. I haven't quite decided if I think it's a real problem though.
I created and use Kanel ([link redacted]) to generate Typescript types from my Postgres database which helps a lot. Knex itself has some attempt at type safety but it gets confused quite quickly, so that's where it's tempting to override things.
I really hope the community converges on just one common solution to this as I see soo many different approaches to the same “there’s no great modular ORM for typescript” problem.
I myself went the route of “lets just have raw sql queries” but extract distinct and specific types from them automatically- https://github.com/ivank/potygen
Great thing is that it handles sql of any complexity - CTEs, views, nested selects/joins/unions/custom functions you name it. All tools you can use to encapsulate logic in sql itself (what ORM were supposed to be for) and then have it be statically validated at compile time thus saving you the need for righting all those trivial unit tests that was also one of the key benefits of ORMs.
Admittedly if there was something like LINQ I would probably not have ventured into building potygen, but I’m glad I did as I learned so much about SQL as a language and what it had to offer - its silly how much logic I used to rely on writing in code that could easily be handled by sql in a much mode concise way.
Looks nice and I appreciate the README - those comparison tables are much more elaborate than my sibling comment, though looking at the single con in the table for potygen itself does make me wonder if they might be a little biased ;-)
That looks very neat! I use libpg_query to parse view definitions to create type information from there, but it's quite basic so far. Are you doing something similar, or did you implement the parser from scratch?
I'm currently observing the same with a client, also using Knex with Typescript. They're trying to work with the Knex-provided types, and built a (currently still small) set of functions for shared logic like wrapping into a transaction. Those require a non-trivial amount of type-juggling and I'm not sure yet whether I like it. I've seen both plain SQL and ORMs in larger code bases, choosing between those and the "query builder with some custom abstractions" is hard.
Compared to plain SQL, the obvious advantage is that you don't have to work with string replacements, but a bit of readability/maintainability is lost because of emerging complexity. Compared to an ORM there is much less working around it's quirks, but also the danger of re-inventing a good part of it.
Btw they also wrote up a sizeable number of record types, Kanel looks interesting for auto-generating those - thanks!
Would it be fair to say that Kanel is similar to half of my library Zapatos, which generates TS types from Postgres, but then also gives you some simple tools to use them in querying the database?
I've been dabbling with Prisma after almost a decade of staying away from ORMs such as EJB3 / Hibernate / iBatis.
With Prisma I like the ability to generate migrations and the DB schema from a single schema definition file. I also like the autogenerated simple crud use cases.
It did not take long to have to go raw. A simple case of selecting the top most occurring unique columns from a table with a join to limit by user id is brutally trivial oneliner in SQL that just rolls off the keyboard. I took a good try to do the same with Prisma and I believe it can be done, but the complexity of what the DSL will look like is laughable.
So I still have the same opinion I've had for these past years, ORMs are okay for simple crud stuff but the moment you need something slightly beyond that bailing for the raw sql is the best thing that you can possibly do for the maintenance team that comes after you have changed projects.
I like the premise, that query-builders are lower-abstraction hearts of how programming languages and SQL can meet. In my limited experience though, I feel like there's almost always very painful limits to query-builders and it's unclear where the boundaries are.
I wish I knew more good options to try, but in JS, I've tangled with Knex (and some Bookshelf, which I have over time felt compounds the problems), and while it usually starts off okay but repetitive, with little shared code, it's rarely clear whether what you want to do is just impossible without gratuitous .raw() madness spiced in or not.
I've seen too many engineers try to out-engineer the limited help something like Knex can offer, try to make better libraries of tools to do common queries. These confound & limit, again and again, provide long term pain, suffering, & misery for everyone who latter stumbles upon them. There's good heart here, but we'd have been better served by a library of knex and/or sql queries to derive from & rewrite, rather than these painful attempts to write higher level tools, which always fall short of the in-practice needs. The sheer expressiveness of sql keeps feeling unmatched, and attempts to capture the main cases keep becoming painful stumbling blocks that limit comprehension & growability. SQL keeps feeling more appealing.
I am very good at SQL and loved composing queries in a query browser and then using those in a project. its easy to debug and optimize. Havnig said that its all like for nothing going to great lengths for an idea which may end up used by no one.
Thinking of using prims ORM for my next side project.
Readit News
- SQL is so easy and so useful that being a professional engineer that can code but can't learn SQL is absurd
- You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd
- If you ever get enough scale to require optimization the ORM will be your enemy
In summation, anything related to ORMs and SQL is immediately invalid, other than questions like "Help! I joined a company where they mandate ORMs and I hate my life!"
- saving object graphs: it's trivial to write SQL for a simple CRUD form. It's far more difficult to work with real models where you have to deconstruct the form the user posted into a dozen tables with joins between them, doing insert/update/delete as needed. With a decent ORM you simply construct an object graph by assigning fields (the result can be very complicated), shove that into the database, and the ORM takes care of the rest. It may not be web scale™, but many of us don't need that.
- constructing queries on the fly: if you ever get any exposure to LINQ, I don't think you'll want to go back to SQL after that. It allows you to easily construct queries based on user input (in a type-safe way, unlike some other solutions), which saved me countless hours when building complicated tables with search and filtering on many (sometimes hundreds) of fields.
Here is a trivial example: https://stackoverflow.com/a/10884757
And with good ORMs like Entity Framework the underlying queries they generate are predictable.
Right up until they are not and generate crap. I went from "predictable" EF generated queries to handcrafted ones reduced the load on the DB by about 70% while having more queries per second...
On "great" side of things, Linq brought massive type safety to .net land, and made queries also compile time checked.
IS it leaky? yes. Every once in a while you'll need to write SQL. Does it solve a lot of problems? Also yes.
Disclaimer: I was once a contributor to NHibernate.
Such a waste of time to maintain that conversion code by hand.
Alas, it's far from a complete solution. It's too easy to type an expression that compiles but blows up at runtime because the underlying provider doesn't support it or gets confused.
- Convenience of using SQL from $LANGUAGE where mapping the result, (re)naming, etc. is handled for you. Do this manually, and eventually you will either make silly mistakes or create your own mini-framework.
- Connection pooling / retrying if desired. Can be done without, but again mini-framework.
- Migrations. Generating a schema from code is neat.
- Making our lives miserable when the ORM inevitably generates horrible SQL somewhere and we need to override it manually. That is, ORMs are NOT a replacement for learning SQL.
In my experience Java domain code written with an ORM is about 4x the LOC compared to using stored procedures to do the same job.
Oh - and that 4x LOC gets you about 1/4 the performance, loosely speaking.
I am a substantially more productive developer since I kicked all those abstractions to the kerb.
It is the database and its structure that is the important part of an application. Building an additional layer of abstraction on top of an already existing abstraction no longer makes sense to me.
We used SQLAlchemy on a Python project which allowed us to use an in-memory SQLite DB in our integration tests. This gave us blazingly fast database tests during development where a database was torn down and recreated, with data, for each test in milliseconds. When the commits were pushed, CI would run the tests against SQLite and MySql.
It worked extremely well and would be a tremendous amount of effort to handwrite SQL for both DBs. I'd do it on ever project if I could.
I use pg and test suites run in parallel against their own db. It’s pretty cheap with CREATE DATABASE helpers plus using container means little to no headache.
The main reason to write mostly generic SQL is so new hire-ees can get up to speed.
Not sure if that's enough to justify ORMs, but DB migration is a real use case and there are things you can do ahead of time to greatly simplify the process.
No, you may never have had to switch databases, but I have worked on codebases where we've had to switch databases, and I'm sure others have to. Most likely when dealing with monoliths where you can't do a full re-write and you can't break out a separate service for various reasons. The repository pattern is your friend in these situations. An ORM should ideally be able to read/write to multiple database drivers which makes this easy by dual-writing to both databases from the repository layer.
Good luck doing that with hand-coded SQL.
But for me 99% of the benefit of using an ORM is compile-time checking of queries. I don't see why in principle that couldn't be possible using raw SQL, but I don't know of any good examples of it.
One of the main reasons that Django has been so popular for so long is the idea of reusable "apps". Eg, Django Admin, which comes bundled with Django but there are also thousands of 3rd party ones that you can use to quickly put together very powerful sets of features out of polished, tested pieces. That whole ecosystem really only works because of the ORM, which means that apps mostly don't have to know/care what database might be used. (I assume some other frameworks have similar stories, but Django's what I'm most familiar with).
As far as scaling, I've developed and supported Django apps that have hundreds of thousands of users and serve thousands of requests per second. I would probably not even put the ORM in the top ten list of problems with achieving scale. I've occasionally found it generating slow queries and had to tweak or bypass it (both extremely easy to do) but honestly, those have been low hanging fruit when it comes to optimizing (and very easy to find thanks to reusable apps like `django-debug-toolbar`, which exists at least in part because of the ORM). Are ORMs unteneble once you're in the millions of users or tens of thousands of requests per second range? Maybe. But that's a very small part of the software landscape and for the vast, vast majority of the rest of us, a good ORM isn't going to be the problem.
I use Django a lot and have found the ORM invaluable for all use cases and can be optimised when needed, with some friction.
You actually might switch databases at some point, but in my experience this always included overriding some of the ORM-queries with custom ones that the particular RDBMS needed for some reason.
So you have to write SQL after all when switching, might as well do it from the beginning.
My reason is that there are no good object databases yet. I would want something that allows you to take your C++ vector or Python list of plain old objects, and stuff it into a data structure:
- with a choice of rowwise (array-of-struct) or columnwise (struct-of-array) storage
- with custom indexes (e.g. not just map<string, MyStruct>, but Database<MyStruct> and tell it to index of propertyA, propertyB, ...)
basically taking performance oriented aspects of relational databases and applying them to an in-language data structure, without leaving the world of trees and objects. Back to the question, an ORM is for me not an alternative to SQL - it is an alternative to dicts and list comprehensions and manual storage.
SQL is not hard, it just sucks. Massively. "So useful" in the same way BASIC was useful for 80's computers.
- You will never switch databases
You might not want, but you might need to
- If you ever get enough scale to require optimization the ORM will be your enemy
BS. Most ORMs do let you drop into SQL, no problem. This is usually one or two queries on a whole project that merit this treatment
If things are slow you can probably optimize stuff at the ORM level itself (or your modeling was done by interns - which would be a problem regardless of ORM or no ORM)
So this is an interesting one. We didn’t switch databases, but deployed the same application in some different infrastructure due to how different companies in our “group” have different setup. So we went from having an API running in Azure connecting to a DB running in MS SQL Server, and had to also deploy that (with some UI changes) to Google Cloud with postgres DB.
It went surprisingly well, but there were a few fiddly bits and I was quite glad I didn’t have to worry about the DB interaction, EF Core handled it for us
I have never seen argument “I use ORM so I don’t need to know SQL”.
It is always people who boast they know SQL that they don’t need to know ORM.
[0] https://github.com/appliedblockchain/tsql
There's a reason why the modern approach is parameterized: The old PHP-style templating led to galore SQL injections, and worse performance. Don't see any reason to ever go back.
I have a number of hobby projects that work with both SQLite and Postgres by virtue of a ORM. SQLite is great for testing and lightweight installs, while Postgres scaled much better.
So yes, there are absolutely use-cases where the ability to switch DBs is very valuable.
But if I have to do OOP on data from database I much prefer ORM over doing it manually.
Actually, knowing how to write efficient SQL is very well rewarded by the market.
So adding another layer on top of it, an ORM, is IMHO not a good idea.
I agree - I would much rather write SQL. But like others have said in this thread, I hate writing custom code to translate anything but the simplest queries into what I actually need.
I also hate trying to combine slightly different queries with custom string manipulation that turns into 750 lines of code before you know it. Most ORMs handle that use case very well.
I have never ever met anyone who genuinely used ORM claim that in any form of shape. The only ever place where I’ve seen it is threads about “how bad ORMs are”, so it is only a strawmen.
ORMs are first and foremost a tool for OLTP-based workflows, helping mapping between objects and db rows, especially for insertions. They are also very great for CRUD operations on tables. They were never meant to abstract away the SQL model, that is one necessarily has to know the underlying tables, and often write queries with manual joins, etc (many ORMs allow custom queries in a cross-DB way). But even in these cases mapping the result is very convenient with an ORM.
Can't we just just accept a tool that augments and improves something without caring that it's not a complete and perfect abstraction over another technology? I use an ORM every day and I also use SQL every day. The ORM is such an improvement for what it does that of course I would use it. But I'm not restricted to using it. In fact, most ORMs encourage mixing SQL with their API; that's not a leaking abstraction -- it's bringing the fire hose.
Also...
I've seen several anti-pattern usages where someone would query a list of IDs, and then in a loop with an accumulator fetch data from another table, one ID at a time.
If you don't grok joins, and someone tells you "yeah whatever don't bother learning SQL, we have an ORM", this happens.
An ORM is an anti-tool.
I created and use Kanel ([link redacted]) to generate Typescript types from my Postgres database which helps a lot. Knex itself has some attempt at type safety but it gets confused quite quickly, so that's where it's tempting to override things.
I myself went the route of “lets just have raw sql queries” but extract distinct and specific types from them automatically- https://github.com/ivank/potygen
Great thing is that it handles sql of any complexity - CTEs, views, nested selects/joins/unions/custom functions you name it. All tools you can use to encapsulate logic in sql itself (what ORM were supposed to be for) and then have it be statically validated at compile time thus saving you the need for righting all those trivial unit tests that was also one of the key benefits of ORMs.
Admittedly if there was something like LINQ I would probably not have ventured into building potygen, but I’m glad I did as I learned so much about SQL as a language and what it had to offer - its silly how much logic I used to rely on writing in code that could easily be handled by sql in a much mode concise way.
Compared to plain SQL, the obvious advantage is that you don't have to work with string replacements, but a bit of readability/maintainability is lost because of emerging complexity. Compared to an ORM there is much less working around it's quirks, but also the danger of re-inventing a good part of it.
Btw they also wrote up a sizeable number of record types, Kanel looks interesting for auto-generating those - thanks!
https://jawj.github.io/zapatos/
With Prisma I like the ability to generate migrations and the DB schema from a single schema definition file. I also like the autogenerated simple crud use cases.
It did not take long to have to go raw. A simple case of selecting the top most occurring unique columns from a table with a join to limit by user id is brutally trivial oneliner in SQL that just rolls off the keyboard. I took a good try to do the same with Prisma and I believe it can be done, but the complexity of what the DSL will look like is laughable.
So I still have the same opinion I've had for these past years, ORMs are okay for simple crud stuff but the moment you need something slightly beyond that bailing for the raw sql is the best thing that you can possibly do for the maintenance team that comes after you have changed projects.
I wish I knew more good options to try, but in JS, I've tangled with Knex (and some Bookshelf, which I have over time felt compounds the problems), and while it usually starts off okay but repetitive, with little shared code, it's rarely clear whether what you want to do is just impossible without gratuitous .raw() madness spiced in or not.
I've seen too many engineers try to out-engineer the limited help something like Knex can offer, try to make better libraries of tools to do common queries. These confound & limit, again and again, provide long term pain, suffering, & misery for everyone who latter stumbles upon them. There's good heart here, but we'd have been better served by a library of knex and/or sql queries to derive from & rewrite, rather than these painful attempts to write higher level tools, which always fall short of the in-practice needs. The sheer expressiveness of sql keeps feeling unmatched, and attempts to capture the main cases keep becoming painful stumbling blocks that limit comprehension & growability. SQL keeps feeling more appealing.
Thinking of using prims ORM for my next side project.