This guide will help you set up a PostgreSQL database for your SaaS application. We recommend Neon DB as the primary choice for its simplicity, performance, and developer experience.
This guide focuses on Neon DB as the primary option, with notes for other PostgreSQL-compatible providers.
After project creation, you'll see the connection details:
# Example Neon DB connection string
postgresql://username:password@endpoint.neon.tech/dbname?sslmode=requireCopy the Database URL - you'll need it for environment configuration.
Using the CLI tool (recommended):
pnpm init:envOr manually add to your .env.local:
DATABASE_URL="postgresql://username:password@endpoint.neon.tech/dbname?sslmode=require"Only DATABASE_URL is required. The application uses this for all database connections.
Verify your database connection:
pnpm db:checkYou should see: "✅ Database connection successful"
Critical Setup Required: This application uses UUID primary keys throughout the database schema. You must enable the UUID extension before running any migrations:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";REQUIRED FIRST STEP: Connect to your database and enable UUID support:
Option 1: Using Neon DB Console
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";Option 2: Using any PostgreSQL client
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";Without this extension, all database operations will fail as the schema uses UUID primary keys throughout.
After enabling UUID, create the database tables:
# Push schema to database (creates tables)
pnpm db:pushThis creates all necessary tables for:
Populate your database with demo data for development:
# Add demo users, organizations, and sample data
pnpm db:seedThis creates:
admin@example.com)user@example.com)Explore your data with Drizzle Studio:
# Opens database management interface
pnpm db:studioAccess the studio at http://localhost:4983 to view and edit your data.
Neon DB supports branching for safe development:
# Your production database
postgresql://username:password@main-endpoint.neon.tech/dbnameNeon DB includes automatic connection pooling:
# Pooled connection (default)
DATABASE_URL="postgresql://username:password@endpoint.neon.tech/dbname?sslmode=require"
# Direct connection (if needed)
DIRECT_URL="postgresql://username:password@endpoint.neon.tech/dbname?sslmode=require&pgbouncer=true"If you prefer Supabase for additional services:
# Supabase provides both pooled and direct connections
DATABASE_URL="postgresql://postgres.pooler:password@host:6543/postgres"
DIRECT_URL="postgresql://postgres:password@host:5432/postgres"For Railway deployments:
DATABASE_URL="postgresql://postgres:password@host:port/railway"
DIRECT_URL="postgresql://postgres:password@host:port/railway"For local development only:
# macOS with Homebrew
brew install postgresql
brew services start postgresql
# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql# Create database and user
createdb my_saas_app
createuser -s my_user
# Set password
psql -c "ALTER USER my_user PASSWORD 'my_password';"DATABASE_URL="postgresql://my_user:my_password@localhost:5432/my_saas_app"
DIRECT_URL="postgresql://my_user:my_password@localhost:5432/my_saas_app"# Generate migration files
pnpm db:generate
# Apply migrations to database
pnpm db:migrate
# Push schema changes directly (development)
pnpm db:push
# Reset database (destructive)
pnpm db:clear# Seed database with demo data
pnpm db:seed
# Reset and re-seed (fresh start)
pnpm db:reset-seed
# Open database studio
pnpm db:studio# Run migrations in production
NODE_ENV=production pnpm db:migrate
# Check database health
pnpm db:check# Test database connection
pnpm db:check
# Common issues:
# 1. Incorrect DATABASE_URL format
# 2. Network/firewall blocking connection
# 3. Database not accepting connections
# 4. SSL mode configuration# Reset migrations (development only)
rm -rf drizzle/
# Generate fresh migrations
pnpm db:generate
# Apply to database
pnpm db:push# For providers requiring SSL
DATABASE_URL="postgresql://user:pass@host:port/db?sslmode=require"
# Disable SSL for local development
DATABASE_URL="postgresql://user:pass@localhost:5432/db?sslmode=disable"sslmode=require)Your database is now configured! Continue with the First Run guide to test your complete setup.
After database setup:
Pro tip: Use Neon DB branching to create separate databases for development, staging, and production environments safely.