I'll speak to my personal experience...
I became allergic to ORMs after experiencing much of the pain that you describe. Like you, I quickly found ORMs were simply an additional domain language / abstraction over my database that provided more pain that usefulness. Every time I wanted to make I change to the code I had to wade through tons of docs and/or stackoverflow posts by other frustrated users. If I wanted type safety I had to express and maintain types/decoders/encoders myself. Huge pain, and things always got stale leading to a massive mistrust in my data layers.
Prisma doesn't feel like those experiences. Their schema-first, client code gen approach works surprisingly well. Using the generated API feels really intuitive, and TypeScript is there the whole time providing guidance and autocomplete for me. The object tree query syntax is quite refreshing compared to the builder pattern approach taken by the alternatives. I always found the builder pattern overwhelming and often a guessing game at how to compose them.
I think Prisma doesn't try to be too clever with their data API. They solve the 99% in a manner that is simple and convenient, for everything else you have the raw query API - much like other solutions.
I'd suggest giving it a try. You may like it.
One advantage is the additional domain language is also the language of array/list manipulation, and not having to maintain any encoders/decoders (I honestly don't know what these are).
Entity framework follows the unit of work pattern from gang of four, and if I remember correctly eloquent follows the active record pattern.
When I started I found the active record pattern much more intuitive, but I prefer the unit of work pattern now. Mostly because I think unit of work works better with transactions/constraints then active record pattern.
You are talking about one where the goal is to make querying the database more idiomatic on your development language, at the cost of some flexibility. This works very well as long as you stay within the bounds of the abstraction, and breaks terribly when you step out of it. The engineering goal is to make the abstraction just broad enough to represent most of the common queries without making it less idiomatic.
The second kind is the type that tries to abstract databases into a specialized query language. The goal here is to bring things you don't get on plain SQL (like type integration or a single DBMS independent language) without losing expressive power. That's the one the GP is talking about.
Maybe you mean something else, but I haven't had any issues when I've had to break out of the ORM and write portions in SQL. (usually once or twice every couple of development man years)
I'm not sure what type integration is, but the ORM I'm most familiar with does allow spanning multiple DBMS's with the same code. (unless when you had to break into DBMS specific SQL for performance reasons)
Is type integration allowing static type checks against your query language? Entity Framework does this as well.
I think EF is the second type of ORM, unless I'm misunderstanding you.
> The problem with this tool, like every other multi-SQL-flavor-SQL ORM and query builder, is that it requires users to learn yet another language. In addition to Node.js and SQL, users need to learn the Prisma query language. This is not trival, and users that are already accustomed to working with SQL will need to relearn PrismaSQL.
I'm not sure I'd fully agree with this! The "other language" in this case is an intuitive and natural API (in Node.js/TypeScript) for querying data [1], so hopefully, there won't be much overhead to "learn" anything new. It should be rather the opposite and pretty straightforward to pick up, auto-completion and type-safety will also contribute to making the experience of querying data fluent without much learning overhead.
We specifically decided to abstract away from SQL because we found that many developers don't feel productive with SQL as their main database abstraction [2] (that's also why so many people roll their own data access layers in the end).
> I think the best approach to this problem is a single-SQL-flavor query builder that attempts to match SQL as closely as possibly while adding in the niceties of being able to pass in JavaScript objects instead of raw SQL strings. Lets be honest: raw SQL-in-JS is no fun.
It sounds like your thinking is generally aligned with ours actually! The main difference is that we concluded that the query builder shouldn't be SQL-flavored but just a natural API for any Node.js or TypeScript devs.
[1] https://www.prisma.io/blog/announcing-prisma-2-n0v98rzc8br1#...
[2] https://www.prisma.io/docs/understand-prisma/why-prisma#appl...
Prisma's approach is well thought out, and I appreciate the new angle on an old and challenging problem. For Typescript users, especially new users, Prisma can be a big win. Abstracting SQL has huge benefits here (especially type safety/static analysis), and I look forward to seeing where this project goes.
That being said, my favorite database is PostgreSQL because it has so many features (and I'm just comfortable with it). At some point, a tool like Prisma (or Knex, TypeORM, etc) just cannot support all PostgreSQL features because it needs to support other flavors too. While some users may find this trade off acceptable, I always find myself hacking around the tool to use the raw features. Therefore, my ideal environment would be a full-featured PostgreSQL query builder.
TL;DR I see the benefits of Prisma, but they're not for me at this point
I’m thinking of porting a project from Firebase to Postgres and whether to use an ORM and which one are hotly contested points in my research thus far.
If you want "more ORM" then Knex then `Objection.js` is a good option. I think the other main option in node land is TypeORM. Either of there is probably a good choice.
You essentially describe Elixir's Ecto, which has been lovely to work with. You may want to check it out.
There is no query based language, the runtime API is generated from your DB layer, and is fully TS/JS, so there is no new language to learn. It is just TS/JS.
You don't know which API to use? Type a dot and all APIs is there, this is called Intellisense.
So, it is not `like every other multi-SQL-flavor-SQL ORM and query builder`
`I think the best approach to this problem is a single-SQL-flavor query builder that attempts to match SQL as closely as possibly`, you do not work with GraphQL, do you...
Knex. I think Objection.js is much better.
It has type checked queries in plain SQL (based on SQLite syntax), compile time consistency checks, autocomplete, schema migrations, and more. The normal queries have all the guarantees, but in case you might want to use some vendor specific features, it also has the option of vendor queries.
Very very cool library.
The problem with this tool, like every other multi-SQL-flavor-SQL ORM and query builder, is that it requires users to learn yet another language. In addition to Node.js and SQL, users need to learn the Prisma query language. This is not trival, and users that are already accustomed to working with SQL will need to relearn PrismaSQL.
I think the best approach to this problem is a single-SQL-flavor query builder that attempts to match SQL as closely as possibly while adding in the niceties of being able to pass in JavaScript objects instead of raw SQL strings. Lets be honest: raw SQL-in-JS is no fun.
This would lead to PostgreSQL, MYSQL, SQLite, etc-specific query builders. Knex is close, but it ultimately doesn't work for most because it's missing some language-specific features (e.g. ON CONFLICT DO UPDATE). While this doesn't exactly meet the type safety benefits of Prisma, the benefits in ease of use and feature-parity of a language-specific query builder far outweigh the difficulties of learning a new query language like Prisma.