Database Setup

Configure PostgreSQL for production with migrations, seeding, and synchronization

Overview

Concordia uses PostgreSQL with Drizzle ORM for database management. This guide covers setting up your production database, running migrations, and managing data.

Database Configuration

Concordia uses separate Drizzle configurations for development and production environments.

Production Configuration

The production database is configured in drizzle-prod.config.ts:

drizzle-prod.config.ts

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/database/schemas/**/*.ts',
  out: './src/database/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL_PROD!,
  },
});

Development Configuration

For comparison, the development config uses DATABASE_URL_LOCAL:

drizzle-dev.config.ts

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/database/schemas.ts',
  out: './src/database/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL_LOCAL!,
  },
});

Environment Variables

Add these variables to your production .env file:

.env

# Database URLs
DATABASE_URL_LOCAL=postgresql://username:password@localhost:port/namedb
DATABASE_URL_TEST=postgresql://username:password@localhost:port/namedb_test
DATABASE_URL_PROD=postgresql://neondb_owner:example.neon.tech/neondb?sslmode=require&channel_binding=require

# Control which database to use
USE_PROD_DB=false
USE_DB_TEST=true

When USE_PROD_DB=true, all database commands target your production database. Handle with care!

PostgreSQL Setup

Concordia works with any PostgreSQL provider. Popular options include:

• Neon - Serverless PostgreSQL with branching
• Supabase - PostgreSQL with built-in auth and storage
• Railway - Simple PostgreSQL deployments
• AWS RDS - Managed PostgreSQL service
• Self-hosted - Traditional PostgreSQL instance

Connection String Format

Your production connection string should include SSL:

postgresql://username:password@host:port/database?sslmode=require

For Neon and similar providers, also add:

?sslmode=require&channel_binding=require

Running Migrations

Generate Migrations

Create migration files from your schema changes:

1. Generate migration

   Run the generate command:

   npm run db:generate
   

   This creates SQL migration files in src/database/migrations/.

2. Review generated SQL

   Check the generated .sql files to verify changes before applying.

3. Commit migrations

   Add migration files to version control:

   git add src/database/migrations/
   git commit -m "Add database migrations"
   

Apply Migrations

Run migrations against your production database:

# Set production flag
export USE_PROD_DB=true

# Run migrations
npm run db:migrate

Production Safety: The migrate script requires explicit confirmation when targeting production:

• You’ll be prompted to type “oui” to confirm
• Or set CONFIRM_PROD=oui to skip prompts (CI/CD)
• Connection details are masked in logs for security

Migration Features

The migration script (scripts/db/db.migrate.ts) includes:

• Safety confirmations for production databases
• Automatic rollback on errors
• Idempotent execution - safe to run multiple times
• Migration tracking via __drizzle_migrations table
• Reset option - --reset flag drops all tables (requires confirmation)

Reset Database (Destructive)

To drop all tables and start fresh:

npm run db:migrate -- --reset

The --reset flag is destructive and requires explicit confirmation, even in development.

Database Seeding

Populate your database with initial data:

# Target production
export USE_PROD_DB=true

# Run seed
npm run db:seed

Seed Data Location

Seed data files are stored in src/database/data/ with the naming convention:

01-users.data.ts
02-blog_posts.data.ts
03-blog_categories.data.ts

The numeric prefix controls insertion order to handle foreign key dependencies.

Seed Features

• Auto-matching: Automatically matches data files to schema tables
• Conflict handling: Uses onConflictDoNothing() for safe re-runs
• Production safety: Requires CONFIRM_PROD=oui for production
• Reset option: --reset flag truncates all tables first

Seed with Reset

npm run db:seed -- --reset

Database Synchronization

Sync data between development and production databases:

Development to Production

# ⚠️ This overwrites production data!
npm run syncdb:dev-to-prod

Dev-to-Prod sync is destructive! It truncates production tables and copies development data.

Always:

• Backup production first
• Confirm when prompted
• Use during initial setup, not ongoing operations

How Sync Works

The sync script (scripts/db/db.sync.ts):

1. Connects to both databases
2. Gets list of all tables
3. For each table:
   • Reads all rows from source
   • Truncates destination table
   • Inserts all rows with proper column mapping
4. Maintains referential integrity with CASCADE

Backup and Maintenance

Manual Backup

Use pg_dump for backups:

pg_dump $DATABASE_URL_PROD > backup.sql

Restore from Backup

psql $DATABASE_URL_PROD < backup.sql

Database Comparison

Compare schemas between environments:

npm run db:compare

Connection Check

Verify database connectivity:

npm run db:check

Production Checklist

1. Configure environment

   Set DATABASE_URL_PROD with SSL-enabled connection string.

2. Run migrations

   USE_PROD_DB=true npm run db:migrate
   
3. Seed initial data (optional)

   USE_PROD_DB=true npm run db:seed
   
4. Verify tables

   Connect with psql or GUI tool to verify schema.

5. Set up backups

   Configure automated backups with your PostgreSQL provider.

Troubleshooting

Migration Errors

If migrations fail:

1. Check __drizzle_migrations table - see which migrations applied
2. Review SQL syntax - some PostgreSQL features vary by version
3. Check permissions - ensure user has CREATE/ALTER rights
4. Manual rollback - migrations use transactions, so failures rollback automatically

Connection Issues

# Test connection
npm run db:check

# Verify SSL settings in connection string
# Most managed PostgreSQL requires: ?sslmode=require

Schema Drift

If production schema differs from expected:

# Compare schemas
npm run db:compare

# Generate migrations from current schema
npm run db:generate

# Review and apply
npm run db:migrate

Next Steps

SMTP Configuration

Configure email delivery for authentication and notifications

Hosting

Deploy your application to production