Migrations

Migrations are version-controlled schema definitions that let you create, modify, and inspect your SQLite tables using the Schema facade.

• Introduction
• Generating Migrations
• Migration Structure
• Running Migrations
• Creating Tables
  • Available Column Types
  • Column Modifiers
• Modifying Tables
• Dropping Tables
• Checking Schema

Introduction

Migrations are like version control for your database, allowing your team to define and share the application's database schema definition. If you have ever had to tell a teammate to manually add a column to their local database schema, you've faced the problem that database migrations solve.

Generating Migrations

Use the make:migration command to generate a migration:

dart run magic:artisan make:migration create_users_table
dart run magic:artisan make:migration CreateUsersTable    # PascalCase also works
dart run magic:artisan make:migration add_avatar_to_users

This creates a file in lib/database/migrations/ with:

• Timestamp-prefixed filename (e.g., m_2024_01_15_120000_create_users_table.dart)
• Migration class with up and down methods
• Proper imports

[!NOTE] Migrations starting with create_ and ending with _table automatically use a special stub with Schema.create() boilerplate.

Migration Structure

A migration class contains two methods: up and down. The up method adds new tables, columns, or indexes, while the down method should reverse the operations:

import 'package:magic/magic.dart';

class CreateUsersTable extends Migration {
  @override
  String get name => '2024_01_15_120000_create_users_table';

  @override
  void up() {
    Schema.create('users', (Blueprint table) {
      table.id();
      table.string('name');
      table.string('email').unique();
      table.string('password');
      table.boolean('is_active').defaultValue(true);
      table.timestamps();
    });
  }

  @override
  void down() {
    Schema.dropIfExists('users');
  }
}

Migration Naming Convention

Use timestamp prefixes for proper ordering: YYYY_MM_DD_HHMMSS_description

• 2024_01_15_120000_create_users_table
• 2024_01_15_120001_add_avatar_to_users
• 2024_01_15_120002_rename_name_to_full_name

Running Migrations

Run your migrations in main.dart or a service provider:

void main() async {
  await Magic.init(...);
  
  // Run migrations
  final migrations = await Migrator().run([
    CreateUsersTable(),
    CreatePostsTable(),
    CreateCommentsTable(),
  ]);

  if (migrations.isNotEmpty) {
    Log.info('Ran ${migrations.length} migration(s)');
  }
  
  runApp(MagicApplication(...));
}

The Migrator keeps track of which migrations have already run, so calling run() multiple times is safe.

Creating Tables

Use Schema.create() to define a new table:

Schema.create('posts', (Blueprint table) {
  table.id();
  table.string('title');
  table.text('content').nullable();
  table.integer('user_id');
  table.boolean('is_published').defaultValue(false);
  table.timestamps();
});

Available Column Types

Column Modifiers

table.string('email').unique();          // Unique constraint
table.string('bio').nullable();          // Allow NULL
table.integer('status').defaultValue(0); // Default value
table.boolean('active').defaultValue(true);

Modifying Tables

Use Schema.table() to modify an existing table:

Schema.table('users', (Blueprint table) {
  // Add new columns
  table.string('avatar_url').nullable();
  table.string('phone').nullable();
  
  // Rename a column
  table.renameColumn('name', 'full_name');
  
  // Drop a column
  table.dropColumn('legacy_field');
});

[!NOTE] Column dropping requires SQLite 3.35.0+ (2021). Column renaming requires SQLite 3.25.0+ (2018).

Modifying Column Types

SQLite does not support directly modifying column types. Use the add-copy-drop pattern:

@override
void up() {
  // 1. Add new column
  Schema.table('users', (table) {
    table.string('name_new').nullable();
  });
  
  // 2. Copy data
  DB.statement('UPDATE users SET name_new = name');
  
  // 3. Drop old, rename new
  Schema.table('users', (table) {
    table.dropColumn('name');
    table.renameColumn('name_new', 'name');
  });
}

Dropping Tables

Use Schema.dropIfExists() to drop a table only when it exists (safe for down() methods), or Schema.drop() to drop unconditionally (throws if the table is absent). Use Schema.rename() to rename a table in place.

// Drop if exists (safe for down() methods)
Schema.dropIfExists('temporary_data');

// Drop unconditionally (throws if the table does not exist)
Schema.drop('old_table');

// Rename a table
Schema.rename('posts', 'articles');

Checking Schema

The Schema facade provides three introspection helpers. Schema.hasTable() returns true when the named table exists. Schema.hasColumn() returns true when a specific column is present on the table. Schema.getColumns() returns the full list of column names for a table.

// Check if a table exists
if (await Schema.hasTable('users')) {
  // Safe to query users
}

// Check if a specific column exists
if (await Schema.hasColumn('users', 'avatar_url')) {
  // Column exists, safe to read it
}

// Get all column names for a table
final columns = await Schema.getColumns('users');
// ['id', 'name', 'email', 'created_at', 'updated_at']

[!TIP] Always write both up() and down() methods to allow rolling back migrations during development.