Database: Migrations

Introduction

Database migrations are version control for your database schema. They allow your team to define, share, and track changes to the database structure over time. With migrations, you can:

Track Changes - Every schema modification is recorded and versioned
Collaborate Safely - Team members can pull schema changes alongside code changes
Rollback Errors - Undo problematic migrations when issues arise
Environment Parity - Ensure consistent database structure across development, staging, and production
Automate Deployment - Run migrations as part of your deployment pipeline

Elegant's migration system provides a database-agnostic API that works consistently across MySQL, MariaDB, PostgreSQL, and SQLite.

Generating Migrations

Generate Migration Files

Use the make:migration command to create a new migration file:

elegant make:migration CreateUsersTable

This generates a timestamped migration file in your resources/database/migrations directory:

resources/database/migrations/20250101120000_CreateUsersTable.ts

The timestamp prefix ensures migrations run in the correct order.

Custom Migration Path

Specify a different location for your migrations:

elegant make:migration CreatePostsTable --path ./database/migrations

Naming Conventions

Use descriptive names that clearly indicate what the migration does:

# Good migration names
elegant make:migration CreateUsersTable
elegant make:migration AddEmailVerificationToUsers
elegant make:migration CreateOrdersTable
elegant make:migration AddIndexToProductsSku

# Avoid vague names
elegant make:migration UpdateDatabase
elegant make:migration Changes

Migration Structure

Basic Structure

A migration class contains two essential methods:

up() Method - Defines forward changes (creating tables, adding columns, etc.)
down() Method - Defines how to undo those changes (dropping tables, removing columns, etc.)

import { Migration } from '@pristine/elegant';

export class CreateUsersTable extends Migration {
  /**
   * Run the migration - creates schema changes
   */
  async up() {
    this.schema.create('users', (table) => {
      table.id();
      table.string('name');
      table.string('email').unique();
      table.string('password');
      table.boolean('active').default(true);
      table.timestamps();
    });
  }

  /**
   * Reverse the migration - undoes schema changes
   */
  async down() {
    this.schema.dropTable('users');
  }
}

Using Specific Database Connections

By default, migrations execute against your primary database connection. However, you can specify an alternative connection for migrations that need to run against a different database:

export class CreateUsersTable extends Migration {
  connection = 'analytics';

  async up() { /* Migration logic */ }

  async down() { /* Rollback logic */ }
}

Setting the connection property directs Elegant to run this migration against the named connection defined in your elegant.config.js file. This is particularly useful when managing multiple databases or when certain tables need to reside in separate database instances. Conditional Migration Execution Elegant allows you to control whether a migration should execute based on runtime conditions. Implement the shouldRun() method to define custom logic that determines if the migration proceeds:

Conditional Migration Execution

export class CreateUsersTable extends Migration {
  shouldRun() {
    return process.env.ENABLE_USERS_TABLE === 'true';
  }

  async up() { /* Migration logic */ }

  async down() { /* Rollback logic */ }
}

When shouldRun() returns false, Elegant skips the migration entirely. This feature enables environment-specific migrations, feature flag integration, or conditional schema changes based on application configuration. The method evaluates each time you run migrations, providing dynamic control over which schema changes apply to your database.

Creating Tables

Basic Table Creation

Create a new table using the method: create

await this.schema.create('users', (table) => {
  table.id();
  table.string('name');
  table.string('email');
  table.timestamps();
});

Complete Example

Here's a comprehensive table definition:

export class CreateUsersTable extends Migration {
  async up() {
    await this.schema.create('users', (table) => {
      // Primary key
      table.id();
      
      // String columns
      table.string('name', 100);
      table.string('email').unique();
      table.text('bio').nullable();
      
      // Numeric columns
      table.integer('age').nullable();
      table.decimal('balance', 10, 2).default(0);
      
      // Boolean columns
      table.boolean('active').default(true);
      table.boolean('email_verified').default(false);
      
      // Date/Time columns
      table.timestamp('email_verified_at').nullable();
      table.timestamps(); // created_at and updated_at
    });
  }
  
  async down() {
    await this.schema.drop('users');
  }
}

Column Types

String Columns

// VARCHAR with default length (255)
table.string('name');

// VARCHAR with custom length
table.string('code', 50);

// CHAR with specified length
table.char('country_code', 2);

// TEXT column for long content
table.text('description');

// LONGTEXT for very large content
table.longText('article_content');

Numeric Columns

// Integer types
table.tinyInteger('status');      // TINYINT
table.smallInteger('quantity');   // SMALLINT
table.integer('views');           // INT
table.bigInteger('large_number'); // BIGINT

// Unsigned integers
table.unsignedTinyInteger('age');
table.unsignedInteger('count');
table.unsignedBigInteger('id');

// Decimal and floating point
table.decimal('price', 8, 2);     // DECIMAL(8,2)
table.float('rating');            // FLOAT
table.double('precise_value');    // DOUBLE

Boolean Columns

// Boolean (stored as TINYINT(1) in MySQL/MariaDB)
table.boolean('active');
table.boolean('is_admin').default(false);

Date and Time Columns

// Date only
table.date('birth_date');

// Time only
table.time('opening_time');

// DateTime
table.dateTime('event_start');

// Timestamp
table.timestamp('created_at');

// Timestamp with current time default
table.timestamp('updated_at').default('CURRENT_TIMESTAMP');

// Convenience method for created_at and updated_at
table.timestamps();

JSON Columns

// JSON column (where supported)
table.json('metadata');
table.json('settings').nullable();

Binary Columns

table.enum('status', ['pending', 'active', 'suspended', 'deleted']);
table.enum('role', ['user', 'admin', 'moderator']).default('user');

Column Modifiers

Constraints and Options

Add constraints and options to columns:

await this.schema.create('products', (table) => {
  table.id();
  
  // Nullable columns
  table.string('description').nullable();
  
  // Default values
  table.integer('stock').default(0);
  table.boolean('featured').default(false);
  table.timestamp('published_at').default('CURRENT_TIMESTAMP');
  
  // Unique constraints
  table.string('sku').unique();
  table.string('slug').unique();
  
  // Unsigned (positive numbers only)
  table.integer('quantity').unsigned();
  
  // Auto-increment
  table.integer('sort_order').autoIncrement();
  
  // Comments (where supported)
  table.string('code').comment('Product identification code');
  
  table.timestamps();
});

Primary Keys

// Auto-incrementing integer primary key (recommended)
table.id();

// Custom primary key name
table.id('user_id');

// Composite primary key
table.integer('user_id');
table.integer('role_id');
table.primary(['user_id', 'role_id']);

// UUID primary key
table.uuid('id').primary();

Foreign Key Constraints

Elegant automatically generates foreign key constraint names using the fk_ prefix followed by the column name.

Defining Foreign Keys

Establish relationships between tables using foreign key constraints:

await this.schema.create('posts', (table) => {
  table.integer('user_id').unsigned();
  
  // Create a foreign key constraint
  table.foreign('user_id')
    .on('users')
    .references('id')
    .onDelete('CASCADE')
    .onUpdate('CASCADE');
});

Composite Foreign Keys

When working with composite primary keys, Elegant supports foreign key constraints that reference multiple columns. The constraint name automatically follows the fk_ prefix pattern, with each column name joined by underscores.

Defining Composite Foreign Keys

Create a foreign key constraint that references multiple columns in another table:

table.interger('user_id')
table.foreign(['user_id', 'author_id'])
  .on('users')
  .references('id')
  .onDelete('CASCADE')
  .onUpdate('CASCADE');

Shorthand Syntax

Elegant provides convenient shorthand methods for common foreign key patterns for numeric column.

Inline Foreign Key

Add a foreign key constraint directly to a column definition:

table.integer('author_id').foreign()

Separate Declaration

Alternatively, declare the foreign key constraint separately:

table.integer('author_id')
table.foreign('author_id')

Both shorthand approaches automatically infer the referenced table by converting the column name to its plural form (removing the _ id suffix). For example, company_id references the companies table.

Generated SQL

CREATE TABLE `users` (
  `company_id` INT,
  CONSTRAINT `fk_company_id`
    FOREIGN KEY (`company_id`)
    REFERENCES `companies`(`id`)
)

Explicit Foreign Key Configuration

Specify the referenced table and column explicitly while maintaining inline syntax:

// Creates an unsigned integer column with foreign key constraint
table.integer('user_id')
  .foreign('users', 'id')
  .onDelete('CASCADE')
  .onUpdate('CASCADE')

This approach combines column creation with explicit foreign key configuration and referential actions in a single fluent chain.

Indexes

Improve query performance with indexes:

await this.schema.create('products', (table) => {
  table.id();
  table.string('name');
  table.string('sku');
  table.string('category');
  table.decimal('price', 10, 2);
  
  // Single column index
  table.index('sku');
  
  // Named index
  table.index('category', 'idx_products_category');
  
  // Composite index
  table.index(['category', 'price']);
  
  // Unique index
  table.unique('sku');
  table.unique(['category', 'sku']);
  
  table.timestamps();
});

Modifying Tables

Adding Columns

Add columns to existing tables:

export class AddPhoneToUsers extends Migration {
  async up() {
    await this.schema.table('users', (table) => {
      table.string('phone', 20).nullable();
      table.string('address').nullable();
      table.alter();
    });
  }
  
  async down() {
    await this.schema.table('users', (table) => {
      table.dropColumn('phone');
      table.dropColumn('address');
      table.alter();
    });
  }
}

Modifying Columns

Change existing column definitions:

export class ModifyUsersNameColumn extends Migration {
  async up() {
    await this.schema.table('users', (table) => {
      // Change column type or attributes
      table.string('name', 200).change();
      table.text('bio').nullable().change();
      table.alter();
    });
  }
  
  async down() {
    await this.schema.table('users', (table) => {
      table.string('name', 100).change();
      table.text('bio').notNullable().change();
      table.alter()
    });
  }
}

Renaming Columns

Rename columns within a table:

export class RenameUsersNameColumn extends Migration {
  async up() {
    this.schema.table('users', (table) => {
      table.renameColumn('name', 'full_name');
    });
  }
  
  async down() {
    this.schema.table('users', (table) => {
      table.renameColumn('full_name', 'name');
    });
  }
}

Dropping Columns

Remove columns from tables:

export class RemovePhoneFromUsers extends Migration {
  async up() {
    this.schema.table('users', (table) => {
      table.dropColumn('phone');
      // Drop multiple columns
      table.dropColumn(['address', 'city', 'state']);
    });
  }
  
  async down() {
    this.schema.table('users', (table) => {
      table.string('phone', 20).nullable();
      table.string('address').nullable();
      table.string('city', 100).nullable();
      table.string('state', 2).nullable();
    });
  }
}

Managing Indexes

Add or remove indexes:

export class AddIndexesToProducts extends Migration {
  async up() {
    this.schema.table('products', (table) => {
      // Add indexes
      table.index('category');
      table.index(['category', 'status'], 'idx_category_status');
      table.unique('sku');
    });
  }
  
  async down() {
    this.schema.table('products', (table) => {
      // Drop indexes
      table.dropIndex('category');
      table.dropIndex('idx_category_status');
      table.dropUnique('sku');
    });
  }
}

Renaming and Dropping Tables

Renaming Tables

export class RenameUsersToAccounts extends Migration {
  async up() {
    await this.schema.rename('users', 'accounts');
  }
  
  async down() {
    await this.schema.rename('accounts', 'users');
  }
}

Dropping Tables

export class DropUsersTable extends Migration {
  async up() {
    await this.schema.dropTable('users');
  }
  
  async down() {
    // Recreate the table
    this.schema.table('users', (table) => {
      table.id();
      table.string('name');
      table.string('email');
      table.timestamps();
    });
  }
}

Drop If Exists

Safely drop tables that may not exist:

export class DropOldTables extends Migration {
  async up() {
    await this.schema.dropTableIfExists('old_users');
    await this.schema.dropTableIfExists('deprecated_logs');
  }
  
  async down() {
    // Usually no down action needed for cleanup migrations
  }
}

Creating Functions

Create stored functions within your migrations using the table.fn() method. Functions are reusable database routines that return a single value and can be called from queries.

Basic Function Definition

await this.schema.fn('get_user_email', (fn) => { 
  fn('get_user_email', (fn) => { 
    fn.params.int('user_id_in');
     fn.returns.string('email_out');
    fn.body = `
      SELECT email INTO email_out FROM users WHERE id = user_id_in; 
      RETURN email_out; 
    `;
  });
})

Return Type - Specify what the function returns using fn.returns:

// Returns integer 
fn.returns.int('id'); 
// Returns VARCHAR(255) 
fn.returns.string('name'); 
// Returns DECIMAL(10,2) 
fn.returns.decimal('weight', 10, 2); 
// Returns boolean
fn.returns.boolean('is_active'); 

Complete Examples

Calculate User Statistics:

await this.schema.fn('calculate_user_posts_count', (fn) => { 
  fn.params.int('user_id_in');
   fn.returns.int('post_count')
  fn.body = `
    SELECT COUNT(*) INTO post_count 
    FROM posts WHERE posts.user_id = user_id_in 
      AND posts.deleted_at IS NULL; 
   RETURN post_count; 
  `;
});

fn.paramsare automatically declared with the respective type and available within the function body
fn.returns - the return type is automatically defined and is available withint the function body

Format User Display Name:

await this.schema.fn('get_user_display_name', (fn) => { 
  fn.params.int('user_id');
  fn.body = `
    DECLARE display_name VARCHAR(255); 
    SELECT CONCAT(first_name, ' ', last_name) INTO display_name 
    FROM users WHERE id = user_id; RETURN display_name;
  `; 
  fn.returns.string(); 
});

Multiple Parameters

Functions can accept multiple parameters:

await this.schema.fn('calculate_discount_price', (fn) => { 
  fn.params.decimal('original_price', 10, 2); 
  fn.params.decimal('discount_percent', 5, 2); 
  fn.body = `RETURN original_price * (1 - discount_percent / 100);`; 
  fn.returns.decimal(10, 2); 
});

Using Functions in Queries

Once created, you can call these functions in your queries:

// Using the query builder
const email = await db.table('users') 
  .selectRaw('get_user_email(id) as email') 
     .where('id', userId) 
  .first();
// Using raw queries 
const postCount = await db.raw( 'SELECT calculate_user_posts_count(?) as count', [userId] );

Database Support

Function creation is currently supported for:

MySQL - Full support
MariaDB - Full support
PostgreSQL - Syntax may differ, test thoroughly
SQLite - Not supported (SQLite uses a different approach for functions)

Best Practices

Keep Functions Simple - Complex logic is often better in application code
Handle NULL Values - Use COALESCE() or NULL checks
Name Descriptively - Use clear names like calculate_* or get_*
Document Logic - Add comments in complex function bodies
Test Thoroughly - Functions are harder to debug than application code

Dropping Functions

To remove a function in a migration's down() method:

async down() { 
   await this.schema.dropFn('users')
   // postgres requires you specify the parameters to drop function
   await this.schema.dropFn('users', fn => {
     fn.params.string('user_id')
   })
   // you can also run a raw query to drop the function
  await this.db.raw('DROP FUNCTION IF EXISTS get_user_email'); 
}

Running Migrations

Execute All Pending Migrations

Run all migrations that haven't been executed yet:

elegant migrate

This command:

Checks which migrations have already run
Executes pending migrations in chronological order
Records successful migrations in the migrations tracking table

Check Migration Status

View which migrations have run and which are pending:

elegant migrate:status

Output example:

Migration Date	Migration Name	Status
2025-10-25T21:06:01.524Z	CreateContactsTable	pending
2025-10-25T22:47:22.000	CreateElegantMigrationTable	success

Rolling Back Migrations

Rollback Last Batch

Undo the most recent migration batch:

elegant migrate:rollback

This executes the down() method of the last batch of migrations.

Rollback Multiple Batches

Rollback a specific number of migration batches:

# Rollback last 3 batches
elegant migrate:rollback --step=3

Rollback All Migrations

Reset the database to its initial state:

elegant migrate:reset

Generating SQL Statements

Elegant can generate SQL statements from your migration files without executing them against the database. This is useful for review, version control, or integration with external database management tools.

Output Options

Save to Specific File Path

Generate SQL statements for a migration and save them to a designated file location. Use the -o or --outputFile option to specify the exact file path:

elegant make:statement --migration User -o resources/statements/User.sql

If the target directory doesn't exist, add the -f or --force flag to create it automatically:

elegant make:statement --force --migration User -o resources/statements/User.sql

Save to Specific Directory

Generate SQL statements and save them to a directory without specifying the filename. Use the -p or --outputDir option to define the target directory. The file will be named after the model class with a .sql extension:

elegant make:statement --migration User -p resources/statements

This creates resources/statements/User.sql. If the directory doesn't exist, include the -f or --force flag:

elegant make:statement --force --migration User -p resources/statements

Output to Console

Print the generated SQL directly to standard output by omitting both file and directory options:

elegant make:statement --migration User

This allows you to:

Review SQL statements before execution
Pipe output to other database tools or scripts
Integrate with version control workflows
Process statements through custom pipelines

Example with piping:

elegant make:statement --migration User | mysql -u root -p database_name

Migration Best Practices

Always Provide Down Methods Every migration should have a working down() method:

// Good
export class CreateUsersTable extends Migration {
  async up() {
    this.schema.table('users', (table) => {
      // table definition
    });
  }
  
  async down() {
    this.schema.dropTable('users');
  }
}

// Bad - missing down() implementation
export class CreateUsersTable extends Migration {
  async up() {
    this.schema.table('users', (table) => {
      // table definition
    });
  }
  
  async down() {
    // TODO: Implement rollback
  }
}

2. Keep Migrations Focused

Each migration should do one thing:

// Good - single purpose
export class AddEmailVerificationToUsers extends Migration {
  // Only adds email verification fields
}

// Bad - does too much
export class UpdateUsersAndPosts extends Migration {
  // Modifies multiple unrelated tables
}

3. Test Migrations Both Ways

Always test both up() and down() methods:

# Run migration
elegant migrate

# Test rollback
elegant migrate:rollback

# Re-run migration
elegant migrate

4. Use Descriptive Names

Migration names should clearly describe what they do:

// Good
CreateUsersTable
AddEmailVerificationToUsers
AddIndexToProductsSku
CreateOrdersTable

// Bad
UpdateDatabase
Changes
Migration1
Fix

5. Handle Production Carefully

In production environments:

Backup first - Always backup before running migrations
Review changes - Understand what each migration does
Plan downtime - Some migrations may require brief downtime
Test in staging - Run migrations in a staging environment first
Monitor performance - Large table alterations can be slow

6. Version Control

Always commit migration files to version control
Never modify migrations that have run in production
Create new migrations to fix issues instead of editing old ones

Database-Specific Considerations

MySQL / MariaDB

await this.schema.create('users', (table) => {
  table.id();
  table.string('name');
  
  // MySQL specific options
  table.engine('InnoDB');
  table.charset('utf8mb4');
  table.collation('utf8mb4_unicode_ci');
});

PostgreSQL

// PostgreSQL supports more advanced column types
table.json('metadata');          // JSON column
table.jsonb('settings');         // JSONB column (more efficient)
table.uuid('id').primary();      // UUID primary key

SQLite

SQLite has some limitations:

Limited ALTER TABLE support
No ENUM type (use CHECK constraints)
Foreign keys must be enabled explicitly

// SQLite limitations
// ✓ Supported
table.string('name');
table.integer('age');

// ✗ Limited support
table.renameColumn('old', 'new');  // Not supported
table.dropColumn('column');         // Not supported

Troubleshooting

Migration Failed

If a migration fails midway:

Check the error message for details
Review the migration code
Fix the issue
Rollback if necessary: elegant migrate:rollback
Re-run the migration: elegant migrate

Next Steps

Learn about Query Builder to interact with your database
Explore Elegant ORM for model-based interactions
Review database configuration options

01 October 2025