Database Setup

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.

Database Provider Options

Neon DB (Recommended)

  • Serverless PostgreSQL with automatic scaling
  • Generous free tier (512MB storage, 10GB data transfer)
  • Instant database creation with endpoint URL
  • Branch-based development for testing
  • Built-in connection pooling
  • Excellent performance and reliability

Alternative PostgreSQL Providers

  • Supabase - PostgreSQL + additional services (auth, storage, real-time)
  • Railway - Simple deployment platform with PostgreSQL
  • PlanetScale - MySQL alternative (requires schema changes)
  • Local PostgreSQL - For development only
This guide focuses on Neon DB as the primary option, with notes for other PostgreSQL-compatible providers.

Quick Setup with Neon DB

1

Create Neon DB Account

  1. Visit neon.tech and sign up for a free account
  2. Click "Create a project"
  3. Choose your region (closest to your users for best performance)
  4. Give your project a meaningful name (e.g., "my-saas-app")
2

Get Database URL

After project creation, you'll see the connection details:
# Example Neon DB connection string
postgresql://username:password@endpoint.neon.tech/dbname?sslmode=require
Copy the Database URL - you'll need it for environment configuration.
3

Configure Environment Variables

Using the CLI tool (recommended):
pnpm init:env
Or 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.
4

Test Connection

Verify your database connection:
pnpm db:check
You should see: "✅ Database connection successful"

Initialize Database Schema

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";
1

Enable UUID Extension

REQUIRED FIRST STEP: Connect to your database and enable UUID support:Option 1: Using Neon DB Console
  1. Go to your Neon project dashboard
  2. Open SQL Editor
  3. Run this command:
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.
2

Push Initial Schema

After enabling UUID, create the database tables:
# Push schema to database (creates tables)
pnpm db:push
This creates all necessary tables for:
  • User authentication and profiles (UUID IDs)
  • Organizations and memberships (UUID IDs)
  • Subscriptions and billing (UUID IDs)
  • Blog posts and content (UUID IDs)
  • Blog posts and content
3

Seed Demo Data

Populate your database with demo data for development:
# Add demo users, organizations, and sample data
pnpm db:seed
This creates:
  • Demo admin user (admin@example.com)
  • Demo regular user (user@example.com)
  • Sample organization with projects
  • Test subscription plans
4

Open Database Studio

Explore your data with Drizzle Studio:
# Opens database management interface
pnpm db:studio
Access the studio at http://localhost:4983 to view and edit your data.

Neon DB Features

Database Branching

Neon DB supports branching for safe development: 
# Your production database
postgresql://username:password@main-endpoint.neon.tech/dbname

Connection Pooling

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

Monitoring and Analytics

  • Query performance insights in Neon console
  • Connection monitoring and usage statistics
  • Automatic backups and point-in-time recovery
  • Usage alerts for free tier limits

Alternative Provider Setup

Supabase PostgreSQL

If you prefer Supabase for additional services:
1

Create Supabase Project

  1. Visit supabase.com and create account
  2. Create new project with PostgreSQL
  3. Get connection string from Settings → Database
2

Configure URLs

# Supabase provides both pooled and direct connections
DATABASE_URL="postgresql://postgres.pooler:password@host:6543/postgres"
DIRECT_URL="postgresql://postgres:password@host:5432/postgres"

Railway PostgreSQL

For Railway deployments:
1

Add PostgreSQL Service

  1. Create Railway project
  2. Add PostgreSQL service from templates
  3. Get connection URL from service variables
2

Configure Environment

DATABASE_URL="postgresql://postgres:password@host:port/railway"
DIRECT_URL="postgresql://postgres:password@host:port/railway"

Local PostgreSQL

For local development only:
1

Install PostgreSQL

# macOS with Homebrew
brew install postgresql
brew services start postgresql

# Ubuntu/Debian
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
2

Create Database

# Create database and user
createdb my_saas_app
createuser -s my_user

# Set password
psql -c "ALTER USER my_user PASSWORD 'my_password';"
3

Configure Local URL

DATABASE_URL="postgresql://my_user:my_password@localhost:5432/my_saas_app"
DIRECT_URL="postgresql://my_user:my_password@localhost:5432/my_saas_app"

Database Management Commands

Schema Management

# 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

Data Management

# Seed database with demo data
pnpm db:seed

# Reset and re-seed (fresh start)
pnpm db:reset-seed

# Open database studio
pnpm db:studio

Production Commands

# Run migrations in production
NODE_ENV=production pnpm db:migrate

# Check database health
pnpm db:check

Troubleshooting

Connection Issues

# 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

Migration Errors

# Reset migrations (development only)
rm -rf drizzle/

# Generate fresh migrations
pnpm db:generate

# Apply to database
pnpm db:push

SSL Certificate Issues

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

Security Best Practices

Connection Security

  • Always use SSL in production (sslmode=require)
  • Restrict database access to application servers only
  • Use connection pooling to manage connections efficiently
  • Monitor connection usage to detect anomalies

Credentials Management

  • Never commit database URLs to version control
  • Use environment variables for all database configuration
  • Rotate database passwords regularly
  • Use read-only users for reporting queries

Backup Strategy

  • Enable automatic backups (Neon DB includes this)
  • Test restore procedures regularly
  • Keep backups encrypted and secure
  • Document recovery procedures
Your database is now configured! Continue with the First Run guide to test your complete setup.

Next Steps

After database setup:
  1. First Run - Test your complete configuration
  2. Authentication - Configure authentication providers
  3. Deployment - Deploy to production
Pro tip: Use Neon DB branching to create separate databases for development, staging, and production environments safely.
    Database Setup | ShipSaaS Documentation | ShipSaaS - Launch your SaaS with AI in days