tekir

Migrations

Version-controlled database schema changes with a fluent Schema Builder.

Overview

Migrations are TypeScript files in database/migrations/ that define how your database schema evolves over time. Each migration has an up() method to apply changes and a down() method to reverse them. Migrations are tracked in a _migrations table so each file runs exactly once.

Each migration runs inside a transaction: if any statement in up() fails, the whole migration is rolled back so the schema never lands in a half-applied state (on engines that support transactional DDL). Table and column identifiers passed to the Schema Builder are validated against a strict allow-list, so generated DDL cannot be corrupted by unexpected names.

database/
  migrations/
    20240101120000_create_users.ts
    20240102120000_create_posts.ts
    20240103120000_add_bio_to_users.ts

Creating Migrations

Generate a new migration file:

tekir make:migration create_posts
Created: database/migrations/20240101120000_create_posts.ts

Edit the generated file. Extend BaseMigration and use the Schema builder in up() and down():

database/migrations/20240101_create_posts.ts
import { BaseMigration, type Schema } from '@tekir/db'

export default class CreatePosts extends BaseMigration {
  async up(schema: Schema) {
    schema.createTable('posts', (table) => {
      table.id()
      table.string('title').notNullable()
      table.text('content').nullable()
      table.integer('user_id').references('users', 'id').onDelete('CASCADE')
      table.string('status').defaultTo('draft')
      table.timestamps()
    })
  }

  async down(schema: Schema) {
    schema.dropTable('posts')
  }
}

Schema Builder

The Schema object provides a fluent API for creating, altering, and dropping tables. The builder is driver-agnostic: it generates correct SQL for SQLite, PostgreSQL, and MySQL.

// The Schema Builder generates correct SQL for each driver:
//
// SQLite:      INTEGER PRIMARY KEY AUTOINCREMENT
// PostgreSQL:  SERIAL PRIMARY KEY
// MySQL:       INT PRIMARY KEY AUTO_INCREMENT
//
// SQLite:      TEXT with DEFAULT (datetime('now'))
// PostgreSQL:  TIMESTAMP with DEFAULT NOW()
// MySQL:       TIMESTAMP with DEFAULT CURRENT_TIMESTAMP
//
// Write one migration, run on any database.

Column Types

schema.createTable('examples', (table) => {
  table.id()                        // INTEGER PRIMARY KEY AUTOINCREMENT
  table.string('name', 100)         // VARCHAR(100), default 255
  table.text('body')                // TEXT
  table.integer('count')            // INTEGER
  table.real('price')               // REAL / DOUBLE PRECISION
  table.boolean('active')           // INTEGER (SQLite) / BOOLEAN (PG)
  table.timestamp('published_at')   // TEXT (SQLite) / TIMESTAMP (PG)
  table.json('metadata')            // TEXT (SQLite) / JSONB (PG)
  table.blob('avatar')              // BLOB / BYTEA
})

Column Modifiers

Every column method returns a ColumnBuilder with chainable modifiers:

table.string('email')
  .notNullable()                    // NOT NULL
  .unique()                         // UNIQUE constraint
  .defaultTo('[email protected]')    // DEFAULT '[email protected]'

table.integer('user_id')
  .references('users', 'id')       // FOREIGN KEY
  .onDelete('CASCADE')             // ON DELETE CASCADE
  .onUpdate('SET NULL')            // ON UPDATE SET NULL

table.string('slug')
  .nullable()                      // allows NULL
  .index()                         // CREATE INDEX

Shorthands

schema.createTable('posts', (table) => {
  table.id()

  // timestamps() adds both created_at and updated_at
  table.timestamps()
  // Equivalent to:
  // table.timestamp('created_at').defaultTo('now')
  // table.timestamp('updated_at').nullable().defaultTo('now')

  // softDeletes() adds deleted_at
  table.softDeletes()
  // Equivalent to:
  // table.timestamp('deleted_at').nullable()
})

Alter Table

Use schema.alterTable() to add, drop, or rename columns on existing tables:

database/migrations/20240103_add_bio_to_users.ts
import { BaseMigration, type Schema } from '@tekir/db'

export default class AddBioToUsers extends BaseMigration {
  async up(schema: Schema) {
    schema.alterTable('users', (table) => {
      table.text('bio').nullable()
      table.string('avatar_url').nullable()
    })
  }

  async down(schema: Schema) {
    schema.alterTable('users', (table) => {
      table.dropColumn('bio')
      table.dropColumn('avatar_url')
    })
  }
}

Additional operations:

schema.alterTable('users', (table) => {
  table.renameColumn('name', 'full_name')
})

// Rename the table itself
schema.renameTable('posts', 'articles')

// Raw SQL for anything the builder doesn't cover
schema.raw('CREATE INDEX idx_posts_title ON posts (title)')

Running Migrations

Apply all pending migrations:

# Run all pending migrations
tekir migrate
Migrated: 20240101_create_users
  Migrated: 20240102_create_posts
  2 migration(s) applied

Migrations in a single migrate run share a batch number. Rollback operates on batches, so you can revert an entire deploy at once.

Rollback

Revert the most recent batch of migrations. Pass a number to rollback multiple batches.

# Rollback the last batch
tekir migrate:rollback

# Rollback last 2 batches
tekir migrate:rollback 2
Rolled back: 20240102_create_posts
  1 migration(s) rolled back

Status

See which migrations have been applied and which are pending:

tekir migrate:status
Migration                              Status     Batch
  ----------------------------------------------------------
  20240101_create_users                    Migrated   1
  20240102_create_posts                    Migrated   1
  20240103_add_bio_to_users                Pending    -

Fresh

Drop all tables and re-run every migration from scratch. Development only, never use in production.

# Drop ALL tables and re-run all migrations from scratch
tekir migrate:fresh
Dropping all tables...
  Migrated: 20240101_create_users
  Migrated: 20240102_create_posts
  Fresh migration complete (2 migrations)

Workflow

Development

# 1. Create a migration
tekir make:migration create_posts

# 2. Edit the generated file in database/migrations/

# 3. Run it
tekir migrate

# 4. Need to change something? Rollback, edit, re-run
tekir migrate:rollback
tekir migrate

# 5. Start fresh (development only)
tekir migrate:fresh

CI / Production

# CI / Production, run pending migrations before starting
tekir migrate
tekir serve