prisma: `Drift detected: Your database schema is not in sync with your migration history` - every migration on Supabase

Solved by disabling all the extensions I could in the Supabase dashboard and adding the following migration to the migrations directory manually:

-- Dumbass migration to fix Supabase morons
--
-- Name: extensions; Type: SCHEMA; Schema: -; Owner: -
--
CREATE SCHEMA IF NOT EXISTS extensions;

CREATE EXTENSION IF NOT EXISTS "vector";

--
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA extensions;

--
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';

CREATE SCHEMA IF NOT EXISTS pgsodium;

CREATE EXTENSION IF NOT EXISTS pgsodium WITH SCHEMA pgsodium;

CREATE SCHEMA IF NOT EXISTS vault;

--
-- Name: vault; Type: SCHEMA; Schema: -; Owner: -
--
--
-- Name: supabase_vault; Type: EXTENSION; Schema: -; Owner: -
--
CREATE EXTENSION IF NOT EXISTS supabase_vault WITH SCHEMA vault;

--
-- Name: EXTENSION supabase_vault; Type: COMMENT; Schema: -; Owner: -
--
COMMENT ON EXTENSION supabase_vault IS 'Supabase Vault Extension';

While in the schema.prisma:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [pgsodium(schema: "pgsodium"), supabase_vault, uuid_ossp(map: "uuid-ossp", schema: "extensions"), vector]
}

Looking for a production level solution here too we’re trying to use the vector extension and running into this issue during migrate dev

So now, how to solve this 🤔 ?

I think the problem is on Prisma Migrate side when postgresqlExtensions is enabled. Regardless of if multiSchema is enabled, Migrate will look in all schemas for extensions during the drift detection.

Suggestion: Only check the current schemas for extensions for drift detection, meaning:

• without multiSchema, the schema defined in the connection URL or default (public)
• with multiSchema, the schemas defined in the datasource property schemas = ["public", "something"]

Then on Supabase, it should be possible to use the following schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions", "multiSchema"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [uuid_ossp(map: "uuid-ossp", schema: "extensions")]
  schemas    = ["public", "extensions"]
}

model Test {
  id   BigInt @id @default(autoincrement())
  uuid String @default(dbgenerated("uuid_generate_v4()"))

  @@schema("public")
}

Which would mean ignoring the schemas not present in the schemas property.

Current list of all schemas on Supabase:

I tried some things today:

Using the following schema

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions", "multiSchema"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [pg_graphql(schema: "graphql"), pg_stat_statements(schema: "extensions"), pgcrypto(schema: "extensions"), pgjwt(schema: "extensions"), plpgsql(schema: "pg_catalog"), pgjwt(schema: "extensions"), uuid_ossp(map: "uuid-ossp", schema: "extensions")]
  schemas    = ["public", "extensions", "graphql"]
}

model Test {
  id   BigInt @id @default(autoincrement())
  uuid String @default(dbgenerated("uuid_generate_v4()"))

  @@schema("public")
}

first migrate dev

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

If you are running this the first time on an existing database, please make sure to read this documentation page:
https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/troubleshooting-development

[+] Added extensions
  - pg_graphql

[+] Added extensions
  - pg_stat_statements

[+] Added extensions
  - pgcrypto

[+] Added extensions
  - pgjwt

[+] Added extensions
  - pgsodium

[+] Added extensions
  - supabase_vault

[+] Added extensions
  - uuid-ossp

✔ We need to reset the following schemas: "extensions, graphql, public" at "db.qaakkxacphtuxwvxhskf.supabase.co:5432"
Do you want to continue? All data will be lost. … yes

Applying migration `20230510155930_`

second run, reduces the number of extensions added in the drift

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[+] Added extensions
  - pgsodium

[+] Added extensions
  - supabase_vault

? We need to reset the following schemas: "extensions, graphql, public" at "db.qaakkxacphtuxwvxhskf.supabase.co:5432"
Do you want to continue? All data will be lost. › (y/N)

Trying to add these 2 last extensions and schemas is not doable, migrate dev will need to reset these, and I think that they are not supposed to be migrated / reset by anyone and should only be managed by Supabase.

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[+] Added extensions
  - pgsodium

[+] Added extensions
  - supabase_vault

[+] Added enums
  - key_status
  - key_type

[+] Added tables
  - key
  - secrets

[*] Changed the `key` table
  [+] Added unique index on columns (key_id, key_context, key_type)
  [+] Added unique index on columns (name)
  [+] Added foreign key on columns (parent_key)

[*] Changed the `secrets` table
  [+] Added foreign key on columns (key_id)

? We need to reset the following schemas: "extensions, graphql, pgsodium, public, vault" at "db.qaakkxacphtuxwvxhskf.supabase.co:5432"
Do you want to continue? All data will be lost. › (y/N)

Do you want to continue? All data will be lost. … yes

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
db error: ERROR: cannot drop view pg_stat_statements_info because extension pg_stat_statements requires it
HINT: You can drop extension pg_stat_statements instead.
   0: sql_schema_connector::best_effort_reset
           with namespaces=Some(Namespaces("vault", ["extensions", "graphql", "pgsodium", "public"]))
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:341
   1: schema_core::state::Reset
             at schema-engine/core/src/state.rs:425

I tried to deactivate extensions from https://app.supabase.com/project/ljoyzzvkboexvsemukhj/database/extensions but pgsodium cannot be deactivated

Toggle PGSODIUM failed: failed to delete pg.extensions with the given ID: cannot drop extension pgsodium because other objects depend on it

I need to talk to some colleagues about this, my first thought is that Migrate needs, somehow to ignore these Supabase specific schemas (vault, pgsodium) at least, and maybe more?

I have a workaround to offer those finding this page: remove previewFeatures and extensions before running prisma migrate dev.

My setup: I am using Supabase with Prisma 4.13. While my testing and production servers run on Supabase’s cloud service, I do unit testing off a local Docker setup of Supabase. My test runner, the one that comes with RedwoodJS, routinely runs this command automatically to reset then seed the Docker database:

yarn prisma db push --force-reset --accept-data-loss

I use postgis, which is a PostgreSQL extension, one that is unsupported by Prisma. There are postgis fields in my schema with the “unsupported” type. During the data reset step above, the Supabase in Docker loses the extension, so the subsequent push fails. To get unit testing to work, I found that I had to add the postgresqlExtensions preview feature to my schema.prisma file as follows:

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

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [postgis(schema: "public")]
}

Notice that the datasource.extensions section specifies a schema for the extension (“public”).

Recently, running “prisma migrate dev” to apply changes to my hosted Supabase testing server started giving me the “Drift detected: Your database schema is not in sync with your migration history” message, citing the same extensions mentioned above. I assume it’s because Supabase changed the extensions in some of the schemas they manage, which is in their purview. I suspect that, with these features on, Prisma started paying attention to the extensions in other schemas as well, thinking such changes to those schemas represented drift, when for all intents and purposes to the developer, they do not.

The workaround was to remove the previewFeatures and extensions from schema.prisma. Once removed, I was able to run prisma migrate dev without “drift” being found.

Here’s the code at the top of my schema.prisma file now:

generator client {
  provider        = "prisma-client-js"
  binaryTargets   = ["native"]
  //previewFeatures = ["postgresqlExtensions"] // uncomment for unit testing
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  //extensions = [postgis(schema: "public")] // uncomment for unit testing
}

I uncomment these two lines when I want to run the unit tests. This will be my workaround until the issue no longer exists. I hope this helps!

I solved it this way:

Add extensions manually in extensions=[…], then do prisma db pull, this will fill out the extensions with the appropriate schema, then add the missing schemas to schemas=[…]

If a migration fails, check which extension made it fail and repeat the process above until there are no more extension left to add in prisma schema

I have added pg_trgm that is not by default on supabase

Notes: I could reproduce this easily with 4.14.0

When starting a fresh project db on supabase With the following schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

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

model Test {
  id BigInt @id @default(autoincrement())
}

running npx prisma migrate dev outputs like in this issue

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

If you are running this the first time on an existing database, please make sure to read this documentation page:
https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/troubleshooting-development

[+] Added extensions
  - pg_graphql

[+] Added extensions
  - pg_stat_statements

[+] Added extensions
  - pgcrypto

[+] Added extensions
  - pgjwt

[+] Added extensions
  - pgsodium

[+] Added extensions
  - supabase_vault

[+] Added extensions
  - uuid-ossp

? We need to reset the "public" schema at "db.....supabase.co:5432"
Do you want to continue? All data will be lost. › (y/N)

This drift shows in both cases: if the schema was changed or not. The reset, if saying yes, will be successful but needs to be done every time migrate dev is called which is not expected.

npx prisma db pull only adds extensions = [uuid_ossp(map: "uuid-ossp", schema: "extensions")]

I find from documentation. link

that resolved my life here, thank you

I find from documentation. link

npx prisma db pull

doesn’t sync db with schema. why?