Prisma Setup

If you're looking for the best ORM to pair with AxiomDB, we highly recommend Prisma! This guide will take you step-by-step through setting up Prisma with AxiomDB's dual-URL connection model, all the way from your initial configuration to production deployment patterns. Let's get started!

The dual-URL model

To give you both speed and security, AxiomDB provides two different connection URLs for each branch. Prisma uses them for different jobs:

Setting up your configuration

Environment variables

First, grab the connection strings for your branch and add both of them to your local .env file:

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

Next, let's configure your schema.prisma file to use both URLs:

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

Why do we need two different URLs?

You might wonder why we need two connection strings. Prisma Migrate needs direct access to PostgreSQL for a few things that connection poolers like PgBouncer just can't handle:

1. Creating the shadow database — When diffing your schema to see what's changed, Prisma needs to create a temporary database in the background. For safety, your regular runtime role (_rw) isn't allowed to create databases.
2. Advisory locks — During a migration, Prisma uses advisory locks to make sure two migrations don't run at the same time. Since PgBouncer runs in transaction mode, it doesn't support advisory locks.
3. Prepared statements — Certain migration tasks use prepared statements that PgBouncer might not proxy correctly.
4. DDL operations — Making schema changes (like CREATE TABLE or ALTER TABLE) requires higher database permissions, which are only granted to the owner role.

By separating these roles, your regular app role only has basic read/write access. This means that even if someone manages to compromise your app's credentials, they won't be able to drop tables or modify your schema!

Working with migration commands

During development

When you are building your app locally, you can use these commands to manage your database schema:

# 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

When you run migrate dev, Prisma uses your DIRECT_URL to:

1. Spin up a temporary shadow database using your owner role.
2. Compare your actual Prisma schema against that shadow database.
3. Generate the required SQL migration files.
4. Apply those migrations to your local development database.
5. Log the migration inside the _prisma_migrations table.

Deploying to production

When you are ready to roll out changes to production, use this command:

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

Unlike development commands, migrate deploy uses the DIRECT_URL to:

1. Scan your migrations/ folder for any migrations that haven't run yet.
2. Apply them sequentially to your database.
3. Record the completed migrations in the _prisma_migrations table.

Because migrate deploy doesn't need to spin up a shadow database or prompt you for confirmations, it is perfect for CI/CD pipelines and automated deployments.

Inspecting your schema

If you ever need to inspect an existing database or update your client, use these commands:

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

# Generate the Prisma client
npx prisma generate

Running db pull uses the DIRECT_URL to look at your database's actual schema and update your schema.prisma file to match. This is super helpful if you made changes to the database outside of Prisma—like editing tables directly in the AxiomDB console.

How the shadow database works

To create the temporary shadow database that Prisma needs to diff your schemas, the direct owner role (_owner) has special CREATEDB permissions.

Here is the exact lifecycle of the shadow database during migrate dev:

1. Prisma creates a temporary database (like _prisma_shadow_db).
2. It runs all of your existing migrations on it.
3. It applies your new schema modifications to the shadow database.
4. It compares the shadow database against your real database to find any differences.
5. It writes those differences into a new SQL migration file.
6. It cleans up by dropping the shadow database.
7. Finally, it applies the new migration to your actual database.

Since this workflow requires creating and dropping databases, it needs the CREATEDB privilege, which is reserved for the owner role.

Fixing shadow database issues

If Prisma complains that it cannot create a shadow database, don't panic! Try these troubleshooting steps:

1. Check that directUrl is defined in your schema.prisma file.
2. Double-check that your DIRECT_URL points to port 5432 (direct PostgreSQL) and not port 6432 (PgBouncer).
3. Make sure your current IP address is allowed in your network settings.
4. Confirm that your owner role actually has CREATEDB privileges by running:

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

Making PgBouncer work with Prisma

AxiomDB sets up PgBouncer on port 6432 in session mode to ensure it plays nicely with Prisma.

Why session mode?

PgBouncer can run in three different modes:

Prisma requires session mode because it relies on prepared statements to optimize queries, keeps connections open as long as the application is running, and needs consistent session affinity to handle advisory locks during migrations.

Best practices for connection pooling

To keep your connection count within PgBouncer's limits, configure your schema:

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, initialize the Prisma client with your pooled URL:

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

Choosing your pool size

By default, Prisma sets the pool size to num_cpus * 2 + 1. We recommend these pool sizes depending on your environment:

Just make sure that your application's connection pool size doesn't exceed PgBouncer's default_pool_size limit for your database!

Connecting from other frameworks

If you ever need to connect to your database using tools outside of Prisma, the AxiomDB web console has you covered! Click the Connect button on any branch to grab pre-formatted connection blocks for:

• Prisma — Your schema.prisma datasource block and matching .env variables.
• Drizzle — Connection configurations for drizzle.config.ts.
• Kysely — TypeScript pool configurations.
• node-postgres — Setup code for pg.Pool.
• SQLAlchemy — Connection strings for Python.
• Django — Configurations for your DATABASES settings file.
• Laravel — Database settings for your .env file.
• Go pgx — Settings for initializing a pgxpool.
• Rust SQLx — Setup code for a Pool<Postgres> pool.

Deploying to Production

Setting up a CI/CD pipeline

Here is a simple example of how to deploy your app and apply database migrations using a GitHub Actions workflow:

# 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

Organizing your environments

We recommend mapping different database branches to match your hosting environments:

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

You can then configure each environment's .env file to target the correct branch:

# 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"

A safer way to run and roll back migrations

With database branching, you can make your deployments much safer:

1. Take a snapshot — Create a backup of your target branch first.
2. Test on a feature branch — Create a temporary feature branch and test your migrations there.
3. Verify — Make sure your schema changes and data look correct on the feature branch.
4. Deploy to production — Go ahead and run the migrations against your main database branch.
5. Roll back if needed — If anything goes wrong, you can quickly restore your snapshot to 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 common issues

Seeing a "Can't create database" error?

• Why it's happening: Prisma is trying to run migrations using the restricted runtime database role instead of the owner role.
• How to fix it: Double-check that your directUrl is defined and that DIRECT_URL in your .env points to port 5432 (direct PostgreSQL).

Seeing a "prepared statement does not exist" error?

• Why it's happening: Prisma is trying to use prepared statements while PgBouncer is running in transaction mode.
• How to fix it: AxiomDB runs PgBouncer in session mode by default. If you've customized PgBouncer, make sure to change the pool mode to session:

[pgbouncer]
pool_mode = session

Getting a "Connection refused" error during migrations?

• Why it's happening: Your computer's IP address hasn't been added to your network allowlist for direct access (port 5432).
• How to fix it: Simply add your IP address to your network settings:

axm network allow --current

Are migrations running slowly?

• Why it's happening: Running migrations on very large tables with millions of rows can take some time.
• How to fix it:
  • Use migrate deploy instead of migrate dev in your production environments to bypass shadow database checks.
  • Schedule your migrations during your application's lowest traffic hours.
  • Consider setting relationMode = "prisma" in your schema so Prisma handles relation checks in code instead of database constraints.

Seeing a error about _prisma_migrations not being found?

• Why it's happening: You have spun up a new branch but haven't run any Prisma migrations on it yet.
• How to fix it: Run npx prisma migrate dev --name init to set up your migration tracking table and apply your initial schema. Don't worry—it's normal for monitoring tools to report a "not configured" status until your migrations table exists.