Migrations & Schema Sync

Migrations & Schema Sync Prorm gives you two complementary ways to get your model definitions into a real database schema: prorm.sync(), which reconciles tables directly from your models, and the file-based migration system (Migrator + QueryInterface), which records each incremental change as a versioned, reversible script. This guide covers both, plus the CLI that drives migrations and the query interface used to introspect a live schema. sync() is convenient for local development and tests. Migrations are what you want for anything you have to reproduce or roll back deliberately — staging, production, or shared databases. prorm.sync() sync() walks every model registered with prorm.define(...) and makes the database match. Its behaviour is controlled by SyncOptions: sync() returns the Prorm instance, so it is chainable, and it runs beforeSync / afterSync hooks (globally and per model) unless you pass hooks: false. Modes The three modes are mutually exclusive — force wins over alter, and with neither set you get the default "create missing tables only" behaviour. alter also accepts an object to control whether columns absent from the model are dropped. alter: true is equivalent to alter: { drop: true }: Other SyncOptions Option | Type | Default | Purpose | force | boolean | false | Drop and recreate all tables. | alter | boolean \| { drop?: boolean } | false | Reconcile columns in place, preserving data. | match | RegExp | – | Only sync if the configured database name matches. | logging | boolean \| (sql, timing?) => void | false | Log the SQL that sync emits. | hooks | boolean | true | Run beforeSync / afterSync hooks. | indexes | boolean | true | Also sync indexes. | constraints | boolean | true | Also sync constraints. | match is a safety valve: sync silently no-ops unless the database name matches the pattern, which is handy for guarding destructive force runs. Migrations A migration is a module that exports two async functions, up and down. Each receives a QueryInterface (the schema-manipulation API) and the Prorm instance: down should undo exactly what up did, in reverse. If a migration has no meaningful rollback you can omit down, but then reverting it only removes the tracking row without touching the schema. File naming and ordering Migration files are named <timestamp>-<name> where the timestamp is a 14-digit YYYYMMDDHHMMSS value, e.g. 20240315120000-create-users-table.js. Migrations are run in ascending order of that name (via localeCompare), so the timestamp prefix is what guarantees correct ordering — not filesystem order. Reverts run in the opposite order. .js and .ts migrations are both supported. TypeScript files are loaded through ts-node, which is registered lazily the first time a .ts migration is loaded; if ts-node is not installed you'll get a clear error telling you to install it or use .js. Changing a column without downtime A migration that rewrites a column in one step locks the table and gives you no way back. Splitting it into three leaves a working system between each pair. Only the last step destroys anything, which is why it is last and separate. Stop after step two and you have a working system on the new column with the old one still there as a fallback — see Migration rollback. Running migrations programmatically Migrator discovers migration files, tracks which have run, and runs or reverts them. Migrator also loads files automatically the first time you call up() / down() if none have been registered yet, so findMigrations() is only strictly required when you want to inspect state (pending(), executed(), status()) beforehand. Reverting Each of these returns a MigratorResult — { executed, reverted, migrations }. Inspecting state Migration storage & tracking Applied migrations are recorded in a tracking table so the migrator knows what has already run. By default it is called PrormMeta; override it with MigratorOptions.tableName (and optionally schema). PrormMigration.ensureMigrationsTable() creates it with: name — the migration name (unique) batch — an integer grouping migrations, incremented per run timestamp — unix seconds when it was applied Batch numbers let downToBatch(target) roll back everything applied after a given point. You never touch this table directly; the migrator manages it. MigratorOptions also accepts extension (default .js, .ts always allowed) and a custom filter(filename) to control which files are treated as migrations. CLI Prorm ships an orm-cli binary that wraps the same Migrator. Connection details come from a config file created by orm-cli init or from DATABASEURL / DBDIALECT / DBNAME / DBUSER / DBPASSWORD / DBHOST / DBPORT environment variables. Common flags: --name <name>, --steps <n>, --path <dir> (custom migrations directory), --verbose. Note the -- separator before flags. Run orm-cli --help for the full reference. Newly created migrations use a CommonJS template (module.exports = { up, down }) with a .js extension. Schema introspection with QueryInterface The same QueryInterface passed into migrations is a full schema-manipulation and introspection API. Inside a migration you already have it; standalone you can reach it via migrator.getQueryInterface(). Schema-changing operations available on QueryInterface include: Category | Methods | Tables | createTable, dropTable, dropAllTables, renameTable | Columns | addColumn, removeColumn, changeColumn, renameColumn | Indexes | addIndex, removeIndex | Constraints | addConstraint, removeConstraint | Foreign keys | addForeignKey, removeForeignKey | Partitions | createPartitionedTable, createPartition, attachPartition, detachPartition, dropPartition | Data | bulkInsert, bulkUpdate, bulkDelete | Raw | prormQuery(sql, options) | Note that QueryInterface's constraint support is intentionally narrow: addConstraint only materialises unique constraints (as a unique index); primaryKey, foreignKey, and check are best expressed at table-creation time or via addForeignKey / raw SQL. Choosing sync vs migrations Use prorm.sync({ force: true }) in tests and throwaway local databases — fast, no files to maintain, but destructive. Use prorm.sync({ alter: true }) for quick iteration on a dev database you want to keep. Use migrations for anything shared or long-lived: they are versioned, ordered, reversible, and tracked in PrormMeta, so every environment can be brought to the same schema state deterministically. Next: Indexes & constraints — making those queries fast and those invariants enforced. Related reading Schema diffing — see how the live schema differs from your models Query interface — DDL outside a migration