connecting/prisma

18 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Increase Prisma connect_timeout parameter

To address connection timeout issues when Prisma cannot reach the database server before timeout, increase the connect_timeout parameter in the connection string. Example: .../postgres?connect_timeout=30

Prisma query parameters in connection string

Prisma allows configuration through query parameters appended to the connection string in the format: connection_string.../postgres?KEY1=VALUE&KEY2=VALUE&KEY3=VALUE. These parameters address specific errors and configuration needs.

Schema drift in Prisma caused by Supabase managed schemas

Supabase manages certain schemas like auth and storage and updates them to introduce new features. If Prisma is granted access to these managed schemas, it will detect external schema changes as drift and attempt to overwrite them, potentially causing data loss.

Baselining Prisma migrations

Baselining re-syncs Prisma with the database by capturing the current database schema as the starting point for future migrations. This resolves drift detected errors when the database schema is not in sync with migration history.

Prisma multi-schema configuration

When referencing external schemas not managed by Supabase, list all relevant schemas in the datasource block of schema.prisma file using the schemas property: datasource db { provider = "postgresql", schemas = ["public", "other_schema"] }

Avoid direct references to Supabase managed schemas in Prisma

Schemas managed by Supabase, such as auth and storage, may change to support new features. Referencing these schemas directly in Prisma migrations will cause schema drift in the future. It is best to remove references to these schemas and instead duplicate values into Prisma-managed tables using triggers.

PostgreSQL trigger to sync auth.users to profiles table

To avoid direct Prisma references to auth.users, create a trigger that automatically copies user data into a Prisma-managed profiles table. Create a PL/pgSQL function with security definer that inserts new auth.users data into the public.profiles table, then attach it to the auth.users table using: CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

Limit row return sizes for large queries

To address the Server has closed the connection error which may be related to large return values, limit the total amount of rows returned for particularly large requests.

Cross-schema references require explicit schema listing

A Prisma migration referencing a schema it is not permitted to manage will cause the error: Cross schema references are only allowed when the target schema is listed in the schemas property of your data-source. Ensure all referenced schemas are listed in the datasource block.

Create custom Prisma database user in Supabase

Create a custom database user for Prisma with the following SQL commands. Create user with bypassrls, createdb flags: `create user "prisma" with password 'custom_password' bypassrls createdb;`. Grant the prisma role to postgres: `grant "prisma" to "postgres";`. Grant schema usage and creation: `grant usage on schema public to prisma;` and `grant create on schema public to prisma;`. Grant all privileges on tables, routines, and sequences: `grant all on all tables in schema public to prisma;`, `grant all on all routines in schema public to prisma;`, `grant all on all sequences in schema public to prisma;`. Set default privileges for future objects: `alter default privileges for role postgres in schema public grant all on tables to prisma;`, `alter default privileges for role postgres in schema public grant all on routines to prisma;`, `alter default privileges for role postgres in schema public grant all on sequences to prisma;`. To change password later: `alter user "prisma" with password 'new_password';`.

Prisma connection string configuration for Supabase

For server-based deployments, set DATABASE_URL to the Supavisor Session pooler string ending with port 5432: `postgres://prisma.[PROJECT-REF]:[PR••••••D]@[DB-REGION].pooler.supabase.com:5432/postgres`. For serverless deployments, set DATABASE_URL to the Supavisor Transaction Mode string with port 6543 and append `?pgbouncer=true`: `postgres://prisma.[PROJECT-REF]:[PR••••••D]@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true`. Also create a DIRECT_URL variable using the Session mode string (port 5432) for migrations in serverless environments: `postgres://prisma.[PROJECT-REF]:[PR••••••D]@aws-0-us-east-1.pooler.supabase.com:5432/postgres`.

Prisma project initialization with npm

Initialize a new Prisma project with npm using these commands: `npm init -y`, `npm install prisma tsx @types/pg --save-dev`, `npm install @prisma/client @prisma/adapter-pg dotenv pg`, `npx tsc --init`, `npx prisma init`. Similar commands exist for pnpm (using `pnpm` and `pnpx`), yarn (using `yarn` and `npx`), and bun (using `bun` and `bunx`).

Prisma configuration for Supabase with prisma.config.ts

Configure prisma.config.ts with `import "dotenv/config"` at the top. For server-based deployments, set `datasource.url` to `env("DATABASE_URL")`. For serverless deployments, set `datasource.url` to `env("DIRECT_URL")` to use the direct connection for migrations. The config file should also specify `schema: "prisma/schema"` and `migrations.path: "prisma/migrations"`.

Prisma migration for new projects

For new Supabase projects, create table models in prisma/schema.prisma, then run migration and generation: `npx prisma migrate dev --name first_prisma_migration` and `npx prisma generate`. Similar commands exist for other package managers: `pnpx` for pnpm, `npx` for yarn, `bunx` for bun.

Prisma schema synchronization for populated projects

For existing Supabase databases, synchronize with Prisma using: `npx prisma db pull` to introspect the database. Create migration directory: `mkdir -p prisma/migrations/0_init_supabase`. Generate migration script: `npx prisma migrate diff --from-empty --to-schema prisma/schema.prisma --script > prisma/migrations/0_init_supabase/migration.sql`. Mark migration as applied: `npx prisma migrate resolve --applied 0_init_supabase`. Generate Prisma client: `npx prisma generate`. Similar commands work for pnpm, yarn, and bun with their respective package managers.

Test Prisma connection to Supabase

Create index.ts to test the connection using PrismaPg adapter: `import "dotenv/config"; import { PrismaClient } from "./generated/prisma/client"; import { PrismaPg } from "@prisma/adapter-pg"; const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL }); export const prisma = new PrismaClient({ adapter }); async function main() { const val = await prisma.user.findMany({ take: 10 }); console.log(val); } main().then(async () => { await prisma.$disconnect(); }).catch(async (e) => { console.error(e); await prisma.$disconnect(); process.exit(1); });`

Prisma monitor and control via custom user role

Creating a custom Prisma database user provides better control over Prisma's access and enables monitoring through Supabase tools like the Query Performance Dashboard and Log Explorer. This is recommended as an alternative to using the default postgres user.

Disable Supabase PostgREST API when using Prisma exclusively

If you plan to use only Prisma instead of the Supabase Data API (PostgREST), turn it off in the API Settings on the dashboard.

Give your agent this brain