"Database error: Error querying the database: db error: ERROR: type "citext" does not exist" but citext extension enabled

[dilizarov]

Bug description

Migrations after the first one fail due to type citext not existing, but I've confirmed numerous times and ensured that the citext extension is enabled on my database before I run the prisma migrate command. After running the prisma migrate command and seeing the error, I can confirm that the extension is removed from my database.

I'm assuming something funky is happening with prisma/shadow DB stuff?

How to reproduce

1. Create a DB via psql and run CREATE EXTENSION citext; on the DB.
2. Ensure your prisma folder has no migrations folder (so you're starting brand new)
3. Create a prisma.schema with a model that has a citext attribute like email String @unique @db.Citext.
4. Run prisma migrate dev --preview-feature
5. Since this is the first migration, it should work.
6. Edit your prisma.schema - maybe by adding an attribute like name String to a model.
7. Run prisma migrate dev --preview-feature
8. You should see the following error output:

prisma:tryLoadEnv Environment variables loaded from /Users/david/Code/project/packages/server2/.env +0ms
  prisma:tryLoadEnv Environment variables loaded from ./prisma/.env +3ms
Environment variables loaded from .env
Environment variables loaded from prisma/.env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "project", schema "public" at "localhost:5432"

  prisma:migrateEngine:rpc starting migration engine with binary: /Users/david/Code/project/packages/server2/node_modules/@prisma/engines/migration-engine-darwin +0ms
  prisma:migrateEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"devDiagnostic","params":{"migrationsDirectoryPath":"/Users/david/Code/project/packages/server2/prisma/migrations"}} +3ms
  prisma:migrateEngine:stderr Feb 21 15:05:30.427  INFO migration_engine: Starting migration engine RPC server git_hash="3c463ebd78b1d21d8fdacdd27899e280cf686223" +0ms
  prisma:migrateEngine:stderr Feb 21 15:05:30.434  INFO quaint::single: Starting a postgresql connection. +5ms
  prisma:migrateEngine:stderr Feb 21 15:05:30.733  INFO DevDiagnostic:calculate_drift:sql_schema_from_migration_history: quaint::single: Starting a postgresql connection. +299ms
  prisma:migrateEngine:rpc {
  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: 'Migration `20210221102106_hello_world` failed to apply cleanly to a temporary database. \n' +
  prisma:migrateEngine:rpc         'Error:\n' +
  prisma:migrateEngine:rpc         'Database error: Error querying the database: db error: ERROR: type "citext" does not exist\n' +
  prisma:migrateEngine:rpc         '   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history\n' +
  prisma:migrateEngine:rpc         '             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:270\n' +
  prisma:migrateEngine:rpc         '   1: sql_migration_connector::sql_database_migration_inferrer::calculate_drift\n' +
  prisma:migrateEngine:rpc         '             at migration-engine/connectors/sql-migration-connector/src/sql_database_migration_inferrer.rs:40\n' +
  prisma:migrateEngine:rpc         '   2: migration_core::api::DevDiagnostic\n' +
  prisma:migrateEngine:rpc         '             at migration-engine/core/src/api.rs:79',
  prisma:migrateEngine:rpc       meta: [Object],
  prisma:migrateEngine:rpc       error_code: 'P3006'
  prisma:migrateEngine:rpc     }
  prisma:migrateEngine:rpc   },
  prisma:migrateEngine:rpc   id: 1
  prisma:migrateEngine:rpc } +370ms
Error: Error: P3006

Migration `20210221102106_hello_world` failed to apply cleanly to a temporary database.
Error:
Database error: Error querying the database: db error: ERROR: type "citext" does not exist
   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:79

    at Object.<anonymous> (/Users/david/Code/project/packages/server2/node_modules/prisma/build/index.js:54905:26)
    at MigrateEngine.handleResponse (/Users/david/Code/project/packages/server2/node_modules/prisma/build/index.js:54780:38)
    at LineStream.<anonymous> (/Users/david/Code/project/packages/server2/node_modules/prisma/build/index.js:54865:18)
    at LineStream.emit (events.js:209:13)
    at LineStream.EventEmitter.emit (domain.js:476:20)
    at addChunk (_stream_readable.js:305:12)
    at readableAddChunk (_stream_readable.js:286:11)
    at LineStream.Readable.push (_stream_readable.js:220:10)
    at LineStream.Transform.push (_stream_transform.js:150:32)
    at LineStream._pushBuffer (/Users/david/Code/project/packages/server2/node_modules/prisma/build/index.js:54617:19)

9. Head over to psql and you'll notice that the citext extension was removed from your DB.

Expected behavior

I expect the migration to succeed.

Prisma information

DEBUG="*" npx prisma migrate dev --preview-feature is the command I ran.

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

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

model User {
  email      String @unique @db.Citext
}

That schema should suffice.

Environment & setup

• OS: macOS Big Sur 11.1
• Database: PostgreSQL 12.3 on x86_64-apple-darwin19.4.0, compiled by Apple clang version 11.0.3 (clang-1103.0.32.59), 64-bit
• Node.js version: v12.10.0
• Prisma version: 2.17

prisma               : 2.17.0
@prisma/client       : 2.17.0
Current platform     : darwin
Query Engine         : query-engine 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Studio               : 0.353.0

[tomhoule]

You have to enable the extension from a migration for the shadow database to pick it up. So have a CREATE EXTENSION in your first migration, usually.

Keeping the issue open to see if there is any alternative we can think of, but so far I don't consider this a bug.

[janpio]

We could possibly handle that error message and tell the user this might be a probable source for their problem, and explain how to overcome it.

[albertoperdomo]

In order to handle this error gracefully and produce a specific warning, we'd had to special case errors for unknown column types. While this is technically feasible, it is a lot of work so unlikely we do this short or mid- term.

In the meantime, we should make sure the suggested solution (add CREATE EXTENSION to the migration SQL file) is properly documented.

[tomhoule]

Relevant docs issue: prisma/docs#1359

[kiily]

Hi all,

We seem to be facing this issue in our team. We are trying to run the prisma commands in our CI tool and we keep getting this error.

Our initial migration creates the extensions as suggested above.

CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Weirdly, it works absolutely fine locally right now which makes it even harder to debug. The sequence of commands we are running is:

npx prisma migrate reset --force

npx prisma db push --preview-feature

To clarify, the first migration applies fine and then the second command errors. It might be worth noting that we are running against a custom postgres schema so maybe something going on in the shadow db there?

Any help with this would be appreciated 🙏

[tomhoule]

Hi @kiily — db push does not execute migrations, but reset should, so we would definitely expect things to work in your case. Can you open a new issue to investigate this? Comments in closed issues tend not to be seen, and we can't prioritize working on these.

[kiily]

Thanks for the quick reply @tomhoule, let me know if the above is enough. Happy to clarify anything else that might help 👍

[tomhoule]

It's great, thanks! We'll work on it as soon as possible.

[Jolg42]

Closing this there a new issue here #6944