--- name: n8n:db-migrations description: Authors n8n database migrations. Use when creating or modifying files under packages/@n8n/db/src/migrations/, when the user asks to add a column, table, index, foreign key, or backfill, or when the user mentions DB migrations or TypeORM migrations. --- # n8n Migration Guidelines **Rule of thumb:** the `@n8n-io/migrations-review` team gates every migration PR. The fixes they ask for are predictable — work through the [Pre-flight checklist](#pre-flight-checklist) before requesting review. The rest of this document explains the *why* for each item and covers deeper topics. --- ## Table of Contents - [Overview](#overview) - [Pre-flight checklist](#pre-flight-checklist) - [Common Guidance](#common-guidance) - [Schema Migrations](#schema-migrations) - [Data Migrations](#data-migrations) - [Cross-database Compatibility](#cross-database-compatibility) - [Tests](#tests) - [General Design Guidance](#general-design-guidance) - [Schema documentation](#schema-documentation) --- ## Overview ### Directory Structure ``` packages/@n8n/db/src/migrations/ ├── common/ # Default — DSL handles SQLite + Postgres ├── postgresdb/ # PostgreSQL-specific migrations ├── sqlite/ # SQLite-specific migrations ├── dsl/ # Schema builder DSL (table, column, indices) ├── __tests__/ # Migration tests ├── migration-types.ts └── migration-helpers.ts ``` ### Migration Types | Interface | When to use | |---|---| | `ReversibleMigration` | Schema changes that can be cleanly undone (add/drop column, create/drop table). Requires a working `down()`. | | `IrreversibleMigration` | Data transformations, destructive changes, or anything where `down()` would lose data. No `down()` allowed. | ### MigrationContext API Source of truth: `packages/@n8n/db/src/migrations/migration-types.ts`. Check the source for exact signatures when in doubt. ```typescript interface MigrationContext { // Database info dbType: 'postgresdb' | 'sqlite'; isSqlite: boolean; isPostgres: boolean; tablePrefix: string; dbName: string; // Schema DSL schemaBuilder: { createTable, dropTable, addColumns, dropColumns, column, createIndex, dropIndex, addForeignKey, dropForeignKey, addNotNull, dropNotNull }; // Query execution runQuery(sql: string, namedParameters?: object): Promise; runInBatches(query: string, operation: (rows: T[]) => Promise, limit?: number): Promise; copyTable(from: string, to: string, fromFields?: string[], toFields?: string[], batchSize?: number): Promise; // Utilities escape: { tableName(n: string): string; columnName(n: string): string; indexName(n: string): string }; parseJson(data: string | T): T; loadSurveyFromDisk(): string | null; logger: Logger; migrationName: string; queryRunner: QueryRunner; // Avoid direct use — prefer runQuery() } ``` ### DSL Type Mapping Reference Source of truth: `packages/@n8n/db/src/migrations/dsl/column.ts`. | DSL type | PostgreSQL | SQLite | |---|---|---| | `int` | `int` | `integer` | | `bigint` | `bigint` | `integer` | | `smallint` | `smallint` | `integer` | | `varchar(N)` | `varchar(N)` | `varchar(N)` *(length not enforced)* | | `text` | `text` | `text` | | `json` | `json` | `text` | | `uuid` | `uuid` | `varchar` | | `bool` | `boolean` | `boolean` | | `double` | `double precision` | `real` | | `binary` | `bytea` | `blob` | | `timestampTimezone` | `timestamptz` | `datetime` | | `timestampNoTimezone` | `timestamp` | `datetime` | | `timestamp` *(deprecated)* | `timestamp` | `datetime` | Default precision for the timestamp variants is 3 ms; override with `.timestampTimezone(6)`. --- ## Pre-flight checklist Run through this before requesting review. Each item is a real, recurring reviewer flag; the link points to the section that explains the rule. - [ ] Migration was scaffolded with `pnpm --filter=@n8n/db migration:new` (timestamp + registration are automatic; the `migration-timestamp` lint rule catches drift). — [Creating Migrations](#creating-migrations) - [ ] Identifiers go through **`escape.tableName(...)` / `escape.columnName(...)`**. Never hand-write `n8n_table` prefixes. — [Always escape identifiers](#always-escape-identifiers) - [ ] **Match column type to value semantics.** Native `uuid` for UUIDs, `timestampTimezone()` for timestamps, a numeric type for numbers, `bool` for booleans, `json` for structured data. Never `varchar` as a catch-all. — [Column types](#column-types) - [ ] **Pick the narrowest sane type within that category:** `int`/`smallint` not `bigint` when range allows; `text` not `varchar(255)` for unbounded strings; never `double` for version numbers. — [Column types](#column-types) - [ ] **Default `notNull`**, relax only when justified. PK is implicitly NOT NULL. Migration's `notNull` matches the entity's nullability. — [NOT NULL and entity parity](#not-null-and-entity-parity) - [ ] **Enum-like columns** carry `.withEnumCheck([...])` AND `.comment('explains values')`. Opaque IDs / unix timestamps / JSON shapes also get `.comment()`. — [Constrain enum-like strings](#constrain-enum-like-strings), [Add comments on columns](#add-comments-on-columns) - [ ] **Every reference column has an explicit FK** with deliberate `onDelete`. Name FKs explicitly when SQLite recreate cycles risk duplicating them. Avoid polymorphic `(typeCol, idCol)` patterns. — [Foreign Key Constraints](#foreign-key-constraints), [General Design Guidance](#general-design-guidance) - [ ] **Indexes match real query patterns.** A unique constraint already creates an index; a composite PK indexes its prefix. Mirror `withIndexOn(...)` to entity `@Index(...)`. — [Index Management](#index-management) - [ ] **Sparse-unique columns:** use a partial index `WHERE col IS NOT NULL`. — [Index Management](#index-management) - [ ] **Composite index column order** matches your actual `WHERE` / `ORDER BY` usage. — [Index Management](#index-management) - [ ] **Entity ↔ migration parity**: column types, `notNull`, defaults, FKs, `@Index` decorators all match. — [Schema/Entity Drift](#schemaentity-drift) - [ ] **If using `addColumns`, `dropColumns`, `addNotNull`, `dropNotNull`, `addEnumCheck`, or `dropEnumCheck`:** verified whether the target table has incoming FKs. If so, either set `withFKsDisabled = true as const` (in a `sqlite/` subclass if this is a `common/` migration) or use raw `ALTER TABLE ADD COLUMN` for nullable/defaulted columns. — [SQLite table recreation risk](#sqlite-table-recreation-risk) - [ ] **No live-app value imports** in the migration body. Inline types/utility code locally. — [Never import entities as values](#never-import-entities-as-values) - [ ] **`async down()` was tested locally**: `pnpm start && pnpm start -- db:revert && pnpm start` on **both** SQLite and Postgres. — [Reversibility](#reversibility) - [ ] **One logical change per migration**; split unrelated table changes into separate files. — [Don't combine independent schema changes](#dont-combine-independent-schema-changes) - [ ] **`up()` / `down()` reads as a list of intentions.** If either body grows past a screen or mixes schema operations with a multi-statement raw-SQL data move, extract the data move into a `private async` method on the same class (e.g. `private async backfillFromX(ctx)`). The top-level should orchestrate, not implement. - [ ] **Precedent is the bar to fix, not perpetuate.** When the checklist conflicts with what an older migration does (e.g. redundant `.primary.notNull`, hand-quoted identifiers, missing `.comment()`), the checklist wins for new code — don't copy the violation forward. Note the old occurrences in the PR if you spotted them. - [ ] **Regenerated the schema docs** with `pnpm db:schema:docs` and committed the `docs/generated/` changes. The DB Tests CI job fails on stale docs. — [Schema documentation](#schema-documentation) Treat the checklist as a floor, not a ceiling. If any item fails, fix it before opening review. --- ## Common Guidance Rules that apply to every migration — schema or data, common or DB-specific. Read this section before writing anything. ### Creating Migrations > **Temporary timestamp workaround:** This repository currently has future-dated migrations, with the head at `1784000000008` (`2026-07-14T03:33:20.008Z`). Until real time passes that timestamp, a migration created with `Date.now()` would sort before the deployed head and can run out of order on databases that already applied later migrations. Use the generator during this window — it picks `max + 1` when needed. See [PR #30511](https://github.com/n8n-io/n8n/pull/30511) for context. Migration files are named `{TIMESTAMP}-{DescriptiveName}.ts`. The timestamp must be strictly greater than every existing migration timestamp in this package (across `common/`, `postgresdb/`, and `sqlite/`). TypeORM runs unrecorded migrations in timestamp order, so inserting a value below the current max corrupts ordering on databases that have already executed the later migrations. Use the generator — it picks a safe timestamp, writes the scaffold, and regenerates the migration index files (`sqlite/index.ts` and `postgresdb/index.ts` are gitignored build artifacts, generated from the files on disk by `scripts/generate-migration-index.mjs` — never edit or commit them): ```sh pnpm --filter=@n8n/db migration:new [--folder=common|postgresdb|sqlite] ``` `` is PascalCase and describes the change (e.g. `AddTracingToExecution`). `--folder` defaults to `common`; use `postgresdb` or `sqlite` only for dialect-specific migrations. The generator picks `Date.now()` when it's greater than the current head, otherwise `max + 1`. The `migration-timestamp` rule in `@n8n/code-health` enforces both invariants (strict ordering and no far-future fabrication) at lint time; the generator is the easy path, the rule is the safety net. ### Applying and Reverting Migrations Pending migrations are applied during normal n8n startup. In a local checkout, run `pnpm start` with the target code version to apply them manually. To revert the most recently applied reversible migration, use the CLI command: ```sh n8n db:revert ``` In a local checkout, run the same command through the package script: ```sh pnpm start -- db:revert ``` Do **not** revert migrations by editing the migrations table or running hand-written SQL. `db:revert` runs the migration's `down()` method and preserves TypeORM's migration bookkeeping. ### Which directory to choose ``` single schema change, DSL covers it → common/ Postgres-only feature (gen_random_uuid, ALTER COLUMN TYPE, partial expr index) → postgresdb/ SQLite needs different recipe or to skip CASCADE on table recreate → sqlite/ (subclass common/, set withFKsDisabled = true as const) ``` If only Postgres needs the change, put the file under `postgresdb/` only — don't write a no-op SQLite migration with `if (isPostgres)` guards. See [Cross-database Compatibility](#cross-database-compatibility) for when to split per-DB. ### Class shape ```typescript import type { MigrationContext, ReversibleMigration } from '../migration-types'; export class AddFooBar1700000000000 implements ReversibleMigration { async up({ schemaBuilder: { addColumns, column, createIndex }, escape }: MigrationContext) { // ... } async down({ schemaBuilder: { dropIndex, dropColumns } }: MigrationContext) { // ... } } ``` - `ReversibleMigration` (default) requires both `up` and `down`. - `IrreversibleMigration` only when `down()` would lose data unrecoverably — see [Reversibility](#reversibility). - `withFKsDisabled = true as const` only in `sqlite/` subclasses that recreate FK-referenced tables (otherwise SQLite's CASCADE eats data). ### Follow good code hygiene A migration class is still a class — `up()` shouldn't be a 200-line procedure. Break long logical steps into private methods with a name that describes what they do (`backfillSlugs`). `up()` then reads as a short list of step calls. **Don't extract single-line steps.** A method whose body is one DSL call adds no information — the call site is already self-documenting. ```typescript // 🚫: everything inline in up() export class MigrateThing1234567890000 implements IrreversibleMigration { async up(ctx: MigrationContext) { // 80 lines of mixed DDL, raw SQL, batched updates, logging... } } // ✅: up() is a table of contents; only multi-step work gets its own method export class MigrateThing1234567890000 implements IrreversibleMigration { async up(ctx: MigrationContext) { const { schemaBuilder: { addColumns, column, createIndex } } = ctx; // One-liner DSL calls stay inline — naming them adds no information. await addColumns('my_table', [column('slug').varchar(255)], { recreatesOnSqlite: true }); // The non-trivial step gets a named method. await this.backfillSlugs(ctx); await createIndex('my_table', ['slug'], true); } private async backfillSlugs({ escape, runQuery, runInBatches, logger, migrationName }: MigrationContext) { const table = escape.tableName('my_table'); await runInBatches<{ id: string; name: string }>( `SELECT id, name FROM ${table} WHERE slug IS NULL`, async (rows) => { for (const row of rows) { try { const slug = row.name.toLowerCase().replace(/\s+/g, '-'); await runQuery(`UPDATE ${table} SET slug = :slug WHERE id = :id`, { slug, id: row.id }); } catch (error) { logger.warn(`[${migrationName}] Failed to backfill row ${row.id}: ${(error as Error).message}`); } } }, ); } } ``` **Why:** A migration is read more often than it's written — during review, during incident response, and years later when someone has to understand why a column exists. Named steps double as documentation. They also make it easier to skim a diff: a reviewer can tell at a glance whether the change is "added a new step" or "rewrote an existing one." Reversible migrations benefit even more — `down()` can call the same private helpers in reverse. ### Prefer `runQuery()` over `queryRunner` Run SQL through `runQuery()` from `MigrationContext`. Never call `queryRunner.query()` or `queryRunner.manager.*` from a migration. **Why:** `runQuery()` handles named parameter binding consistently, while identifiers still need `escape.tableName()`, `escape.columnName()`, and `escape.indexName()`. `queryRunner.query()` bypasses the parameter helper. `queryRunner.manager` calls couple the migration to TypeORM entity definitions, which change over time — a migration that worked at v1.0 can break at v2.0 if the entity shape evolves. ### Never import entities as values Don't `import { Entity }` and call ORM methods on it. Use raw SQL via `runQuery()` instead. ```typescript // 🚫 value import; ties migration to current entity shape import { ApiKey } from '../../entities'; await queryRunner.manager.update(ApiKey, { id }, { scopes }); // ✅ inline row type, raw SQL type ApiKeyRow = { id: string; scopes: string }; await runQuery(`UPDATE ${table} SET scopes = :scopes WHERE id = :id`, { scopes, id }); ``` **Type-only imports** (`import type { Entity }`) are acceptable for typing query results, but prefer inline types like `type WorkflowRow = { id: string; nodes: string }` to avoid coupling to entities that may be renamed or restructured. **Why:** Migrations are a historical record — they must work against the schema *as it existed when they were written*. Importing live entities means later refactors silently change the meaning of old migrations. ### Always escape identifiers Use `escape.tableName()`, `escape.columnName()`, and `escape.indexName()` for every identifier. Don't hand-roll `${tablePrefix}my_table` or hardcode quoted names like `"model_tmp"`. **Why:** The DB type, table prefix, and quoting rules differ between Postgres and SQLite. The `escape.*` helpers apply the right rules; manual interpolation will eventually be wrong on one of them. ### Prefer inlining over importing from sibling packages `@n8n/db` already depends on `n8n-workflow`, but the more a migration imports from other workspace packages, the more brittle it becomes. Inline small constants and types where you can. Use `parseJson()` from `MigrationContext` instead of importing `jsonParse` from `n8n-workflow`. **Why:** A migration that imports `ERROR_TRIGGER_NODE_TYPE` from `n8n-workflow` is now coupled to that constant's existence and value forever. If the constant is renamed or removed in a refactor years later, the migration breaks at install time on a fresh database. Acceptable exceptions: utilities whose semantics are stable and whose inline implementation would be substantial (e.g. `generateNanoId`). ### Logging Use the `logger` from `MigrationContext` — never `console.log`. ```typescript logger.info(`[${migrationName}] Processing ${count} workflows`); logger.warn(`[${migrationName}] Skipping row ${id}: missing required field`); ``` ### Don't combine independent schema changes One logical change per file. Multiple unrelated tables → split. The reviewer line: "the name of the migration is misleading because it does two things." A migration that adds a column to `workflow_entity` *and* creates `audit_log` should be two migrations. ### Don't edit a previously merged migration Once shipped, migrations are immutable. Write a new migration. To remove a column added by an earlier migration, do it in a separate follow-up migration (typically in a later release — see [Deprecate columns, then drop in a follow-up](#deprecate-columns-then-drop-in-a-follow-up)). ### Don't parameterize values that aren't user input Inline literals where the value is from the migration itself. Named parameters are for runtime values; constants in the migration body can sit directly in the SQL. ### Naming and entity conventions - **Table names**: snake_case, no `_entity` suffix on new tables (old convention only). - **Column names**: camelCase in code; don't repeat the table name in column names (`user.userEmail` → `user.email`). - **Constants**: camelCase, not SCREAMING_CASE. - **Entity name override**: set `@Entity({ name: 'snake_case_name' })` explicitly when the entity class name and table name differ. - **TypeORM relations**: use `Relation` rather than direct references — avoids known circular-import issues. - **Abstract entities**: extend `WithTimestamps` or `WithTimestampsAndStringId` when applicable — the established standard. - **Don't denormalize without a concrete read pattern that benefits.** Justify any duplicated column in the PR description. --- ## Schema Migrations ### Use the DSL for Schema Changes Use the schema builder DSL for additions, removals, and changes. It handles cross-database type mapping automatically. If a helper is missing, either add one or bring it up. ```typescript export class CreateMyTable1234567890000 implements ReversibleMigration { async up({ schemaBuilder: { createTable, column } }: MigrationContext) { await createTable('my_table') .withColumns( column('id').int.primary.autoGenerate2, // Use autoGenerate2, not autoGenerate column('name').varchar(255).notNull, column('workflowId').varchar(36).notNull, column('config').json, // Maps to json (PG) / text (SQLite) column('isActive').bool.notNull.default(false), ) .withTimestamps // Adds createdAt + updatedAt .withIndexOn(['workflowId']) .withForeignKey('workflowId', { tableName: 'workflow_entity', columnName: 'id', onDelete: 'CASCADE', // Always explicit }); } async down({ schemaBuilder: { dropTable } }: MigrationContext) { await dropTable('my_table'); } } ``` ### SQLite table recreation risk Six DSL methods trigger **full table recreation** on SQLite — TypeORM internal