Support DB migration

[christianparpart]

Requirements

• Use C++ high-level syntax to describe the DB migration (ideally declarative syntax)
• support incremental migrations
• support execution of specific migration steps
• (enable) support squashing/folding migrations on C++ level to minimize migration sequence explosion
• support dumping the final state of a database schema up to the point of a given migration (e.g. HEAD) to speed up new installations
• consider writing a small parser to convert from .sql file style migrations to C++ high level migrations
• migration manager: manage migration history via migration history table
• migration manager: retrieve (applied, not yet applied, all) migrations
• migration manager: apply specific migration by name
• migration manager: apply not yet applied migrations
• migration CLI tool to maage creation, and migration/rollback of databases

Technical detail ideas

single migration pipeline architecture:

C++ high level API for declaring the
  sequence of declarative migration operations, forming the "migration plan"
    |
    v
migration plan is translated into SQL script in the correct dialect
    |
    v
SQL script is being executed

A single C++ migration might look like this:

LIGHTWEIGHT_MIGRATION(ChronoTimestamp, TopicToken[, DescriptionText])
{
    // void Migrate(Migration& migration)
    migration.CreateTable([](auto& table) {
        table.Column<std::optional<int>>("secret_number").Indexed();
        table.Column<std::optional<std::string>, MaxChars { 30 }>("description");
        table.Column<Guid>("id");
        table.PrimaryKey("id");
    });
    migration.AlterTable([](auto& table) {
        table.Add().Column<int>("prime_number").Required();
        table.Remove().Columns("blurb");
        // change e.g. column's CHAR(30) width to 40
        table.Change().ColumnWidth("name", 40);
    });
    migration.DropTable("deprecated_records");
}

[FelixTheC]

with my background I would suggest something like this

struct Migration
{
    constexpr std::string revision = "20241011130024120" // current datetime with microseconds and also the suffix of the current file
    constexpr std::string down_revision = "" // the previous one if any
    
    [[ nodiscard ]] bool upgrade()
    {
        migration.CreateTable([](auto& table) {
            table.Column<std::optional<int>>("secret_number").Indexed();
            table.Column<std::optional<std::string>, MaxChars { 30 }>("description");
            table.Column<Guid>("id");
            table.PrimaryKey("id");
        });
    }

   // will be executed if upgrade fails
   [[ nodiscard ]] bool downgrade()
   {
       migration.DropTable("deprecated_records");
   }
}

the idea is if from Alembic