npx prisma db push error after migrating from Planetscale to Neon

[barhouum7]

Question

Hello guys! I've recently migrated my database from Planetscale to Neon, the migration was successful, and everything is okay. But once I tried to run npx prisma db push, I got the following output with warnings and error, even If I change nothing in the schema:

⚠️  There might be data loss when applying the changes:

  • The primary key for the `Billboard` table will be changed. If it partially fails, the table could be left without primary key constraint.
  • The primary key for the `Category` table will be changed. If it partially fails, the table could be left without primary key constraint.
  • The primary key for the `Color` table will be changed. If it partially fails, the table could be left without primary key constraint.
  • The primary key for the `Image` table will be changed. If it partially fails, the table could be left without primary key constraint.
  • The primary key for the `Integration` table will be changed. If it partially fails, the table could be left without primary key 
constraint.
  • The primary key for the `Order` table will be changed. If it partially fails, the table could be left without primary key constraint.
  • The primary key for the `OrderItem` table will be changed. If it partially fails, the table could be left without primary key constraint.
  • The primary key for the `Product` table will be changed. If it partially fails, the table could be left without primary key constraint.
  • The primary key for the `Size` table will be changed. If it partially fails, the table could be left without primary key constraint.
  • The primary key for the `Store` table will be changed. If it partially fails, the table could be left without primary key constraint.


√ Do you want to ignore the warning(s)? ... yes
Error: ERROR: syntax error at or near "RENAME"
   0: sql_schema_connector::apply_migration::migration_step
           with step=AlterTable(AlterTable { table_ids: MigrationPair { previous: TableId(0), next: TableId(1) }, changes: [DropPrimaryKey, RenamePrimaryKey, AlterColumn(AlterColumn { column_id: MigrationPair { previous: TableColumnId(0), next: TableColumnId(6) }, changes: ColumnChanges { type_change: Some(SafeCast), changes: BitFlags<ColumnChange>(0b100, TypeChanged) }, type_change: Some(SafeCast) }), AlterColumn(AlterColumn { column_id: MigrationPair { previous: TableColumnId(1), next: TableColumnId(7) }, changes: ColumnChanges { type_change: Some(SafeCast), changes: BitFlags<ColumnChange>(0b100, TypeChanged) }, type_change: Some(SafeCast) }), AlterColumn(AlterColumn { column_id: MigrationPair { previous: TableColumnId(2), next: TableColumnId(8) }, changes: ColumnChanges { type_change: Some(SafeCast), changes: BitFlags<ColumnChange>(0b100, TypeChanged) }, type_change: Some(SafeCast) }), AlterColumn(AlterColumn { column_id: MigrationPair { previous: TableColumnId(3), next: TableColumnId(9) }, changes: ColumnChanges { type_change: Some(SafeCast), changes: BitFlags<ColumnChange>(0b100, TypeChanged) }, type_change: Some(SafeCast) }), AlterColumn(AlterColumn { column_id: MigrationPair { previous: TableColumnId(4), next: TableColumnId(10) }, changes: ColumnChanges { type_change: Some(SafeCast), changes: BitFlags<ColumnChange>(0b100, TypeChanged) }, type_change: Some(SafeCast) }), AlterColumn(AlterColumn { column_id: MigrationPair { previous: TableColumnId(5), next: TableColumnId(11) }, changes: ColumnChanges { type_change: Some(SafeCast), changes: BitFlags<ColumnChange>(0b100, TypeChanged) }, type_change: Some(SafeCast) }), AddPrimaryKey] })
             at schema-engine\connectors\sql-schema-connector\src\apply_migration.rs:21
   1: sql_schema_connector::apply_migration::apply_migration
             at schema-engine\connectors\sql-schema-connector\src\apply_migration.rs:10
   2: schema_core::state::SchemaPush
             at schema-engine\core\src\state.rs:433

PS: This happens only after I did the migration.
Any hints please guys? Your help is much appreciated, and have a great day everyone!

How to reproduce (optional)

Expected behavior (optional)

No response

Information about Prisma Schema, Client Queries and Environment (optional)

Prisma schema:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider     = "postgresql"
  url          = env("DATABASE_URL") // uses connection pooling
  relationMode = "prisma"
}

model Store {
  id          String      @id @default(uuid())
  name        String
  description String      @db.VarChar(500)
  userId        String
  billboards  Billboard[] @relation("StoreToBillboard")
  categories  Category[]  @relation("StoreToCategory")
  sizes       Size[]      @relation("StoreToSize")
  colors      Color[]     @relation("StoreToColor")
  products    Product[]   @relation("StoreToProduct")
  orders      Order[]     @relation("StoreToOrder")
  integrations Integration[] @relation("StoreToIntegration")
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt
}

...

• OS: Windows

• Database: PostgreSQL Neon

• Node.js version: v20.9.0

• Output of npx prisma -v:

prisma                  : 5.11.0 
@prisma/client          : 5.11.0 
Computed binaryTarget   : windows
Operating System        : win32
Architecture            : x64
Node.js                 : v20.9.0

[ludralph]

Hi @barhouum7 👋

The error message you're seeing is likely due to Prisma trying to apply migrations that are not compatible with your new database provider.

The error message ERROR: syntax error at or near "RENAME" suggests that Prisma is trying to execute a SQL command that is not supported by your new database provider. This can happen when you switch database providers, as different databases have different SQL syntax.

One possible solution to this issue is to reset your migrations. This means you would need to delete your existing migrations and create a new initial migration that reflects the current state of your database. However, please note that this operation can result in data loss, so it's recommended to backup your data before proceeding.

[barhouum7]

Hi @ludralph 👋, thank you for your reply and your suggested solution. That's true, the issue was caused by incompatibility in the previous relations since they were originally generated for MySQL. So, fortunately, I tried npx prisma db pull and magically resolved this issue. It completely rewrites my Prisma schema file with relations from the previous data model as follows:

And here's the output of the pull command:

It basically added maps to the correct relations from the previous data model. And this fixed the error when I do push now.