Database

Migrations

Write versioned migrations with raw SQL or the Schema builder, run them with axil migrate or MigrationRunner, roll back by batch and check status.

9 min readUpdated DocumentationEdit this page
On this page12

Migrations are ordered TypeScript or JavaScript files that change the database schema step by step, each with an up() and a down(). This page covers the migration file format, the Schema builder, the axil migrate and axil generate migration commands, and running MigrationRunner from code.

Entity decorators never create tables, so every table your entities use needs a migration.

Migration files

Migrations live in src/migrations by default. Files run in filename order, so prefix them with a number: 001_create_users.ts, 002_add_role_to_users.ts. The filename without its extension is the migration's name.

001_create_users.ts
TS
1import type { Migration } from '@axiljs/orm'
2
3const migration: Migration = {
4  async up(db) {
5    await db.query(`
6      CREATE TABLE IF NOT EXISTS users (
7        id         SERIAL PRIMARY KEY,
8        name       VARCHAR(255) NOT NULL,
9        email      VARCHAR(255) NOT NULL UNIQUE,
10        created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
11      )
12    `)
13  },
14
15  async down(db) {
16    await db.query('DROP TABLE IF EXISTS users')
17  }
18}
19
20export default migration

db is a transaction context with one method, query(sql, params?). The runner accepts three export styles:

TypeScript
TS
1// 1. Default export (what axil generate writes)
2export default { async up(db) { /* ... */ }, async down(db) { /* ... */ } }
3
4// 2. Named exports
5export async function up(db: TransactionContext) { /* ... */ }
6export async function down(db: TransactionContext) { /* ... */ }
7
8// 3. CommonJS
9module.exports = { async up(db) { /* ... */ }, async down(db) { /* ... */ } }

Raw SQL is written for one database. The primary key line above is PostgreSQL; MySQL uses INT AUTO_INCREMENT PRIMARY KEY and SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT. Placeholders also differ: $1 for PostgreSQL, ? for MySQL and SQLite. The Schema builder below handles the type differences for you.

Schema builder

Schema.create(table, callback, db, driver) builds a CREATE TABLE IF NOT EXISTS statement, plus any indexes, and runs it through db.

TypeScript
TS
1import { Schema, type Migration } from '@axiljs/orm'
2
3const migration: Migration = {
4  async up(db) {
5    await Schema.create('products', (table) => {
6      table.id()
7      table.string('name')
8      table.string('sku', 64).unique()
9      table.text('description').nullable()
10      table.decimal('price', 10, 2).default(0)
11      table.boolean('is_active').default(true)
12      table.foreignKey('category_id', 'categories')
13      table.timestamps()
14      table.softDeletes()
15      table.index(['category_id'])
16    }, db, 'postgres')
17  },
18
19  async down(db) {
20    await Schema.drop('products', db)
21  }
22}
23
24export default migration

Note

The fourth argument, the driver type, defaults to 'postgres'. Pass 'mysql' or 'sqlite' when you target those databases, otherwise you get PostgreSQL column types such as SERIAL.

Columns

Columns are NOT NULL unless you chain .nullable().

MethodPostgreSQLMySQLSQLite
id()SERIAL PRIMARY KEYINTEGER AUTO_INCREMENT PRIMARY KEYINTEGER PRIMARY KEY AUTOINCREMENT
uuid(name = 'id')UUID DEFAULT gen_random_uuid() PRIMARY KEYVARCHAR(36) PRIMARY KEYVARCHAR(36) PRIMARY KEY
string(name, length = 255)VARCHAR(length)VARCHAR(length)VARCHAR(length)
text(name)TEXTTEXTTEXT
integer(name)INTEGERINTEGERINTEGER
bigint(name)BIGINTBIGINTBIGINT
float(name)FLOATFLOATFLOAT
decimal(name, precision = 10, scale = 2)DECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)
boolean(name)BOOLEANBOOLEANINTEGER
date(name)DATEDATEDATE
datetime(name)TIMESTAMPDATETIMETIMESTAMP
timestamp(name)TIMESTAMPTIMESTAMPTIMESTAMP
json(name)JSONJSONTEXT

Modifiers and shortcuts

MethodDescription
.nullable()Allow NULL
.unique()Add a UNIQUE constraint
.default(value)Set a default. Strings are quoted, except CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, LOCALTIMESTAMP, LOCALTIME, NULL and NOW()
foreignKey(name, refTable, refCol = 'id')Nullable INTEGER column with REFERENCES refTable(refCol) ON DELETE CASCADE
timestamps()created_at (not null, default CURRENT_TIMESTAMP) and nullable updated_at
softDeletes()Nullable deleted_at, for entities with @DeletedAt()
index(columns, name?)CREATE INDEX IF NOT EXISTS, default name idx_<table>_<columns>
uniqueIndex(columns, name?)CREATE UNIQUE INDEX IF NOT EXISTS, default name uidx_<table>_<columns>

timestamps() and softDeletes() create the same columns that @CreatedAt(), @UpdatedAt() and @DeletedAt() expect.

Changing existing tables

MethodSQL
Schema.drop(table, db)DROP TABLE IF EXISTS table
Schema.dropIfExists(table, db)DROP TABLE IF EXISTS table
Schema.rename(from, to, db)ALTER TABLE from RENAME TO to
Schema.addColumn(table, name, type, db)ALTER TABLE table ADD COLUMN name type
Schema.dropColumn(table, name, db)ALTER TABLE table DROP COLUMN name

addColumn() takes the column type as raw SQL:

002_add_role_to_users.ts
TS
1import { Schema, type Migration } from '@axiljs/orm'
2
3const migration: Migration = {
4  async up(db) {
5    await Schema.addColumn('users', 'role', "VARCHAR(50) NOT NULL DEFAULT 'user'", db)
6  },
7  async down(db) {
8    await Schema.dropColumn('users', 'role', db)
9  }
10}
11
12export default migration

Generate migrations with the CLI

Terminal
❯axil generate migration add_price_to_products
# creates src/migrations/004_add_price_to_products.ts
  • The name is converted to snake_case and given the next free three-digit number in src/migrations.
  • If a migration with the same name already exists, its file is reused and skipped, unless you pass --force.
  • The file is an empty Migration with up() and down() stubs.
  • axil generate module products also writes src/migrations/<NNN>_create_products.ts when the project has a database, detected from @axiljs/orm plus pg, mysql2 or better-sqlite3 in package.json, or set with --db.

axil g is an alias for axil generate. See CLI project commands.

Run migrations with axil migrate

Terminal
❯axil migrate # apply pending migrations
❯axil migrate status # list ran and pending migrations
❯axil migrate down # roll back the last batch
❯axil migrate up --dir=dist/migrations

Projects created by axil create with a database also get npm run migrate.

The command loads .env from the project directory (variables already set in the environment win) and reads the connection from:

VariableDescription
DATABASE_URLpostgres://, postgresql:// or mysql:// URL. Required for PostgreSQL and MySQL
DATABASE_FILENAMESQLite file path. DB_FILE is accepted as an alias
DB_DRIVEROptional: sqlite, postgres or mysql. Otherwise inferred from the URL scheme, or SQLite when only a filename is set
Terminal
# .env
DATABASE_URL=postgres://app:secret@localhost:5432/shop

The migrations directory is chosen in this order:

  1. --dir, absolute or relative to the project.
  2. src/migrations. Loading .ts files needs Node.js 22.18 or newer (built-in type stripping). On older Node.js, if dist/migrations contains compiled .js migrations, that directory is used instead.
  3. dist/migrations, when src/migrations does not exist.

axil migrate prefers the @axiljs/orm installed in your project over its own copy, and loads the driver package (pg, mysql2 or better-sqlite3) from your project's node_modules. See CLI configuration for other CLI settings.

Run migrations from code

MigrationRunner is what axil migrate uses. Run it yourself to migrate on application start or in tests.

TypeScript
TS
1import { join } from 'node:path'
2import { createConnection, MigrationRunner } from '@axiljs/orm'
3
4const db = await createConnection({ driver: 'postgres', url: process.env.DATABASE_URL })
5
6const runner = new MigrationRunner(db.getDriver(), join(process.cwd(), 'dist', 'migrations'))
7
8await runner.up()       // apply pending migrations as a new batch
9await runner.status()   // print ran / pending to the console
10await runner.down()     // roll back the last batch
MemberDescription
new MigrationRunner(driver, migrationsDir?, options?)migrationsDir defaults to <cwd>/src/migrations. The database type is read from the driver's config. options configures the safety check
up()Run every pending migration as one new batch, in filename order
down()Roll back every migration of the latest batch, in reverse order
status()Print each migration as ran (with its batch) or pending
check(options?)Analyze pending migrations for unsafe statements without running them. See Migration safety

The runner loads .ts and .js files, skips .d.ts, and runs a name only once when both x.ts and x.js exist.

How migrations are tracked

The runner creates a _migrations table on first use:

ColumnDescription
idAuto-increment key
nameMigration filename without extension, unique
batchBatch number. Each up() run that applies anything is one batch
executed_atWhen it ran

A migration counts as applied when its name is in _migrations. Renaming a file that has already run makes it pending again.

Transactions and failures

Each migration's up() or down() runs in its own transaction, together with the _migrations insert or delete. If it throws, that migration is rolled back and the run stops with Migration failed: <name>. Migrations that already finished earlier in the same run stay applied.

Note

MySQL commits implicitly after DDL statements such as CREATE TABLE and ALTER TABLE, so a failed MySQL migration can leave part of its schema change behind. Keep MySQL migrations small, one schema change each. PostgreSQL and SQLite roll DDL back with the transaction.

For transactions in application code, see Transactions. Views and stored procedures can also be created from migrations; see Views and procedures.

A migration can opt out of the transaction with export const transaction = false, which PostgreSQL requires for CREATE INDEX CONCURRENTLY. See Non-transactional migrations.

Safety checks

Since 0.3.5, MigrationRunner can check pending migrations for statements that lock or rewrite large tables or break code that is still deployed, such as non-concurrent index builds, NOT NULL columns without a default, column type changes, renames and drops:

TypeScript
TS
const runner = new MigrationRunner(db.getDriver(), dir, { safety: 'block' })

await runner.up()   // throws MigrationSafetyError if a pending migration has an error finding

runner.check() returns the findings without running anything, and analyzeMigration(sql, { dialect }) checks raw SQL. axil migrate does not enable the check. See Migration safety for the rules, the -- axil:allow <rule> comment and the options.

Was this page helpful?

Help improve the documentation

AxilJS is open source and documentation improvements are welcome.

AxilJS DocumentationMIT License · Built by SyntaxilitY