Prisma Database Setup

Prisma Database Setup

Comprehensive guides for configuring Prisma ORM with various database providers.

When to Apply

Reference this skill when:

• Initializing a new Prisma project
• Switching database providers
• Configuring connection strings and environment variables
• Troubleshooting database connection issues
• Setting up database-specific features
• Generating and instantiating Prisma Client

Rule Categories by Priority

System Prerequisites

• Node.js 20.19.0+
• TypeScript 5.4.0+

Bun Runtime

If you're using Bun, run Prisma CLI commands with bunx --bun prisma ... so Prisma uses the Bun runtime instead of falling back to Node.js.

Supported Databases

Configuration Files

Your configuration shape depends on the provider and Prisma major version:

1. All providers use `prisma/schema.prisma`. 2. Prisma 7 SQL setups typically use `prisma.config.ts` for datasource URLs. 3. MongoDB projects should stay on Prisma 6.x, keep url = env("DATABASE_URL") in the schema, and continue using the classic MongoDB setup.

Driver Adapters

The standard SQL workflow uses a driver adapter. Choose the adapter and driver for your database and pass the adapter to PrismaClient.

MongoDB should not follow the Prisma 7 SQL adapter workflow. Use the latest Prisma 6.x release for MongoDB projects and do not install a SQL @prisma/adapter-* package for it.

Example (PostgreSQL):

import 'dotenv/config'
import { PrismaClient } from '../generated/client'
import { PrismaPg } from '@prisma/adapter-pg'

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
const prisma = new PrismaClient({ adapter })

Prisma Client Setup (Required)

Prisma Client must be installed and generated for any database.

1. Install Prisma CLI and Prisma Client:

   npm install prisma --save-dev
   npm install @prisma/client

1. Add a generator block (prisma-client requires an explicit output path):

   generator client {
     provider = "prisma-client"
     output   = "../generated"
   }

1. Generate Prisma Client:

   npx prisma generate

1. For SQL providers, instantiate Prisma Client with the database-specific driver adapter:

   import { PrismaClient } from '../generated/client'
   import { PrismaPg } from '@prisma/adapter-pg'

   const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
   const prisma = new PrismaClient({ adapter })

1. Re-run prisma generate after every schema change.

Quick Reference

PostgreSQL

datasource db {
  provider = "postgresql"
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

MySQL

datasource db {
  provider = "mysql"
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

SQLite

datasource db {
  provider = "sqlite"
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

MongoDB

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

generator client {
  provider = "prisma-client-js"
}

For MongoDB, stay on the latest Prisma 6.x line and keep the connection URL in schema.prisma. Do not move a MongoDB project to the Prisma 7 SQL adapter setup.

Rule Files

See individual rule files for detailed setup instructions:

references/postgresql.md
references/mysql.md
references/sqlite.md
references/mongodb.md
references/sqlserver.md
references/cockroachdb.md
references/prisma-postgres.md
references/prisma-client-setup.md

How to Use

Choose the provider reference file for your database, then apply references/prisma-client-setup.md to complete client generation and adapter setup. For MongoDB, use references/mongodb.md instead of copying the SQL adapter examples or Prisma 7 config pattern.

CockroachDB Setup

Configure Prisma with CockroachDB.

Prerequisites

• CockroachDB cluster

1. Schema Configuration

In prisma/schema.prisma:

datasource db {
  provider = "cockroachdb"
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

2. Config Configuration

In prisma.config.ts:

import { defineConfig, env } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  datasource: {
    url: env('DATABASE_URL'),
  },
})

3. Environment Variable

In .env:

DATABASE_URL="postgresql://user:password@host:26257/db?sslmode=verify-full"

Note: CockroachDB uses the PostgreSQL wire protocol, so the URL often looks like postgresql, but the provider MUST be cockroachdb in the schema to handle specific CRDB features correctly.

Driver Adapter

Use a driver adapter for the standard SQL workflow. CockroachDB is PostgreSQL-compatible, so use the PostgreSQL adapter.

1. Install adapter and driver:

   npm install @prisma/adapter-pg pg

2. Instantiate Prisma Client with the adapter:

   import 'dotenv/config'
   import { PrismaClient } from '../generated/client'
   import { PrismaPg } from '@prisma/adapter-pg'

   const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
   const prisma = new PrismaClient({ adapter })

ID Generation

CockroachDB uses BigInt or UUID for IDs efficiently.

model User {
  id BigInt @id @default(autoincrement()) // Uses unique_rowid()
}

Or using string UUIDs:

model User {
  id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
}

Common Issues

Schema Introspection

Always use provider = "cockroachdb" to ensure correct type mapping during db pull.

MongoDB Setup

MongoDB projects should stay on the latest Prisma 6.x release. Do not upgrade a MongoDB app to Prisma 7's SQL client path.

Prerequisites

• MongoDB 4.2+
• Replica Set configured (required for transactions)
• Latest Prisma 6.x release, or your team's pinned Prisma 6 version
• Node.js 20.19.0+
• TypeScript 5.4.0+

1. Schema Configuration

Use the standard Prisma 6 MongoDB setup with prisma-client-js.

In prisma/schema.prisma:

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

generator client {
  provider = "prisma-client-js"
}

Driver Adapters

Do not apply the Prisma 7 SQL adapter setup here. MongoDB does not use a SQL @prisma/adapter-* package.

ID Field Requirement

MongoDB models must have a mapped _id field using @id and @map("_id"), usually of type String with auto() and db.ObjectId.

model User {
  id    String @id @default(auto()) @map("_id") @db.ObjectId
  email String @unique
  name  String?
}

Relations

Relations in MongoDB expect IDs to be db.ObjectId type.

model Post {
  id       String @id @default(auto()) @map("_id") @db.ObjectId
  author   User   @relation(fields: [authorId], references: [id])
  authorId String @db.ObjectId
}

2. Environment Variable

In .env:

DATABASE_URL="mongodb+srv://user:password@cluster.mongodb.net/mydb?retryWrites=true&w=majority"

Migrations vs Introspection

• No Migrations: MongoDB is schema-less. prisma migrate commands do not work.
• db push: Use prisma db push to sync indexes and constraints.
• db pull: Use prisma db pull to generate schema from existing data (sampling).

Current Verification Notes

• prisma init --datasource-provider mongodb is still implemented in Prisma's CLI source.
• Prisma's upstream repo still contains MongoDB fixtures and tests.
• Local verification shows Prisma 7 can still recognize MongoDB inputs, but the generated client path does not provide a supported MongoDB upgrade path.
• Local verification shows Prisma 6.x works end to end with prisma-client-js, prisma db push, and new PrismaClient() against a MongoDB replica set.

Version Guidance

• For MongoDB, stay on the latest available Prisma 6.x release.
• Treat Prisma 7 MongoDB migration attempts as unsupported until Prisma ships a real MongoDB upgrade path.

Common Issues

"Transactions not supported"

Ensure your MongoDB instance is a Replica Set. Standalone instances do not support transactions. Atlas clusters are replica sets by default.

"Invalid ObjectID"

Ensure fields referencing IDs are decorated with @db.ObjectId if the target is an ObjectID.

MySQL Setup

Configure Prisma with MySQL (or MariaDB).

Prerequisites

• MySQL or MariaDB database
• Connection string

1. Schema Configuration

In prisma/schema.prisma:

datasource db {
  provider = "mysql"
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

2. Config Configuration

In prisma.config.ts:

import { defineConfig, env } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  datasource: {
    url: env('DATABASE_URL'),
  },
})

3. Environment Variable

In .env:

DATABASE_URL="mysql://user:password@localhost:3306/mydb"

Connection String Format

mysql://USER:PASSWORD@HOST:PORT/DATABASE

• USER: Database user
• PASSWORD: Password
• HOST: Hostname
• PORT: Port (default 3306)
• DATABASE: Database name

Driver Adapter

Use a driver adapter for the standard SQL workflow.

1. Install adapter and driver:

   npm install @prisma/adapter-mariadb mariadb

2. Instantiate Prisma Client with the adapter:

   import 'dotenv/config'
   import { PrismaClient } from '../generated/client'
   import { PrismaMariaDb } from '@prisma/adapter-mariadb'

   const adapter = new PrismaMariaDb({
     host: 'localhost',
     port: 3306,
     connectionLimit: 5,
     user: process.env.MYSQL_USER,
     password: process.env.MYSQL_PASSWORD,
     database: process.env.MYSQL_DATABASE,
   })

   const prisma = new PrismaClient({ adapter })

Text protocol option

If you need the MariaDB driver's text protocol instead of the default binary execute() path, enable useTextProtocol explicitly:

import { PrismaClient } from '../generated/client'
import { PrismaMariaDb } from '@prisma/adapter-mariadb'

const adapter = new PrismaMariaDb(process.env.DATABASE_URL!, {
  useTextProtocol: true,
})

const prisma = new PrismaClient({ adapter })

Use this only when you specifically need text-protocol compatibility for your MariaDB setup.

PlanetScale Setup

PlanetScale uses MySQL but requires specific settings because it doesn't support foreign key constraints.

In prisma/schema.prisma:

datasource db {
  provider     = "mysql"
  relationMode = "prisma" // Emulate foreign keys in Prisma
}

Common Issues

"Too many connections"

MySQL has a connection limit. Adjust connection pool size in URL:

DATABASE_URL="mysql://...?connection_limit=5"

JSON Support

MySQL 5.7+ supports JSON. MariaDB 10.2+ supports JSON (as an alias for LONGTEXT with check constraints). Prisma handles this, but verify your version.

PostgreSQL Setup

Configure Prisma with PostgreSQL.

Prerequisites

• PostgreSQL database (local or cloud)
• Connection string

1. Schema Configuration

In prisma/schema.prisma:

datasource db {
  provider = "postgresql"
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

2. Config Configuration

In prisma.config.ts:

import { defineConfig, env } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  datasource: {
    url: env('DATABASE_URL'),
  },
})

3. Environment Variable

In .env:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

Connection String Format

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA

• USER: Database user
• PASSWORD: Password (URL encoded if special chars)
• HOST: Hostname (localhost, IP, or domain)
• PORT: Port (default 5432)
• DATABASE: Database name
• SCHEMA: Schema name (default public)

Driver Adapter

Use a driver adapter for the standard SQL workflow.

1. Install adapter and driver:

   npm install @prisma/adapter-pg pg

2. Instantiate Prisma Client with the adapter:

   import 'dotenv/config'
   import { PrismaClient } from '../generated/client'
   import { PrismaPg } from '@prisma/adapter-pg'

   const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
   const prisma = new PrismaClient({ adapter })

Common Issues

"Can't reach database server"

• Check host and port
• Check firewall settings
• Ensure database is running

"Authentication failed"

• Check user/password
• Special characters in password must be URL-encoded

"Schema does not exist"

• Ensure ?schema=public (or your schema) is in the URL

Prisma Client Setup

Generate and instantiate Prisma Client for Prisma's standard SQL provider workflow. For MongoDB, follow the provider-specific notes in references/mongodb.md instead of copying the SQL adapter example below.

1. Install dependencies

npm install prisma --save-dev
npm install @prisma/client

2. Add generator block

In prisma/schema.prisma:

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

prisma-client requires an explicit output path and does not generate into node_modules by default.

3. Generate Prisma Client

npx prisma generate

Re-run prisma generate after every schema change to keep the client in sync.

4. Instantiate Prisma Client

import { PrismaClient } from '../generated/client'
import { PrismaPg } from '@prisma/adapter-pg'

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
const prisma = new PrismaClient({ adapter })

If you change the generator output, update the import path to match. For the SQL provider workflow, replace PrismaPg with the adapter for your database.

5. Use a single instance

Each PrismaClient instance creates a connection pool. Reuse a single instance per app process to avoid exhausting database connections.

Prisma Postgres Setup

Configure Prisma with Prisma Postgres (Managed).

Overview

Prisma Postgres is a serverless, managed PostgreSQL database optimized for Prisma.

Setup via CLI

You can provision a Prisma Postgres instance directly via the CLI:

prisma init --db

This will: 1. Log you into Prisma Data Platform. 2. Create a new project and database instance. 3. Update your .env with the connection string.

Connection String

For Prisma CLI flows and Accelerate-style usage, you may see a prisma+postgres:// URL.

For Prisma Client with a driver adapter in Node.js, prefer the direct TCP connection string from the Prisma Postgres dashboard:

DATABASE_URL="postgres://identifier:key@db.prisma.io:5432/postgres?sslmode=require"

1. Schema Configuration

In prisma/schema.prisma:

datasource db {
  provider = "postgresql" // Use postgresql provider
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

2. Config Configuration

In prisma.config.ts:

import { defineConfig, env } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  datasource: {
    url: env('DATABASE_URL'),
  },
})

Driver Adapter

Use a driver adapter for Prisma Postgres in the standard SQL workflow.

Recommended for standard Node.js apps

1. Install adapter and driver:

   npm install @prisma/adapter-pg pg

2. Use the direct TCP connection string from Prisma Console:

   import 'dotenv/config'
   import { PrismaClient } from '../generated/client'
   import { PrismaPg } from '@prisma/adapter-pg'

   const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL })
   const prisma = new PrismaClient({ adapter })

PrismaPg also accepts the connection string directly:

const adapter = new PrismaPg(process.env.DATABASE_URL!)
const prisma = new PrismaClient({ adapter })

For PostgreSQL prepared statement naming, pass adapter options as the second argument:

import { createHash } from 'node:crypto'

const adapter = new PrismaPg(process.env.DATABASE_URL!, {
  statementNameGenerator: ({ sql }) =>
    `prisma_${createHash('sha1').update(sql).digest('hex').slice(0, 16)}`,
})

Edge/serverless option

Use the Prisma Postgres serverless driver only when you need HTTP/WebSocket transport in environments like Workers or Edge Functions:

npm install @prisma/adapter-ppg @prisma/ppg

import { PrismaClient } from '../generated/client'
import { PrismaPostgresAdapter } from '@prisma/adapter-ppg'

const prisma = new PrismaClient({
  adapter: new PrismaPostgresAdapter({
    connectionString: process.env.PRISMA_DIRECT_TCP_URL,
  }),
})

This serverless driver is the specialized path for HTTP/WebSocket-based edge and serverless runtimes, not the default recommendation for standard Node.js apps.

Features

• Serverless: Scales to zero.
• Caching: Integrated query caching (Accelerate).
• Real-time: Database events (Pulse).

Using with Prisma Client

Use the Prisma Postgres adapter shown above when instantiating Prisma Client.

SQLite Setup

Configure Prisma with SQLite.

Prerequisites

• None (file-based)

1. Schema Configuration

In prisma/schema.prisma:

datasource db {
  provider = "sqlite"
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

2. Config Configuration

In prisma.config.ts:

import { defineConfig, env } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  datasource: {
    url: env('DATABASE_URL'),
  },
})

3. Environment Variable

In .env:

DATABASE_URL="file:./dev.db"

Connection String Format

file:PATH

• PATH: Relative path to the database file. Check prisma.config.ts if you need to confirm how your app resolves it.

Driver Adapter

Use a driver adapter for the standard SQL workflow.

1. Install adapter and driver:

   npm install @prisma/adapter-better-sqlite3 better-sqlite3

2. Instantiate Prisma Client with the adapter:

   import { PrismaClient } from '../generated/client'
   import { PrismaBetterSqlite3 } from '@prisma/adapter-better-sqlite3'

   const adapter = new PrismaBetterSqlite3({
     url: process.env.DATABASE_URL ?? 'file:./dev.db',
   })

   const prisma = new PrismaClient({ adapter })

Using Driver Adapter (LibSQL / Turso)

For edge compatibility or Turso:

1. Install:

   npm install @prisma/adapter-libsql @libsql/client

2. Instantiate:

   import { PrismaClient } from '../generated/client'
   import { PrismaLibSql } from '@prisma/adapter-libsql'

   const adapter = new PrismaLibSql({
     url: process.env.TURSO_DATABASE_URL,
     authToken: process.env.TURSO_AUTH_TOKEN,
   })
   const prisma = new PrismaClient({ adapter })

Limitations

• No Enums: SQLite doesn't support enums (Prisma polyfills them or treats as String).
• No Scalar Lists: String[] is not supported directly.
• Concurrency: Write operations lock the file.

Common Issues

"Database file not found"

Ensure the path in DATABASE_URL is correct relative to where Prisma is running or the schema file. file:./dev.db creates it next to schema.

SQL Server Setup

Configure Prisma with Microsoft SQL Server.

Prerequisites

• SQL Server 2017, 2019, 2022, or Azure SQL
• TCP/IP enabled

1. Schema Configuration

In prisma/schema.prisma:

datasource db {
  provider = "sqlserver"
}

generator client {
  provider = "prisma-client"
  output   = "../generated"
}

2. Config Configuration

In prisma.config.ts:

import { defineConfig, env } from 'prisma/config'

export default defineConfig({
  schema: 'prisma/schema.prisma',
  datasource: {
    url: env('DATABASE_URL'),
  },
})

3. Environment Variable

In .env:

DATABASE_URL="sqlserver://localhost:1433;database=mydb;user=sa;password=Password123;encrypt=true;trustServerCertificate=true"

Connection String Format

sqlserver://HOST:PORT;database=DB;user=USER;password=PASS;encrypt=true;trustServerCertificate=true

• encrypt: Required for Azure (true).
• trustServerCertificate: True for self-signed certs (local dev).

Driver Adapter

Use a driver adapter for the standard SQL workflow.

1. Install adapter and driver:

   npm install @prisma/adapter-mssql mssql

2. Instantiate Prisma Client with the adapter:

   import 'dotenv/config'
   import { PrismaClient } from '../generated/client'
   import { PrismaMssql } from '@prisma/adapter-mssql'

   const adapter = new PrismaMssql({
     server: 'localhost',
     port: 1433,
     database: 'mydb',
     user: process.env.SQLSERVER_USER,
     password: process.env.SQLSERVER_PASSWORD,
     options: {
       encrypt: true,
       trustServerCertificate: true,
     },
   })

   const prisma = new PrismaClient({ adapter })

Common Issues

"Login failed for user"

• SQL Server auth vs Windows auth. Prisma typically uses SQL Server authentication (username/password).
• Ensure TCP/IP is enabled in SQL Server Configuration Manager.

"Table not found" (dbo schema)

Prisma assumes dbo schema by default. If using another schema, update the model or connection string? SQL Server provider mostly sticks to default schema.