# How to Perform Database Schema Migrations in Fincept's SQLite Storage Layer

> Learn how to perform database schema migrations in Fincept's SQLite storage layer. FinceptTerminal automatically applies migrations at startup using a versioned registry and MigrationRunner class.

- Repository: [Fincept Corporation/FinceptTerminal](https://github.com/Fincept-Corporation/FinceptTerminal)
- Tags: how-to-guide
- Published: 2026-04-20

---

**FinceptTerminal automatically applies database schema migrations at startup using a versioned registry system that executes SQL transactions through the `MigrationRunner` class.**

FinceptTerminal stores all persistent data in a single SQLite file managed by the `fincept::Database` singleton. When the database initializes, the built-in migration framework ensures the schema matches the current codebase version. This article explains how the migration system works in the Fincept-Corporation/FinceptTerminal repository and how to add new schema changes safely.

## How the Migration System Works

The migration architecture consists of four coordinated components: a static registry, a runner that executes transactions, versioned migration files, and explicit registration functions.

### Migration Registry

The system maintains a static `QVector<Migration>` that holds every migration defined in the application. This registry is declared in [[`fincept-qt/src/storage/sqlite/migrations/MigrationRunner.h`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/migrations/MigrationRunner.h)](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/migrations/MigrationRunner.h) and populated via `MigrationRunner::register_migration()`. Each `Migration` struct contains a version number, descriptive name, and an `apply` function pointer.

### Migration Runner Execution

The `MigrationRunner` class, implemented in [[`MigrationRunner.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/MigrationRunner.cpp)](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/migrations/MigrationRunner.cpp), orchestrates the actual schema changes. When `runner.run()` executes, it performs the following steps:

1. Creates or verifies the `schema_version` table to track applied migrations.
2. Reads the current schema version using `read_current_version()`.
3. Iterates through the sorted registry and executes every migration with a version greater than the current one.
4. Wraps each migration in an immediate SQLite transaction, committing only after the migration's `apply` lambda succeeds and the version is recorded.

If any migration fails, the transaction rolls back, leaving the database in its previous consistent state.

### Versioned Migration Files

Individual migrations reside in `fincept-qt/src/storage/sqlite/migrations/vNNN_*.cpp` files. Each file implements `apply_vNNN(QSqlDatabase&)` which contains the SQL statements for that schema version. The file also defines `register_migration_vNNN()`, which calls `MigrationRunner::register_migration()` with the version number, name, and apply function.

### Explicit Registration Pattern

To prevent MSVC linker stripping of static initializers, FinceptTerminal uses explicit registration. The main entry point in [[`fincept-qt/src/app/main.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/app/main.cpp)](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/app/main.cpp) calls every `register_migration_vXXX()` function before opening the database. This ensures all migrations are present in the registry regardless of optimization settings.

## Running Migrations Automatically

Migrations execute automatically when the `Database` singleton opens the SQLite file. The sequence occurs in `Database::open()` as implemented in [`Database.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/Database.cpp):

```cpp
fincept::Database& db = fincept::Database::instance();
if (auto rc = db.open("/path/to/fincept.db"); rc.is_err()) {
    qFatal("Failed to open DB: %s", rc.error().c_str());
}

```

The internal flow inside `Database::open()` follows this pattern:

```cpp
// 1. Add database connection
db_ = QSqlDatabase::addDatabase("QSQLITE", "fincept_main");
db_.setDatabaseName(path);
db_.open();

// 2. Apply pragmas (WAL mode, foreign keys, etc.)
apply_pragmas();

// 3. Run migrations
MigrationRunner runner(db_);
return runner.run();  // Applies pending schema changes

```

This guarantees that every application start operates on the correct schema version without manual intervention.

## Adding a New Database Schema Migration

To modify the FinceptTerminal database schema, create a versioned migration file and register it in the application entry point.

1. **Create the migration source file** under `fincept-qt/src/storage/sqlite/migrations/` with the next sequential version number (e.g., [`v019_new_feature.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/v019_new_feature.cpp)).

2. **Implement the apply function** that executes your schema changes:

```cpp
#include "storage/sqlite/migrations/MigrationRunner.h"
#include <QSqlError>
#include <QSqlQuery>

namespace fincept {
namespace {

static Result<void> sql(QSqlDatabase& db, const char* stmt) {
    QSqlQuery q(db);
    if (!q.exec(stmt))
        return Result<void>::err(q.lastError().text().toStdString());
    return Result<void>::ok();
}

Result<void> apply_v019(QSqlDatabase& db) {
    // Defensive check: skip if column already exists
    QSqlQuery check(db);
    if (check.exec("PRAGMA table_info(settings)") && check.next()) {
        return Result<void>::ok();
    }
    
    return sql(db, "ALTER TABLE settings ADD COLUMN description TEXT DEFAULT ''");
}

} // anonymous namespace

void register_migration_v019() {
    static bool done = false;
    if (done) return;
    done = true;
    MigrationRunner::register_migration({19, "add_settings_description", apply_v019});
}

} // namespace fincept

```

3. **Add the registration call** in [`fincept-qt/src/app/main.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/app/main.cpp) after existing registration calls:

```cpp
// Existing registrations
fincept::register_migration_v001();
// ... other versions ...
fincept::register_migration_v019();  // Add this line

```

4. **Rebuild and run** FinceptTerminal. The `MigrationRunner` will automatically detect version 19 as pending and apply the schema change within a transaction.

## Key Files in the Migration System

| File | Purpose |
|------|---------|
| [`fincept-qt/src/storage/sqlite/Database.h`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/Database.h) | Singleton wrapper exposing `open()`, `execute()`, and transaction helpers |
| [`fincept-qt/src/storage/sqlite/Database.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/Database.cpp) | Implements database opening, pragma configuration, and triggers `MigrationRunner` |
| [`fincept-qt/src/storage/sqlite/migrations/MigrationRunner.h`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/migrations/MigrationRunner.h) | Declares `Migration` struct and `MigrationRunner` class with registration API |
| [`fincept-qt/src/storage/sqlite/migrations/MigrationRunner.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/migrations/MigrationRunner.cpp) | Executes pending migrations, manages `schema_version` table, handles transactions |
| [`fincept-qt/src/storage/sqlite/migrations/v001_initial.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/storage/sqlite/migrations/v001_initial.cpp) | Example migration creating the initial schema |
| [`fincept-qt/src/app/main.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/app/main.cpp) | Application entry point that explicitly registers all migrations |

## Summary

- **Automatic execution**: FinceptTerminal runs all pending database schema migrations automatically when `Database::open()` initializes the SQLite connection.
- **Transactional safety**: Each migration executes within an immediate SQLite transaction; failures roll back changes and halt the process.
- **Versioned registry**: Migrations register via `MigrationRunner::register_migration()` with sequential version numbers, stored in `schema_version` table.
- **Explicit registration**: Developers must add `register_migration_vXXX()` calls in [`main.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/main.cpp) to prevent linker optimization from stripping static initializers.
- **Idempotent patterns**: Migration functions should defensive-check schema state (e.g., `PRAGMA table_info`) to handle partial failures gracefully.

## Frequently Asked Questions

### How does FinceptTerminal handle database schema migrations on startup?

When the application launches, `Database::open()` creates a `MigrationRunner` instance and calls `runner.run()`. This checks the `schema_version` table, identifies any registered migrations with higher version numbers, and executes them sequentially inside transactions. The process is fully automatic and requires no manual intervention.

### What happens if a database schema migration fails?

If a migration's `apply` function returns an error or throws an exception, `MigrationRunner` rolls back the active transaction. This leaves the database at the last successfully applied version and prevents partial schema changes. The application will typically fail to start, logging the specific SQL error that caused the rollback.

### Why do I need to manually register migrations in [`main.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/main.cpp)?

FinceptTerminal uses explicit registration functions like `register_migration_v019()` in [`fincept-qt/src/app/main.cpp`](https://github.com/Fincept-Corporation/FinceptTerminal/blob/main/fincept-qt/src/app/main.cpp) because static initialization blocks in individual translation units can be stripped by the MSVC linker during optimized builds. Explicit calls ensure every migration object is linked into the final binary and added to the `MigrationRunner` registry.

### Can multiple developers add migrations simultaneously?

Yes, but coordination is required for version numbers. Each migration must have a unique sequential integer version. If two developers both create "v019", collisions will occur. The recommended workflow is to claim the next available version number in the project's issue tracker or pull request description before implementation, ensuring sequential integrity in the `schema_version` table.