Skip to main content

Database Migrations

The setup​

Knex + MySQL, configured in knexfile.ts. Connection shape actually differs by environment, not just by host/credentials:

  • Staging/production connect over a Unix socket path (database.connection.socketPath) — the standard Cloud SQL connector pattern, not a TCP host:port.
  • Local/development connects over plain TCP (host/port), with socketPath explicitly deleted from the config so it doesn't leak through.

Migrations live in src/database/migrations/, tracked in a knex_migrations table, loaded as .ts directly (no separate build step needed to run them). As of this writing there are 241 migration files — a long, append-only history; nothing here gets edited retroactively once merged.

Common commands​

yarn db:migrate              # run all pending migrations
yarn db:migrate:make <name>  # scaffold a new timestamped migration file
yarn db:migrate:rollback     # roll back the most recent migration batch
yarn db:seed                 # run seed files
yarn db:setup                # migrate + seed, for a fresh environment

Writing a migration​

The repo-wide convention is a shared tableName constants map (src/shared/constants/table.ts) rather than hardcoded table-name strings — reference tables through it so a rename only has to happen in one place:

import { Knex } from 'knex';
import { tableName } from '../../shared/constants/table';

const { activityLogs } = tableName;

export async function up(knex: Knex): Promise<void> {
  return knex.schema.table(activityLogs, (table) => {
    table.enum('type', ['activity', 'memory']).notNullable().defaultTo('activity');
    table.index('type', 'idx_activity_logs_type');
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.table(activityLogs, (table) => {
    table.dropIndex('type', 'idx_activity_logs_type');
    table.dropColumn('type');
  });
}

Always write a working down() — with 241 migrations and counting, yarn db:migrate:rollback needs to actually be usable, not a formality.

Seeds are looser than migrations​

Unlike migrations, seed files in src/database/seeds/ don't follow one consistent naming scheme — some are timestamped (20241201000000_populate_vendor_analytics.ts), others are named for what they do with no ordering prefix at all (vendor_profiles.seed.ts, migrate_admins_to_admin_table.seed.ts). Don't assume seed execution order is meaningful or guaranteed the way migration order is — if a seed depends on another having already run, that dependency isn't enforced by the naming convention, only by whatever's actually true at the time someone wrote it.

Key business rules & edge cases​

  • Never edit a migration that's already been merged/run anywhere — even a "harmless" tweak diverges what's actually in knex_migrations on existing environments from what's in git; add a new migration to make the change instead, same as you would in any Knex/Rails/Django-style migration system.
  • Staging and production resolve their DB connection via a Unix socket, not TCP — a migration or script that assumes host/port config (copied from local dev habits) will silently misbehave or fail to connect in those environments.
  • With 241 files and growing, running the full migration history on a brand-new environment can take a while and surface any migrations that were never actually exercised end-to-end elsewhere — budget time for yarn db:setup on a genuinely fresh database, don't assume it's as fast as an incremental yarn db:migrate on an already-current one.
  • knexfile.ts — connection config per environment
  • src/database/migrations/ — migration history
  • src/database/seeds/ — seed files
  • src/shared/constants/table.ts — the tableName map migrations should reference