July 25, 2020

Database Migrations with a Qt Database

  —A look at how to manage database schema revisions.

Recently, I needed to create a database using Qt. I used How to embed a database in your application with SQLite and Qt by Davide Coppola to get started. My experiment involved mimicking database migrations in Diesel. My needs are more modest and I used C++.

A database migration involves executing queries to alter the database structure of a live application so the schema contains what the application expects. It’s useful for application upgrades (and downgrades).

Imagine you have an application, App and that App version 1 (v1) is deployed. When v2 is deployed, there are two important use cases to handle. Assume that v2 contains database schema changes (e.g., v2 might require a table column that doesn’t exist in v1).

1. A new user installs v2. This experience should be identical to a user installing v1. This user has no data and should be able to install and use the application immediately.

2. An existing user upgrades from v1 to v2. This experience should be identical to installing v1 but it’s likely that they have information they want to keep in their existing database. Since the database schema has changed you need a way to update the database structure to the revision required by v2.

   It is this situation where the notion of a database migration is helpful. In a migration you write the SQL statements to move from database schema, revision 1 to revision 2 and execute them. This get more interesting when moving from v1 to higher a version, say v3, v5 or v10.

The idea provided in Diesel is to write queries for up- and down- grading the database schema by creating SQL statements that change the schema or table structure. My example only supports schema upgrades. It’s also assumes upgrading from App, v1, schema revison 1 to the current schema revision.

The essential idea is to recognize that a database schema is a whole-part hierarchy and that the Composite design pattern might be ideal to represent it.

In my implementation, the Component (Schema) is a base class to the Leaf (TableRevision) and the Composite (TableRevisionHistory).

The TableRevisionHistory contains a vector of TableRevisions that are executed in the order they are created by the migrate() method. Following the execution of migrate() the database structure reflects the lastest revision of the schema.

The Schema interface. The database is a parameter and not a state variable because the schema state does not depend upon a database.

class Schema // Component
{
public:
    virtual ~Schema() = 0;
    virtual const bool migrate(Database&) = 0;

protected:
    Schema() noexcept;
};

A table revision contains only the SQL statement defining the revision. A better choice for the SQL statement type might be q QSqlQuery or a QString. Using the definition of an SQL statement below has the advantage of keeping Qt dependencies out of this class.

class TableRevision // Leaf
    : public Schema
{
public:
    typedef const char *sql_statement_type;

    TableRevision(sql_statement_type s)
        : sql_statement(s) { }

    ~TableRevision() { }
    virtual const bool migrate(Database&) override;

private:
    sql_statement_type sql_statement; // The SQL statement used to revise the table structure.
};

To define a table and create a revision. Strictly speaking I don’t need a table revision class (e.g., table could inherit directly from schema). Its presence conveys the intent of the pattern more clearly.

class Table
    : public TableRevision
{
public:
    typedef TableRevision::sql_statement_type sql_statement_type;

    Table(sql_statement_type s)
        : TableRevision(s) { }

    ~Table() { }
};

Table revision0("CREATE TABLE people (id INTEGER PRIMARY KEY, name TEXT)");

The table revision history interface.

class TableRevisionHistory
    : public Schema
{
public:
    typedef Schema* value_type; //!< Schema container value type.
    typedef std::unique_ptr<value_type> pointer_type;  //!< Schema pointer type.
    typedef std::vector<pointer_type> container_type;  //!< Schema container type.

    virtual ~TableRevisionHistory();
    virtual const bool migrate(Database&) override;
    virtual void add(value_type);

protected:
    TableRevisionHistory();

private:
    container_type revision_history;
};

Schema revision isn’t supported explicitly. It’s implied by the number of table revisions stored in the table revision history. This was a surprise, as I thought revision would be part of the schema state.

One way to manage schema revision is to use a database table metadata that includes schema revision information. For example, table could the current revision of each table (e.g., the highest index used during the last update). An upgrade would require that the ‘migrate()’ begin it’s migration at the metadata revision’s plus one.

Another issue with schema revision is how to store it given that there are any number of tables whose revisions might all be different. It seems there is a lot calculation (e.g., schema revision is the sum of all table revisions) or a maintenance issue (e.g., hard coded revision number). Neither approach is strictly necessary.

To add an entry to the revision history.

class TableHistory
    : public TableRevisionHistory
{
public:
    /*! @brief Create the table history object.
     */
    TableHistory()
        : TableRevisionHistory() { }

    /*! @brief Destroy the table history object.
     */
    ~TableHistory() { }
};

TableHistory history;
history.add(&revision0);
if(history.migrate(database)) {
  return true;
}
else {
    return false; // Handle error.
}

Of course, you could managed the revision history any number of ways. A simple vector would suffice. The advantage of using the Composite pattern is the client can use the same interface regardless of the number of tables or the number of revisions of those table.

The source code.