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

Database Migrations in C++: Managing Schema Evolution

In modern C++ projects, especially in server-side development and high-load systems, databases play a key role. As the application evolves, the structure of tables, indexes, and constraints inevitably changes. Manually applying SQL scripts across different environments (dev, staging, production) leads to errors and desynchronization. The solution to this problem is database migrations.

In the C++ ecosystem, there is no single "standard" tool for migrations, like Alembic for Python or Flyway for Java. However, there are several mature approaches and libraries that allow you to implement schema version management in C++ projects. In this article, we will look at the core concepts and practical implementations.

Core Concepts of Migrations in C++

A migration is a file (usually SQL or C++ code) that contains schema changes. Each migration has a unique identifier (version) and can be applied only once. The system tracks applied migrations in a special table (e.g., schema_version).

• Idempotency — re-applying a migration should not break the database.
• Order of application — migrations are executed strictly in ascending version order.
• Rollback — the ability to undo the last migration.

The cpp-migrations Library (Lightweight Solution)

One of the popular open-source libraries for C++ is cpp-migrations. It provides a minimalistic API for working with PostgreSQL, MySQL, and SQLite. The library requires no external dependencies other than the database itself.

Installation and Setup

// Example of using cpp-migrations#include <cpp-migrations/migrator.h>#include <cpp-migrations/postgresql_adapter.h>

int main() {    // Configuring the adapter for PostgreSQL    auto adapter = std::make_shared<migrations::PostgreSQLAdapter>(        "host=localhost port=5432 dbname=mydb user=user password=pass"    );        // Creating the migrator    migrations::Migrator migrator(adapter, "./migrations");        // Apply all pending migrations    migrator.migrate();        return 0;}

Migration Folder Structure

migrations/├── 001_create_users_table.sql├── 002_add_email_column.sql└── 003_create_orders_table.sql

Example SQL migration (001):

-- 001_create_users_table.sqlCREATE TABLE IF NOT EXISTS users (    id SERIAL PRIMARY KEY,    username VARCHAR(100) NOT NULL,    created_at TIMESTAMP DEFAULT NOW());

-- Rollback-- DROP TABLE IF EXISTS users;

Integration with CMake and Automatic Application

To automate migrations when the application starts, you can add a call to the migrator in the constructor of an initializer class:

class DatabaseInitializer {public:    DatabaseInitializer() {        auto adapter = std::make_shared<migrations::SQLiteAdapter>("app.db");        migrations::Migrator migrator(adapter, "migrations");                // Apply migrations on startup        migrator.migrate();                // You can check the status        auto pending = migrator.getPendingMigrations();        if (!pending.empty()) {            std::cout << "Applied " << pending.size() << " migrations." << std::endl;        }    }};

A More Advanced Approach: Using libpqxx + Custom Manager

If you need full control over the process, you can implement your own migration manager based on libpqxx (for PostgreSQL) or mysql-connector-c++. The advantage is flexibility and the absence of unnecessary dependencies.

// Example of a custom manager