Schema Migrations with Drizzle Kit

As applications evolve, database schemas must change to support new features, fix issues, or optimize performance.

dataenforced by 1 rule

Context

As applications evolve, database schemas must change to support new features, fix issues, or optimize performance. These changes need to be tracked in version control, reproducible across development and production environments, applied safely without data loss, and automated within CI/CD pipelines. Without proper migration management, schema drift between environments, lost data during schema changes, and broken deployments due to schema-code mismatches become serious risks.

For an application using Drizzle ORM for schema definition and SQLite as the embedded database, the migration tool must work seamlessly with Drizzle schema files and generate SQL migrations automatically from schema diffs.

Decision

Drizzle Kit manages all schema migrations. SQL migration files are auto-generated by comparing Drizzle schema definitions against the current database state, and these files are tracked in version control. Migration files must never be manually edited unless absolutely necessary.

The migration workflow is:

Modify Drizzle schema files in TypeScript.
Run Drizzle Kit's generate command to create a SQL migration file.
Review the generated SQL for correctness.
Run Drizzle Kit's migrate command to apply the migration.
Commit schema changes and migration files together in the same commit.

A drizzle.config.ts file must exist in each datamodel package to configure the schema source, migration output directory, and database dialect.

Do's and Don'ts

Do

Generate migrations using Drizzle Kit's generate command after every schema change.
Review generated migration SQL before applying it.
Test migrations on sample data before applying to production.
Commit migration files with schema changes in the same commit.
Apply migrations in development, staging, and production in the same order.
Back up databases before applying migrations in production.
Document complex migrations with comments in the SQL.

Don't

Manually edit generated migration files unless absolutely necessary.
Skip migrations or apply them out of order.
Delete migration files after they have been applied.
Modify schema files without generating corresponding migrations.
Apply migrations directly with SQL tools instead of Drizzle Kit.
Commit schema changes without their migrations.
Apply untested migrations to production.

Consequences

Positive

Automatic generation: Drizzle Kit detects schema changes and generates correct SQL.
Version controlled: all migrations are tracked in Git alongside the schema.
Reproducible: same migrations apply consistently across all environments.
Type-safe: schema changes are validated by TypeScript before migration generation.
Integrated: works seamlessly with Drizzle ORM schema definitions.
Safe: SQLite transactions ensure atomic migration application.

Negative

Generated SQL should always be reviewed, adding a manual step to the workflow.
Some complex schema changes (e.g., column renames in SQLite) may require manual SQL.
Migration files accumulate over time, increasing repository size.
Concurrent schema changes by multiple developers may produce conflicting migrations.

Enforcement rules

adrs/DATA-003-schema-migrations-drizzle-kit.rules.ts

/// <reference path="../rules.d.ts" />

export default {
  rules: {
    "drizzle-config-present": {
      description: "Datamodel packages with a schema file must have a drizzle.config.ts",
      async check(ctx) {
        const schemaFiles = ctx.scopedFiles.filter((f) => /[\\/]src[\\/]schema\.ts$/.test(f));

        await Promise.all(
          schemaFiles.map(async (schemaFile) => {
            const packageRoot = schemaFile.replace("/src/schema.ts", "");

            try {
              await ctx.readFile(`${packageRoot}/drizzle.config.ts`);
            } catch {
              ctx.report.violation({
                message: `${packageRoot}: Schema file exists but drizzle.config.ts is missing`,
                file: schemaFile,
                fix: "Create a drizzle.config.ts file to configure schema migrations",
              });
            }
          }),
        );
      },
    },
    "migration-directory-exists": {
      description:
        "Datamodel packages with a drizzle config must have a migrations directory with at least one SQL file",
      async check(ctx) {
        const schemaFiles = ctx.scopedFiles.filter((f) => /[\\/]src[\\/]schema\.ts$/.test(f));

        await Promise.all(
          schemaFiles.map(async (schemaFile) => {
            const packageRoot = schemaFile.replace("/src/schema.ts", "");

            // Only check packages that have a drizzle config
            try {
              await ctx.readFile(`${packageRoot}/drizzle.config.ts`);
            } catch {
              return;
            }

            const migrations = await ctx.glob(`${packageRoot}/drizzle/**/*.sql`);

            if (migrations.length === 0) {
              ctx.report.violation({
                message: `${packageRoot}: Schema file exists but no SQL migrations found in drizzle/ directory`,
                file: schemaFile,
                fix: "Run drizzle-kit generate to create migrations from the schema",
              });
            }
          }),
        );
      },
    },
  },
} satisfies RuleSet;