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

> How to generate and run database migrations with Drizzle ORM

Concordia uses Drizzle ORM for type-safe database migrations. Migrations are automatically generated from schema changes and can be applied to development or production databases.

## Migration System Overview

The migration system uses:

* **Drizzle Kit**: CLI tool for generating migrations from schema files
* **PostgreSQL**: Target database (local or production)
* **TypeScript**: Migration scripts with type safety
* **Version Control**: Migration history tracked in `__drizzle_migrations` table

## Configuration Files

The project has separate configuration files for development and production:

### Development Config

```typescript theme={null}
// 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!,
  },
});
```

### Production Config

```typescript theme={null}
// 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!,
  },
});
```

<Note>
  The production config uses a glob pattern to load all schema files, while development uses a single export file.
</Note>

## Environment Variables

Set these in your `.env` file:

```bash theme={null}
# Local development database
DATABASE_URL_LOCAL=postgresql://user:password@localhost:5432/concordia_dev

# Production database
DATABASE_URL_PROD=postgresql://user:password@host:5432/concordia_prod

# Use production database (default: false)
USE_PROD_DB=false

# Required for production operations
CONFIRM_PROD=oui
```

## Generating Migrations

### Basic Generation

After modifying schema files, generate a migration:

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

This command:

1. Scans schema files in `src/database/schemas/`
2. Compares with the current database state
3. Generates SQL migration files
4. Updates the migration journal

### Generation Process

The generation script (`scripts/db/db.generate.ts`) automatically:

* **Validates schema files** - Ensures all schema files exist and are not empty
* **Checks for missing imports** - Prompts to add unamported schema files to `schemas.ts`
* **Creates migration directory** - Auto-creates `src/database/migrations/meta/` if needed
* **Initializes journal** - Creates `_journal.json` if it doesn't exist
* **Detects new migrations** - Shows which migration files were created

### Interactive Import Management

If you add new schema files, the generator will prompt you:

```bash theme={null}
[INTERACTIF] Certains fichiers ne sont pas importés dans schemas.ts :
  [1] services_bookings.ts
  [2] notification.ts
Voulez-vous ajouter des imports à schemas.ts ? (ex: 1,2 ou rien pour ignorer) :
```

Enter the numbers of schemas to import, separated by commas.

### Production Generation

To generate migrations for production:

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

<Warning>
  Production generation compares against the production database. Make sure you're connected to the correct database!
</Warning>

## Migration Files

Migrations are stored in `src/database/migrations/`:

```
src/database/migrations/
├── 0002_narrow_silver_samurai.sql
├── 0003_calm_chat.sql
├── 0004_tricky_captain_flint.sql
├── 0005_harsh_metal_master.sql
└── meta/
    ├── _journal.json
    ├── 0002_snapshot.json
    ├── 0003_snapshot.json
    ├── 0004_snapshot.json
    └── 0005_snapshot.json
```

### Migration File Format

Each migration is a `.sql` file with SQL statements:

```sql theme={null}
-- Example: 0005_harsh_metal_master.sql
CREATE TABLE "services_media" (
	"id" text PRIMARY KEY NOT NULL,
	"url" text NOT NULL,
	"content_url" text,
	"type" text NOT NULL,
	"encoding_format" text,
	"width" text,
	"height" text,
	"duration" text,
	"license" text,
	"copyright_holder" text,
	"caption" jsonb,
	"description" jsonb,
	"alt" jsonb,
	"thumbnail_url" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "services_categories" (
	"id" text PRIMARY KEY NOT NULL,
	"slug" text NOT NULL,
	"name" jsonb NOT NULL,
	"description" jsonb,
	"icon" text,
	"featured_image_id" text,
	"parent_id" text,
	"sort_order" integer DEFAULT 0 NOT NULL,
	"display_in_home" boolean DEFAULT false NOT NULL,
	"display_in_menu" boolean DEFAULT true NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"is_featured" boolean DEFAULT false NOT NULL,
	"seo_title" jsonb,
	"seo_description" jsonb,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "services_categories_slug_unique" UNIQUE("slug")
);
```

### Migration Journal

The `_journal.json` file tracks migration history:

```json theme={null}
{
  "entries": [
    {
      "idx": 0,
      "version": "7",
      "when": 1771714997015,
      "tag": "0000_harsh_lady_vermin",
      "breakpoints": true
    },
    {
      "idx": 5,
      "version": "7",
      "when": 1772543861570,
      "tag": "0005_harsh_metal_master",
      "breakpoints": true
    }
  ]
}
```

## Running Migrations

### Apply Pending Migrations

Run all pending migrations:

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

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

1. Connects to the database
2. Creates `__drizzle_migrations` table if needed
3. Checks which migrations have been applied
4. Runs pending migrations in order
5. Records each migration in the tracking table

### Migration Output

```bash theme={null}
═══════════════════════════════════════════════════════
   🚀 Migration Drizzle - Connexion local/dev 🚀
═══════════════════════════════════════════════════════

Cible DB: postgresql://***@localhost:5432/concordia_dev (local/dev)
Connexion au client PostgreSQL...

[migrate] → 0005_harsh_metal_master

✔️  Toutes les migrations ont été appliquées.

═══════════════════════════════════════════════════════
```

### Production Migrations

To run migrations on production:

```bash theme={null}
USE_PROD_DB=true npm run db:migrate
```

You'll be prompted for confirmation:

```bash theme={null}
 PROD ATTENTION !! Vous ciblez la base de production : concordia_prod

Êtes-vous sûr de vouloir exécuter les migrations sur PROD ? (oui/non):
```

Or set `CONFIRM_PROD=oui` to skip the prompt:

```bash theme={null}
USE_PROD_DB=true CONFIRM_PROD=oui npm run db:migrate
```

### No Pending Migrations

If all migrations are already applied:

```bash theme={null}
✔️  Aucun changement — aucune migration en attente pour la base concordia_dev (local/dev).

✔️  Aucune migration à appliquer.
Toutes les migrations sont déjà appliquées.
```

## Migration Tracking

Migrations are tracked in the `__drizzle_migrations` table:

```sql theme={null}
CREATE TABLE __drizzle_migrations (
  id text PRIMARY KEY,
  hash text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now()
);
```

Query applied migrations:

```sql theme={null}
SELECT * FROM __drizzle_migrations ORDER BY created_at ASC;
```

## Database Reset (Development)

To completely reset the database and rerun all migrations:

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

<Warning>
  This will **DROP ALL TABLES** in the database. Only use this in development!
</Warning>

The reset process:

1. Prompts for confirmation
2. Drops all tables in the public schema
3. Clears the migration history
4. Reruns all migrations from scratch

## Migration Best Practices

### 1. Always Generate Before Migrating

```bash theme={null}
# Generate migrations first
npm run db:generate

# Review the generated SQL files
cat src/database/migrations/0006_*.sql

# Then apply them
npm run db:migrate
```

### 2. Version Control

Commit migration files to git:

```bash theme={null}
git add src/database/migrations/
git commit -m "Add user profile table migration"
```

### 3. Never Modify Applied Migrations

Once a migration has been applied (especially in production), never modify it. Instead:

1. Create a new migration to fix the issue
2. Generate it with `npm run db:generate`
3. Apply it with `npm run db:migrate`

### 4. Test Migrations Locally First

Always test migrations in development before production:

```bash theme={null}
# 1. Generate migration
npm run db:generate

# 2. Test locally
npm run db:migrate

# 3. Verify application works
npm run dev

# 4. Deploy to production
USE_PROD_DB=true CONFIRM_PROD=oui npm run db:migrate
```

### 5. Handle Schema Changes Carefully

<Tabs>
  <Tab title="Adding Columns">
    Safe - Add new nullable columns or columns with defaults:

    ```typescript theme={null}
    // Safe: nullable column
    export const user = pgTable("user", {
      // ... existing fields
      bio: text("bio"), // nullable by default
    });

    // Safe: column with default
    export const post = pgTable("post", {
      // ... existing fields
      views: integer("views").default(0).notNull(),
    });
    ```
  </Tab>

  <Tab title="Removing Columns">
    Be careful - Ensure no code references the column:

    ```typescript theme={null}
    // Before removing, search codebase for references
    // Then remove from schema
    export const user = pgTable("user", {
      id: text("id").primaryKey(),
      name: text("name").notNull(),
      // oldField: text("old_field"), // REMOVED
    });
    ```
  </Tab>

  <Tab title="Renaming Columns">
    Requires data migration:

    ```typescript theme={null}
    // 1. Add new column
    export const user = pgTable("user", {
      // ... existing
      fullName: text("full_name"), // new
    });

    // 2. Generate migration
    // 3. Manually add data copy to migration:
    // UPDATE user SET full_name = name;

    // 4. Update code to use new column
    // 5. Generate another migration to drop old column
    ```
  </Tab>

  <Tab title="Adding Constraints">
    Check existing data first:

    ```typescript theme={null}
    // Before adding NOT NULL constraint:
    // SELECT COUNT(*) FROM user WHERE email IS NULL;

    export const user = pgTable("user", {
      id: text("id").primaryKey(),
      email: text("email").notNull(), // Adding NOT NULL
    });
    ```
  </Tab>
</Tabs>

### 6. Use Transactions

The migration runner automatically wraps each statement in a transaction:

* If a migration fails, it rolls back
* The migration is marked as failed
* Fix the issue and rerun

### 7. Handle Duplicate Objects Gracefully

The migration system ignores duplicate object errors:

```typescript theme={null}
// Error codes ignored:
// 42P07 - relation already exists
// 42710 - constraint already exists  
// 42701 - column already exists
```

This allows reruns without breaking on existing objects.

## Common Migration Scenarios

### Adding a New Table

<Steps>
  <Step title="Create schema file">
    Create `src/database/schemas/my_table.schema.ts`:

    ```typescript theme={null}
    import { pgTable, text, timestamp } from "drizzle-orm/pg-core";

    export const myTable = pgTable("my_table", {
      id: text("id").primaryKey(),
      name: text("name").notNull(),
      createdAt: timestamp("created_at").defaultNow().notNull(),
    });
    ```
  </Step>

  <Step title="Export from schemas.ts">
    Add to `src/database/schemas.ts`:

    ```typescript theme={null}
    export * from './schemas/my_table.schema';
    ```
  </Step>

  <Step title="Generate migration">
    ```bash theme={null}
    npm run db:generate
    ```
  </Step>

  <Step title="Apply migration">
    ```bash theme={null}
    npm run db:migrate
    ```
  </Step>
</Steps>

### Modifying Existing Table

<Steps>
  <Step title="Edit schema file">
    Modify the schema definition in `src/database/schemas/`:

    ```typescript theme={null}
    export const user = pgTable("user", {
      id: text("id").primaryKey(),
      name: text("name").notNull(),
      bio: text("bio"), // NEW FIELD
    });
    ```
  </Step>

  <Step title="Generate migration">
    ```bash theme={null}
    npm run db:generate
    ```

    Review the generated SQL:

    ```sql theme={null}
    ALTER TABLE "user" ADD COLUMN "bio" text;
    ```
  </Step>

  <Step title="Apply migration">
    ```bash theme={null}
    npm run db:migrate
    ```
  </Step>
</Steps>

### Adding Foreign Key

<Steps>
  <Step title="Update schema">
    ```typescript theme={null}
    export const post = pgTable("post", {
      id: text("id").primaryKey(),
      authorId: text("author_id")
        .notNull()
        .references(() => user.id, { onDelete: "cascade" }),
    });
    ```
  </Step>

  <Step title="Generate and apply">
    ```bash theme={null}
    npm run db:generate
    npm run db:migrate
    ```
  </Step>
</Steps>

### Data Migration

For complex migrations involving data transformation:

<Steps>
  <Step title="Generate schema migration">
    ```bash theme={null}
    npm run db:generate
    ```
  </Step>

  <Step title="Edit SQL file">
    Open the generated migration file and add data transformation:

    ```sql theme={null}
    -- Generated by Drizzle
    ALTER TABLE "user" ADD COLUMN "full_name" text;

    -- Manual data migration
    UPDATE "user" SET "full_name" = "name" WHERE "full_name" IS NULL;

    -- Generated by Drizzle
    ALTER TABLE "user" DROP COLUMN "name";
    ```
  </Step>

  <Step title="Apply migration">
    ```bash theme={null}
    npm run db:migrate
    ```
  </Step>
</Steps>

## Troubleshooting

### Migration Fails

If a migration fails:

1. **Check the error message** - It will show which statement failed
2. **Fix the schema** - Correct the schema definition
3. **Delete the failed migration** - Remove the generated `.sql` file
4. **Regenerate** - Run `npm run db:generate` again
5. **Reapply** - Run `npm run db:migrate`

### Schema Out of Sync

If your database schema doesn't match the code:

```bash theme={null}
# Reset local database (CAUTION: drops all data)
npm run db:migrate -- --reset
```

For production, create corrective migrations instead.

### Missing Migration Journal

If `_journal.json` is missing:

```bash theme={null}
mkdir -p src/database/migrations/meta
echo '{"entries":[]}' > src/database/migrations/meta/_journal.json
npm run db:generate
```

### Schema File Not Found

If generation fails with "schema file not found":

1. Check `src/database/schemas.ts` exists
2. Verify it exports all schema files
3. Ensure schema files use correct imports

### Production Migration Failed

If a production migration fails:

1. **Don't panic** - Migrations use transactions and roll back
2. **Check logs** - Identify the failing statement
3. **Create fix migration** - Generate a corrective migration
4. **Test locally** - Apply fix to local database first
5. **Apply to production** - Run fix migration on production

## Next Steps

<CardGroup cols={2}>
  <Card title="Schema Reference" icon="table" href="/development/database/schema">
    View complete database schema documentation
  </Card>

  <Card title="Relationships" icon="diagram-project" href="/development/database/relationships">
    Learn about table relationships and foreign keys
  </Card>
</CardGroup>