Support for CREATE EXTENSION during migration

[chrisj-back2work]

What is the problem?

Add-on data types like @db.Citext work on initial migration, not subsequent migrations.

Paste all your error logs here:

Applying migration `20211115114223_init`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20211115114223_init

Database error code: 42704

Database error:
ERROR: type "citext" does not exist

Position:
 82
 83 -- CreateTable
 84 CREATE TABLE "system_actors" (
 85     "id" SERIAL NOT NULL,
 86     "organization_id" INTEGER NOT NULL,
 87     "name" CITEXT NOT NULL,

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42704"), message: "type \"citext\" does not exist", detail: None, hint: None, position: Some(Original(2420)), where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_type.c"), line: Some(270), routine: Some("typenameType") }

Paste all relevant code snippets here:

See detailed steps

What are detailed steps to reproduce this?

Delete existing db/migrations dir.

Create a new / empty Postgres DB and shadow DB.

Apply this configuration to both of the DBs:

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

Have fields in a Prisma model like:

 name String @db.Citext

Run an initial migration:

blitz prisma migrate dev --name init

That works fine. Now with the db/migrations folder in place:

Create a new / empty Postgres DB and shadow DB.

Run an after-initial migration:

yarn prisma migrate reset

That fails with:

A migration failed to apply ...

Migration name: whatever

Database error code: 42704

Database error:
ERROR: type "citext" does not exist

Run blitz -v and paste the output here:

macOS Monterey | darwin-x64 | Node: v16.9.1

blitz: 0.41.1 (global)
blitz: 0.41.1 (local)

  Package manager: yarn 
  System:
    OS: macOS 12.0.1
    CPU: (12) x64 Intel(R) Core(TM) i7-9750H CPU @ 2.60GHz
    Memory: 82.29 MB / 32.00 GB
    Shell: 3.2.57 - /bin/bash
  Binaries:
    Node: 16.9.1 - ~/.asdf/installs/nodejs/16.9.1/bin/node
    Yarn: 1.22.17 - ~/.asdf/installs/yarn/1.22.17/bin/yarn
    npm: 7.21.1 - ~/.asdf/plugins/nodejs/shims/npm
    Watchman: Not Found
  npmPackages:
    @prisma/client: 3.3.0 => 3.3.0 
    blitz: 0.41.1 => 0.41.1 
    prisma: 3.3.0 => 3.3.0 
    react: alpha => 18.0.0-alpha-6bce0355c-20211031 
    react-dom: alpha => 18.0.0-alpha-6bce0355c-20211031 
    typescript: ~4.3 => 4.3.5 

Please include below any other applicable logs and screenshots that show your problem:

If I manually add these lines to the top of my migration.sql file:

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

... the later migrations work.

It's not clear why that's needed, since these extensions were already installed.

Maybe Blitz is creating temporary DBs or schemas that don't have this add-on?

The solution could be, capture CREATE EXTENSION commands in migrate.sql

[beerose]

Hey! I think this is more of a Prisma issue because Blitz uses Prisma to run the migrations and other db related things also go through prisma.

[chrisj-back2work]

Confirmed that before prisma migrate reset, the extensions show up in both DBs; and after the command fails, the extensions are missing from main (but not shadow).

[chrisj-back2work]

Hey! I think this is more of a Prisma issue because Blitz uses Prisma to run the migrations and other db related things also go through prisma.

I'll do some research on the prisma side -- will bring back a solution if I find one.

[chrisj-back2work]

This is a known issue. The workaround is what I was already doing: add the CREATE EXTENSION language to the top of the migration.sql file

https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/enable-native-database-functions

This is worthy of a small doco add. Assuming you accept PRs on doco, I'll try that.

[beerose]

Yes, we do! Here's the repo: https://github.com/blitz-js/blitzjs.com

[beerose]

I'm going to close this issue, as it's not strictly related to Blitz. Blitz uses Prisma, but all the issues with Prisma or the underlying database are mostly outside of Blitz control.

You're welcome to submit a PR to our docs if you have ideas on how to make the experience better (we could, for example, put a link to Prisma docs saying that "if you have problems with prisma/db, refer to their docs"?).