> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/Ishaq74/concordia/llms.txt
> Use this file to discover all available pages before exploring further.

# 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`:

```typescript drizzle-prod.config.ts theme={null}
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`:

```typescript drizzle-dev.config.ts theme={null}
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:

```bash .env theme={null}
# 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
```

<Warning>
  When `USE_PROD_DB=true`, all database commands target your production database. Handle with care!
</Warning>

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

<Steps>
  <Step title="Generate migration">
    Run the generate command:

    ```bash theme={null}
    npm run db:generate
    ```

    This creates SQL migration files in `src/database/migrations/`.
  </Step>

  <Step title="Review generated SQL">
    Check the generated `.sql` files to verify changes before applying.
  </Step>

  <Step title="Commit migrations">
    Add migration files to version control:

    ```bash theme={null}
    git add src/database/migrations/
    git commit -m "Add database migrations"
    ```
  </Step>
</Steps>

### Apply Migrations

Run migrations against your production database:

```bash theme={null}
# Set production flag
export USE_PROD_DB=true

# Run migrations
npm run db:migrate
```

<Warning>
  **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
</Warning>

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

```bash theme={null}
npm run db:migrate -- --reset
```

<Warning>
  The `--reset` flag is **destructive** and requires explicit confirmation, even in development.
</Warning>

## Database Seeding

Populate your database with initial data:

```bash theme={null}
# 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

```bash theme={null}
npm run db:seed -- --reset
```

## Database Synchronization

Sync data between development and production databases:

<CodeGroup>
  ```bash Development to Production theme={null}
  # ⚠️ This overwrites production data!
  npm run syncdb:dev-to-prod
  ```

  ```bash Production to Development theme={null}
  # Safe: pulls prod data to local
  npm run syncdb:prod-to-dev
  ```
</CodeGroup>

<Warning>
  **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
</Warning>

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

```bash theme={null}
pg_dump $DATABASE_URL_PROD > backup.sql
```

### Restore from Backup

```bash theme={null}
psql $DATABASE_URL_PROD < backup.sql
```

### Database Comparison

Compare schemas between environments:

```bash theme={null}
npm run db:compare
```

### Connection Check

Verify database connectivity:

```bash theme={null}
npm run db:check
```

## Production Checklist

<Steps>
  <Step title="Configure environment">
    Set `DATABASE_URL_PROD` with SSL-enabled connection string.
  </Step>

  <Step title="Run migrations">
    ```bash theme={null}
    USE_PROD_DB=true npm run db:migrate
    ```
  </Step>

  <Step title="Seed initial data (optional)">
    ```bash theme={null}
    USE_PROD_DB=true npm run db:seed
    ```
  </Step>

  <Step title="Verify tables">
    Connect with `psql` or GUI tool to verify schema.
  </Step>

  <Step title="Set up backups">
    Configure automated backups with your PostgreSQL provider.
  </Step>
</Steps>

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

```bash theme={null}
# 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:

```bash theme={null}
# Compare schemas
npm run db:compare

# Generate migrations from current schema
npm run db:generate

# Review and apply
npm run db:migrate
```

## Next Steps

<Card title="SMTP Configuration" icon="envelope" href="/deployment/smtp-configuration">
  Configure email delivery for authentication and notifications
</Card>

<Card title="Hosting" icon="server" href="/deployment/hosting">
  Deploy your application to production
</Card>