In development:

1. Never use db push. Except for prototyping. After the first migration, you should use prisma migrate dev instead.
2. Once you have done your first migration, use prisma migrate dev. It will warn you about any data loss and it will ask you what's the purpose of the migration (like a git commit -m). At the end, it will create a new migration file and apply it to your development database.
3. If you have to edit a migration, to prevent data loss, have a look to this documentation. Here is the process:
   1. Use prisma migrate dev --create-only.
   2. Edit the autogenerated migration file (you will need to write some SQL yourself)
   3. Then apply your edited migration with prisma migrate dev.

With this process, you will have a migrations folder with all your migrations that will keep track of your database schema evolution. You will be able to rollback to any previous migration if you need to and you will be able to deploy your migrations to your production database.

In staging / preview / production:

All staging / preview / production environments work the same way.

In your CI/CD, you will have to run prisma migrate deploy instead of prisma migrate dev. This command accept data loss and will not prompt you for a migration name (to avoid blocking your building pipeline).

This command will apply all the migrations that are not applied yet.

Hope it will help!

For more infos, have a look to this documentation as well.

They serve two different environments. The prisma db push is not to be used in your production environment, as stated in the docs

db push uses the same engine as Prisma Migrate to synchronize your Prisma schema with your database schema, and is best suited for schema prototyping. The db push command:

1. Introspects the database to infer and executes the changes required to make your database schema reflect the state of your Prisma schema.

2. By default, after changes have been applied to the database schema, generators are triggered (for example, Prisma Client). You do not need to manually invoke prisma generate.

3. If db push anticipates that the changes could result in data loss, it will:

• Throw an error
• Require the --accept-data-loss option if you still want to make the changes

Note: db push does not interact with or rely on migrations. The migrations table will not be updated, and no migration files will be generated.

The prisma migrate dev is used in you local environment, as explained in the docs

migrate dev is a development command and should never be used in a production environment.

This command:

1. Replays the existing migration history in the shadow database in order to detect schema drift (edited or deleted migration file, or a manual changes to the database schema)
2. Applies pending migrations to the shadow database (for example, new migrations created by colleagues)
3. Generates a new migration from any changes you made to the Prisma schema before running migrate dev
4. Applies all unapplied migrations to the development database and updates the _prisma_migrations table
5. Triggers the generation of artifacts (for example, the Prisma Client)

The migrate dev command will prompt you to reset the database in the following scenarios:

• Migration history conflicts caused by modified or missing migrations
• The database schema has drifted away from the end-state of the migration history

If you have any other question regarding this, there is this comparison in the docs explaining when to use one or the other.