8 ms·

We migrated to SQL. Our biggest learning? Don't use Prisma ORM

19 points by droptablemain 3y ago

catlover76 3y ago
Not surprising for a product/library that far down on the "magic" scale. I don't know what it is about the TypeScript/JavaScript/NodeJS world that keeps seeking ever more magical and oppressive and comprehensive solutions to accomplish tasks and goals that software has been capable of achieving for some time now. Knex is a good ORM for a NodeJS back-end. It's simple and generic. It does the job while giving developers sufficient flexibility. I just can never see the appeal of things like Prisma.
- trinovantes 3y ago
  drizzle-orm is another good JS sql query builder I'm able to write raw sql queries with the `sql` template literal function https://orm.drizzle.team/docs/sql https://orm.drizzle.team/docs/sql
  - catlover76 3y ago
    Looks cool, thanks for sharing
  - droptablemain 3y ago
    +1 for Drizzle. I was really drawn to Prisma because it seemed elegant, but the problems surfaced by this blog post are really concerning.
  - joshxyz 3y ago
    there's also https://kysely.dev/ https://kysely.dev/ but personally i handwrite my queries with https://github.com/porsager/postgres https://github.com/porsager/postgres for flexibility and performance most orms use node-pg lib which has shit performance.
    - ShadowBanThis01 3y ago
      I'm hand-writing stuff right now for Deno with MariaDB. Is there a "default" or "native" Postgres client, and what makes Porsager better if so?
      - joshxyz 3y ago
        There's a core client interface here: - https://www.postgresql.org/docs/current/client-interfaces.html https://www.postgresql.org/docs/current/client-interfaces.ht... On what makes it postgres.js faster, from author himself: > it seems Postgres.js is actually faster than, not only pg, but of any driver out-there - https://github.com/porsager/postgres/discussions/627 https://github.com/porsager/postgres/discussions/627 - https://porsager.github.io/imdbench/sql.html https://porsager.github.io/imdbench/sql.html
        ShadowBanThis01 3y ago
        Thanks for the reply!
- chrisandchris 3y ago
  > Not surprising for a product/library that far down on the "magic" scale. [...] Using MongoDb with AWS Lambda and join it with 10 another services to serve a GraphQL endpoint just sounds much better than any PHP/Python REST API backed by MySQL. No wonder you'll end up from time to time with things you'll deeply regret.
- sgarland 3y ago
  Because the entire Node ecosystem is deeply unserious. The base premise of “let’s use a frontend language which asserts that 1+one == 1one as a backend” is fundamentally flawed. That, and the existence and popularity of things like left-pad, speaks volumes.
  - toastercat 3y ago
    This is an outdated take. The left-pad incident was 7 years ago, and newer mainstream packages are generally doing a better job curating dependencies. That said, it is true that the Node/JS world is primarily hype-driven by "thought leaders" and people seeking personal brand and clout. Go on JS/React Twitter and prepare to loudly groan at the self importance on display. There are good packages out there that try to do things in a sane way without wasting your time. Postgres.js [1] is one of them. [1] https://github.com/porsager/postgres https://github.com/porsager/postgres
- SebastianKra 3y ago
  I'm not even old and I've already heard this a million times: "Nothing wrong with software development anymore. So let's just solve the same problems and write the same workarounds over and over again, while complexity explodes". I can absolutely see the appeal of Prisma, even though I wouldn't use it. The only Problem with the JS ecosystem, is that we need more discussion of the drawbacks, like this article. Too often, a new solution is paraded as the definitive next step, before we can even know that. On the other hand it's neat that I can mix and match my dependencies based on how progressive/conservative I want to be. (Knex isn't an ORM)
polishdude20 3y ago
We migrated from knex to Prisma and so far, I haven't seen any benefits.
DemocracyFTW2 3y ago
There is no concept of SQL-level joins in Prisma. This was one of the most shocking revelations to us. In some queries we inspected that supposedly should have used SQL Joins or subqueries, we discovered that at a low level, Prisma was fetching data from both tables and then combining the result in its “Rust” engine. This was a path for an absolute trash performance. This is terrifying. OTOH they were coming from MongoDB, so what were they expecting? A sane DB layer? /s
- sgarland 3y ago
  This is absolutely awful, and anyone who chose it as an ORM either didn’t read the docs, or didn’t understand the implications. “I’ll just SELECT * from all these tables, and figure out what I want in my app.”
  - DemocracyFTW2 3y ago
    Right, who needs an SQL `join` when you can do all your `join`s in Ruby, JS, Python whatever? What? SQL?
mannyv 3y ago
We started off using prisma, but now we only use it to manage our schemas. Migrations fail all the time, so we just do a db push. And yes, we tried to reset our migrations as per the docs. Doesn'tmwork. FYI, if you use findMany it pulls the ids then uses the ids for its update. They consider using UPDATE an optimization request. WTF?
kebsup 3y ago
I don't understand why raw SQL is considered less productive. I bet anything with more than two joins is easier to write in raw SQL than any ORM.
- fragmede 3y ago
  SQL is declarative while most other languages are procedural. An ORM lets you pretend that the database speaks a procedural language, which lets you build the query procedurally, which may be easier to write.
- sgarland 3y ago
  I think Django is roughly the same level of complexity, once you’ve learned its syntax of course. Actually, it may be too easy in some regards (thinking specifically of implicit joins), such that unaware devs may not realize that they’re causing performance issues. In general though, I agree with your premise - I don’t understand why people are so reluctant to learn SQL. It’s not a difficult language. Understanding the quirks of your RDBMS, sure. But the base language? Nah.
- AirMax98 3y ago
  Raw SQL will probably not give you the type safety of an ORM.
  - kebsup 3y ago
    I use intellij, which gives basic type warnings, plus I generate Typescript types from db, which I assign to the query. I don't know what more could I need. :D

19 points by droptablemain 3y ago

Not surprising for a product/library that far down on the "magic" scale. I don't know what it is about the TypeScript/JavaScript/NodeJS world that keeps seeking ever more magical and oppressive and comprehensive solutions to accomplish tasks and goals that software has been capable of achieving for some time now. Knex is a good ORM for a NodeJS back-end. It's simple and generic. It does the job while giving developers sufficient flexibility. I just can never see the appeal of things like Prisma.

trinovantes 3y ago
drizzle-orm is another good JS sql query builder I'm able to write raw sql queries with the `sql` template literal function https://orm.drizzle.team/docs/sql https://orm.drizzle.team/docs/sql
- catlover76 3y ago
  Looks cool, thanks for sharing
- droptablemain 3y ago
  +1 for Drizzle. I was really drawn to Prisma because it seemed elegant, but the problems surfaced by this blog post are really concerning.
- joshxyz 3y ago
  there's also https://kysely.dev/ https://kysely.dev/ but personally i handwrite my queries with https://github.com/porsager/postgres https://github.com/porsager/postgres for flexibility and performance most orms use node-pg lib which has shit performance.
  - ShadowBanThis01 3y ago
    I'm hand-writing stuff right now for Deno with MariaDB. Is there a "default" or "native" Postgres client, and what makes Porsager better if so?
    - joshxyz 3y ago
      There's a core client interface here: - https://www.postgresql.org/docs/current/client-interfaces.html https://www.postgresql.org/docs/current/client-interfaces.ht... On what makes it postgres.js faster, from author himself: > it seems Postgres.js is actually faster than, not only pg, but of any driver out-there - https://github.com/porsager/postgres/discussions/627 https://github.com/porsager/postgres/discussions/627 - https://porsager.github.io/imdbench/sql.html https://porsager.github.io/imdbench/sql.html
      - ShadowBanThis01 3y ago
        Thanks for the reply!
chrisandchris 3y ago
> Not surprising for a product/library that far down on the "magic" scale. [...] Using MongoDb with AWS Lambda and join it with 10 another services to serve a GraphQL endpoint just sounds much better than any PHP/Python REST API backed by MySQL. No wonder you'll end up from time to time with things you'll deeply regret.
sgarland 3y ago
Because the entire Node ecosystem is deeply unserious. The base premise of “let’s use a frontend language which asserts that 1+one == 1one as a backend” is fundamentally flawed. That, and the existence and popularity of things like left-pad, speaks volumes.
- toastercat 3y ago
  This is an outdated take. The left-pad incident was 7 years ago, and newer mainstream packages are generally doing a better job curating dependencies. That said, it is true that the Node/JS world is primarily hype-driven by "thought leaders" and people seeking personal brand and clout. Go on JS/React Twitter and prepare to loudly groan at the self importance on display. There are good packages out there that try to do things in a sane way without wasting your time. Postgres.js [1] is one of them. [1] https://github.com/porsager/postgres https://github.com/porsager/postgres
SebastianKra 3y ago
I'm not even old and I've already heard this a million times: "Nothing wrong with software development anymore. So let's just solve the same problems and write the same workarounds over and over again, while complexity explodes". I can absolutely see the appeal of Prisma, even though I wouldn't use it. The only Problem with the JS ecosystem, is that we need more discussion of the drawbacks, like this article. Too often, a new solution is paraded as the definitive next step, before we can even know that. On the other hand it's neat that I can mix and match my dependencies based on how progressive/conservative I want to be. (Knex isn't an ORM)

polishdude20 3y ago

We migrated from knex to Prisma and so far, I haven't seen any benefits.

DemocracyFTW2 3y ago

There is no concept of SQL-level joins in Prisma. This was one of the most shocking revelations to us. In some queries we inspected that supposedly should have used SQL Joins or subqueries, we discovered that at a low level, Prisma was fetching data from both tables and then combining the result in its “Rust” engine. This was a path for an absolute trash performance. This is terrifying. OTOH they were coming from MongoDB, so what were they expecting? A sane DB layer? /s

sgarland 3y ago
This is absolutely awful, and anyone who chose it as an ORM either didn’t read the docs, or didn’t understand the implications. “I’ll just SELECT * from all these tables, and figure out what I want in my app.”
- DemocracyFTW2 3y ago
  Right, who needs an SQL `join` when you can do all your `join`s in Ruby, JS, Python whatever? What? SQL?

mannyv 3y ago

We started off using prisma, but now we only use it to manage our schemas. Migrations fail all the time, so we just do a db push. And yes, we tried to reset our migrations as per the docs. Doesn'tmwork. FYI, if you use findMany it pulls the ids then uses the ids for its update. They consider using UPDATE an optimization request. WTF?

kebsup 3y ago

I don't understand why raw SQL is considered less productive. I bet anything with more than two joins is easier to write in raw SQL than any ORM.

fragmede 3y ago
SQL is declarative while most other languages are procedural. An ORM lets you pretend that the database speaks a procedural language, which lets you build the query procedurally, which may be easier to write.
sgarland 3y ago
I think Django is roughly the same level of complexity, once you’ve learned its syntax of course. Actually, it may be too easy in some regards (thinking specifically of implicit joins), such that unaware devs may not realize that they’re causing performance issues. In general though, I agree with your premise - I don’t understand why people are so reluctant to learn SQL. It’s not a difficult language. Understanding the quirks of your RDBMS, sure. But the base language? Nah.
AirMax98 3y ago
Raw SQL will probably not give you the type safety of an ORM.
- kebsup 3y ago
  I use intellij, which gives basic type warnings, plus I generate Typescript types from db, which I assign to the query. I don't know what more could I need. :D