Prisma•6d ago

Prisma db pull Extremely Slow on Supabase with Custom Schema

Issue: Prisma db pull is extremely slow when introspecting a schema on Supabase. Details:
- Using PostgreSQL on Supabase
- Prisma version:

prisma                  : 5.17.0
@prisma/client          : 5.17.0
Computed binaryTarget   : windows
Operating System        : win32
Architecture            : x64
Node.js                 : v22.14.0
Query Engine (Node-API) : libquery-engine 393aa359c9ad4a4bb28630fb5613f9c281cde053 (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Schema Engine           : schema-engine-cli 393aa359c9ad4a4bb28630fb5613f9c281cde053 (at node_modules\@prisma\engines\schema-engine-windows.exe)
Schema Wasm             : @prisma/prisma-schema-wasm 5.17.0-31.393aa359c9ad4a4bb28630fb5613f9c281cde053
Default Engines Hash    : 393aa359c9ad4a4bb28630fb5613f9c281cde053
Studio                  : 0.502.0
Preview Features        : multiSchema

prisma                  : 5.17.0
@prisma/client          : 5.17.0
Computed binaryTarget   : windows
Operating System        : win32
Architecture            : x64
Node.js                 : v22.14.0
Query Engine (Node-API) : libquery-engine 393aa359c9ad4a4bb28630fb5613f9c281cde053 (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Schema Engine           : schema-engine-cli 393aa359c9ad4a4bb28630fb5613f9c281cde053 (at node_modules\@prisma\engines\schema-engine-windows.exe)
Schema Wasm             : @prisma/prisma-schema-wasm 5.17.0-31.393aa359c9ad4a4bb28630fb5613f9c281cde053
Default Engines Hash    : 393aa359c9ad4a4bb28630fb5613f9c281cde053
Studio                  : 0.502.0
Preview Features        : multiSchema

- Debug logs show it gets stuck at:

PS D:\Gitlab\Project - Copy> prisma db pull
Prisma schema loaded from prisma\schema.prisma
Environment variables loaded from .env
Datasource "db": PostgreSQL database "postgres", schemas "project-schema" at "aws-0-us-east-1.pooler.supabase.com:6543"

⠼ Introspecting based on datasource defined in prisma\schema.prisma

PS D:\Gitlab\Project - Copy> prisma db pull
Prisma schema loaded from prisma\schema.prisma
Environment variables loaded from .env
Datasource "db": PostgreSQL database "postgres", schemas "project-schema" at "aws-0-us-east-1.pooler.supabase.com:6543"

⠼ Introspecting based on datasource defined in prisma\schema.prisma

Has anyone faced this before? Any fixes?

10 Replies

Prisma AI Help•6d ago

You selected the bot's instantaneous insights. Let me compile your answer right away!

matsyuiOP•6d ago

Im using this connection:

matsyuiOP•6d ago

schema.prisma:

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["project-schema"]
}

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["project-schema"]
}

Additional info: it's in a custom schema because i migrated a mysql database to supabase using pgloader

matsyuiOP•6d ago

Trying solution atm: - moving tables to public

DO $$ 
DECLARE 
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'project-schema') 
    LOOP
        EXECUTE 'CREATE TABLE public."' || r.tablename || '" AS TABLE "project-schema"."' || r.tablename || '";';
    END LOOP;
END $$;

DO $$ 
DECLARE 
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'project-schema') 
    LOOP
        EXECUTE 'CREATE TABLE public."' || r.tablename || '" AS TABLE "project-schema"."' || r.tablename || '";';
    END LOOP;
END $$;

- prisma db pull the public schema

matsyuiOP•6d ago

almost out for work still Introspecting what the hek did i do wrong 😭 is this updated T_T https://www.prisma.io/docs/orm/overview/databases/supabase#specific-considerations

Supabase | Prisma Documentation

Guide to Supabase

matsyuiOP•6d ago

i dont actually know if there's problem with my prisma, or there's a problem with the supabase URI, or maybe i am the problem. :thinkinghyperfast:

Prisma AI Help•6d ago

I can see you're experiencing slow performance with Prisma's db pull command when working with Supabase. This is a known issue, particularly on Windows systems and when using Supabase's connection pooler URLs. The Issue Based on the knowledge sources, there are several potential causes for your slow introspection: 1. Connection pooler URL: You're using the pooler URL (aws-0-us-east-1.pooler.supabase.com:6543), which is not recommended for Prisma migrations or introspection operations Link to Prisma issue #21740. 2. Windows performance: There's a documented issue with significantly slower database operations on Windows, particularly with PostgreSQL databases Link to Prisma issue #11340. 3. Multi-schema usage: You're using the multiSchema preview feature, which can add complexity to the introspection process. Solutions Here are the recommended fixes: 1. Use Direct Connection URL The most important fix is to use Supabase's direct connection URL instead of the pooler URL for database introspection and migrations:

datasource db {
  provider   = "postgresql"
  // Uses connection pooling - for regular queries
  url        = env("DATABASE_URL")
  // Uses direct connection - for migrations and introspection
  directUrl  = env("DIRECT_URL")
  schemas    = ["project-schema"]
}

datasource db {
  provider   = "postgresql"
  // Uses connection pooling - for regular queries
  url        = env("DATABASE_URL")
  // Uses direct connection - for migrations and introspection
  directUrl  = env("DIRECT_URL")
  schemas    = ["project-schema"]
}

In your .env file:

DATABASE_URL="postgres://postgres.[id]:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true"
DIRECT_URL="postgres://postgres:[password]@db.[your-project-id].supabase.co:5432/postgres"

DATABASE_URL="postgres://postgres.[id]:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true"
DIRECT_URL="postgres://postgres:[password]@db.[your-project-id].supabase.co:5432/postgres"

This configuration is specifically recommended for Supabase as mentioned in Prisma issue #21740. 2. Add Connection Timeout You might also want to add a connection timeout parameter to your URL:

?connect_timeout=300

?connect_timeout=300

This was suggested as a solution in Pr isma issue #13807. 3. Consider Using WSL on Windows If you're still experiencing slow performance on Windows, consider using Windows Subsystem for Linux (WSL). Tests have shown dramatic performance improvements: - Windows CMD: ~84 seconds - Windows WSL (Ubuntu): ~7 seconds This significant difference was documented in Prisma issue #11340. By implementing these changes, particularly using the direct connection URL for introspection operations, you should see a significant improvement in the performance of prisma db pull with your Supabase database. kapa.ai: I've analyzed your question about slow Prisma db pull performance with Supabase and provided solutions based on documented issues and their resolutions. Let me know if you need any clarification on implementing these fixes!

matsyuiOP•6d ago

its been 1hour Prisma schema loaded from prisma\schema.prisma Environment variables loaded from .env Datasource "db": PostgreSQL database "postgres", schema "public" at "db.xjrojetqxogakrymemsd.supabase.co:5432" ✖ Introspecting based on datasource defined in prisma\schema.prisma Error: P1001 Can't reach database server at db.xjrojetqxogakrymemsd.supabase.co:5432 Please make sure your database server is running at db.xjrojetqxogakrymemsd.supabase.co:5432.

Prisma AI Help•6d ago

🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

matsyuiOP•6d ago

PROBLEM SOLVED! Solution: Switch to the ASIA server. I chose SG because I don't know why I picked the US when I'm in Asia aylmao. :1526_pingumad:

Gaming

Programming

Prisma db pull Extremely Slow on Supabase with Custom Schema

Did you find this page helpful?