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, and unregistered again when it is destroyed. Registration is tied to the object's lifetime, so a migration that does not live for the whole program – one with automatic storage duration, say – leaves no dangling pointer behind in the manager it registered with.

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"});

Partial (filtered) indexes

Pass a SqlCreateIndexOptions to index only the rows that match a predicate. SQLite and PostgreSQL call this a partial index, SQL Server a filtered index; all three accept the same WHERE clause after the column list.

A typical use is deduplicating on an optional key: rows that have a key must be unique, rows without one may repeat. An unconditional unique index would reject the second row with an empty key.

plan.CreateIndex("idx_queue_key", "queue", {"dedup_key"},
                 {.type = IndexType::Unique, .whereExpression = R"("dedup_key" <> '')"});
// CREATE UNIQUE INDEX "idx_queue_key" ON "queue" ("dedup_key") WHERE "dedup_key" <> ''

whereExpression is the predicate body without the leading WHERE. It is emitted verbatim, so quote identifiers and literals yourself. To stay portable, keep to what SQL Server accepts in a filter: comparisons of a column with a constant, IS [NOT] NULL and IN (...) lists, joined with AND (no OR, functions or computed expressions).

‍SQL Server: a filtered index requires ANSI_WARNINGS ON both to be created and for every INSERT, UPDATE and DELETE on its table; otherwise SQL Server fails the statement with error 1934. Lightweight's default string-truncation mode (SqlStringTruncationMode::Truncate) turns ANSI_WARNINGS off, so connections that apply or write through a filtered index must use SqlStringTruncationMode::Error, set per connection with SqlConnection::SetStringTruncationMode or for new connections with SqlConnection::SetDefaultStringTruncationMode. SQLite and PostgreSQL have no such requirement.

‍Limitation: the backup tool (dbtool backup / SqlBackup) reads indexes from the live schema and does not capture index predicates yet. It backs a partial index up as an index over all rows, and a single-column partial unique index can also mark its column unique. Restoring data that relies on the predicate (such as two rows with an empty dedup_key) therefore fails for such tables.

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);
    }
}

Compatibility Flags

Some databases predate the migration set — they were created by older tooling and differ from what the migrations declare. A compat policy returns, per migration, a set of opt-in flags that relax strict rendering for exactly those migrations. Migration plugins usually install one; it can also be set directly:

manager.SetCompatPolicy([](SqlMigration::MigrationBase const& migration) {
    return std::set<std::string> { std::string(CompatFlagFkMatchReferencedTextName) };
});

Flag Effect

lup-truncate String values in Insert / Update steps are truncated to the destination column's width, with a warning, instead of failing.

fk-match-referenced-text A foreign-key column declared by CreateTable / AlterTable (AddForeignKeyColumn) takes the narrow/wide variant of the column it references when the two differ only in that respect (CHAR(n)↔NCHAR(n), VARCHAR(n)↔NVARCHAR(n)). The referenced column's type is read from the live database (or from an earlier migration of the same run), and each change is logged as a warning. SQL Server only — PostgreSQL and SQLite store both kinds the same way.

Flags only change the SQL sent to the database. Checksums are computed from the migration as declared, so the same migration has the same checksum on every database.

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