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.
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.
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 migrationdb is a transaction context with one method, query(sql, params?). The runner accepts three export styles:
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.
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 migrationNote
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().
| Method | PostgreSQL | MySQL | SQLite |
|---|---|---|---|
id() | SERIAL PRIMARY KEY | INTEGER AUTO_INCREMENT PRIMARY KEY | INTEGER PRIMARY KEY AUTOINCREMENT |
uuid(name = 'id') | UUID DEFAULT gen_random_uuid() PRIMARY KEY | VARCHAR(36) PRIMARY KEY | VARCHAR(36) PRIMARY KEY |
string(name, length = 255) | VARCHAR(length) | VARCHAR(length) | VARCHAR(length) |
text(name) | TEXT | TEXT | TEXT |
integer(name) | INTEGER | INTEGER | INTEGER |
bigint(name) | BIGINT | BIGINT | BIGINT |
float(name) | FLOAT | FLOAT | FLOAT |
decimal(name, precision = 10, scale = 2) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) |
boolean(name) | BOOLEAN | BOOLEAN | INTEGER |
date(name) | DATE | DATE | DATE |
datetime(name) | TIMESTAMP | DATETIME | TIMESTAMP |
timestamp(name) | TIMESTAMP | TIMESTAMP | TIMESTAMP |
json(name) | JSON | JSON | TEXT |
Modifiers and shortcuts
| Method | Description |
|---|---|
.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
| Method | SQL |
|---|---|
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:
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 migrationGenerate migrations with the CLI
- 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
Migrationwithup()anddown()stubs. axil generate module productsalso writessrc/migrations/<NNN>_create_products.tswhen the project has a database, detected from@axiljs/ormpluspg,mysql2orbetter-sqlite3inpackage.json, or set with--db.
axil g is an alias for axil generate. See CLI project commands.
Run migrations with axil migrate
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:
| Variable | Description |
|---|---|
DATABASE_URL | postgres://, postgresql:// or mysql:// URL. Required for PostgreSQL and MySQL |
DATABASE_FILENAME | SQLite file path. DB_FILE is accepted as an alias |
DB_DRIVER | Optional: sqlite, postgres or mysql. Otherwise inferred from the URL scheme, or SQLite when only a filename is set |
The migrations directory is chosen in this order:
--dir, absolute or relative to the project.src/migrations. Loading.tsfiles needs Node.js 22.18 or newer (built-in type stripping). On older Node.js, ifdist/migrationscontains compiled.jsmigrations, that directory is used instead.dist/migrations, whensrc/migrationsdoes 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.
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| Member | Description |
|---|---|
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:
| Column | Description |
|---|---|
id | Auto-increment key |
name | Migration filename without extension, unique |
batch | Batch number. Each up() run that applies anything is one batch |
executed_at | When 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:
const runner = new MigrationRunner(db.getDriver(), dir, { safety: 'block' })
await runner.up() // throws MigrationSafetyError if a pending migration has an error findingrunner.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.
Related
Was this page helpful?