Database Configuration

The Next.js SaaS Starter uses PostgreSQL as its database with Drizzle ORM for type-safe database operations and migrations.

Database Stack

Database: PostgreSQL 16.4 (Alpine Linux)
ORM: Drizzle ORM
Migration Tool: Drizzle Kit
Client Library: postgres (node-postgres)

Quick Start

The fastest way to get your database up and running:

# Run the interactive setup (recommended)
pnpm db:setup

# Generate migration files
pnpm db:generate

# Apply migrations to database
pnpm db:migrate

# Seed the database with initial data
pnpm db:seed

Database Setup Options

The pnpm db:setup script offers two options:

Option 1: Local PostgreSQL (Docker)

Recommended for development. Automatically sets up a PostgreSQL container. Requirements:

Docker installed and running

What it does:

Creates a docker-compose.yml file
Starts a PostgreSQL 16.4 container
Configures the database with default credentials
Sets POSTGRES_URL to postgres://postgres:postgres@localhost:54322/postgres

Docker Compose Configuration:

docker-compose.yml

services:
  postgres:
    image: postgres:16.4-alpine
    container_name: next_saas_starter_postgres
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "54322:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

The PostgreSQL container runs on port 54322 (not the default 5432) to avoid conflicts with other PostgreSQL installations.

Managing the Docker Container:

# Start the database
docker compose up -d

# Stop the database
docker compose down

# View logs
docker compose logs postgres

# Remove all data (destructive!)
docker compose down -v

Option 2: Remote PostgreSQL

Use a managed PostgreSQL service for development or production. Recommended Providers:

When prompted by pnpm db:setup, choose option R and enter your connection string.

Drizzle Configuration

The database configuration is defined in drizzle.config.ts:

drizzle.config.ts

import type { Config } from 'drizzle-kit';

export default {
  schema: './lib/db/schema.ts',
  out: './lib/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.POSTGRES_URL!,
  },
} satisfies Config;

schema

string

Path to your database schema definition (lib/db/schema.ts)

out

string

Directory where migration files are generated (lib/db/migrations)

dialect

string

Database dialect - postgresql in this case

dbCredentials.url

string

PostgreSQL connection string from the POSTGRES_URL environment variable

Database Schema

The starter includes the following tables (defined in lib/db/schema.ts):

Users Table

Stores user accounts and authentication information.

users: {
  id: serial (primary key)
  name: varchar(100)
  email: varchar(255) unique, not null
  passwordHash: text, not null
  role: varchar(20), default 'member'
  createdAt: timestamp, default now()
  updatedAt: timestamp, default now()
  deletedAt: timestamp (soft delete)
}

Teams Table

Stores team/organization information and Stripe subscription details.

teams: {
  id: serial (primary key)
  name: varchar(100), not null
  createdAt: timestamp, default now()
  updatedAt: timestamp, default now()
  stripeCustomerId: text unique
  stripeSubscriptionId: text unique
  stripeProductId: text
  planName: varchar(50)
  subscriptionStatus: varchar(20)
}

Team Members Table

Join table linking users to teams with roles.

teamMembers: {
  id: serial (primary key)
  userId: integer → users.id
  teamId: integer → teams.id
  role: varchar(50), not null
  joinedAt: timestamp, default now()
}

Activity Logs Table

Tracks user and team activities for audit trails.

activityLogs: {
  id: serial (primary key)
  teamId: integer → teams.id
  userId: integer → users.id
  action: text, not null
  timestamp: timestamp, default now()
  ipAddress: varchar(45)
}

Invitations Table

Manages team invitation system.

invitations: {
  id: serial (primary key)
  teamId: integer → teams.id
  email: varchar(255), not null
  role: varchar(50), not null
  invitedBy: integer → users.id
  invitedAt: timestamp, default now()
  status: varchar(20), default 'pending'
}

Database Commands

All database operations are available as npm scripts:

# Interactive setup - creates .env and optionally starts Docker
pnpm db:setup

Command Details

pnpm db:setup

command

Script: npx tsx lib/db/setup.tsInteractive setup wizard that:

Checks for Stripe CLI
Sets up PostgreSQL (Docker or remote)
Configures Stripe keys
Generates AUTH_SECRET
Creates .env file

pnpm db:generate

command

Script: drizzle-kit generateGenerates SQL migration files from schema changes. Run this after modifying lib/db/schema.ts.

pnpm db:migrate

command

Script: drizzle-kit migrateApplies pending migrations to your database. Safe to run multiple times.

pnpm db:seed

command

Script: npx tsx lib/db/seed.tsSeeds the database with:

Test user: [email protected] / admin123 (owner role)
Test team: “Test Team”
Stripe products: Base ( $8/month) and Plus ($ 12/month)

pnpm db:studio

command

Script: drizzle-kit studioLaunches Drizzle Studio - a web-based database GUI at https://local.drizzle.studio

Database Connection

The database client is initialized in lib/db/drizzle.ts:

lib/db/drizzle.ts

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
import dotenv from 'dotenv';

dotenv.config();

if (!process.env.POSTGRES_URL) {
  throw new Error('POSTGRES_URL environment variable is not set');
}

export const client = postgres(process.env.POSTGRES_URL);
export const db = drizzle(client, { schema });

The application will throw an error on startup if POSTGRES_URL is not set. Make sure your .env file is properly configured.

Seeding the Database

The seed script (lib/db/seed.ts) creates initial data for development and testing:

What Gets Seeded

1. Test User

Email: [email protected]
Password: admin123
Role: owner

2. Test Team

Name: “Test Team”
Test user is added as owner

3. Stripe Products Base Plan:

Price: $8/month
Trial: 7 days

Plus Plan:

Price: $12/month
Trial: 7 days

The seed script creates Stripe products in your connected Stripe account. Make sure you’re using test mode during development.

Running the Seed Script

pnpm db:seed

This will:

Connect to your database
Create the test user and team
Create Stripe products and prices
Exit with success or error status

After seeding, you can log in with [email protected] / admin123 to test the application.

Migrations Workflow

When you modify the database schema:

Edit the schema in lib/db/schema.ts
Generate migration:
```
pnpm db:generate
```
This creates SQL files in lib/db/migrations/
Review the migration (recommended): Check the generated SQL to ensure it matches your intentions
Apply the migration:
```
pnpm db:migrate
```
Update TypeScript types: Drizzle automatically infers types from your schema. Import and use them:
```
import type { User, Team } from '@/lib/db/schema';
```

Using Drizzle Studio

Drizzle Studio provides a visual interface to browse and edit your database:

pnpm db:studio

This opens a web interface where you can:

Browse all tables and records
Run queries
Edit data directly
Explore relationships
View schema information

Drizzle Studio runs locally and connects directly to your database using the POSTGRES_URL from your .env file.

Production Considerations

Connection Pooling

For production deployments, consider using connection pooling:

import postgres from 'postgres';

const client = postgres(process.env.POSTGRES_URL!, {
  max: 10, // Maximum number of connections
  idle_timeout: 20,
  connect_timeout: 10,
});

Migrations in Production

Always test migrations in a staging environment before applying to production.

Options for running migrations in production:

Manual deployment: Run pnpm db:migrate during deployment
Build-time: Add migration to your build script
Automated: Use a database migration service

Backup Strategy

Regularly backup your production database:

Enable automated backups on your database provider
Test restore procedures
Store backups in multiple locations
Keep backups for at least 30 days

Troubleshooting

Connection Refused

If you see “connection refused” errors:

Verify Docker container is running: docker ps
Check the port is correct (54322 for local setup)
Ensure POSTGRES_URL in .env is correct

Migration Errors

If migrations fail:

Check database connection
Ensure no syntax errors in schema
Review the generated SQL in lib/db/migrations/
Check for conflicting migrations

Seed Script Fails

Common issues:

Database not migrated: Run pnpm db:migrate first
Stripe API error: Verify STRIPE_SECRET_KEY is set and valid
Duplicate data: Drop and recreate database if needed

Next Steps

Environment Variables

Configure all required environment variables

Stripe Setup

Set up payment processing and subscriptions

Get Started

Core Features

Configuration

Deployment

Database Stack

Quick Start

Database Setup Options

Option 1: Local PostgreSQL (Docker)

Option 2: Remote PostgreSQL

Drizzle Configuration

Database Schema

Users Table

Teams Table

Team Members Table

Activity Logs Table

Invitations Table

Database Commands

Command Details

Database Connection

Seeding the Database

What Gets Seeded

Running the Seed Script

Migrations Workflow

Using Drizzle Studio

Production Considerations

Connection Pooling

Migrations in Production

Backup Strategy

Troubleshooting

Connection Refused

Migration Errors

Seed Script Fails

Next Steps

Environment Variables

Stripe Setup

Build docs developers (and LLMs) love

Get Started

Core Features

Configuration

Deployment

​Database Stack

​Quick Start

​Database Setup Options

​Option 1: Local PostgreSQL (Docker)

​Option 2: Remote PostgreSQL

​Drizzle Configuration

​Database Schema

​Users Table

​Teams Table

​Team Members Table

​Activity Logs Table

​Invitations Table

​Database Commands

​Command Details

​Database Connection

​Seeding the Database

​What Gets Seeded

​Running the Seed Script

​Migrations Workflow

​Using Drizzle Studio

​Production Considerations

​Connection Pooling

​Migrations in Production

​Backup Strategy

​Troubleshooting

​Connection Refused

​Migration Errors

​Seed Script Fails

​Next Steps

Environment Variables

Stripe Setup

Build docs developers (and LLMs) love

Database Stack

Quick Start

Database Setup Options

Option 1: Local PostgreSQL (Docker)

Option 2: Remote PostgreSQL

Drizzle Configuration

Database Schema

Users Table

Teams Table

Team Members Table

Activity Logs Table

Invitations Table

Database Commands

Command Details

Database Connection

Seeding the Database

What Gets Seeded

Running the Seed Script

Migrations Workflow

Using Drizzle Studio

Production Considerations

Connection Pooling

Migrations in Production

Backup Strategy

Troubleshooting

Connection Refused

Migration Errors

Seed Script Fails

Next Steps