Schema diffing & migration generation

SchemaDiffer compares your model definitions against the schema the database
actually has, and MigrationGenerator turns that diff into up / down SQL.
Together they answer "what has drifted?" and "what would fix it?" — without
applying anything, so you can read the statements before they run.

This is the reviewable middle ground between sync({ alter: true }), which
changes the database immediately, and writing every
migration by hand.

import { Prorm, createSchemaDiffer, createMigrationGenerator } from 'ts-prorm-orm';

const prorm = new Prorm({ dialect: 'postgres', url: process.env.DATABASE_URL });
await prorm.connect();
// … define or import your models here …

const dialect   = prorm.getDialectInstance()!;
const differ    = createSchemaDiffer(dialect, { compareIndexes: true });
const generator = createMigrationGenerator(dialect, { generateDown: true });

for (const model of Object.values(prorm.models)) {
  const diff = await differ.diffModel(model);
  if (!diff.hasChanges) continue;
  const script = generator.generateMigration(diff);
  console.log(script.name);
  console.log(script.up.join(';\n'));
}

Nothing in the pipeline touches the database except the reads on the left; the
only write is the last step, which you run.

Diffing

Call	Returns
`diffModel(Model)`	A `SchemaDiff` for one model.
`diffAll()`	A `SchemaDiff` for each registered model that has changes.
`getColumnChanges(...)`	Column-level changes only.
`getIndexChanges(...)`	Index-level changes only.
`getConstraintChanges(...)`	Constraint-level changes only.
`getForeignKeyChanges(...)`	Foreign-key changes only.

A SchemaDiff is:

{
  modelName: 'User',
  tableName: 'users',
  schemaName: undefined,
  changes: {
    columns:     [{ type: 'column', changeType: 'create' | 'modify' | 'delete',
                    columnName, modelDefinition?, databaseDefinition?, changedProperties? }],
    indexes:     [{ type: 'index', changeType, indexName, … }],
    constraints: [{ type: 'constraint', changeType, constraintName, constraintType, … }],
    foreignKeys: [{ type: 'foreignKey', changeType, … }],
  },
  hasChanges: true,
  generatedAt: Date,
}

Every change carries a changeType of create (in the model, not in the
database), modify (in both but different — changedProperties says which) or
delete (in the database, not in the model).

diffAll() finds the models through the Prorm instance that created the
dialect; you can also pass the models (diffAll(models)) or the instance
(createSchemaDiffer(dialect, { prorm })). Before 4.9.2 it always threw
Dialect is not associated with a Prorm instance.

Options

Option	Default	Effect
`compareIndexes`	on	Include index differences.
`compareConstraints`	on	Include constraint differences.
`compareForeignKeys`	on	Include foreign-key differences.
`serializeColumnType`	dialect default	Override how a `DataType` is rendered for comparison.
`skipColumnProperties`	`[]`	Column properties to leave out of the comparison, such as `['defaultValue']`.

Databases normalise things — a VARCHAR(255) may report a collation you never
asked for, a default may come back quoted differently — and the differ reports
each as a modify. skipColumnProperties leaves those properties out of the
comparison (before 4.9.2 it was declared but not applied):

import { createSchemaDiffer } from 'ts-prorm-orm';

const differ = createSchemaDiffer(prorm.getDialectInstance()!, {
  skipColumnProperties: ['defaultValue'],
});
const diff = await differ.diffModel(prorm.model('User'));

On the walkthrough below that turns
[ 'modify', 'email', [ 'defaultValue' ] ] into no change at all, and id
into [ 'modify', 'id', [ 'allowNull' ] ].

Generating migrations

import { createMigrationGenerator } from 'ts-prorm-orm';

const generator = createMigrationGenerator(prorm.getDialectInstance()!);
const script = generator.generateMigration(diff, {
  namePrefix: 'auto',
  generateDown: true,
  includeComments: true,
});

{
  name: 'auto_1757110000000_users',
  modelName: 'User',
  tableName: 'users',
  up:   ['ALTER TABLE "users" ADD COLUMN "nickname" VARCHAR(50)', 'CREATE INDEX …'],
  down: ['DROP INDEX …', 'ALTER TABLE "users" DROP COLUMN "nickname"'],
  generatedAt: Date,
}

up is emitted in order — columns, then indexes, then constraints, then
foreign keys — and down is that list reversed, so a rollback undoes the
work in the opposite order to how it was applied. generateDown: false leaves
down empty.

generateAllMigrations(diffs) maps the whole set in one call.

Option	Default	Effect
`namePrefix`	`'migration'`	Leading segment of the migration name.
`generateDown`	`true`	Produce rollback statements.
`includeComments`	`true`	Prepend `-- Migration: <table>`, a timestamp and a blank line to `up`.
`dialectOptions`	—	Passed through to the dialect.

Writing the migration to a file

The generator produces statements, not files. Combining it with the
migration layout is a few lines:

import { writeFileSync } from 'node:fs';
import { createMigrationGenerator, createSchemaDiffer, type Prorm } from 'ts-prorm-orm';

declare const prorm: Prorm; // your connected instance, models defined
const differ = createSchemaDiffer(prorm.getDialectInstance()!);
const generator = createMigrationGenerator(prorm.getDialectInstance()!);

for (const model of Object.values(prorm.models)) {
  const diff = await differ.diffModel(model);
  if (!diff.hasChanges) continue;
  const script = generator.generateMigration(diff, { includeComments: false });
  writeFileSync(`./migrations/${script.name}.js`, `
module.exports = {
  async up(queryInterface) {
${script.up.map((s) => `    await queryInterface.prormQuery(${JSON.stringify(s)});`).join('\n')}
  },
  async down(queryInterface) {
${script.down.map((s) => `    await queryInterface.prormQuery(${JSON.stringify(s)});`).join('\n')}
  },
};
`);
}

A worked example

A users table was created with a legacyCode column. The model has since
dropped it and gained a nullable nickname and an index:

  prorm.define('User', {
    id:         { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    email:      { type: DataTypes.STRING(255), allowNull: false },
-   legacyCode: { type: DataTypes.STRING(20) },
+   nickname:   { type: DataTypes.STRING(50) },
  }, {
    tableName: 'users',
    timestamps: false,
+   indexes: [{ name: 'idx_users_nickname', fields: ['nickname'] }],
  });

Diff the model

import { createSchemaDiffer } from 'ts-prorm-orm';

const dialect = prorm.getDialectInstance()!;
const differ = createSchemaDiffer(dialect, { compareConstraints: false });
const diff = await differ.diffModel(prorm.model('User'));

console.log(diff.changes.columns.map((c) => [c.changeType, c.columnName, c.changedProperties]));

On SQLite:

[ [ 'modify', 'id', [ 'allowNull', 'defaultValue' ] ],
  [ 'modify', 'email', [ 'defaultValue' ] ],
  [ 'create', 'nickname', undefined ],
  [ 'delete', 'legacyCode', undefined ] ]

Two of those four are real. The id and email "modifications" are the
database reporting its own defaults back differently from how the model states
them — the cosmetic drift described under Options, which
skipColumnProperties: ['defaultValue'] removes. diff.changes.indexes holds
the new index: [ [ 'create', 'idx_users_nickname' ] ].

Generate the migration

import { createMigrationGenerator } from 'ts-prorm-orm';

const script = createMigrationGenerator(prorm.getDialectInstance()!, { namePrefix: 'auto' })
  .generateMigration(diff);

That is SQLite. Before 4.9.2 the PostgreSQL output also carried a
DROP INDEX "undefined" for the primary key's index, and neither engine
reported the model's new index; index rows read back from the database are now
normalised per engine, and nameless, primary-key and constraint-backed indexes
are never reported as deleted.

script.name is auto_<timestamp>_users, and down reverses the work: it
drops the new index, re-adds legacyCode (as VARCHAR(20) on SQLite) and
drops nickname.

Review it, line by line

Statement	Verdict
`ALTER COLUMN "id" DROP NOT NULL` / `DROP DEFAULT`	Noise from the drift above. On PostgreSQL it would strip the `SERIAL` default from the key; SQLite has no `ALTER COLUMN` at all. Delete.
`ALTER COLUMN "email" DROP DEFAULT`	Noise. Delete.
`ADD COLUMN "nickname" STRING`	Right intent, wrong type: the generator wrote the portable key, not `VARCHAR(50)`. Fix the type.
`DROP COLUMN "legacyCode"`	Destroys every value in the column. Keep only if that is the decision — or rename it aside instead.
`CREATE INDEX "idx_users_nickname"`	Right. Keep it, after the column it indexes.
`ADD CONSTRAINT "users_pkey"`	The table already has its key. Delete.

Write the migration you actually want

Run it

$ npx prorm migration:run
Database connection established
Running 1 pending migration(s)...
Running migration: 20260926181500-users-nickname
Migration 20260926181500-users-nickname completed
Executed 1 migration(s)

Reading a diff before you trust it

Generated migrations are a starting point, not an answer. Four things to check
every time:

A delete on a column is a data loss. The differ cannot know whether the
column is gone on purpose or the model is simply out of date.

A rename looks like a delete plus a create. Nothing in the diff carries
the intent that name became full_name; applied as generated, that drops
the data. Write renames by hand.

A modify on a type may not be safe. Narrowing VARCHAR(255) to
VARCHAR(50) compiles fine and fails, or truncates, on real rows.

The SQL is generic. The generator emits ALTER TABLE … ALTER COLUMN and
ADD CONSTRAINT, which SQLite does not support, and may render a type by its
portable name. Rewrite such statements with the
QueryInterface, which knows each dialect's workarounds.

[!CAUTION]
Never pipe script.up straight into the database. A generated DROP COLUMN
runs as happily as an ADD COLUMN, and in the example above it would have
removed legacyCode from every row with no backup and no prompt.

How it compares to `sync({ alter: true })`

	`sync({ alter: true })`	`SchemaDiffer` + `MigrationGenerator`
Applies changes	immediately	never — you decide
Rollback	none	generated `down`
Reviewable	no	yes — plain SQL strings
Drops columns	only with `{ drop: true }`	reports them; you choose
Good for	local development	staging and production change management

Migrations — running and tracking migrations

QueryInterface — the API migrations are written against

Indexes & constraints

Defining models

Schema diffing & migration generation

Diffing#

Options#

Generating migrations#

Writing the migration to a file#

A worked example#

Diff the model#

Generate the migration#

Review it, line by line#

Write the migration you actually want#

Run it#

Reading a diff before you trust it#

How it compares to sync({ alter: true })#

Related reading#