Migrating from Prisma + PostgreSQL to Supabase: Errors and Solutions

Overview

This article explains how I resolved errors encountered when switching an existing Prisma setup with a defined schema.prisma and PostgreSQL to use Supabase's PostgreSQL.

Version

• Note: I have only tested this with the following versions, so behavior may differ in other versions.

$ npx prisma --version
prisma                  : 6.8.2
@prisma/client          : 6.8.2

Local Migration Failure

This covers the error handling when running prisma migrate dev after starting a local container with the Supabase CLI and pointing DATABASE_URL to it.

Error Details

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
  - pg_graphql

[+] Added extensions
  - pg_net

[+] Added extensions
  - pg_stat_statements

[+] Added extensions
  - pgcrypto

[+] Added extensions
  - pgjwt

[+] Added extensions
  - supabase_vault

[+] Added extensions
  - uuid-ossp

- The migrations recorded in the database diverge from the local migrations directory.

It seems the error occurred because the PostgreSQL extensions automatically configured by Supabase were not included in the migration contents.

I referred to the following issue for resolution 👇

https://github.com/prisma/prisma/issues/19100

• You can list which extensions are configured in Supabase's PostgreSQL using the following SQL:

SELECT
  ext.extname AS extension_name,
  ext.extversion AS extension_version,
  ext.extrelocatable AS extension_relocatable,
  pn.nspname AS extension_schema
FROM pg_extension ext
INNER JOIN pg_namespace pn ON ext.extnamespace = pn.oid
ORDER BY ext.extname ASC

Final Solution

By using the following mechanism to "instruct Prisma to assume that one or more migrations have already been applied," I marked the Added extensions mentioned above as already applied and then proceeded with a normal migration.

https://www.prisma.io/docs/orm/prisma-migrate/workflows/baselining

• Create the 0_init directory with mkdir -p prisma/migrations/0_init

• Create a migration.sql file with the following content (*1)

  -- CreateSchema
  CREATE SCHEMA IF NOT EXISTS "extensions";
  
  -- CreateSchema
  CREATE SCHEMA IF NOT EXISTS "graphql";
  
  -- CreateSchema
  CREATE SCHEMA IF NOT EXISTS "public";
  
  -- CreateSchema
  CREATE SCHEMA IF NOT EXISTS "vault";
  
  -- CreateExtension
  CREATE EXTENSION IF NOT EXISTS "pg_graphql" WITH SCHEMA "graphql";
  
  -- CreateExtension
  CREATE EXTENSION IF NOT EXISTS "pg_net" WITH SCHEMA "extensions";
  
  -- CreateExtension
  CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "extensions";
  
  -- CreateExtension
  CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions";
  
  -- CreateExtension
  CREATE EXTENSION IF NOT EXISTS "pgjwt" WITH SCHEMA "extensions";
  
  -- CreateExtension
  CREATE EXTENSION IF NOT EXISTS "supabase_vault" WITH SCHEMA "vault";
  
  -- CreateExtension
  CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "extensions";
  

• Execute the following command to mark 0_init as applied in _prisma_migrations

  npx prisma migrate resolve --applied 0_init
  

• Then, proceed with prisma migrate dev, etc., as usual.

---

*1 Work history until file creation

Create the following schema.prisma:

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

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

Create prisma/migrations/0_init/migration.sql with the following command:

npx prisma migrate diff \
  --from-empty \
  --to-schema-datamodel prisma/schema.prisma \
  --script > prisma/migrations/0_init/migration.sql 

Since 0_init will be marked as resolved in _prisma_migrations, revert schema.prisma to its original state.

---

Deployment

In an app deployed to Google Cloud's Cloud Run, I encountered the following error when trying to run prisma migrate using Cloud Build.

Datasource "db": PostgreSQL database "postgres", schema "public" at "aws-xxxxx.pooler.supabase.com:6543"
Error: P1001: Can't reach database server at `aws-xxxxx.pooler.supabase.com:6543`

It seems that the connection to Supabase is not working correctly.

For the DATABASE_URL in schema.prisma, I set the DATABASE_URL from "Connect" > "ORMs" in the Supabase dashboard.

According to the article below, specifying DIRECT_URL is required, so I tried setting it. I'll test by setting the DIRECT_URL from the configuration above in schema.prisma.

https://qiita.com/Naoki_ganbarimasu/items/f18eac6e1403bb235ba6

• The documentation for DIRECT_URL can be found here 👇

https://www.prisma.io/docs/orm/reference/prisma-schema-reference

The error persisted even after trying the above...

After various trial and error, I looked closely at the Cloud Build settings and found that a private connection pool was configured, which was the cause 😇

options:
  logging: CLOUD_LOGGING_ONLY
  pool: # ← A private connection pool was set here
    name: xxxxxx

References

https://zenn.dev/probmkr/scraps/1f412ad1a0d278