I empathize with the frustration that this library is trying to solve. It's pretty nifty too! Ultimately, however, I don't believe this is the correct approach.
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.
I get where you are coming from, but from your comment I am guessing you have yet to try Prisma.
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.
That sounds awful, and completely different from my experience with ORMs. My experience has almost exclusively been entity framework, which despite having some warts (rank over partition queries are impossible) has been a very pleasant experience.
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).
Interesting, coming from Eloquent ORM (PHP), I hated Entity Framework. It seemed to want to do far too much clever stuff (like save an entire object graph at once), and didn't have nearly as many escape hatches as I'm accustomed to (Eloquent will let you inject raw SQL nearly anywhere). I've had positive experiences like ORMs, but only when they are thin layers over the underlying SQL.
What was the problem with raw SQL you ran into with Entity Framework?
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.
Nikolas from the Prisma team here, thanks a lot for your comment!
> 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.
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
Do you use an ORM with PostgreSQL in Node.js right now?
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.
You might want to consider Hasura. It's GraphQL layer on top of Postgres with realtime support. We're in the middle of porting a Firebase project to Postgres, and we're using a mix of Hasura, raw SQL, and knex. To be honest, we have more raw SQL (via `knex.raw`) than anything. We use the knex query builder for highly dynamic queries, and Hasura where we need realtime support.
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.
Specifically in Node.js, https://github.com/gajus/slonik has drastically improved the experience of writing and composing SQL queries. I am the author of Slonik.
We use slonik and absolutely love it. Alongside polymode (for inline sql mode) in emacs it’s wonderful to use. Thank you for writing and maintaining such a great library.
"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."
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...
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.
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.