Migration Generator

The Migration Generator creates CQL-based database migration files that use type-safe Schema DSL for defining schema changes.

Usage

azu generate migration MIGRATION_NAME [attributes] [OPTIONS]

Description

Migrations in Azu applications use CQL's powerful migration system to version control database schema changes. They provide:

Type-Safe Migrations: Crystal classes with compile-time type checking
Automatic Schema Sync: Schema file automatically updates after migrations
CQL Schema DSL: Expressive syntax for defining tables, columns, and indexes
Version Tracking: Int64 timestamp-based version numbers
Rollback Support: Reversible migrations with up and down methods

Migrations inherit from CQL::Migration(VERSION) and use CQL's Schema DSL for all database operations.

Options

MIGRATION_NAME - Name of the migration to generate (required)
attributes - Column definitions in name:type format (optional)
--timestamps - Add timestamps (default: true)
-f, --force - Overwrite existing files
-h, --help - Show help message

Examples

Generate a migration with attributes

azu generate migration CreateUsers name:string email:string age:int32

This creates:

src/db/migrations/20240115103045_create_users.cr - The migration file

Generate a simple migration

azu generate migration AddIndexToUsers

Generate migration with foreign keys

azu generate migration CreatePosts title:string content:text user_id:references

Generate migration with various types

azu generate migration CreateProducts name:string price:float64 active:bool published_at:datetime

Generated Files

Migration File (`src/db/migrations/TIMESTAMP_create_table_name.cr`)

The generator creates a properly formatted CQL migration using the Schema DSL:

require "cql"

# Migration to create users table
# Generated at 2024-01-15 10:30:45 UTC
class CreateUsers < CQL::Migration(20240115103045_i64)
  def up
    # Create users table
    schema.table :users do
      primary :id, Int64
      column :name, String
      column :email, String
      column :age, Int32
      timestamps
    end

    # Create the table in the database
    schema.users.create!

    # Add indexes (automatically generated for email fields, etc.)
    schema.alter :users do
      create_index :email_idx, [:email], unique: true
    end
  end

  def down
    # Drop users table
    schema.users.drop!
  end
end

Migration Patterns

Create Table Migration

class CreateUsers < CQL::Migration(20240115103045_i64)
  def up
    schema.table :users do
      primary :id, Int64
      column :name, String
      column :email, String
      column :password_digest, String
      column :active, Bool, default: false
      timestamps
    end

    schema.users.create!

    # Add unique index for email
    schema.alter :users do
      create_index :email_idx, [:email], unique: true
    end
  end

  def down
    schema.users.drop!
  end
end

Add Column Migration

class AddEmailToUsers < CQL::Migration(20240115104530_i64)
  def up
    schema.alter :users do
      add_column :email, String
    end

    # Add index for the new column
    schema.alter :users do
      create_index :email_idx, [:email], unique: true
    end
  end

  def down
    schema.alter :users do
      drop_index :email_idx
      drop_column :email
    end
  end
end

Create Index Migration

class AddIndexesToUsers < CQL::Migration(20240115105020_i64)
  def up
    schema.alter :users do
      create_index :email_idx, [:email], unique: true
      create_index :created_at_idx, [:created_at]
      create_index :name_email_idx, [:name, :email]
    end
  end

  def down
    schema.alter :users do
      drop_index :name_email_idx
      drop_index :created_at_idx
      drop_index :email_idx
    end
  end
end

Create Table with Foreign Key

class CreatePosts < CQL::Migration(20240115110000_i64)
  def up
    schema.table :posts do
      primary :id, Int64
      column :title, String
      column :content, String
      column :user_id, Int64
      column :published, Bool, default: false
      timestamps

      # Foreign key constraint
      foreign_key [:user_id], references: :users, references_columns: [:id]
    end

    schema.posts.create!

    # Add indexes
    schema.alter :posts do
      create_index :user_id_idx, [:user_id]
      create_index :published_idx, [:published]
    end
  end

  def down
    schema.posts.drop!
  end
end

Create Join Table Migration

class CreateUsersPostsJoin < CQL::Migration(20240115111530_i64)
  def up
    schema.table :users_posts do
      column :user_id, Int64
      column :post_id, Int64
      timestamps

      foreign_key [:user_id], references: :users, references_columns: [:id]
      foreign_key [:post_id], references: :posts, references_columns: [:id]
    end

    schema.users_posts.create!

    schema.alter :users_posts do
      create_index :user_id_idx, [:user_id]
      create_index :post_id_idx, [:post_id]
      create_index :user_post_idx, [:user_id, :post_id], unique: true
    end
  end

  def down
    schema.users_posts.drop!
  end
end

Column Types

Supported Column Types (CQL Schema DSL)

The generator supports the following attribute type mappings:

Generator Type

Crystal Type

Usage

string, text

String

Text data

int32, integer

Int32

32-bit integers

int64

Int64

64-bit integers (default for primary)

float32

Float32

32-bit floating point

float64, float

Float64

64-bit floating point

bool, boolean

Bool

Boolean values (true/false)

datetime, time

Time

Date and time

date

Date

Date only

email

String

Email (with unique index auto-generated)

url

String

URL string

json

JSON::Any

JSON data

uuid

UUID

UUID values

references

Int64

Foreign key reference

CQL Schema DSL Syntax

schema.table :users do
  # Primary key (Int64 by default)
  primary :id, Int64

  # String columns
  column :name, String
  column :email, String

  # Numeric columns
  column :age, Int32
  column :user_id, Int64
  column :price, Float64

  # Boolean columns (with default)
  column :active, Bool, default: false
  column :verified, Bool, default: false

  # Time columns
  timestamps  # Adds created_at and updated_at

  # Foreign keys (defined inline)
  foreign_key [:user_id], references: :users, references_columns: [:id]
end

Adding Indexes

Indexes are added using schema.alter:

schema.alter :users do
  # Single column index
  create_index :email_idx, [:email]

  # Unique index
  create_index :email_idx, [:email], unique: true

  # Composite index
  create_index :name_email_idx, [:name, :email]

  # Drop index
  drop_index :email_idx
end

Index Types

Single Column Indexes

# Basic index
add_index :users, :email

# Unique index
add_index :users, :email, unique: true

# Named index
add_index :users, :email, name: "idx_users_email"

Multi-Column Indexes

# Composite index
add_index :users, [:first_name, :last_name]

# Unique composite index
add_index :users, [:email, :domain], unique: true

# Named composite index
add_index :users, [:status, :created_at], name: "idx_users_status_created"

Partial Indexes

# Partial index (PostgreSQL)
add_index :users, :email, where: "active = true"

# Partial unique index
add_index :users, :email, unique: true, where: "deleted_at IS NULL"

Running Migrations

Run All Migrations

azu db:migrate
# Running migrations...
# ✓ All migrations completed successfully
# ✓ Schema file updated: src/db/schema.cr

Run Specific Number of Migrations

azu db:migrate --steps 2

Migrate to Specific Version

azu db:migrate --version 20240115103045

Rollback Migrations

# Rollback last migration
azu db:rollback

# Rollback multiple migrations
azu db:rollback --steps 3

# Rollback to specific version
azu db:rollback --version 20240115103045

View Verbose Output

azu db:migrate --verbose
# Shows detailed migration information

How Migrations Run

When you execute azu db:migrate, the CLI:

Creates a temporary Crystal script
Loads src/db/schema.cr and all migration files from src/db/migrations/*.cr
Initializes CQL's Migrator with auto-sync enabled
Runs pending migrations using migrator.up
Automatically updates src/db/schema.cr to reflect current database state
Cleans up temporary files

The schema file (src/db/schema.cr) is always kept in sync with your database!

Best Practices

1. Use Descriptive Names

# Good: Clear and descriptive
class CreateUsersTable < CQL::Migration
class AddEmailColumnToUsers < CQL::Migration
class AddIndexToUsersEmail < CQL::Migration

# Avoid: Vague names
class Migration1 < CQL::Migration
class UpdateTable < CQL::Migration

2. Keep Migrations Focused

Each migration should make one logical change:

# Good: Single purpose
class AddEmailToUsers < CQL::Migration
  def up
    add_column :users, :email, :string
  end
end

# Good: Separate migration for index
class AddEmailIndexToUsers < CQL::Migration
  def up
    add_index :users, :email
  end
end

3. Always Provide Rollback Logic

class CreateUsers < CQL::Migration
  def up
    create_table :users do |t|
      t.string :name
      t.string :email
      t.timestamps
    end
  end

  def down
    drop_table :users
  end
end

4. Use Data Types Appropriately

# Good: Appropriate data types
create_table :users do |t|
  t.string :name, null: false, limit: 100
  t.string :email, null: false, unique: true
  t.text :bio, null: true
  t.integer :age, null: true
  t.decimal :balance, precision: 10, scale: 2, default: 0
  t.boolean :active, default: true
  t.timestamps
end

5. Add Indexes for Performance

# Add indexes for frequently queried columns
add_index :users, :email, unique: true
add_index :posts, :user_id
add_index :posts, :created_at
add_index :posts, [:user_id, :created_at]

Testing Migrations

Unit Testing

describe CreateUsers do
  describe "#up" do
    it "creates users table" do
      migration = CreateUsers.new

      migration.up

      # Verify table exists
      table_exists?(:users).should be_true
    end

    it "creates required columns" do
      migration = CreateUsers.new

      migration.up

      # Verify columns exist
      column_exists?(:users, :name).should be_true
      column_exists?(:users, :email).should be_true
    end
  end

  describe "#down" do
    it "drops users table" do
      migration = CreateUsers.new

      migration.up
      migration.down

      table_exists?(:users).should be_false
    end
  end
end

Integration Testing

describe "Migration integration" do
  it "runs migrations successfully" do
    # Run migrations
    system("azu db:migrate")

    # Verify database state
    # Check tables, columns, indexes, etc.
  end

  it "rolls back migrations successfully" do
    # Run migrations
    system("azu db:migrate")

    # Rollback
    system("azu db:rollback")

    # Verify rollback
  end
end

Common Migration Patterns

1. Table Creation

class CreatePosts < CQL::Migration
  def up
    create_table :posts do |t|
      t.string :title, null: false
      t.text :content, null: false
      t.string :slug, null: false, unique: true
      t.integer :user_id, null: false
      t.boolean :published, default: false
      t.datetime :published_at, null: true
      t.timestamps
    end

    add_index :posts, :user_id
    add_index :posts, :slug, unique: true
    add_index :posts, :published
  end

  def down
    drop_table :posts
  end
end

2. Adding Foreign Keys

class AddForeignKeyToPosts < CQL::Migration
  def up
    add_foreign_key :posts, :users, column: :user_id
  end

  def down
    remove_foreign_key :posts, :users
  end
end

3. Data Migration

class MigrateUserData < CQL::Migration
  def up
    # Add new column
    add_column :users, :full_name, :string

    # Migrate data
    execute "UPDATE users SET full_name = CONCAT(first_name, ' ', last_name)"

    # Remove old columns
    remove_column :users, :first_name
    remove_column :users, :last_name
  end

  def down
    # Add old columns back
    add_column :users, :first_name, :string
    add_column :users, :last_name, :string

    # Restore data (simplified)
    execute "UPDATE users SET first_name = SPLIT_PART(full_name, ' ', 1), last_name = SPLIT_PART(full_name, ' ', 2)"

    # Remove new column
    remove_column :users, :full_name
  end
end

4. Schema Changes

class ModifyUserSchema < CQL::Migration
  def up
    # Change column type
    change_column :users, :age, :integer, null: true

    # Add new column with default
    add_column :users, :status, :string, default: "active"

    # Add index
    add_index :users, :status
  end

  def down
    remove_index :users, :status
    remove_column :users, :status
    change_column :users, :age, :string, null: false
  end
end

azu db:migrate - Run database migrations
azu db:rollback - Rollback database migrations
azu db:create - Create the database
azu db:drop - Drop the database
azu db:reset - Reset the database
azu db:setup - Setup database (create and migrate)
azu generate model - Generate CQL data models
azu generate scaffold - Generate full CRUD scaffold

Key Differences from Other ORMs

CQL vs ActiveRecord/Eloquent

Traditional ORMs:

# ActiveRecord style
class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string :name
      t.string :email
      t.timestamps
    end
  end
end

CQL Migrations:

# CQL Schema DSL style
class CreateUsers < CQL::Migration(20240115103045_i64)
  def up
    schema.table :users do
      primary :id, Int64
      column :name, String
      column :email, String
      timestamps
    end
    schema.users.create!
  end

  def down
    schema.users.drop!
  end
end

Key Benefits:

✅ Type-safe at compile time
✅ Automatic schema file synchronization
✅ Int64 timestamp versions (no collisions)
✅ Crystal's performance benefits
✅ Explicit up/down methods (no magic change)

Additional Resources

Migration System Guide: ../../MIGRATION_FIXES_SUMMARY.md
CQL Documentation: ../integration/cql-orm.md
Database Commands: ../commands/database.md
CQL GitHub: https://github.com/azutoolkit/cql
CQL Examples: https://github.com/azutoolkit/cql/tree/master/examples/migrations

PreviousCustom Validator Generator NextData Migration Generator

Last updated 3 months ago

Good night

hashtagUsage

hashtagDescription

hashtagOptions

hashtagExamples

hashtagGenerate a migration with attributes

hashtagGenerate a simple migration

hashtagGenerate migration with foreign keys

hashtagGenerate migration with various types

hashtagGenerated Files

hashtagMigration File (src/db/migrations/TIMESTAMP_create_table_name.cr)

hashtagMigration Patterns

hashtagCreate Table Migration

hashtagAdd Column Migration

hashtagCreate Index Migration

hashtagCreate Table with Foreign Key

hashtagCreate Join Table Migration

hashtagColumn Types

hashtagSupported Column Types (CQL Schema DSL)

hashtagCQL Schema DSL Syntax

hashtagAdding Indexes

hashtagIndex Types

hashtagSingle Column Indexes

hashtagMulti-Column Indexes

hashtagPartial Indexes

hashtagRunning Migrations

hashtagRun All Migrations

hashtagRun Specific Number of Migrations

hashtagMigrate to Specific Version

hashtagRollback Migrations

hashtagView Verbose Output

hashtagHow Migrations Run

hashtagBest Practices

hashtag1. Use Descriptive Names

hashtag2. Keep Migrations Focused

hashtag3. Always Provide Rollback Logic

hashtag4. Use Data Types Appropriately

hashtag5. Add Indexes for Performance

hashtagTesting Migrations

hashtagUnit Testing

hashtagIntegration Testing

hashtagCommon Migration Patterns

hashtag1. Table Creation

hashtag2. Adding Foreign Keys

hashtag3. Data Migration

hashtag4. Schema Changes

hashtagRelated Commands

hashtagKey Differences from Other ORMs

hashtagCQL vs ActiveRecord/Eloquent

hashtagAdditional Resources

Usage

Description

Options

Examples

Generate a migration with attributes

Generate a simple migration

Generate migration with foreign keys

Generate migration with various types

Generated Files

Migration File (`src/db/migrations/TIMESTAMP_create_table_name.cr`)

Migration Patterns

Create Table Migration

Add Column Migration

Create Index Migration

Create Table with Foreign Key

Create Join Table Migration

Column Types

Supported Column Types (CQL Schema DSL)

CQL Schema DSL Syntax

Adding Indexes

Index Types

Single Column Indexes

Multi-Column Indexes

Partial Indexes

Running Migrations

Run All Migrations

Run Specific Number of Migrations

Migrate to Specific Version

Rollback Migrations

View Verbose Output

How Migrations Run

Best Practices

1. Use Descriptive Names

2. Keep Migrations Focused

3. Always Provide Rollback Logic

4. Use Data Types Appropriately

5. Add Indexes for Performance

Testing Migrations

Unit Testing

Integration Testing

Common Migration Patterns

1. Table Creation

2. Adding Foreign Keys

3. Data Migration

4. Schema Changes

Related Commands

Key Differences from Other ORMs

CQL vs ActiveRecord/Eloquent

Additional Resources