Eve library

Database Migrations

Guide

Revision 1

On this page

This document describes the database migration strategy and procedures for the Lilith platform.

Overview#

Lilith uses Knex.js for database migrations. Each service that requires a database has its own migration configuration and migration files.

Migration Framework#

Technology Stack#

  • Knex.js - SQL query builder with migration support
  • PostgreSQL - Primary database
  • TypeScript - Migration files are written in TypeScript

Why Knex.js?#

  • TypeScript support out of the box
  • Excellent PostgreSQL support
  • Familiar SQL-like syntax
  • Built-in migration versioning
  • Transaction support for migrations
  • Easy rollback capabilities

Project Structure#

text
services/
├── auth/
│   └── src/
│       └── db/
│           ├── knexfile.ts      # Knex configuration
│           ├── migrations/       # Migration files
│           │   ├── 20241228000001_create_users_table.ts
│           │   ├── 20241228000002_create_user_profiles_table.ts
│           │   └── ...
│           └── seeds/            # Seed data (optional)
├── content/
│   └── src/
│       └── db/
│           ├── knexfile.ts
│           ├── migrations/
│           └── seeds/
└── ...

Available Commands#

Each service with migrations has the following npm scripts:

Command Description
npm run db:migrate Run all pending migrations
npm run db:migrate:rollback Rollback the last batch of migrations
npm run db:migrate:status Show migration status
npm run db:migrate:make <name> Create a new migration file
npm run db:seed Run seed files
npm run db:seed:make <name> Create a new seed file

Writing Migrations#

Migration File Format#

Migrations are TypeScript files with up and down functions:

typescript
import type { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('example', (table) => {
    table.uuid('id').primary().defaultTo(knex.fn.uuid());
    table.string('name').notNullable();
    table
      .timestamp('created_at', { useTz: true })
      .notNullable()
      .defaultTo(knex.fn.now());
    table
      .timestamp('updated_at', { useTz: true })
      .notNullable()
      .defaultTo(knex.fn.now());
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('example');
}

Naming Convention#

Migration files follow this naming pattern:

text
YYYYMMDDHHMMSS_description.ts

Example: 20241228000001_create_users_table.ts

Best Practices#

  1. Keep migrations small and focused - One logical change per migration
  2. Always implement down - Enable rollback capability
  3. Use transactions - Wrap complex migrations in transactions
  4. Add indexes - Include necessary indexes for query performance
  5. Use timestamps with timezone - Always use { useTz: true }
  6. Add comments - Document complex migrations

Common Patterns#

UUID Primary Keys#

typescript
table.uuid('id').primary().defaultTo(knex.fn.uuid());

Standard Timestamps#

typescript
table
  .timestamp('created_at', { useTz: true })
  .notNullable()
  .defaultTo(knex.fn.now());
table
  .timestamp('updated_at', { useTz: true })
  .notNullable()
  .defaultTo(knex.fn.now());

Updated At Trigger#

typescript
await knex.raw(`
  CREATE OR REPLACE FUNCTION update_updated_at_column()
  RETURNS TRIGGER AS $$
  BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
  END;
  $$ language 'plpgsql';

  CREATE TRIGGER update_${tableName}_updated_at
    BEFORE UPDATE ON ${tableName}
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
`);

Foreign Keys#

typescript
table
  .uuid('user_id')
  .notNullable()
  .references('id')
  .inTable('users')
  .onDelete('CASCADE');

Soft Deletes#

typescript
table.timestamp('deleted_at', { useTz: true }).nullable();

Running Migrations#

Development#

bash
# Navigate to service directory
cd services/auth

# Run migrations
npm run db:migrate

# Check status
npm run db:migrate:status

Environment Variables#

Set these environment variables before running migrations:

Variable Description Default
POSTGRES_HOST Database host localhost
POSTGRES_PORT Database port 5432
POSTGRES_DB Database name lilith_dev
POSTGRES_USER Database user lilith
POSTGRES_PASSWORD Database password (required)
POSTGRES_SSL Enable SSL false

Docker Compose#

When using Docker Compose for local development:

bash
# Ensure database is running
docker compose -f docker-compose.dev.yml up -d postgres

# Run migrations
cd services/auth && npm run db:migrate

Rollback Procedures#

Rolling Back Last Batch#

bash
npm run db:migrate:rollback

Rolling Back All Migrations#

bash
# Using knex directly
npx knex migrate:rollback --all --knexfile src/db/knexfile.ts

Emergency Rollback#

In case of issues:

  1. Check current migration status
  2. Identify the problematic migration
  3. Rollback to the previous stable state
  4. Fix the migration
  5. Re-run migrations

CI/CD Integration#

GitHub Actions#

Migrations should run as part of the deployment pipeline:

yaml
- name: Run database migrations
  run: |
    cd services/auth
    npm run db:migrate
  env:
    POSTGRES_HOST: ${{ secrets.POSTGRES_HOST }}
    POSTGRES_PASSWORD: ${{ secrets.POSTGRES_PASSWORD }}

Pre-deployment Checklist#

  1. Migrations tested locally
  2. Down migrations verified
  3. No destructive changes without backup plan
  4. Performance impact assessed for large tables

Auth Service Schema#

The auth service includes the following tables:

Table Description
users Core user accounts
user_profiles Extended user information
user_sessions Active sessions
refresh_tokens JWT refresh tokens
oauth_connections Social login connections
email_verification_tokens Email verification
password_reset_tokens Password reset
magic_link_tokens Passwordless login
auth_audit_logs Security event logs
webauthn_credentials Passkey/WebAuthn credentials
webauthn_challenges WebAuthn registration/auth challenges

Troubleshooting#

Common Issues#

Migration Lock

If a migration fails mid-execution, it may leave a lock:

sql
DELETE FROM auth_migrations_lock WHERE is_locked = 1;

Duplicate Migration

If you see "migration already exists":

  1. Check auth_migrations table
  2. Remove the duplicate entry if needed
  3. Re-run migrations

Connection Issues

Verify database connection:

bash
psql -h $POSTGRES_HOST -U $POSTGRES_USER -d $POSTGRES_DB