Output format·Databases

n8n Migration Guidelines

Name: n8n Migration Guidelines
Author: n8n-io

Authors n8n database migrations. Use when creating or modifying files under packages/@n8n/db/src/migrations/, when the user asks to add a…

You say

Install this skill Read the source first Free Written by n8n-io · unverified publisher

Context cost: 11.2k tokensestimated from the bundle, loaded when it triggers
Bundle: 1 file · 44.6 kBtext throughout, nothing executable
Licence: Source-availablefree to use
Last change: —no release on file
Servers it uses: Noneruns standalone

What it does

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.

Installed, it changes the agent in these ways.

What this skill changes about the agent is not written down here yet. The listing was collected from its source, and the description is in its own SKILL.md.

Output format

Produces one artefact, exactly shaped.

database

Filed under

Databases

The skill itself

This is the whole product. A skill is instructions the model reads, so there is nothing behind the listing you cannot see first — the front matter loads with every session, and the body below it loads when the skill triggers.

SKILL.md44.6 kB · 783 lines

--- 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. ---

6# n8n Migration Guidelines

8**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.

10---

12## Table of Contents

14- [Overview](#overview)

15- [Pre-flight checklist](#pre-flight-checklist)

16- [Common Guidance](#common-guidance)

17- [Schema Migrations](#schema-migrations)

18- [Data Migrations](#data-migrations)

19- [Cross-database Compatibility](#cross-database-compatibility)

20- [Tests](#tests)

21- [General Design Guidance](#general-design-guidance)

22- [After authoring](#after-authoring)

24---

26## Overview

28### Directory Structure

30```

31packages/@n8n/db/src/migrations/

32├── common/ # Default — DSL handles SQLite + Postgres

33├── postgresdb/ # PostgreSQL-specific migrations

34├── sqlite/ # SQLite-specific migrations

35├── dsl/ # Schema builder DSL (table, column, indices)

36├── __tests__/ # Migration tests

37├── migration-types.ts

38└── migration-helpers.ts

39```

41### Migration Types

43| Interface | When to use |

44|---|---|

45| ReversibleMigration | Schema changes that can be cleanly undone (add/drop column, create/drop table). Requires a working down(). |

46| IrreversibleMigration | Data transformations, destructive changes, or anything where down() would lose data. No down() allowed. |

48### MigrationContext API

50Source of truth: packages/@n8n/db/src/migrations/migration-types.ts. Check the source for exact signatures when in doubt.

52```typescript

53interface MigrationContext {

54 // Database info

55 dbType: 'postgresdb' | 'sqlite';

56 isSqlite: boolean;

57 isPostgres: boolean;

58 tablePrefix: string;

59 dbName: string;

61 // Schema DSL

62 schemaBuilder: { createTable, dropTable, addColumns, dropColumns, column,

63 createIndex, dropIndex, addForeignKey, dropForeignKey,

64 addNotNull, dropNotNull };

66 // Query execution

67 runQuery<T>(sql: string, namedParameters?: object): Promise<T>;

68 runInBatches<T>(query: string, operation: (rows: T[]) => Promise<void>, limit?: number): Promise<void>;

69 copyTable(from: string, to: string, fromFields?: string[], toFields?: string[], batchSize?: number): Promise<void>;

71 // Utilities

72 escape: { tableName(n: string): string; columnName(n: string): string; indexName(n: string): string };

73 parseJson<T>(data: string | T): T;

74 loadSurveyFromDisk(): string | null;

75 logger: Logger;

76 migrationName: string;

77 queryRunner: QueryRunner; // Avoid direct use — prefer runQuery()

78}

79```

81### DSL Type Mapping Reference

83Source of truth: packages/@n8n/db/src/migrations/dsl/column.ts.

85| DSL type | PostgreSQL | SQLite |

86|---|---|---|

87| int | int | integer |

88| bigint | bigint | integer |

89| smallint | smallint | integer |

90| varchar(N) | varchar(N) | varchar(N) *(length not enforced)* |

91| text | text | text |

92| json | json | text |

93| uuid | uuid | varchar |

94| bool | boolean | boolean |

95| double | double precision | real |

96| binary | bytea | blob |

97| timestampTimezone | timestamptz | datetime |

98| timestampNoTimezone | timestamp | datetime |

99| timestamp *(deprecated)* | timestamp | datetime |

100

101Default precision for the timestamp variants is 3 ms; override with .timestampTimezone(6).

102

103---

104

105## Pre-flight checklist

106

107Run through this before requesting review. Each item is a real, recurring reviewer flag; the link points to the section that explains the rule.

108

109- [ ] 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)

110- [ ] Identifiers go through **escape.tableName(...) / escape.columnName(...)**. Never hand-write n8n_table prefixes. — [Always escape identifiers](#always-escape-identifiers)

111- [ ] **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)

112- [ ] **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)

113- [ ] **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)

114- [ ] **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)

115- [ ] **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)

116- [ ] **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)

117- [ ] **Sparse-unique columns:** use a partial index WHERE col IS NOT NULL. — [Index Management](#index-management)

118- [ ] **Composite index column order** matches your actual WHERE / ORDER BY usage. — [Index Management](#index-management)

119- [ ] **Entity ↔ migration parity**: column types, notNull, defaults, FKs, @Index decorators all match. — [Schema/Entity Drift](#schemaentity-drift)

120- [ ] **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)

121- [ ] **No live-app value imports** in the migration body. Inline types/utility code locally. — [Never import entities as values](#never-import-entities-as-values)

122- [ ] **async down() was tested locally**: pnpm start && pnpm start -- db:revert && pnpm start on **both** SQLite and Postgres. — [Reversibility](#reversibility)

123- [ ] **One logical change per migration**; split unrelated table changes into separate files. — [Don't combine independent schema changes](#dont-combine-independent-schema-changes)

124- [ ] **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.

125- [ ] **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.

126

127Treat the checklist as a floor, not a ceiling.

128If any item fails, fix it before opening review.

129

130---

131

132## Common Guidance

133

134Rules that apply to every migration — schema or data, common or DB-specific. Read this section before writing anything.

135

136### Creating Migrations

137

138> **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.

139

140Migration 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.

141

142Use the generator — it picks a safe timestamp, writes the scaffold, and registers the migration in the relevant index.ts files:

143

144```sh

145pnpm --filter=@n8n/db migration:new <Name> [--folder=common|postgresdb|sqlite]

146```

147

148<Name> 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.

149

150The 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.

151

152### Applying and Reverting Migrations

153

154Pending migrations are applied during normal n8n startup. In a local checkout, run pnpm start with the target code version to apply them manually.

155

156To revert the most recently applied reversible migration, use the CLI command:

157

158```sh

159n8n db:revert

160```

161

162In a local checkout, run the same command through the package script:

163

164```sh

165pnpm start -- db:revert

166```

167

168Do **not** revert migrations by editing the migrations table or running

169hand-written SQL. db:revert runs the migration's down() method and

170preserves TypeORM's migration bookkeeping.

171

172### Which directory to choose

173

174```

175single schema change, DSL covers it → common/

176Postgres-only feature (gen_random_uuid,

177 ALTER COLUMN TYPE, partial expr index) → postgresdb/

178SQLite needs different recipe or to skip

179 CASCADE on table recreate → sqlite/ (subclass common/, set withFKsDisabled = true as const)

180```

181

182If 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.

183

184### Class shape

185

186```typescript

187import type { MigrationContext, ReversibleMigration } from '../migration-types';

188

189export class AddFooBar1700000000000 implements ReversibleMigration {

190 async up({ schemaBuilder: { addColumns, column, createIndex }, escape }: MigrationContext) {

191 // ...

192 }

193

194 async down({ schemaBuilder: { dropIndex, dropColumns } }: MigrationContext) {

195 // ...

196 }

197}

198```

199

200- ReversibleMigration (default) requires both up and down.

201- IrreversibleMigration only when down() would lose data unrecoverably — see [Reversibility](#reversibility).

202- withFKsDisabled = true as const only in sqlite/ subclasses that recreate FK-referenced tables (otherwise SQLite's CASCADE eats data).

203

204### Follow good code hygiene

205

206A 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.

207

208```typescript

209// 🚫: everything inline in up()

210export class MigrateThing1234567890000 implements IrreversibleMigration {

211 async up(ctx: MigrationContext) {

212 // 80 lines of mixed DDL, raw SQL, batched updates, logging...

213 }

214}

215

216// ✅: up() is a table of contents; only multi-step work gets its own method

217export class MigrateThing1234567890000 implements IrreversibleMigration {

218 async up(ctx: MigrationContext) {

219 const { schemaBuilder: { addColumns, column, createIndex } } = ctx;

220

221 // One-liner DSL calls stay inline — naming them adds no information.

222 await addColumns('my_table', [column('slug').varchar(255)], { recreatesOnSqlite: true });

223

224 // The non-trivial step gets a named method.

225 await this.backfillSlugs(ctx);

226

227 await createIndex('my_table', ['slug'], true);

228 }

229

230 private async backfillSlugs({ escape, runQuery, runInBatches, logger, migrationName }: MigrationContext) {

231 const table = escape.tableName('my_table');

232 await runInBatches<{ id: string; name: string }>(

233 SELECT id, name FROM ${table} WHERE slug IS NULL,

234 async (rows) => {

235 for (const row of rows) {

236 try {

237 const slug = row.name.toLowerCase().replace(/\s+/g, '-');

238 await runQuery(UPDATE ${table} SET slug = :slug WHERE id = :id, { slug, id: row.id });

239 } catch (error) {

240 logger.warn([${migrationName}] Failed to backfill row ${row.id}: ${(error as Error).message});

241 }

242 }

243 },

244 );

245 }

246}

247```

248

249**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.

250

251### Prefer runQuery() over queryRunner

252

253Run SQL through runQuery() from MigrationContext. Never call queryRunner.query() or queryRunner.manager.* from a migration.

254

255**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.

256

257### Never import entities as values

258

259Don't import { Entity } and call ORM methods on it. Use raw SQL via runQuery() instead.

260

261```typescript

262// 🚫 value import; ties migration to current entity shape

263import { ApiKey } from '../../entities';

264await queryRunner.manager.update(ApiKey, { id }, { scopes });

265

266// ✅ inline row type, raw SQL

267type ApiKeyRow = { id: string; scopes: string };

268await runQuery(UPDATE ${table} SET scopes = :scopes WHERE id = :id, { scopes, id });

269```

270

271**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.

272

273**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.

274

275### Always escape identifiers

276

277Use escape.tableName(), escape.columnName(), and escape.indexName() for every identifier. Don't hand-roll ${tablePrefix}my_table or hardcode quoted names like "model_tmp".

278

279**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.

280

281### Prefer inlining over importing from sibling packages

282

283@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.

284

285**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.

286

287Acceptable exceptions: utilities whose semantics are stable and whose inline implementation would be substantial (e.g. generateNanoId).

288

289### Logging

290

291Use the logger from MigrationContext — never console.log.

292

293```typescript

294logger.info([${migrationName}] Processing ${count} workflows);

295logger.warn([${migrationName}] Skipping row ${id}: missing required field);

296```

297

298### Don't combine independent schema changes

299

300One 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.

301

302### Don't edit a previously merged migration

303

304Once 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)).

305

306### Don't parameterize values that aren't user input

307

308Inline 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.

309

310### Naming and entity conventions

311

312- **Table names**: snake_case, no _entity suffix on new tables (old convention only).

313- **Column names**: camelCase in code; don't repeat the table name in column names (user.userEmail → user.email).

314- **Constants**: camelCase, not SCREAMING_CASE.

315- **Entity name override**: set @Entity({ name: 'snake_case_name' }) explicitly when the entity class name and table name differ.

316- **TypeORM relations**: use Relation<T> rather than direct references — avoids known circular-import issues.

317- **Abstract entities**: extend WithTimestamps or WithTimestampsAndStringId when applicable — the established standard.

318- **Don't denormalize without a concrete read pattern that benefits.** Justify any duplicated column in the PR description.

319

320---

321

322## Schema Migrations

323

324### Use the DSL for Schema Changes

325

326Use 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.

327

328```typescript

329export class CreateMyTable1234567890000 implements ReversibleMigration {

330 async up({ schemaBuilder: { createTable, column } }: MigrationContext) {

331 await createTable('my_table')

332 .withColumns(

333 column('id').int.primary.autoGenerate2, // Use autoGenerate2, not autoGenerate

334 column('name').varchar(255).notNull,

335 column('workflowId').varchar(36).notNull,

336 column('config').json, // Maps to json (PG) / text (SQLite)

337 column('isActive').bool.notNull.default(false),

338 )

339 .withTimestamps // Adds createdAt + updatedAt

340 .withIndexOn(['workflowId'])

341 .withForeignKey('workflowId', {

342 tableName: 'workflow_entity',

343 columnName: 'id',

344 onDelete: 'CASCADE', // Always explicit

345 });

346 }

347

348 async down({ schemaBuilder: { dropTable } }: MigrationContext) {

349 await dropTable('my_table');

350 }

351}

352```

353

354### SQLite table recreation risk

355

356Six DSL methods trigger **full table recreation** on SQLite — TypeORM internally creates a temp copy, drops the original, and renames:

357

358| Method | TypeORM internal call |

359|---|---|

360| addColumns() | queryRunner.addColumns() |

361| dropColumns() | queryRunner.dropColumns() |

362| addNotNull() | queryRunner.changeColumn() |

363| dropNotNull() | queryRunner.changeColumn() |

364| addEnumCheck() | queryRunner.changeColumn() |

365| dropEnumCheck() | queryRunner.changeColumn() |

366

367All six require a final options parameter with recreatesOnSqlite: true — TypeScript rejects calls that omit it.

368

369**The danger:** If the target table has incoming FK constraints with CASCADE from other tables, the DROP TABLE during recreation fires cascading deletes and **wipes rows from those referencing tables**.

370

371**Decision tree:**

372

3731. Does the target table have incoming FK constraints from other tables?

374 - **No** → Safe to use the DSL method directly (with the ack parameter).

375 - **Yes** → Continue to step 2.

3762. Is this an addColumns call where every new column is nullable or has a default?

377 - **Yes** → Use raw ALTER TABLE ADD COLUMN instead (avoids table recreation entirely):

378 ```typescript

379 await runQuery(

380 ALTER TABLE ${escape.tableName('my_table')} ADD COLUMN ${escape.columnName('col')} TEXT,

381 );

382 ```

383 See 1733133775640-AddMockedNodesColumnToTestDefinition.ts for a real example.

384 - **No** → Continue to step 3.

3853. Set withFKsDisabled = true as const on the migration class. For common migrations, create a SQLite subclass in sqlite/ that extends the common migration and adds the flag:

386 ```typescript

387 // sqlite/1234567890000-MyMigration.ts

388 import { MyMigration1234567890000 as BaseMigration } from '../common/1234567890000-MyMigration';

389

390 export class MyMigration1234567890000 extends BaseMigration {

391 withFKsDisabled = true as const;

392 }

393 ```

394

395**How withFKsDisabled works:** The migration wrapper calls PRAGMA foreign_keys=OFF before up()/down(), runs the migration inside a manual transaction, then re-enables foreign keys. This prevents CASCADE from firing during the internal table drop. It also sets transaction = false to avoid TypeORM's default transaction (since SQLite can't nest transactions with PRAGMA changes).

396

397> **Note:** On Postgres, these methods use ALTER TABLE directly and don't recreate the table. The risk is SQLite-specific.

398

399### Column types

400

401**Match column type to value semantics.** Never varchar as a catch-all for non-string values — storing numbers as strings loses sort order, range queries, and SUM/AVG aggregations.

402

403- DATE not timestamp when only the date matters.

404- A numeric type (bigint, int, smallint) for byte counts and measurements — never varchar.

405- Native uuid over varchar(36) when the value is actually a UUID. Postgres stores uuid as 16 bytes vs ~37 for varchar(36); the difference compounds across joined tables and indexes.

406- bool for booleans; json for structured data.

407- timestampTimezone() (default 3-ms precision) or timestampNoTimezone() deliberately. **.timestamp() is deprecated.**

408

409**Pick the narrowest sane type within that category.**

410

411- smallint for small bounded counters/enums; int over bigint unless overflow is plausible.

412- Use bigint proactively for monotonically-growing counters that can overflow int (insights/usage counters).

413- Don't use double for version-like fields; floating-point precision bites. Use a string or split major/minor.

414- text over varchar(255) for unbounded user-supplied strings unless a real limit applies. (SQLite ignores varchar(N) length entirely; validate at the app layer if needed.)

415

416### NOT NULL and entity parity

417

418- A primary key is implicitly NOT NULL; don't redeclare.

419- Migration's notNull must match the entity's nullability annotation. Mismatch causes runtime nulls TypeORM can't reconcile.

420- Default to NOT NULL; relax only with explicit reasoning ("does this need to be nullable, and when?").

421

422### Add comments on columns

423

424Use .comment() on columns whose purpose isn't obvious from the name alone — especially JSON blobs, flags, opaque IDs, unix timestamps, and columns whose values come from external systems. The comment ends up in the schema; a code comment doesn't.

425

426```typescript

427column('config').json.comment('Serialized node parameters at time of publish'),

428column('isArchived').bool.notNull.default(false).comment('Soft-delete flag; filtered out in list queries'),

429```

430

431### Constrain enum-like strings

432

433For columns that should hold one of a small set of values, use .withEnumCheck([...]) on the column. When adding a CHECK constraint via raw SQL, name it explicitly.

434

435### Default values reflect realistic initial state

436

437Don't set the default of a status column to a terminal value — "running" makes more sense than "done" for a status that will transition.

438

439### Primary Keys

440

441Every table needs a primary key. Choose the type in this order:

442

4431. **Integer** — column('id').int.primary.autoGenerate2. Preferred for new tables: compact, fast joins, no ordering surprises. autoGenerate2 uses Postgres IDENTITY (preferred over the deprecated serial-based autoGenerate).

4442. **UUID** — column('id').uuid.primary. Use when IDs are generated client-side, exposed in URLs, or need to be unguessable. Generate UUIDs in application code via randomUUID() from node:crypto; do **not** chain .autoGenerate2 on .uuid (the DSL throws — DEFAULT uuid_generate_v4() fails on managed Postgres like Supabase because it needs the uuid-ossp extension in public). Use .uuid instead of .varchar(36).

4453. **String** — column('id').varchar(36).primary for IDs whose format isn't a UUID (e.g. nanoid-style IDs). Convention: nanoid length 16 for entity IDs.

446

447**Keep ID-column types consistent across related tables.** Mixing uuid and varchar(36) for what is "the same kind of ID" creates JOIN footguns.

448

449**DSL behavior to know:**

450- .primary already implies notNull. Don't chain .notNull together with .primary — it's redundant.

451- .primary already creates the primary-key index. Don't add a separate .withIndexOn(['id']) for it.

452

453**Composite primary keys are first-class** — chain .primary on each participating column. Skip the surrogate id when natural keys work.

454

455```typescript

456await createTable('membership')

457 .withColumns(

458 column('userId').uuid.primary,

459 column('roleId').uuid.primary,

460 );

461```

462

463### Foreign Key Constraints

464

465**FKs are the default; opting out needs justification.**. For polymorphic refs (one column points at different tables based on a sibling type column), see [General Design Guidance](#general-design-guidance).

466

467**Specify onDelete explicitly.** Don't rely on database defaults. Answer "what happens when [parent] is deleted?" in the PR description.

468

469| Relationship type | onDelete | Example |

470|---|---|---|

471| Child is meaningless without parent | CASCADE | annotation_tag_mapping → annotation |

472| Child should outlive parent (keep history) | SET NULL | workflow_publish_history.userId → user |

473| Audit / statistics / history tables | NO ACTION or SET NULL | workflow_statistics → workflow_entity |

474| Reference should prevent deletion | RESTRICT | (use when business logic forbids orphaning) |

475

476For SET NULL, the FK column must be nullable. For CASCADE, consider whether the cascade depth is bounded — long cascade chains can lock many tables in a single delete.

477

478**SQLite quirks:**

479- Dropping/recreating tables can leave duplicated FKs across up/down cycles. **Name FKs explicitly** so down migrations can target them.

480- Down migrations sometimes fail on engines with FK-protected indexes (Cannot drop index 'X': needed in a foreign key constraint). Test both directions on every engine.

481

482**NULL semantics in joins/uniqueness differ across engines.** Comparing NULL with non-NULL filters rows on at least SQLite. Partial uniqueness across NULLs differs Postgres vs SQLite. Be deliberate when a column is nullable and participates in a unique constraint or join condition.

483

484### Index Management

485

486```typescript

487// Creating indices

488await schemaBuilder.createIndex('my_table', ['columnA', 'columnB']);

489await schemaBuilder.createIndex('my_table', ['email'], true); // unique

490

491// Partial unique index — uniqueness only on non-null rows

492await schemaBuilder.createIndex(

493 'my_table',

494 ['externalRef'],

495 true, // isUnique

496 undefined, // customIndexName

497 '"externalRef" IS NOT NULL', // whereClause

498);

499

500// Dropping indices (defensively)

501await schemaBuilder.dropIndex('my_table', ['columnA'], { skipIfMissing: true });

502```

503

504**Best practices:**

505

506- **Add indexes sparingly, and only when you've measured a speedup.** Every index slows down inserts/updates and consumes disk. Don't add one "just in case" — run the query against a realistic dataset, confirm it's slow, add the index, confirm the planner uses it and the query is now fast. If you can't show a measurable improvement, don't ship the index.

507- **A unique constraint already creates an index — don't double up.** A composite primary key indexes its prefix columns; a separate index on the prefix is redundant.

508- **Index foreign key columns.** Joins and cascading deletes hit FKs on every operation; an unindexed FK degrades into a sequential scan on the child table.

509- **Column order matters in composite indexes.** An index on (A, B) serves WHERE A = ? and WHERE A = ? AND B = ?, **not** WHERE B = ?. ORDER BY direction in the index must match the query's ORDER BY (e.g. (sessionId, createdAt ASC, id DESC)).

510- **Don't index low-cardinality columns alone** (booleans, status enums with 2–3 values). Either skip the index or make it a partial index — both Postgres and SQLite (since 3.8.0) support WHERE clauses on indexes.

511- **Partial unique indexes for sparse-unique columns.** Add WHERE col IS NOT NULL to exclude NULL rows: smaller index, no uniqueness checks against the NULL bucket.

512- **Unique indexes enforce uniqueness AND speed up lookups.** Prefer them over a separate unique constraint + index pair.

513- **Drop unused indexes.** If a query plan no longer uses it, drop it in a follow-up migration.

514- **Name indexes via the DSL,** never hand-roll names. The DSL prefixes them consistently so they line up across environments.

515- **Mirror DSL indexes onto the entity with @Index.** The migration creates the runtime index; the entity decorator keeps fresh-DB setups in sync.

516- **Use .withIndexOn(...) when defining a new table** rather than a separate createIndex(...) call.

517

518### Reversibility

519

520- ReversibleMigration: the down() **must actually work**. If shrinking a column, truncate data gracefully. If dropping a table, consider that the table may have been populated.

521- IrreversibleMigration: use when the up() **destroys information a faithful down() would need** — not as an escape hatch for tedious down() code. Examples: backfills that overwrite values without capturing the prior state; encryption operations that don't keep plaintext; aggregations that lose row-level detail.

522- **Never write an empty or broken down().** If you can't reverse it, use IrreversibleMigration.

523- down() must restore the previous schema, not just drop new objects — its effect should let up() be re-run cleanly afterwards.

524- **Test the down migration** on both engines: pnpm start && pnpm start -- db:revert && pnpm start. Down failures often surface as FK-protected indexes blocking column drops.

525

526### Schema/Entity Drift

527

528Schema, entity, and OpenAPI types must agree. Caught regularly:

529

530- notNull lost on the entity but present in the migration (or vice versa).

531- Entity says string but the column is something else.

532- @Index mirrors don't exist on the entity.

533- up and entity disagree on defaults or constraints.

534

535When a new column is required for data integrity (e.g. activeVersionId should be set whenever active is TRUE), enforce it via a CHECK constraint in the migration **and** a runtime invariant in app code.

536

537### Deprecate columns, then drop in a follow-up

538

539Don't drop a column the same release you stop writing to it. Wait one release, then drop. This protects rolling deploys and provides a quick rollback path if the "stop writing" change has unforeseen issues.

540

541---

542

543## Data Migrations

544

545Data migrations transform existing rows: parsing JSON, backfilling columns, migrating data between tables, cleaning up invalid data.

546

547### Always Handle Dirty / Legacy Data

548

549This has been the **#1 source of migration bugs**.

550

551- **Wrap JSON parsing in try/catch.** Log a warning and skip the row — never crash the whole migration. Use parseJson() from MigrationContext; it handles edge cases better than raw JSON.parse.

552- **Check for null/undefined before accessing properties:** node.type && isTriggerNode(node.type).

553- **Array.isArray() before iterating.**

554- **Account for ALL historical versions** of a data structure, not just the current one. A migration shipping today may run on a database last touched two years ago.

555- **Filter out invalid rows in SQL:** WHERE workflowId IS NOT NULL.

556

557```typescript

558await runInBatches<Row>(selectQuery, async (rows) => {

559 for (const row of rows) {

560 try {

561 const nodes = parseJson(row.nodes);

562 if (!Array.isArray(nodes)) continue; // guard against unexpected shape

563

564 for (const node of nodes) {

565 if (!node.type) continue; // skip nodes missing required fields

566 // ... transform ...

567 }

568

569 await runQuery(UPDATE ${table} SET nodes = :nodes WHERE id = :id, {

570 nodes: JSON.stringify(nodes),

571 id: row.id,

572 });

573 } catch (error) {

574 logger.warn([${migrationName}] Failed to process row ${row.id}: ${error.message}. Skipping.);

575 }

576 }

577});

578```

579

580### Push the transformation into SQL, not Node

581

582Prefer INSERT … SELECT, UPDATE … FROM, DELETE … WHERE over fetching rows to Node and writing them back. Loading whole tables into JS memory is slow and OOM-prone on large instances; the database can do the same work in place much faster.

583

584When SQL alone can't express the transformation, fall back to runInBatches. Filter early in SQL (LIKE/WHERE) to reduce the row count before parsing on the Node side.

585

586### Use Batch Operations

587

588**Never SELECT * unbounded on tables that could have millions of rows.**

589

590```typescript

591// ✅: batched processing

592await runInBatches<Workflow>(

593 SELECT id, nodes FROM ${tableName} WHERE ${condition},

594 async (workflows) => {

595 for (const workflow of workflows) {

596 // ... process each workflow ...

597 }

598 },

599 100, // batch size (default: 100, use 100-500)

600);

601

602// ✅: batched table copy

603await copyTable('old_table', 'new_table', ['col1', 'col2'], ['col1', 'col2'], 500);

604```

605

606A sequential scan on the entire table is very slow on larger instances. If you must iterate, batch.

607

608### Order backfill inserts deliberately

609

610When the migration writes rows whose order is observable downstream (auto-increment IDs, default sort order, "most recent first" UI lists), add explicit ORDER BY to the source SELECT — typically updatedAt or createdAt. Without one, the database picks any order and the chronology that was implicit in the old schema is lost.

611

612### Mixed Schema + Data Migrations

613

614When a migration both adds a column and backfills data, structure it clearly with one method per concern:

615

616```typescript

617export class AddAndBackfillColumn1234567890000 implements IrreversibleMigration {

618 async up(ctx: MigrationContext) {

619 await ctx.schemaBuilder.addColumns(

620 'my_table',

621 [ctx.schemaBuilder.column('newCol').text],

622 { recreatesOnSqlite: true },

623 );

624 await this.backfillNewCol(ctx);

625 }

626

627 private async backfillNewCol({ escape, runQuery, runInBatches }: MigrationContext) {

628 const table = escape.tableName('my_table');

629 await runInBatches<{ id: string; oldCol: string }>(

630 SELECT id, oldCol FROM ${table},

631 async (rows) => {

632 for (const row of rows) {

633 const transformed = transform(row.oldCol);

634 await runQuery(UPDATE ${table} SET newCol = :val WHERE id = :id, {

635 val: transformed,

636 id: row.id,

637 });

638 }

639 },

640 );

641 }

642}

643```

644

645The schema change and the data backfill have different failure modes, different transaction implications, and different testing needs — keeping them in separate methods makes review easier and lets down() (if reversible) call the same helpers in reverse.

646

647### For deletions, prefer keeping old rows as a fallback

648

649Self-hosted instances may have unexpected data shapes. If the migration results in missing or inconsistent data, the old row is the only recovery path.

650

651Default to two-release expand-contract:

652- **Release N (this migration):** write the new location, leave the old in place.

653- **Release N+1 (separate follow-up migration, after the new code has been observed in production):** drop the old location.

654

655Skip the gap only when the old location is genuinely throwaway (e.g. a temp table this same migration created), or when compliance forces immediate deletion — in which case mark the migration IrreversibleMigration and call out the trade-off in the PR description.

656

657### Keep denormalized columns in sync

658

659Where data is duplicated across two tables (e.g. workflow_entity.nodes vs workflow_history.nodes), the backfill must update both copies. Out-of-sync denormalized data tends to be discovered weeks later, usually in production.

660

661### Don't add JSON-substring scans on hot tables

662

663Add a real column (e.g. isDraft, similar to isArchived) instead of WHERE settings::text LIKE '%foo%'. Substring scans on JSON blobs degrade into full table scans and don't index.

664

665### Avoid storing large blobs inline on hot rows

666

667Move opt-out large columns to a side table — backups, replication, and read performance all benefit. The row-level lock on a hot table also shrinks when the row payload is smaller.

668

669### Verify data integrity when copying tables

670

671Count source vs temp before swapping; throw on mismatch. Silent row loss during a copy is one of the worst failure modes because it surfaces only when someone notices the missing data.

672

673### Atomic SQL within the migration's transaction

674

675Some migrations override with transaction = false as const for big DDL on engines that disallow it inside a transaction. The DSL/wrapper sets transaction = false automatically when withFKsDisabled = true. Otherwise, leave transactions alone — TypeORM wraps each migration in one by default.

676

677---

678

679## Cross-database Compatibility

680

681### Single Migration File or Separate for SQLite & Postgres

682

683- **Small differences** (a single statement, a CHECK constraint, slightly different syntax): keep one migration in common/ and branch on isSqlite / isPostgres.

684- **Large differences** (different table recreation strategies, different intermediate steps, fundamentally different SQL): write **separate files** in postgresdb/ and sqlite/. A common migration full of if (isSqlite) { ... } blocks is harder to read and review than two focused files.

685

686If only Postgres needs the change, just put the file in postgresdb/; don't write a no-op SQLite migration with if (isPostgres). For SQLite column adds, follow the [SQLite table recreation risk](#sqlite-table-recreation-risk) decision tree before deciding whether a common migration is enough or a SQLite subclass/raw ALTER TABLE path is needed.

687

688### SQLite supports modern syntax

689

690- UPSERT / ON CONFLICT DO NOTHING works on SQLite — use the same syntax as Postgres rather than INSERT OR REPLACE.

691- SQLite has a real JSON type.

692- SQLite supports ALTER TABLE ... RENAME TO.

693

694**INSERT OR REPLACE ≠ ON CONFLICT DO NOTHING.** OR REPLACE overwrites; ON CONFLICT DO NOTHING ignores. SQLite supports both — pick the one that matches Postgres semantics for the same code path.

695

696### Postgres-version-aware UUID generation

697

698gen_random_uuid() requires Postgres ≥ 13. n8n dropped Postgres 12 — prefer it over uuid_generate_v4() (which needs the uuid-ossp extension and breaks on managed services like Supabase). For UUID PKs, generate at the application level with randomUUID() — see [Primary Keys](#primary-keys).

699

700### SQLite doesn't enforce varchar(N) length

701

702Validate at the app layer if length matters.

703

704### Boolean defaults render differently across engines

705

706DEFAULT (false) vs DEFAULT 0 vs DEFAULT FALSE. Let the DSL handle it; don't hand-write boolean defaults in raw SQL.

707

708### Prefer ALTER over drop-and-recreate

709

710For renames, use ALTER TABLE ... RENAME TO. Faster, atomic, no data-loss risk.

711

712---

713

714## Tests

715

716**Every data migration ships with an integration test.** Schema-only migrations can usually be reviewed by reading the DSL calls. Data migrations cannot — they encode assumptions about row shape, JSON structure, NULL handling, and edge cases that only show up when the migration actually runs against representative data.

717

718A data migration runs *once* per database, on production data, with no opportunity to retry cleanly. The cost of a bad migration is a customer-facing incident; the cost of a test is ten minutes.

719

720Tests live in packages/cli/test/migration/, named to match the migration file (e.g. 1773000000000-create-credential-dependency-table.test.ts). Use the helpers from @n8n/backend-test-utils:

721

722- **initDbUpToMigration(MigrationName)** runs every migration *up to but not including* yours, leaving the DB in the exact state your migration will see in production.

723- **runSingleMigration(MigrationName)** runs just your migration on top of that state.

724

725Full helper API: packages/@n8n/backend-test-utils/MIGRATION_TESTING.md.

726

727```typescript

728import { initDbUpToMigration, runSingleMigration } from '@n8n/backend-test-utils';

729

730describe('AddAndBackfillColumn1234567890000', () => {

731 beforeEach(async () => {

732 await initDbUpToMigration('AddAndBackfillColumn1234567890000');

733 });

734

735 it('backfills newCol from oldCol', async () => {

736 // Seed rows in the pre-migration schema

737 await dataSource.query(INSERT INTO my_table (id, oldCol) VALUES ('1', 'foo'));

738

739 await runSingleMigration('AddAndBackfillColumn1234567890000');

740

741 const [row] = await dataSource.query(SELECT newCol FROM my_table WHERE id = '1');

742 expect(row.newCol).toBe('transformed-foo');

743 });

744

745 it('skips rows with NULL oldCol without crashing', async () => {

746 await dataSource.query(INSERT INTO my_table (id, oldCol) VALUES ('1', NULL));

747 await runSingleMigration('AddAndBackfillColumn1234567890000');

748 // assert no error and row still exists

749 });

750});

751```

752

753**Insert fixtures via raw SQL only.** Repositories evolve with the schema and break older tests over time. Use context.escape.tableName(...) and context.runQuery(sql, params) directly.

754

755**Test name describes behavior**, not the SQL: 'backfills newCol from oldCol', not 'runs UPDATE on my_table'.

756

757**What to cover:**

758- The happy path (correctly transforms a typical row).

759- Each edge case the migration claims to handle (NULL fields, malformed JSON, missing keys, legacy schema versions).

760- Idempotency where applicable — running the migration twice shouldn't double-apply transformations.

761- Both SQLite and Postgres if the migration branches on DB type.

762

763---

764

765## General Design Guidance

766

767These are widely-applicable database design principles that aren't tied to a single recurring PR comment, but worth keeping in mind because the *cost* of getting them wrong shows up in the codebase (manual orchestration where the DB could have done the work for free).

768

769### Avoid polymorphic (typeCol, idCol) pairs

770

771A "polymorphic" column pair is one column that points at different tables depending on a sibling type column — e.g. dependencyType: 'externalSecretProvider' | ... plus dependencyId: string. SQL FKs target exactly one table, so polymorphic idCols cannot have an FK declaration.

772

773**Consequences:**

774- No insert validation (you can insert a dependencyId that doesn't match any row).

775- No cascade/restrict on parent delete — application code has to manually walk every table that might point at the deleted row and delete dependents inside a transaction (see credential_dependency + secrets_provider_connection deletion paths for a real example of this cost).

776- Orphan rows are possible by construction.

777

778**Alternatives:**

779- **Separate join tables per relation type** (credential_external_secret_dependency, credential_node_dependency, …). Each has a real FK. Queries that need "all dependencies" become a UNION.

780- **One nullable FK per possible target** with a CHECK constraint that exactly one is set. Each column is a real FK.

781- **Supertype table**: hoist parents into a single dependency_target with its own type column, then have one FK to that table.

782-

783

In the file

SKILL.md6,065 words

Files1

LicenceSource-available

Why you can read it

Nothing in a skill executes. The client loads the text and the model follows it, so a skill can be audited the way a runbook is — by reading it.

What it costs in context

Skills are not billed by the call. They are paid for in context: every token the instructions occupy is a token your code, your diff and your conversation cannot use. Here is what this one takes and when it takes it.

≈70

always loaded

The name and description, so the model knows the skill exists and when to reach for it.

11,080

on trigger

The instruction body, read only when the skill fires.

5.6%

of a 200k window

Ten skills this size would take about 56% of the window before you open a file.

050k100k150k200k context window

11.2k tokens, estimated from the bundle at four bytes to the token, held for the rest of the session once it triggers. Heavy. Teams tend to install this one per project rather than globally, and load it only when the job comes up.

Servers bill, skills cost

A server charges by the month. A skill charges once per session, in context, and then keeps charging it for as long as the session lives.

Before and after

The same question, put to the same model twice: once as it comes, and once with these instructions loaded.

No worked example has been published for this skill yet.

Adoption

Installsnone yet

Ratingno reviews yet

The procedure it runs

The procedure has not been published here. It is in the skill’s own SKILL.md, which its author has not sent to the marketplace yet.

Prose, not code

These steps are written for a model to follow, not executed by a runtime. It can still be told to skip one, and it will say so when it does.

Servers it uses

None. This skill calls no MCP servers at all.

Everything it needs is in the instructions, so it works in a project with nothing connected — the model reads the file and changes how it works with what it can already reach.

It writes no files and reaches no network. All it changes is how the model reasons and writes.

What it asks for

Writes filesno

Network accessno

Read from the allowed-tools line of this skill’s own SKILL.md. A skill grants no permissions of its own — it can only ask for tools your client already has.

What it will not do

Every skill is narrow, and the useful ones say where they stop. These are the jobs this one is the wrong tool for.

What this skill is not for has not been published here. Nothing is implied by that: it is a section the author has not filled in.

If this is not it

20 other skills in Databases

What is in the bundle

1 file, 44.6 kB on disk. A bundle is text throughout: the instructions the model reads, plus the templates it fills in.

SKILL.md44.6 kB

What is not in it

No dependencies and nothing executable: a skill is text the agent reads, so the bundle is 1 file you can review in full before installing. The Source-available licence covers the templates and examples as well as the instructions.

Install

Installing copies the bundle into your project. Nothing runs at install time — the files sit on disk until the model reads them.

# n8n Migration Guidelines · 11.2k tokens when loaded
npx mcprush@latest skill add n8n-io/n8n-migration-guidelines

Writes to .claude/skills/n8n-migration-guidelines/ in the current project. Add --global to put it in your home directory instead, for every project.

Which clients pick it up on their own

A skill is a folder of text. A client with a skills folder reads it without being told; everywhere else the same text works, it is just handed to the model rather than found.

Claude Code.claude/skills/

Claude Desktop

ChatGPT

Cursor.cursor/skills/

VS Code.github/skills/

Codex CLI.agents/skills/

Gemini CLI.gemini/skills/

Grok.grok/skills/

Zed.agents/skills/

Windsurf.windsurf/skills/

Agent SDK.claude/skills/

HTTP / API

This release

Versionnot versioned

Publishedno release date on file

PriceFree

Referencen8n-io/n8n-migration-guidelines

Versions

Its author publishes no version number, so there is nothing here to pin to: what you install is the folder as it stands today. Instructions change more often than APIs do — a skill can be rewritten entirely without anything it depends on moving.

No earlier releases have been published to the marketplace.

Pinning

Nothing to pin to: this skill carries no version number of its own. What you install is what the folder holds on the day you install it.

Reviews

—no reviews yet · no installs yet

Nobody has reviewed this skill yet. The rating is the mean of the reviews written here, so there is none until somebody writes the first.

Who can post

Only accounts that have had the skill installed for fourteen days, so a review is written after living with it rather than after reading it. Publishers may reply once.

Who wrote it

n8n-io

Publishes on mcprush.

0 servers listed18 skills listednot claimed

Profile

Publisher

Servers0

Claim this skill