Database Configuration

This guide provides comprehensive instructions for configuring PostgreSQL database for SaaS Core, covering both local development and production environments.

Database Overview

SaaS Core uses the following database technology stack:

PostgreSQL: Primary database
Drizzle ORM: Database ORM and query builder
Drizzle Kit: Database migration tool

Local Development Environment Setup

1. Install PostgreSQL

macOS (using Homebrew)

# Install PostgreSQL
brew install postgresql@15

# Start PostgreSQL service
brew services start postgresql@15

# Create database user
createuser -s postgres

Ubuntu/Debian

# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Switch to postgres user
sudo -u postgres psql

Windows

Download PostgreSQL installer
Run the installer and follow the wizard to complete installation
Remember the superuser password you set

2. Create Development Database

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE saas_core_dev;

# Create application user
CREATE USER saas_user WITH PASSWORD 'your_secure_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE saas_core_dev TO saas_user;

# Exit
\q

3. Configure Environment Variables

Add to your .env.local file:

# Local development database
DATABASE_URL="postgresql://saas_user:your_secure_password@localhost:5432/saas_core_dev"

Production Database Options

1. Neon (Recommended)

Neon is a modern PostgreSQL cloud service:

Advantages

Free tier includes 0.5GB storage
Automatic backup and recovery
Branching functionality (similar to Git)
Serverless architecture
Excellent developer experience

Setup Steps

Visit Neon website and create an account

Create a new project:

Project name: saas-core
PostgreSQL version: 15
Region: Choose the region closest to your users

Get the connection string:

postgresql://username:[email protected]/dbname?sslmode=require

2. Supabase

Supabase provides PostgreSQL database with additional features:

Setup Steps

Visit Supabase and create an account
Create a new project
Get the connection string from Settings > Database

3. Railway

Railway is a simple and easy-to-use cloud platform:

Setup Steps

Visit Railway and log in
Create a new project and add PostgreSQL service
Get DATABASE_URL from the Variables tab

4. Other Options

AWS RDS: Enterprise-grade solution
Google Cloud SQL: Google Cloud Platform
Azure Database: Microsoft Cloud Platform
DigitalOcean Managed Databases: Simple and reliable

Drizzle ORM Configuration

1. Database Schema

View src/db/schema.ts for the complete database schema:

// Main data tables
export const users = pgTable('users', {
  id: text('id').primaryKey(),
  name: text('name'),
  email: text('email').notNull().unique(),
  emailVerified: timestamp('emailVerified', { mode: 'date' }),
  // Stripe related fields
  stripeCustomerId: text('stripeCustomerId'),
  subscriptionId: text('subscriptionId'),
  subscriptionStatus: text('subscriptionStatus'),
  // Points system
  points: integer('points').default(0),
  purchasedPoints: integer('purchasedPoints').default(0),
  giftedPoints: integer('giftedPoints').default(0),
  // Timestamps
  createdAt: timestamp('createdAt', { mode: 'date' }).defaultNow(),
  updatedAt: timestamp('updatedAt', { mode: 'date' }).defaultNow(),
})

2. Database Connection Configuration

View src/db/drizzle.ts for connection configuration:

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

const connectionString = process.env.DATABASE_URL!

// Disable prepared statements for compatibility with some cloud services
const client = postgres(connectionString, { prepare: false })

export const db = drizzle(client, { schema })

3. Drizzle Configuration File

View drizzle.config.ts:

import type { Config } from 'drizzle-kit'

export default {
  schema: './src/db/schema.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config

Database Migrations

1. Initialize Database

# Push schema to database (development environment)
pnpm run db:push

# Or generate and run migrations (recommended for production)
pnpm run db:generate
pnpm run db:migrate

2. Available Database Commands

# Push schema changes to database
pnpm run db:push

# Generate migration files
pnpm run db:generate

# Run migrations
pnpm run db:migrate

# Open database management interface
pnpm run db:studio

3. Migration File Management

Migration files are located in the drizzle/ directory:

drizzle/
├── 0000_fixed_spiral.sql          # Initial schema
├── 0001_newsletter_subscriptions.sql  # Newsletter feature
├── 0002_add_user_role.sql         # User roles
├── 0003_add_user_points.sql       # Points system
├── 0004_add_points_history.sql    # Points history
└── meta/                          # Metadata files

Database Management

1. Drizzle Studio

Drizzle Studio provides visual database management:

# Start Drizzle Studio
pnpm run db:studio

# Visit https://local.drizzle.studio

2. Common SQL Queries

-- View user statistics
SELECT
  COUNT(*) as total_users,
  COUNT(CASE WHEN "emailVerified" IS NOT NULL THEN 1 END) as verified_users,
  COUNT(CASE WHEN "subscriptionStatus" = 'active' THEN 1 END) as active_subscribers
FROM users;

-- View subscription statistics
SELECT
  "subscriptionPlan",
  COUNT(*) as count,
  SUM(points) as total_points
FROM users
WHERE "subscriptionStatus" = 'active'
GROUP BY "subscriptionPlan";

-- View points usage
SELECT
  action,
  COUNT(*) as count,
  SUM(points) as total_points
FROM "pointsHistory"
GROUP BY action
ORDER BY total_points DESC;

3. Data Backup

Local Backup

# Create backup
pg_dump -U saas_user -h localhost saas_core_dev > backup.sql

# Restore backup
psql -U saas_user -h localhost saas_core_dev < backup.sql

Cloud Service Backup

Most cloud services provide automatic backup features:

Neon: Automatic daily backups, retained for 7 days
Supabase: Automatic backups with manual snapshot creation
Railway: Automatic backups with downloadable backup files

Database Security

1. Connection Security

# Use SSL connection (required for production)
DATABASE_URL="postgresql://user:pass@host:5432/db?sslmode=require"

# Connection pool configuration
DATABASE_URL="postgresql://user:pass@host:5432/db?sslmode=require&connection_limit=20"

2. Access Control

-- Create read-only user (for analytics)
CREATE USER analytics_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE saas_core TO analytics_user;
GRANT USAGE ON SCHEMA public TO analytics_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_user;

-- Create backup user
CREATE USER backup_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE saas_core TO backup_user;
GRANT USAGE ON SCHEMA public TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

3. Data Encryption

Use strong passwords
Enable SSL/TLS connections
Regularly rotate passwords
Restrict network access

Performance Optimization

1. Index Optimization

View drizzle/add_performance_indexes.sql:

-- User email index (already has unique constraint)
-- CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- User subscription status index
CREATE INDEX CONCURRENTLY idx_users_subscription_status ON users(subscription_status)
WHERE subscription_status IS NOT NULL;

-- Points history user index
CREATE INDEX CONCURRENTLY idx_points_history_user_id ON points_history(user_id);

-- Points history timestamp index
CREATE INDEX CONCURRENTLY idx_points_history_created_at ON points_history(created_at);

2. Query Optimization

// Use indexed queries
const activeSubscribers = await db.query.users.findMany({
  where: eq(users.subscriptionStatus, 'active'),
  columns: {
    id: true,
    email: true,
    subscriptionPlan: true,
  }
})

// Paginated queries
const paginatedUsers = await db.query.users.findMany({
  limit: 20,
  offset: page * 20,
  orderBy: desc(users.createdAt)
})

3. Connection Pool Configuration

// Configure connection pool
const client = postgres(connectionString, {
  prepare: false,
  max: 20,          // Maximum connections
  idle_timeout: 20, // Idle timeout (seconds)
  connect_timeout: 10, // Connection timeout (seconds)
})

Testing Database Configuration

You can create test scripts to verify database configuration:

# Create test script (optional)
cat > scripts/test-database.js << 'EOF'
const { drizzle } = require('drizzle-orm/postgres-js');
const postgres = require('postgres');

async function testDatabase() {
  try {
    const client = postgres(process.env.DATABASE_URL, { prepare: false });
    const db = drizzle(client);

    // Test connection
    const result = await client`SELECT version()`;
    console.log('✅ Database connection successful');
    console.log('PostgreSQL version:', result[0].version);

    // Test if tables exist
    const tables = await client`
      SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
    `;

    console.log('✅ Table check:');
    tables.forEach(table => {
      console.log(`  - ${table.table_name}`);
    });

    await client.end();
    console.log('✅ Database test completed');

  } catch (error) {
    console.error('❌ Database test failed:', error.message);
  }
}

testDatabase();
EOF

# Run test
node scripts/test-database.js

Troubleshooting

1. Connection Failure

Problem: connection refused or timeout Solution:

Check if database service is running
Verify connection string format
Check firewall settings

2. SSL Connection Issues

Problem: SSL-related errors Solution:

# Disable SSL (development only)
DATABASE_URL="postgresql://user:pass@localhost:5432/db?sslmode=disable"

# Force SSL (production)
DATABASE_URL="postgresql://user:pass@host:5432/db?sslmode=require"

3. Migration Failure

Problem: Migration execution failed Solution:

Check database permissions
Verify schema file syntax
Execute SQL statements manually

📞 Need Help?

If you encounter issues during database configuration, consult the Drizzle official documentation or contact technical support.