search

prisma / prisma

Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB
https://www.prisma.io
Apache License 2.0
38.87k stars 1.52k forks source link

`prisma migrate` improved UX #9336

Open lirancr opened 2 years ago

lirancr commented 2 years ago

Problem

Although Prisma migrate is still young and is a great start i would not consider it production ready. I've been working with database over django backends for a few years and encountered multiple scenarios in which the current migration tool just wont cut it:

  1. Migrations are not transactional - When a migration fail it should never leave you database in an unknown state, should should just apply no changes and prompt an error. Leaving a database in an intermediate state is a horrible thing to have on production systems and is not easily recoverable in realtime. Consider a migration file created on a developer machine against local database, locally it run successfully but when deployed it failed. your application is now in a broken state until you manually revert/fix those changes which can take hours.

  2. Migrations are limited to SQL - In certain situations you might want do some more complex operations/transformations on your data during migration (for the sake of simplicity lets say you want to re-format a text column based on some new pattern your app is expecting). These sort of transformations are either not possible to plain SQL format or just a nightmare to create. This is where other tools (Such as Django and TypeORM) let you write your own migration scripts in plain JS to perform those transformations before saving the changes to the database

  3. Migrations are not reverse-able - although stated as an open issue i still need to stress this out. Yes data loses can occur by rolling back a migration but so does migration upwards. Rollback a migration can come in handy in all sort of situations when a migration is not fully ready and you need to experiment, recreating the entire database every time feels like a backwards solution to this issue.

these are the main issues i have with prisma, its a well thought out ORM solution but i really find these lacking features a blocker for any real project that want's to commit to the ORM

Suggested solution

In order

  1. Wrap migration with transaction syntax on execution on supported database - sure it wont help databases with no transactions but its better than none.

  2. instead of generating an consuming SQL files, generate a JS file with a function that just trigger a raw query with the sql. This makes it clear to developers how to extend the migration system to do complex things on their data. export default () => { prismaClient.$rawQuery('...whatever SQL currently being used...') }

  3. Based on my second proposal you can just as well use the same approach to support rollback migration export default () => { return { up: () => prismaClient.$rawQuery('...whatever SQL currently being used...'), down: () => prismaClient.$rawQuery('...reverse to whatever SQL currently being used...') } }

Alternatives

Currently i haven't found any good alternative to the migration tool which integrate with prisma's DSL as prisma migrate alternatives.

Additional context

I'm somewhat biased by the pleasant experience & trust i've had working with Django's built in ORM (at least in the migrations aspect)

oflynned commented 2 years ago

+1 on this, this is a major point for me that's currently preventing a full Prisma adoption. I'm at the point where I may migrate back to TypeORM for the migration interface.

Jolg42 commented 2 years ago

Note: related https://github.com/lirancr/prisma-rays

tomhoule commented 2 years ago

Hi @lirancr , we answer a bunch of these questions in the internal Migrate FAQ. Happy to start a discussion here if you have questions or objections.

lirancr commented 2 years ago

@tomhoule thanks for the reply. My objection to the current workflow is that from my personal experience, its not true, it might work for some organizations but not all. I guess the built in tool is highly openionated.

I ended up wrapping it with my own implementation to get another openionated workflow. https://github.com/lirancr/prisma-rays

This is far from ideal as some of the limitations of the underlying tool are bleeding into the wrapper (not to mention its just slow because of the excess work it has to do)

I guess if i have one suggestion here is to ask for the prisma schema parser / change detector to be a standalone. I believe we will see many intersting workflows and use cases rise up if people can leverage just the engine that convert prisma schema into sql (and the schema comparitor)

I bet more utility around prisma will increase it's adoption

tomhoule commented 2 years ago

More flexibility and exposing lower level, composable utilities is definitely something we want to work on. We'll get there in time, but it's work.

floelhoeffel commented 2 years ago

Hey @lirancr - we have a proposal around some new migrate commands that might help with some of the problems you described. Please take a look and leave us some feedback: https://github.com/prisma/prisma/issues/10561

lirancr commented 2 years ago

@floelhoeffel that's awesome! I suppose this can easily be incorporated inside a CI pipeline script as any kind of manual recovery is not possible with CI

floelhoeffel commented 2 years ago

@lirancr yes, command should be able to run unattended!

Would you be able to describe the CI/CI process you have in mind to me? That would be helpful 🙇

do4gr commented 2 years ago

Hey, the proposed new commands are now out in 3.9. We've also included a guide how to do up and down migrations. Please check it out and let us know what you think! https://github.com/prisma/prisma/releases/tag/3.9.0