Database Migrations

The Baasix migrations system provides a robust way to manage database schema changes and data transformations across different versions of your application.

Overview

The migration system:

Tracks all migrations in a baasix_Migration table
Supports versioning using semantic versioning (e.g., 0.1.0-alpha.7) or timestamps (e.g., 20241224_001_add_column)
Provides rollback capabilities for reversible migrations
Auto-runs migrations on startup (optional)
Handles upgrades from older Sequelize-based versions automatically

Quick Start

1. Enable Automatic Migrations

Add the following to your .env file:

# Enable automatic migration execution on startup (default: false)
MIGRATIONS_AUTO_RUN=true

# Custom directory for user migrations (default: ./migrations in project root)
# MIGRATIONS_DIR=/path/to/your/migrations

2. Check Migration Status

# Via API (requires admin authentication)
GET /migrations/status

Response:

{
  "data": {
    "currentVersion": "0.1.0-alpha.7",
    "lastBatch": 3,
    "appliedCount": 12,
    "pendingCount": 2,
    "totalMigrations": 14,
    "isUpToDate": false,
    "lastMigration": {
      "version": "0.1.0-alpha.7",
      "name": "Add workflow triggers",
      "status": "completed",
      "executedAt": "2024-12-24T10:30:00Z"
    },
    "isSequelizeUpgrade": false
  }
}

3. Run Pending Migrations

# Run all pending migrations
POST /migrations/run

# Run specific migration
POST /migrations/run
{
  "version": "0.1.0-alpha.7"
}

# Run migrations up to a version
POST /migrations/run
{
  "toVersion": "0.1.0-alpha.7"
}

# Dry run (preview without executing)
POST /migrations/run
{
  "dryRun": true
}

Creating Migrations

Via API

POST /migrations/create
{
  "name": "add_user_preferences",
  "type": "schema",
  "description": "Adds a preferences JSONB column to the users table"
}

This creates a new migration file in your migrations directory with a template.

Manual Migration Files

Create a file in your migrations directory (default: ./migrations/):

// migrations/20241224_add_user_preferences.ts
import type { MigrationContext, MigrationResult } from "@tspvivek/baasix";

export const version = "20241224_add_user_preferences";
export const name = "Add user preferences";
export const description = "Adds a preferences JSONB column to the users table";
export const type = "schema";

export async function up(context: MigrationContext): Promise<MigrationResult> {
  const { sql, log } = context;

  log("Adding preferences column to users table...");

  await sql`
    ALTER TABLE "users" 
    ADD COLUMN IF NOT EXISTS "preferences" JSONB DEFAULT '{}'
  `;

  log("Column added successfully");

  return {
    success: true,
    message: "Added preferences column",
    metadata: {
      affectedTable: "users",
      columnAdded: "preferences",
    },
  };
}

export async function down(context: MigrationContext): Promise<MigrationResult> {
  const { sql, log } = context;

  log("Removing preferences column from users table...");

  await sql`
    ALTER TABLE "users" 
    DROP COLUMN IF EXISTS "preferences"
  `;

  return {
    success: true,
    message: "Removed preferences column",
  };
}

export default { version, name, description, type, up, down };

Migration Script Structure

Each migration script must export:

Property	Required	Description
`version`	Yes	Unique version identifier (semver or timestamp)
`name`	Yes	Human-readable name
`description`	No	Detailed description
`type`	No	`upgrade`, `hotfix`, `schema`, `data`, or `rollback` (default: `upgrade`)
`up`	Yes	Function to run the migration
`down`	No	Function to rollback the migration

Version Naming Conventions

You can use either:

Semantic Versioning: 0.1.0-alpha.7, 1.2.3
Timestamp-based: 20241224_001_add_column, 20241224153000_migration_name

The system automatically sorts versions in the correct order.

Migration Context

The up and down functions receive a context object:

interface MigrationContext {
  db: DrizzleDatabase;        // Drizzle ORM database instance
  sql: PostgresClient;        // Raw SQL client for complex queries
  schemaManager: SchemaManager; // Access to schema definitions
  env: EnvAccessor;           // Environment variables
  log: (message: string) => void; // Logging function
}

Migration Types

Type	Description	Use Case
`upgrade`	System version upgrades	Moving from v1.0 to v1.1
`hotfix`	Critical fixes	Urgent production fixes
`schema`	Database structure changes	Adding columns, tables, indexes
`data`	Data transformations	Migrating data between formats
`rollback`	Reverting changes	Emergency rollbacks

API Reference

All endpoints require admin authentication.

Get Migration Status

GET /migrations/status

Returns current migration status including version, pending count, and last migration details.

List All Migrations

GET /migrations

Returns all migration records with full details.

List Pending Migrations

GET /migrations/pending

Returns list of migrations that haven't been applied yet.

Check If Migrations Needed

GET /migrations/check

Quick check if any migrations need to be run.

Get Specific Migration

GET /migrations/:version

Returns details for a specific migration by version.

Run Migrations

POST /migrations/run

Execute pending migrations. Supports options:

version: Run specific version only
toVersion: Run all up to specified version
dryRun: Preview without executing

Create Migration File

POST /migrations/create

Generate a new migration file with template.

Rollback Migration

POST /migrations/rollback/:version

Rollback a specific migration (requires down function).

Rollback Last Batch

POST /migrations/rollback-batch

Rollback all migrations from the last batch.

Mark as Completed

POST /migrations/mark-completed/:version

Mark a migration as completed without running it (for existing databases).

Mark All as Completed

POST /migrations/mark-all-completed

Mark all pending migrations as completed.

Rollback Strategies

Rollback Specific Migration

POST /migrations/rollback/0.1.0-alpha.7

Rollback Last Batch

Migrations run together share a batch number. You can rollback all migrations from the last batch:

POST /migrations/rollback-batch

Rollback with Metadata

POST /migrations/rollback/0.1.0-alpha.7
{
  "reason": "Critical bug discovered"
}

Handling Existing Databases

For existing installations that already have schema changes but no migration records:

Mark Single Migration as Completed

POST /migrations/mark-completed/0.1.0-alpha.7
{
  "metadata": {
    "reason": "Database already has these changes"
  }
}

Mark All Pending as Completed

POST /migrations/mark-all-completed
{
  "toVersion": "0.1.0-alpha.7"
}

Admin UI

Baasix includes a built-in admin interface for managing migrations located at Settings → Migrations.

The UI provides:

Status Overview: Current version, pending count, batch information
Pending Migrations: List with ability to run or mark as complete
Migration History: Full history table with search, filtering, and details
Quick Actions: Run all, mark all complete, rollback batch
Create Migration: Generate new migration files
Rollback: Rollback individual migrations or entire batches

Best Practices

1. Always Test Migrations

# Use dry run first
POST /migrations/run
{
  "dryRun": true
}

2. Write Rollback Functions

Always implement down functions when possible:

export async function down(context: MigrationContext): Promise<MigrationResult> {
  // Reverse the changes from up()
}

3. Use Transactions for Complex Migrations

export async function up(context: MigrationContext): Promise<MigrationResult> {
  const { sql, log } = context;

  await sql.begin(async (tx) => {
    await tx`ALTER TABLE "users" ADD COLUMN "temp" TEXT`;
    await tx`UPDATE "users" SET "temp" = "old_column"`;
    await tx`ALTER TABLE "users" DROP COLUMN "old_column"`;
    await tx`ALTER TABLE "users" RENAME COLUMN "temp" TO "new_column"`;
  });

  return { success: true };
}

4. Log Progress

export async function up(context: MigrationContext): Promise<MigrationResult> {
  const { log } = context;

  log("Starting migration...");
  // Step 1
  log("Step 1: Creating new column");
  // Step 2
  log("Step 2: Migrating data");

  return { success: true };
}

5. Include Metadata

Return useful metadata about what changed:

return {
  success: true,
  metadata: {
    tablesModified: ["users", "posts"],
    recordsUpdated: 1500,
    indexesCreated: ["idx_users_email"],
  },
};

6. Handle Idempotency

Make migrations safe to run multiple times:

// Good - Won't fail if run twice
await sql`ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "preferences" JSONB`;

// Bad - Will fail if column exists
await sql`ALTER TABLE "users" ADD COLUMN "preferences" JSONB`;

Upgrading from Sequelize

Baasix automatically detects if you're upgrading from an older Sequelize-based version and handles the transition:

Detection: If baasix_User table exists but baasix_Migration doesn't
Baseline Migration: Creates migration table and applies necessary schema updates
Admin Alert: Shows upgrade notification in the admin UI

The baseline migration handles common upgrade tasks like:

Removing VIRTUAL fields from schema definitions
Adding computed columns as real database columns
Updating indexes and constraints

Database Schema

The baasix_Migration table stores:

Field	Type	Description
id	UUID	Primary key
version	String	Unique version identifier
name	String	Migration name
description	Text	Detailed description
type	Enum	upgrade, hotfix, schema, data, rollback
status	Enum	pending, running, completed, failed, rolled_back, skipped
batch	Integer	Batch number for grouping
executedAt	DateTime	When successfully executed
executionTimeMs	Integer	Execution time in milliseconds
errorMessage	Text	Error message if failed
errorStack	Text	Error stack trace
metadata	JSONB	Additional data
checksum	String	Script integrity hash
canRollback	Boolean	Has rollback function
rolledBackAt	DateTime	When rolled back

Programmatic Usage

import { MigrationService } from '@tspvivek/baasix';

// Get migration status
const status = await MigrationService.getStatus();

// Run pending migrations
const results = await MigrationService.runPendingMigrations();

// Check if migrations needed
const check = await MigrationService.checkMigrationNeeded();

// Create a new migration
const filepath = await MigrationService.createMigration('My Migration', {
  type: 'schema',
  description: 'Description of changes',
});

// Rollback specific version
await MigrationService.rollbackMigration('0.1.0-alpha.7');

// Rollback last batch
await MigrationService.rollbackLastBatch();

Environment Variables

Variable	Default	Description
`MIGRATIONS_AUTO_RUN`	`false`	Auto-run pending migrations on startup
`MIGRATIONS_DIR`	`./migrations`	Custom migrations directory
`MIGRATIONS_UPGRADE_BASELINE`	`true`	Auto-apply baseline for Sequelize upgrades

Troubleshooting

Migration Failed Mid-Execution

Check the error in the admin UI or baasix_Migration table
Fix the issue in the migration script

Update the migration status to pending if you want to retry:

UPDATE "baasix_Migration" SET status = 'pending' WHERE version = 'x.x.x';

Run the migration again

Migration Stuck in "Running" State

If the server crashed during migration:

UPDATE "baasix_Migration" 
SET status = 'failed', 
    "errorMessage" = 'Server crashed during execution'
WHERE status = 'running';

Version Conflicts

If you have conflicting versions, rename your migration file with a more specific version number or timestamp.

Migrations Not Detected

Ensure your migration files:

Are in the correct directory (default: ./migrations)
Export version, name, and up function
Have .ts or .js extension
Don't have syntax errors

Schema Reference Guide - Learn about data models
Database Schema Guide - Database structure details
Deployment Guide - Production deployment strategies
Services Reference - Programmatic service access

On this page