Introduction

SQL migrations provide a structured way to evolve your database schema over time. Each migration represents a discrete change (creating tables, adding columns, etc.) that can be applied or reverted independently.

Key benefits:

Version control for database schema
Reproducible database setup across environments
Safe rollback capabilities
Checksum verification to detect unauthorized changes

Creating Migrations

Using the LIGHTWEIGHT_SQL_MIGRATION Macro

The simplest way to create a migration:

#include <Lightweight/SqlMigration.hpp>
 
LIGHTWEIGHT_SQL_MIGRATION(20260126120000, "Create users table")
{
    plan.CreateTable("users")
        .PrimaryKeyWithAutoIncrement("id")
        .RequiredColumn("email", Varchar(255))
        .RequiredColumn("name", Varchar(100))
        .Column("phone", Varchar(20))
        .Timestamps();
}

The migration is automatically registered with the MigrationManager when the program starts.

Using the Migration Class

For more control, including rollback support:

#include <Lightweight/SqlMigration.hpp>
 
using namespace Lightweight::SqlMigration;
 
static Migration createUsersTable(
    MigrationTimestamp { 20260126120000 },
    "Create users table",
    // Up migration
    [](SqlMigrationQueryBuilder& plan) {
        plan.CreateTable("users")
            .PrimaryKeyWithAutoIncrement("id")
            .RequiredColumn("email", Varchar(255))
            .Timestamps();
    },
    // Down migration (optional)
    [](SqlMigrationQueryBuilder& plan) {
        plan.DropTable("users");
    }
);

Timestamp Format

Migration timestamps use the format YYYYMMDDHHMMSS (14 digits):

MigrationTimestamp { 20260126143052 } // 2026-01-26 14:30:52

Timestamps must be:

Unique across all migrations
Monotonically increasing (newer migrations have higher timestamps)

Plugin Macro for Shared Libraries

When creating migrations in a shared library plugin, add this macro to exactly one source file:

#include <Lightweight/SqlMigration.hpp>
 
LIGHTWEIGHT_MIGRATION_PLUGIN()
 
// Your migrations here...
LIGHTWEIGHT_SQL_MIGRATION(20260126120000, "Create users table")
{
    // ...
}

The LIGHTWEIGHT_MIGRATION_PLUGIN() macro exports the AcquireMigrationManager() function that dbtool uses to load migrations from the plugin.

Table Operations

CreateTable

Create a new table with various column types:

plan.CreateTable("posts")
    .PrimaryKeyWithAutoIncrement("id", Bigint())
    .RequiredColumn("title", Varchar(200))
    .Column("body", Text())
    .RequiredColumn("published", Bool())
    .RequiredForeignKey("user_id", Bigint(), { .tableName = "users", .columnName = "id" })
    .Timestamps();

Column modifiers (chain after column declaration):

.Unique() - Add unique constraint
.Index() - Create an index on this column
.UniqueIndex() - Create a unique index

plan.CreateTable("users")
    .PrimaryKeyWithAutoIncrement("id")
    .RequiredColumn("email", Varchar(255)).Unique().Index()
    .RequiredColumn("username", Varchar(50)).UniqueIndex();

Conditional creation:

plan.CreateTableIfNotExists("users")
    .PrimaryKeyWithAutoIncrement("id")
    .RequiredColumn("email", Varchar(255));

AlterTable

Modify an existing table:

plan.AlterTable("users")
    .AddColumn("phone", Varchar(20))
    .AddNotRequiredColumn("nickname", Varchar(50))
    .RenameColumn("email", "email_address")
    .AddIndex("email_address")
    .AddUniqueIndex("phone")
    .DropColumn("legacy_field");

Available operations:

Method	Description
`.AddColumn(name, type)`	Add a non-nullable column
`.AddNotRequiredColumn(name, type)`	Add a nullable column
`.RenameColumn(old, new)`	Rename a column
`.DropColumn(name)`	Remove a column
`.AlterColumn(name, type, nullable)`	Change column type or nullability
`.AddIndex(column)`	Create an index
`.AddUniqueIndex(column)`	Create a unique index
`.DropIndex(column)`	Remove an index
`.AddForeignKey(column, ref)`	Add foreign key to existing column
`.AddForeignKeyColumn(name, type, ref)`	Add new column with foreign key
`.DropForeignKey(column)`	Remove foreign key constraint
`.RenameTo(newName)`	Rename the table

‍SQLite note: SQLite has no native ALTER TABLE … ALTER COLUMN or … ADD/DROP CONSTRAINT, so .AlterColumn(...), .AddForeignKey(...), and .DropForeignKey(...) are applied by rebuilding the table. That rebuild runs only when the migration is applied through MigrationManager (ApplyPendingMigrations()); the generated ToSql() text for these operations is a -- LIGHTWEIGHT_SQLITE_GUARD: sentinel comment that does nothing if executed directly. Applying such a migration via SqlStatement::MigrateDirect throws rather than silently skipping the change.

Conditional operations:

plan.AlterTable("users")
    .AddColumnIfNotExists("phone", Varchar(20))
    .DropColumnIfExists("obsolete_field")
    .DropIndexIfExists("old_index");

DropTable

Remove a table:

plan.DropTable("obsolete_table");

Conditional drop:

plan.DropTableIfExists("maybe_exists");

Cascade drop (removes foreign key constraints):

plan.DropTableCascade("table_with_dependencies");

Data Manipulation

Insert

Insert data during migrations:

plan.Insert("settings")
    .Set("key", "app_version")
    .Set("value", "1.0.0");

Update

Update existing data:

plan.Update("settings")
    .Set("value", "2.0.0")
    .Where("key", "=", "app_version");

Delete

Remove data:

plan.Delete("settings")

.Where("key", "=", "deprecated_setting");

CreateIndex

Create standalone indexes:

plan.CreateIndex("idx_users_email", "users", {"email"});

plan.CreateUniqueIndex("idx_users_phone", "users", {"phone"});

Composite indexes:

plan.CreateIndex("idx_posts_user_date", "posts", {"user_id", "created_at"});

Raw SQL

For database-specific features or complex operations:

plan.RawSql("CREATE EXTENSION IF NOT EXISTS pgcrypto");

plan.RawSql("ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 0)");

SQL Column Types

C++ Type	SQL Type	Notes
`Integer()`	INTEGER	32-bit integer
`Smallint()`	SMALLINT	16-bit integer
`Bigint()`	BIGINT	64-bit integer
`Tinyint()`	TINYINT	8-bit integer
`Real()`	REAL/FLOAT	Floating point
`Bool()`	BOOLEAN/BIT	Boolean
`Char(n)`	CHAR(n)	Fixed-length string
`Varchar(n)`	VARCHAR(n)	Variable-length string
`NChar(n)`	NCHAR(n)	Fixed-length Unicode string
`NVarchar(n)`	NVARCHAR(n)	Variable-length Unicode string
`Text()`	TEXT	Large text
`DateTime()`	DATETIME/TIMESTAMP	Date and time
`Date()`	DATE	Date only
`Time()`	TIME	Time only
`Guid()`	UNIQUEIDENTIFIER/UUID	UUID/GUID
`Decimal(p, s)`	DECIMAL(p, s)	Fixed-point number
`Binary(n)`	BINARY(n)	Fixed-length binary
`VarBinary(n)`	VARBINARY(n)	Variable-length binary

Usage:

using namespace Lightweight::SqlColumnTypeDefinitions;
 
plan.CreateTable("example")
    .PrimaryKeyWithAutoIncrement("id", Bigint())
    .RequiredColumn("name", Varchar(100))
    .Column("price", Decimal { .precision = 10, .scale = 2 })
    .Column("created_at", DateTime());

Migration Manager API

Custom Default Schema

When the target database expects unqualified DDL to land in a non-default schema (e.g. lasa instead of dbo / public), tell the manager about it before opening the first connection:

auto& manager = MigrationManager::GetInstance();
manager.SetDefaultSchema("lasa"); // empty disables
manager.CreateMigrationHistory();
manager.ApplyPendingMigrations();

SetDefaultSchema installs a post-connect hook that emits the dialect- specific "make this the session default" statement for every new connection:

PostgreSQL — SET search_path TO "lasa", public. Both schema_migrations and unqualified DDL inside migrations land in lasa.
SQL Server — no portable session-level switch exists; the hook is a no-op. Configure the connecting login's DEFAULT_SCHEMA server-side (ALTER USER … WITH DEFAULT_SCHEMA = lasa). Migrations that need to write to a specific schema regardless of the login default should use the WithSchema(...) builder.
SQLite — no schema concept; the hook is a no-op.

Schema names are validated against [A-Za-z0-9_] and rejected via std::invalid_argument otherwise. Passing "" clears any previously installed hook.

dbtool exposes this via the --schema flag and dbtool-gui exposes it as a "Schema" input on both the Profile and the direct-ODBC connection tabs.

Applying Migrations Programmatically

#include <Lightweight/SqlMigration.hpp>
 
using namespace Lightweight::SqlMigration;
 
// Get the singleton instance
auto& manager = MigrationManager::GetInstance();
 
// Create the schema_migrations table if it doesn't exist
manager.CreateMigrationHistory();
 
// Apply all pending migrations
size_t applied = manager.ApplyPendingMigrations(
    [](MigrationBase const& m, size_t current, size_t total) {
        std::println("[{}/{}] Applying {} - {}",
            current + 1, total, m.GetTimestamp().value, m.GetTitle());
    }
);
 
std::println("Applied {} migrations", applied);

Status & Verification

// Get migration status summary
auto status = manager.GetMigrationStatus();
std::println("Applied: {}, Pending: {}", status.appliedCount, status.pendingCount);
 
// Verify checksums of applied migrations
auto mismatches = manager.VerifyChecksums();
for (auto const& result : mismatches) {
    if (!result.matches) {
        std::println("Checksum mismatch for {}: stored={}, computed={}",
            result.timestamp.value, result.storedChecksum, result.computedChecksum);
    }
}

Preview (Dry-Run)

Generate SQL without executing:

auto statements = manager.PreviewPendingMigrations(
    [](MigrationBase const& m, size_t i, size_t n) {
        std::println("-- Migration: {} - {}", m.GetTimestamp().value, m.GetTitle());
    }
);
 
for (auto const& sql : statements) {
    std::println("{};", sql);
}

Rollback

Revert migrations:

// Revert a single migration
auto const* migration = manager.GetMigration(MigrationTimestamp { 20260126120000 });
if (migration) {
    manager.RevertSingleMigration(*migration);
}
 
// Revert all migrations after a timestamp
auto result = manager.RevertToMigration(
    MigrationTimestamp { 20260101000000 },
    [](MigrationBase const& m, size_t i, size_t n) {
        std::println("Rolling back {} - {}", m.GetTimestamp().value, m.GetTitle());
    }
);
 
if (result.failedAt) {
    std::println("Failed at {}: {}", result.failedAt->value, result.errorMessage);
}

Mark as Applied

Mark a migration as applied without executing:

auto const* migration = manager.GetMigration(MigrationTimestamp { 20260126120000 });
if (migration) {
    manager.MarkMigrationAsApplied(*migration);
}

Migration Tracking

schema_migrations Table

Lightweight automatically creates a schema_migrations table to track applied migrations:

Column	Type	Description
version	BIGINT	Migration timestamp
checksum	VARCHAR(64)	SHA-256 checksum of migration SQL
applied_at	DATETIME	When the migration was applied

Concurrency Control

Use SqlScopedLock (the generic distributed-lock RAII type) to prevent concurrent migrations:

#include <Lightweight/SqlScopedLock.hpp>
 
auto& connection = manager.GetDataMapper().Connection();
SqlScopedLock lock { connection, "lightweight_migration", std::chrono::seconds(30) };
manager.ApplyPendingMigrations();
// Lock released automatically at scope exit.

SqlScopedLock is not migration-specific — any caller that needs a named cross-process token can use it (cron leadership, "only one worker processes batch X", queue ownership, …). Pick any string for the lock name; two processes that pass the same string will serialise on it.

For structured error handling — distinguishing timeout from deadlock from driver error programmatically — use the non-throwing SqlScopedLock::TryConstruct(connection, name, timeout) factory, which returns std::expected<SqlScopedLock, SqlLockError>.

The dialect-specific primitive is selected automatically by the active SqlQueryFormatter:

SQL Server: sp_getapplock / sp_releaseapplock
PostgreSQL: pg_advisory_lock / pg_advisory_unlock
SQLite: _lightweight_locks table guarded by a unique constraint

On SQLite the bookkeeping table _lightweight_locks is treated as infrastructure — dbtool hard-reset drops it alongside schema_migrations rather than mistaking it for user data.

Best Practices

Always write Down() - Even if rollback is unlikely, having a Down() implementation enables recovery from mistakes.
Test migrations on a copy - Apply migrations to a test database before production.
Use descriptive titles - Migration titles should explain the purpose:
LIGHTWEIGHT_SQL_MIGRATION(20260126120000, "Add email verification fields to users")
Keep migrations small - One logical change per migration is easier to understand and rollback.
Never modify applied migrations - Instead of editing an existing migration, create a new one. Checksums will detect modifications.
Use conditional operations - Use IfNotExists/IfExists variants when idempotency is important.
Backup before migrating - Use dbtool backup before applying migrations to production.
Review dry-run output - Always preview migrations with --dry-run before applying:
dbtool migrate --dry-run