Hello @lumenwrites

npx prisma db push is meant for development use only. The development db is considered "resettable" and "seedable" as its used to develop your migrations and to test/tweak them if needed. Looks like you have used it in your production environment. In production, you should use npx prisma migrate deploy and it would ensure your db won't get reset in any case.

Now in order to resolve your current situation we need to tweak the migrations history using some primitives which migrate provides like the diff command(https://www.prisma.io/docs/reference/api-reference/command-reference#migrate-diff)

1. You will need to generate a new migration which will be the difference between migrations history stored in your disk in the migrations folder and your production database. The --to-schema-datasource flag here point to the database stored in your .env file but you can also change this flag to --to-url if you prefer to give the database url in the command itself.

mkdir -p prisma/migrations/add-comments-and-post/
npx prisma migrate diff --from-migrations prisma/migrations --to-schema-datasource prisma/schema.prisma --shadow-database-url   --script >  prisma/migrations/add-comments-and-post/migration.sql

The --shadow-database-url parameter is required and you will need to point it to any empty postgres database either a local one or hosted one. Prisma will use that to reply migration history and calculate the diff.

This will create a new migration in prisma/migrations/add-comments-and-post/migration.sql which will have the SQL for the model you have added via db push.

2. Now you have the migration ready, we will need to mark it as applied. This is because those tables already exists in the database and we can't run the actual CREATE sql statements to the database. We will just mark the migration as applied as it was already applied when you ran npx prisma db push.

npx prisma migrate resolve --applied add-comments-and-post

3. Now, you should be ready to go. Run npx prisma migrate dev once now and it should say everything is in sync.

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.

had same issue so change the port to 5432, even in their docs they are mentioning 6543 but this is for Transaction pooler and you need Session pooler which is 5432

I would recommend you to pass the schema file location while invoking npx prisma db push command.

npx prisma db push --schema='/location/to/schema.prisma'

Also, I would recommend you to run the command from root location of your project.

Here's a reference for db push command.

Hi @rainnfx 👋

Thank you for raising this question.

It's not a stupid question at all! Managing schema changes without data loss is a common concern. The prisma db push command is designed to quickly synchronize your Prisma schema with your database schema. However, it does not generate migration files and is best suited for prototyping in a development environment. When prisma db push detects changes that could result in data loss (e.g., adding a required field without a default value), it will prompt you to reset the database.

You should use prisma migrate commands instead of prisma db push. This command helps you create and apply migrations during development. This command helps you create and apply migrations during development.

• Expand and Contract Pattern:

This pattern allows you to evolve your schema without downtime and data loss. It involves making non-destructive changes first (e.g., adding a nullable field) and then gradually evolving the schema.
You can read more about this pattern in the documentation.

Hi @BNK989 👋

Thank you for raising this question.

Can you try to use the direct connection string rather than the pooled one. Here is an example of a direct connection string:

DIRECT_URL="postgres://postgres:__PASSWORD__@db.__YOUR_SUPABASE_PROJECT__.supabase.co:5432/postgres"

The default port for the pooler is 6543, but if you are using the direct connection, it should be 5432. Ensure that you are using the correct port in your connection string.