Prisma Setup

Prisma is the recommended ORM for AxiomDB. This guide covers every aspect of integrating Prisma with AxiomDB's dual-URL connection model, from initial configuration through production deployment patterns.

The dual-URL model

AxiomDB provides two URLs per branch. Prisma uses them for different purposes:

Configuration

Environment variables

Use both URLs in .env:

DATABASE_URL="postgresql://square_experience_rw:***@db.squareexp.com:6432/sq_square_experience_main?sslmode=require"
DIRECT_URL="postgresql://square_experience_owner:***@db.squareexp.com:5432/sq_square_experience_main?sslmode=require"

Schema configuration

Wire both in schema.prisma:

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

Why two URLs are required

Prisma Migrate needs direct Postgres access for operations that PgBouncer cannot safely proxy:

1. Shadow database creation — Prisma creates a temporary database to diff your schema against the current state. The runtime role (_rw) intentionally cannot create databases — this is a security boundary.
2. Advisory locks — Migrations use advisory locks to prevent concurrent schema changes. PgBouncer in transaction mode doesn't support advisory locks.
3. Prepared statements — Some migration operations use prepared statements that PgBouncer may not handle correctly.
4. DDL operations — CREATE TABLE, ALTER TABLE, CREATE INDEX, and other DDL operations require the owner role.

The runtime role only has CONNECT and read/write privileges. It cannot create databases, manage roles, or perform DDL operations. If your application's database credentials are compromised, the attacker cannot modify the schema.

Migration commands

Development

# Create a new migration from schema changes
npx prisma migrate dev --name <migration-name>

# Reset the database (drops all data, reapplies migrations)
npx prisma migrate reset

# Check migration status
npx prisma migrate status

migrate dev uses DIRECT_URL to:

1. Create a shadow database (temporary, using the owner role).
2. Diff your Prisma schema against the shadow database.
3. Generate SQL migration files.
4. Apply the migration to the development database.
5. Record the migration in _prisma_migrations.

Production

# Apply pending migrations (no shadow database, no prompts)
npx prisma migrate deploy

migrate deploy uses DIRECT_URL to:

1. Read pending migrations from the migrations/ directory.
2. Apply them in order.
3. Record each migration in _prisma_migrations.

migrate deploy does not create a shadow database, does not generate new migrations, and does not prompt for confirmation. It is designed for CI/CD pipelines and production deployments.

Schema inspection

# Pull the current database schema into Prisma
npx prisma db pull

# Generate the Prisma client
npx prisma generate

db pull uses DIRECT_URL to introspect the database schema and update your schema.prisma file. This is useful when the database schema was modified outside of Prisma (e.g., through the SQL editor or manual migrations).

Shadow database permissions

The direct owner role (_owner) is allowed to create the temporary shadow database Prisma needs. Runtime roles are intentionally not allowed to create databases.

How the shadow database works

When you run migrate dev, Prisma:

1. Creates a temporary database (e.g., _prisma_shadow_db).
2. Applies all existing migrations to the shadow database.
3. Applies your schema changes to the shadow database.
4. Diffs the shadow database against the current database.
5. Generates a migration SQL file.
6. Drops the shadow database.
7. Applies the migration to the real database.

This process requires the CREATEDB privilege, which only the owner role has.

Troubleshooting shadow database errors

If Prisma says it cannot create a shadow database:

1. Verify directUrl is set in schema.prisma.
2. Verify DIRECT_URL points at port 5432 (direct Postgres, not PgBouncer).
3. Verify your device IP is allowlisted for direct access.
4. Verify the owner role has CREATEDB privilege.

-- Check role privileges
SELECT rolname, rolcreatedb FROM pg_roles WHERE rolname LIKE '%_owner';

PgBouncer compatibility

AxiomDB runs PgBouncer on port 6432 in session mode for Prisma compatibility.

Why session mode

PgBouncer supports three pooling modes:

Prisma requires session mode because it:

• Uses prepared statements for query optimization.
• Holds connections open for the lifetime of the application process.
• Uses advisory locks during migrations (which require session affinity).

Connection pooling best practices

Configure Prisma's connection pool to work within PgBouncer's limits:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
  // Connection pool configuration
  relationMode = "prisma" // Optional: use Prisma's relation mode
}

In your application code, configure the Prisma client with appropriate pool settings:

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
});

Connection pool sizing

The default Prisma connection pool size is num_cpus * 2 + 1. For most applications:

PgBouncer's default_pool_size controls how many Postgres connections each user/database pair can use. Ensure your Prisma pool size doesn't exceed PgBouncer's limits.

Framework snippets

The AxiomDB console and CLI provide pre-formatted connection blocks for every major framework. Click Connect on any branch to see snippets for:

• Prisma — schema.prisma datasource block + .env variables.
• Drizzle — drizzle.config.ts with connection string.
• Kysely — TypeScript pool configuration.
• node-postgres — pg.Pool configuration.
• SQLAlchemy — Python connection string.
• Django — DATABASES settings.
• Laravel — .env database configuration.
• Go pgx — pgxpool configuration.
• Rust SQLx — Pool<Postgres> configuration.

Production deployment

CI/CD pipeline

# Example GitHub Actions workflow
name: Deploy
on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Install dependencies
        run: npm ci

      - name: Run migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          DIRECT_URL: ${{ secrets.DIRECT_URL }}

      - name: Generate Prisma client
        run: npx prisma generate

      - name: Deploy application
        run: npm run deploy

Environment-specific URLs

Use different branches for different environments:

main branch     → production
staging branch  → staging environment
dev branch      → development environment
feature-*       → preview environments

Each environment gets its own .env with branch-specific URLs:

# Production
DATABASE_URL="postgresql://...@db.squareexp.com:6432/sq_app_main?sslmode=require"
DIRECT_URL="postgresql://...@db.squareexp.com:5432/sq_app_main?sslmode=require"

# Staging
DATABASE_URL="postgresql://...@db.squareexp.com:6432/sq_app_main_br_staging?sslmode=require"
DIRECT_URL="postgresql://...@db.squareexp.com:5432/sq_app_main_br_staging?sslmode=require"

Migration rollback strategy

AxiomDB's branch model makes migration rollbacks safer:

1. Before migration — Create a snapshot of the target branch.
2. Run migration — Apply the migration to a feature branch first.
3. Test — Verify schema changes and data integrity on the feature branch.
4. Deploy to main — Apply the same migration to the main branch.
5. Rollback if needed — Restore from the snapshot into a new branch.

# Create snapshot before risky migration
axm backups create --name pre-migration-2026-05-08

# Create feature branch for testing
axm branches create --name test-migration --source main --lifespan 7d

# Get branch URLs
axm branches urls --name test-migration

# Run migration against feature branch
npx prisma migrate dev --name add-new-table

# Test thoroughly...

# Apply to main when ready
axm branches urls --name main
npx prisma migrate deploy

Troubleshooting

"Can't create database" error

Cause: The runtime role is being used for migrations instead of the owner role.

Fix: Verify DIRECT_URL is set and points at port 5432. Prisma uses directUrl for shadow database creation.

"prepared statement does not exist" error

Cause: Prisma is trying to use prepared statements through PgBouncer in transaction mode.

Fix: AxiomDB runs PgBouncer in session mode by default. If you're using a custom PgBouncer configuration, switch to session mode:

[pgbouncer]
pool_mode = session

"connection refused" on migration

Cause: Your device IP is not allowlisted for direct access (port 5432).

Fix: Add your IP to the network allowlist:

axm network allow --current

Migrations are slow

Cause: Large tables with many rows can make migrations slow.

Fix: Consider:

• Using migrate deploy instead of migrate dev in production (no shadow database).
• Running migrations during low-traffic periods.
• Using Prisma's relationMode = "prisma" for better control over foreign key constraints.

_prisma_migrations table not found

Cause: The branch database was created without running any Prisma migrations.

Fix: Run npx prisma migrate dev --name init to create the migrations table and apply initial migrations. The monitoring endpoints return not_configured when the table doesn't exist — this is expected behavior, not an error.