Database Migrations in C++: Overview of Tools and Libraries

Database Migrations in C++: Schema Management and Versioning

In modern C++ projects working with relational databases (PostgreSQL, MySQL, SQLite), supporting schema evolution is a mandatory practice. Migrations allow you to automatically apply changes to table structures, indexes, and constraints, preserving data integrity between application versions. Unlike dynamic languages, C++ does not have a built-in migration tool, but there are several mature libraries that solve this problem.

Key Requirements for a C++ Migration Library

• Versioning: Each migration must have a unique identifier (numeric or timestamp).
• Rollback Support: The ability to apply "up" (create/modify) and "down" (delete/rollback) operations.
• DBMS Integration: Works through standard drivers (libpq, sqlite3, MySQL Connector).
• Transactionality: Each migration is executed in a separate transaction for atomicity.
• Automatic Application: A command-line utility or API for running on application startup.

Popular Migration Tools for C++

1. cpp-migrations (Recommended)

A lightweight open-source library supporting PostgreSQL, MySQL, SQLite. Migrations are described as C++ classes with up() and down() methods. The version is recorded in a migrations.json file.

// Example migration for cpp-migrations#include <migration.hpp>

class CreateUsersTable : public Migration {public:    void up() override {        execute("CREATE TABLE users ("                "id SERIAL PRIMARY KEY,"                "name VARCHAR(100) NOT NULL,"                "email VARCHAR(255) UNIQUE,"                "created_at TIMESTAMP DEFAULT NOW()"                ")");    }

    void down() override {        execute("DROP TABLE IF EXISTS users");    }};

REGISTER_MIGRATION(CreateUsersTable, "001_create_users");

Running migrations via the API:

#include <migration_runner.hpp>

int main() {    MigrationRunner runner("postgresql://user:pass@localhost/db");    runner.applyPending();  // applies all pending migrations    return 0;}

2. libpq-based Migrations (Custom Approach)

If the project already uses libpq (the native PostgreSQL client), you can implement a simple migration manager based on SQL files. Example structure:

migrations/├── 001_create_users.sql├── 001_create_users.down.sql├── 002_add_role_column.sql└── 002_add_role_column.down.sql

C++ loader code:

#include <pqxx/pqxx>#include <filesystem>#include <fstream>

void applyMigrations(pqxx::connection &conn) {    pqxx::work txn(conn);    // Create a table to track applied migrations    txn.exec("CREATE TABLE IF NOT EXISTS _migrations ("             "id SERIAL PRIMARY KEY,"             "name TEXT UNIQUE,"             "applied_at TIMESTAMP DEFAULT NOW())");        for (const auto &entry : std::filesystem::directory_iterator("migrations")) {        std::string filename = entry.path().filename().string();        if (filename.find(".down.") != std::string::npos) continue;        if (filename.find(".sql") == std::string::npos) continue;                // Check if the migration has been applied        auto check = txn.exec_params("SELECT 1 FROM _migrations WHERE name = $1", filename);        if (check.empty()) {            std::ifstream file(entry.path());            std::string sql((std::istreambuf_iterator<char>(file)),                            std::istreambuf_iterator<char>());            txn.exec(sql);            txn.exec_params("INSERT INTO _migrations (name) VALU