4 ms·

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.

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.

agustif 6y ago
TypeORM? Zapatos maybe?
- gmac 6y ago
  https://typeorm.io https://typeorm.io https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
  - agustif 6y ago
    Yeah thanks I ever can't remember how to do markdown links so I don't even try
nikolasburk 6y ago
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. [1] https://www.prisma.io/blog/announcing-prisma-2-n0v98rzc8br1#thinking-in-objects-a-natural-and-familiar-query-api https://www.prisma.io/blog/announcing-prisma-2-n0v98rzc8br1#... [2] https://www.prisma.io/docs/understand-prisma/why-prisma#application-developers-should-care-about-data--not-sql https://www.prisma.io/docs/understand-prisma/why-prisma#appl...
- snowoutside 6y ago
  Thanks for your response Nikolas! 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
  - arcturus17 6y ago
    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.
    - nicoburns 6y ago
      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.
ctrlplusb 6y ago
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. I'd suggest giving it a try. You may like it.
- JamesBarney 6y ago
  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).
  - ctrlplusb 6y ago
    The tax of untyped languages and/or simple/generic API abstractions over databases.
  - marcosdumay 6y ago
    Oh, there are two different kinds of ORM. 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.
gajus 6y ago
Specifically in Node.js, https://github.com/gajus/slonik https://github.com/gajus/slonik has drastically improved the experience of writing and composing SQL queries. I am the author of Slonik.
- SirensOfTitan 6y ago
  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.
ssijak 6y ago
JOOQ from jvm world is exactly that, typesafe sql with dsl pretty much the same as good ol’ SQL
galacticdessert 6y ago
What you descrive is very similar to Rezoom.SQL for F#. https://github.com/rspeele/Rezoom.SQL https://github.com/rspeele/Rezoom.SQL 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.
udfalkso 6y ago
"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.
albertgao 6y ago
You need to try it 1st dude... 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.

agustif 6y ago

TypeORM? Zapatos maybe?

gmac 6y ago
https://typeorm.io https://typeorm.io https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
- agustif 6y ago
  Yeah thanks I ever can't remember how to do markdown links so I don't even try

nikolasburk 6y ago

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. [1] https://www.prisma.io/blog/announcing-prisma-2-n0v98rzc8br1#thinking-in-objects-a-natural-and-familiar-query-api https://www.prisma.io/blog/announcing-prisma-2-n0v98rzc8br1#... [2] https://www.prisma.io/docs/understand-prisma/why-prisma#application-developers-should-care-about-data--not-sql https://www.prisma.io/docs/understand-prisma/why-prisma#appl...

snowoutside 6y ago
Thanks for your response Nikolas! 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
- arcturus17 6y ago
  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.
  - nicoburns 6y ago
    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.

ctrlplusb 6y ago

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. I'd suggest giving it a try. You may like it.

JamesBarney 6y ago
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).
- ctrlplusb 6y ago
  The tax of untyped languages and/or simple/generic API abstractions over databases.
- marcosdumay 6y ago
  Oh, there are two different kinds of ORM. 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.

gajus 6y ago

Specifically in Node.js, https://github.com/gajus/slonik https://github.com/gajus/slonik has drastically improved the experience of writing and composing SQL queries. I am the author of Slonik.

SirensOfTitan 6y ago
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.

ssijak 6y ago

JOOQ from jvm world is exactly that, typesafe sql with dsl pretty much the same as good ol’ SQL

galacticdessert 6y ago

What you descrive is very similar to Rezoom.SQL for F#. https://github.com/rspeele/Rezoom.SQL https://github.com/rspeele/Rezoom.SQL 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.

udfalkso 6y ago

"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.

albertgao 6y ago

You need to try it 1st dude... 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.