Database Migrations

The API uses Knex migrations for managing database schema changes. This ensures consistency across all environments and provides version control for your database schema.

Why Migrations?

Benefits

• Version Control - Schema changes are tracked in Git
• Reproducibility - Easily recreate the schema in any environment
• Rollback - Revert schema changes if needed
• Team Collaboration - Multiple developers can work on schema changes
• Automated Deployment - Run migrations automatically during deployment

When to Use Migrations

Always use migrations for:

• Creating tables
• Adding/removing columns
• Changing column types
• Adding/removing indexes
• Adding/removing constraints
• Modifying foreign keys

Never directly modify the database schema - always create a migration.

Migration Files

Migrations are stored in:

api/b3api/src/knex_migrations/

Each migration file has two functions:

• up() - Apply the migration
• down() - Rollback the migration

Creating Migrations

Basic Migration

cd api/b3api
npx knex migrate:make create_products_table

This creates a new migration file:

src/knex_migrations/20250101120000_create_products_table.ts

Migration Template

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('products', (table) => {
    table.increments('id').primary();
    table.string('name', 255).notNullable();
    table.decimal('price', 10, 2).notNullable();
    table.text('description').nullable();
    table.integer('stock').defaultTo(0);
    table.boolean('active').defaultTo(true);
    table.timestamps(true, true);  // created_at, updated_at
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.dropTable('products');
}

Running Migrations

Run All Pending Migrations

cd api/b3api
npm run migrate

Or:

node ./dist/migrate

Check Migration Status

npx knex migrate:status

Output:

Current Version: 20250101120000
Latest Version: 20250101120000

Migrations:
  ✓ 20240101000000_initial.ts
  ✓ 20240115000000_add_users.ts
  ✓ 20250101120000_create_products_table.ts

Rollback Last Migration

npx knex migrate:rollback

Rollback All Migrations

npx knex migrate:rollback --all

Common Migration Patterns

Creating a Table

export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('tickets', (table) => {
    table.increments('id').primary();
    table.string('title', 255).notNullable();
    table.text('description').nullable();
    table.enum('status', ['open', 'in_progress', 'closed']).defaultTo('open');
    table.enum('priority', ['low', 'medium', 'high']).defaultTo('medium');
    table.integer('assigned_to').unsigned().nullable();
    table.integer('created_by').unsigned().notNullable();
    table.timestamps(true, true);

    // Foreign keys
    table.foreign('assigned_to').references('id').inTable('users');
    table.foreign('created_by').references('id').inTable('users');

    // Indexes
    table.index('status');
    table.index('assigned_to');
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.dropTable('tickets');
}

Adding Columns

export async function up(knex: Knex): Promise<void> {
  return knex.schema.table('products', (table) => {
    table.string('sku', 100).nullable();
    table.string('barcode', 100).nullable();
    table.index('sku');
    table.index('barcode');
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.table('products', (table) => {
    table.dropColumn('sku');
    table.dropColumn('barcode');
  });
}

Removing Columns

export async function up(knex: Knex): Promise<void> {
  return knex.schema.table('products', (table) => {
    table.dropColumn('old_column');
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.table('products', (table) => {
    table.string('old_column', 255).nullable();
  });
}

Renaming Columns

export async function up(knex: Knex): Promise<void> {
  return knex.schema.table('products', (table) => {
    table.renameColumn('old_name', 'new_name');
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.table('products', (table) => {
    table.renameColumn('new_name', 'old_name');
  });
}

Changing Column Type

export async function up(knex: Knex): Promise<void> {
  return knex.schema.alterTable('products', (table) => {
    table.text('description').alter();  // Change from string to text
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.alterTable('products', (table) => {
    table.string('description', 500).alter();
  });
}

Adding Indexes

export async function up(knex: Knex): Promise<void> {
  return knex.schema.table('tickets', (table) => {
    table.index('status');
    table.index(['assigned_to', 'status']);  // Composite index
    table.unique('external_id');
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.table('tickets', (table) => {
    table.dropIndex('status');
    table.dropIndex(['assigned_to', 'status']);
    table.dropUnique('external_id');
  });
}

Adding Foreign Keys

export async function up(knex: Knex): Promise<void> {
  return knex.schema.table('tickets', (table) => {
    table.foreign('category_id')
      .references('id')
      .inTable('categories')
      .onDelete('SET NULL')
      .onUpdate('CASCADE');
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.table('tickets', (table) => {
    table.dropForeign('category_id');
  });
}

Data Migrations

export async function up(knex: Knex): Promise<void> {
  // First, add the column
  await knex.schema.table('users', (table) => {
    table.string('full_name', 255).nullable();
  });

  // Then, populate it with data
  const users = await knex('users').select('id', 'first_name', 'last_name');
  for (const user of users) {
    await knex('users')
      .where('id', user.id)
      .update({
        full_name: `${user.first_name} ${user.last_name}`
      });
  }

  // Finally, make it not nullable
  await knex.schema.alterTable('users', (table) => {
    table.string('full_name', 255).notNullable().alter();
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.table('users', (table) => {
    table.dropColumn('full_name');
  });
}

Column Types

Common Knex column types:

// Numbers
table.increments('id');              // Auto-increment primary key
table.integer('count');               // Integer
table.bigInteger('big_count');        // Big integer
table.decimal('price', 10, 2);        // Decimal(10,2)
table.float('rating');                // Float
table.double('precise_value');        // Double

// Strings
table.string('name', 255);            // VARCHAR(255)
table.text('description');            // TEXT
table.text('content', 'longtext');    // LONGTEXT

// Dates
table.date('birth_date');             // DATE
table.datetime('event_time');         // DATETIME
table.timestamp('created_at');        // TIMESTAMP
table.timestamps(true, true);         // created_at & updated_at

// Boolean
table.boolean('is_active');           // BOOLEAN

// JSON
table.json('metadata');               // JSON
table.jsonb('settings');              // JSONB (PostgreSQL)

// Enum
table.enum('status', ['active', 'inactive']);

// Binary
table.binary('file_data');            // BLOB

Column Modifiers

table.string('name', 255)
  .notNullable()                  // NOT NULL
  .defaultTo('Unknown')           // DEFAULT 'Unknown'
  .unique()                       // UNIQUE constraint
  .unsigned()                     // UNSIGNED (for integers)
  .index()                        // Create index
  .comment('User name');          // Add comment

Migration Workflow

1. Create Migration

npx knex migrate:make add_email_to_users

2. Edit Migration

export async function up(knex: Knex): Promise<void> {
  return knex.schema.table('users', (table) => {
    table.string('email', 255).nullable().unique();
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.table('users', (table) => {
    table.dropColumn('email');
  });
}

3. Build the API

npm run build

4. Run Migration

npm run migrate

5. Update LoopBack Model

// src/models/user.model.ts
@model()
export class User extends Entity {
  @property({
    type: 'string',
    required: false,
  })
  email?: string;
}

6. Regenerate SDK

cd web
yarn build-sdk

Best Practices

1. Always Write Down Migrations

Every migration should have a corresponding rollback:

// ✅ Good
export async function down(knex: Knex): Promise<void> {
  return knex.schema.dropTable('products');
}

// ❌ Bad
export async function down(knex: Knex): Promise<void> {
  // TODO: implement rollback
}

2. One Change Per Migration

Keep migrations focused:

// ✅ Good: One migration for adding email
npx knex migrate:make add_email_to_users

// ✅ Good: Another migration for adding phone
npx knex migrate:make add_phone_to_users

// ❌ Bad: Multiple unrelated changes
npx knex migrate:make update_users_table

3. Test Migrations

Test both up and down:

# Run migration
npm run migrate

# Test rollback
npx knex migrate:rollback

# Run again
npm run migrate

4. Use Transactions

Wrap complex migrations in transactions:

export async function up(knex: Knex): Promise<void> {
  return knex.transaction(async (trx) => {
    await trx.schema.createTable('products', ...);
    await trx.schema.createTable('categories', ...);
  });
}

5. Don't Modify Existing Migrations

Once a migration is deployed, never modify it. Create a new migration instead:

# ✅ Good: New migration to fix issue
npx knex migrate:make fix_users_email_length

# ❌ Bad: Modifying existing migration
# Edit: 20240101_add_email_to_users.ts

Troubleshooting

Migration Failed

Check the error message and fix the issue:

# View the error
npm run migrate

# Fix the migration file
# Then try again
npm run migrate

Rollback Failed

If rollback fails, you may need to manually fix the database:

# Check migration status
npx knex migrate:status

# Manually rollback
mysql -h mysqldb -u root -pYpp01o#3 lb4 -e "DROP TABLE products;"

# Update migration table
mysql -h mysqldb -u root -pYpp01o#3 lb4 -e "DELETE FROM knex_migrations WHERE name='20250101_create_products';"

Migration Out of Order

Migrations run in timestamp order. Never modify timestamps:

# ✅ Good: Use current timestamp
npx knex migrate:make new_migration

# ❌ Bad: Manually changing timestamp
mv 20250101_migration.ts 20240101_migration.ts

Next Steps

• Controllers - Learn about API endpoints
• Getting Started - Set up development
• Authentication - Secure your API