I always feel like I'm missing something because it looks like you have to learn a specific library instead of a general purpose language.
When I need to add new data to a table I have, I just put the "new_column Type" in my prisma.schema for table X. and run "npx prisma migrate dev." Then everywhere else in my code, I already have the updated type for my object X.new_column and can access it with the guardrails of not betraying what type it is.
I'd rather do that than ALTER TABLE to create and update my read queries, while making sure I don't make a mistake in the raw SQL.
And migrations are a separate feature that a number of ORMs have, you can have clean migrations with pure SQL using a tool like gomigrate.
I think the real utility is that the ORM is doing the marshalling for you.
What's worse is there's no traditional up / down migration for those familiar with knex. There's no "down" at all from what we saw.
There is an issue opened on Drizzle about it and they say they've started work on an up/down migrator, but who knows when that will be done.
https://github.com/drizzle-team/drizzle-orm/discussions/1339
So I've spent the past day converting everything to Kysely instead. ChatGPT was pretty good about converting from Drizzle to Kysely if you fed it the Drizzle schema and repository code.
In terms of developer experience, (maybe we didn't play around with it enough), Drizzle returns things as arrays in postgres, whereas in Kysely you can have it return a single item (or throw it if you want if it can't be found) instead of an array. It's a slight annoyance to have to de-structure the results in drizzle for a single item.
Overall for us, the deal-breaker was the sub-par and too-magical migrations in Drizzle. If you wanted to write custom business logic for your migration, it seems like you had to write it in the generated SQL files directly.
Compare this to Kysely, where you have a knex-style up/down and can write your business logic and migration changes in Typescript.
Drizzle Studio via drizzle-kit, however, is pretty great, and we're continuing to use that since it doesn't require any drizzle-specific schema defs to work.
I would recommend Kysely over Drizzle at this point in time and re-evaluate once Drizzle has a better experience around migrations.
There's a vast array of features and capabilities that can best be made use of by ........ writing SQL.
ChatGPT is incredibly good at writing SQL so its super easy.
AND, BONUS ...... you get to carry accumulated knowledge forward because you are actually learning SQL instead of yet another abstraction.
Write stored procedures - they are awesome, write SQL that returns JSON, use all those weird and wonderful database features like JSON querying storage, indexing and returns, like full text search, write CTE's, write event triggers, write Row Based Access Control, make message queues with SKIP LOCKED. Write Foreign Data Wrappers, make partial indexes and window functions.
Stop being scared of SQL, stop needing to hide behind someone's database abstraction and learn the joy of SQL. Let the database do the work.
Prisma is now taking a lot of ideas from the drizzle team, if I were to start over again I’d just use prisma as it’s actually production ready and is starting to incorporate the good parts of drizzle.
