Database

PostgreSQL setup, Drizzle ORM migration commands, and database management for AI Review.

AI Review uses PostgreSQL as its database. Schema migrations are managed with Drizzle ORM.

Configuration

Set the DATABASE_URL environment variable to your PostgreSQL connection string:

DATABASE_URL="postgresql://user:password@host:5432/ai_review"

This is the only required database environment variable. The application connects using Drizzle’s PostgreSQL client.

Migration commands

Development

Run migrations against the development database:

pnpm db:migrate:dev

Or using the script directly:

./scripts/db-migrate.sh development migrate

The development environment does not prompt for confirmation before running.

To push schema changes directly without generating a migration file (development only):

pnpm db:push:dev

db:push overwrites the database schema without generating a migration history. Only use it in development environments.

Staging

Run migrations against the staging database:

pnpm db:migrate:staging

Or using the script directly:

./scripts/db-migrate.sh staging migrate

Non-development environments require you to type the environment name to confirm before the migration runs.

Production

Run migrations against the production database:

pnpm db:migrate:production

Or using the script directly:

./scripts/db-migrate.sh production migrate

Non-development environments require you to type the environment name (production) at the confirmation prompt before the migration executes. This is a safety guard against accidental runs.

Production migration best practices:

• Run the migration before deploying the new application version
• Confirm a valid backup exists before running
• Use a minimal-privilege database account for the application process
• Validate the migration on staging first

Drizzle Studio

Open the Drizzle Studio GUI to inspect and manage the database:

pnpm --filter server db:studio

This starts a local web interface connected to the database configured via DATABASE_URL.

Generating migrations

When you modify the Drizzle schema files, generate a new migration:

pnpm --filter server db:generate

This creates a new migration file in the server’s migrations directory. Commit this file alongside your schema changes.

Development data directory

The development Docker Compose configuration stores PostgreSQL data in:

./data/postgres/

The full development data layout is:

./data/
├── logs/
├── postgres/
├── redis/
└── gitlab/

Resetting the development environment

pnpm env:reset is a destructive operation. It deletes all local development containers, volumes, and the contents of ./data. This cannot be undone.

pnpm env:reset

The script will prompt you to confirm before proceeding. After the reset, re-run pnpm env:init to restore the development environment and re-run migrations.