prisma migrate gives: Error querying the database: db error: ERROR: syntax error at or near "NOT"

[HStromfelt]

Bug description

prisma migrate dev --preview-feature is failing with Error querying the database: db error: ERROR: syntax error at or near "NOT"

The full debug output is:

yarn prisma migrate dev --preview-feature
yarn run v1.22.10
warning ../package.json: No license field
$ /home/ec2-user/environment/node_modules/.bin/prisma migrate dev --preview-feature
  prisma:loadEnv project root found at /home/ec2-user/environment/package.json +0ms
  prisma:tryLoadEnv Environment variables loaded from /home/ec2-user/environment/.env +0ms
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "ebdb", schema "public" at "localhost:5432"

  prisma:migrateEngine:rpc starting migration engine with binary: /home/ec2-user/environment/node_modules/@prisma/engines/migration-engine-rhel-openssl-1.1.x +0ms
  prisma:migrateEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"devDiagnostic","params":{"migrationsDirectoryPath":"/home/ec2-user/environment/prisma/migrations"}} +5ms
  prisma:migrateEngine:stderr Mar 09 12:00:17.017  INFO migration_engine: Starting migration engine RPC server git_hash="da6fafb57b24e0b61ca20960c64e2d41f9e8cff1" +0ms
  prisma:migrateEngine:stderr Mar 09 12:00:17.031  INFO quaint::single: Starting a postgresql connection. +13ms
  prisma:migrateEngine:stderr Mar 09 12:00:17.273  INFO DevDiagnostic:calculate_drift:sql_schema_from_migration_history: quaint::single: Starting a postgresql connection. +242ms
  prisma:migrateEngine:rpc { jsonrpc: '2.0',
  prisma:migrateEngine:rpc   error:
  prisma:migrateEngine:rpc    { code: 4466,
  prisma:migrateEngine:rpc      message: 'An error happened. Check the data field for details.',
  prisma:migrateEngine:rpc      data:
  prisma:migrateEngine:rpc       { is_panic: false,
  prisma:migrateEngine:rpc         message:
  prisma:migrateEngine:rpc          'Database error: Error querying the database: db error: ERROR: syntax error at or near "NOT"\n   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history\n             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:270\n   1: sql_migration_connector::sql_database_migration_inferrer::calculate_drift\n             at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:40\n   2: migration_core::api::DevDiagnostic\n             at migration-engine/core/src/api.rs:106',
  prisma:migrateEngine:rpc         backtrace: null } },
  prisma:migrateEngine:rpc   id: 1 } +370ms
Error: Error: Database error: Error querying the database: db error: ERROR: syntax error at or near "NOT"
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:270
   1: sql_migration_connector::sql_database_migration_inferrer::calculate_drift
             at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:40
   2: migration_core::api::DevDiagnostic
             at migration-engine/core/src/api.rs:106

    at Object.registerCallback (/home/ec2-user/environment/node_modules/prisma/build/index.js:55009:26)
    at MigrateEngine.handleResponse (/home/ec2-user/environment/node_modules/prisma/build/index.js:54882:38)
    at LineStream.byline_12.default.on (/home/ec2-user/environment/node_modules/prisma/build/index.js:54967:18)
    at LineStream.emit (events.js:198:13)
    at LineStream.EventEmitter.emit (domain.js:448:20)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at LineStream.Readable.push (_stream_readable.js:224:10)
    at LineStream.Transform.push (_stream_transform.js:151:32)
    at LineStream._pushBuffer (/home/ec2-user/environment/node_modules/prisma/build/index.js:54719:19)
error Command failed with exit code 1.

How to reproduce

I am not entirely sure what the exact steps that lead to the problem are, but hopefully the following is helpful:

I had deleted the previous migrations folder by accident, which had a single migration file in it. This migration file set up a basic User model.

After seeing the above errors, I did try a complete refresh of the database (dropped and recreated), as well as uninstall/reinstall and reset of prisma. Unfortunately the problem persisted.

Expected behavior

prisma migrate to run normally and create the migration sql.

Prisma information

My current prisma schema is:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}


generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native"]
}

model Place {
  id        Int         @id @default(autoincrement())
  createdAt DateTime    @default(now())
  updatedAt DateTime    @updatedAt
  place_id  String      @unique
  users     UserPlace[]
}

model User {
  id           Int         @id @default(autoincrement())
  createdAt    DateTime    @default(now())
  updatedAt    DateTime    @updatedAt
  email        String      @unique
  firstName    String
  lastName     String
  promotion    Boolean     @default(false)
  places       UserPlace[]
}

model UserPlace {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  placeId   Int?
  place     Place?   @relation(fields: [placeId], references: [id])
  userId    Int?
  user      User?    @relation(fields: [userId], references: [id])
}

Environment & setup

• OS: Amazon Linux 2
• Database: PostgreSQL (localhost)
• Node.js version: v10.23.0
• Prisma version

prisma               : 2.18.0
@prisma/client       : 2.18.0
Current platform     : rhel-openssl-1.1.x
Query Engine         : query-engine da6fafb57b24e0b61ca20960c64e2d41f9e8cff1 (at node_modules/@prisma/engines/query-engine-rhel-openssl-1.1.x)
Migration Engine     : migration-engine-cli da6fafb57b24e0b61ca20960c64e2d41f9e8cff1 (at node_modules/@prisma/engines/migration-engine-rhel-openssl-1.1.x)
Introspection Engine : introspection-core da6fafb57b24e0b61ca20960c64e2d41f9e8cff1 (at node_modules/@prisma/engines/introspection-engine-rhel-openssl-1.1.x)
Format Binary        : prisma-fmt da6fafb57b24e0b61ca20960c64e2d41f9e8cff1 (at node_modules/@prisma/engines/prisma-fmt-rhel-openssl-1.1.x)
Studio               : 0.356.0

Thanks for your time!

[tomhoule]

Thanks for creating this issue! We'll look into it as soon as possible

[tomhoule]

Can you share the contents of the migrations folder? If it is confidential, you can send it to schemas@prisma.io.

[HStromfelt]

Thanks for the response! I sent it via email. To clarify, this is the folder that I since deleted. I did try replacing it but the error still occurred.

I should also note that the current schema (posted above) and the migrations folder that I sent over don't match much anymore. I found that using yarn prisma db push --preview-feature coupled with yarn prisma migrate reset --preview-feature allowed me to continue working.
I have no sensitive data, so I was ok to lose all the data with each db change, but this means the db has changed quite a bit since the last migration was created.

[HStromfelt]

I have some more info to provide.

I modified the schema again to add note and tags to the UserPlace model. I then ran a test to do an update query on UserPlaces, using the following:

...
    const userPlace = await prisma.userPlace.update({
      where: { id },
      data: { tags: { create: { name: e.name} },
      include: {
        tags: true // Include all tags in the returned object
      }
    });
...

This returned a similar error with "message: "syntax error at or near \"ON\"". I did some googling as I am a bit of newbie to all things database and I found some reference to ON CONFLICT working on postgresql 9.5, not 9.4.
So today I started a new environment (using AWS Cloud9), however instead of running postgres locally, I used a hosted postgresql server running 12.5.

I reran prisma init and after copying over my up-to-date schema, I was able to successfully run the migration! And the query I posted above also works.

I am still not sure of the exact cause of all the issues, but I assume it had to do with the postgresql version and perhaps running it locally (but wrong)? Hopefully this extra information can expose the cause.

[tomhoule]

I've tried locally, following the steps you gave, and I can't reproduce the problems with 2.19.0. Given than nobody in this thread is able to reproduce the issue, let's close it. If you see it reappear, please open a new issue with as much context as possible so we have a better chance at fixing it. Thanks fer reporting your problem @HStromfelt !

[HStromfelt]

Thanks a lot for looking into it! I'll keep an eye out and make sure I document as much as possible if I manage to mess it up again