Versioning

QuantumDB currently tracks the database schema in a linear manner historically. Each version has at most one parent version, and at most one child version.This means that every operation you apply on an existing database schema version, produces a new identifiable version of the database schema.

8fb3b4e71a Initial database schema cf3323a79b Add column "last_seen" to table "users" ed9eb9be2e Add index "user_last_seen" on "users"."last_seen"

Changesets

QuantumDB, like a lot of other schema evolution tools, allows the user to define their changes in sets of operations which belong to each other and need to be executed together in a particular sequence. Although each operation in a changeset will yield a new version of the database schema, each change set can be identified by the final resulting version of that change set.

Below you can see an example changelog with 5 versions in total, 2 change sets each containing 2 operations. The first change set can be identified as ed9eb9be2e, whereas the second change set can be identified as cc92b7b121.

Author: Michael de Jong
Track the time the user was last seen.

Author: Michael de Jong
Track when users are created and updated.

8fb3b4e71a Initial database schema cf3323a79b Add column "last_seen" to table "users" ed9eb9be2e Add index "user_last_seen" on "users"."last_seen" 72ae28b49a Add column "created_at" to table "users" cc92b7b121 Add column "updated_at" to table "users"

In addition we also allow the user to specify an author, and a description for each change set for auditing and debugging purposes.

Using the following code we can define these changelog entries in Java.

changelog.addChangeSet("add_last_seen_column", "Michael de Jong",
        "Track the time the user was last seen.",
        addColumn("users", "last_seen", timestamp(true)),
        createIndex("users", false, "last_seen"));

changelog.addChangeSet("track_users", "Michael de Jong",
        "Track when users are created and updated.",
        addColumn("users", "created_at", timestamp(true), "NOW()", Hint.NOT_NULL),
        addColumn("users", "updated_at", timestamp(true)));

Or alternatively, in a XML file to be used together with the CLI tool.

<?xml version="1.0" encoding="UTF-8"?>

<changelog xmlns="http://www.quantumdb.io/xml/ns/quantumdb-changelog"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://www.quantumdb.io/xml/ns/quantumdb-changelog-0.3.xsd">

  <changeset id="add_last_seen_column" author="Michael de Jong">
    <description>Track the time the user was last seen.</description>
    <operations>
      <addColumn tableName="users">
        <column name="last_seen" type="timestamp with time zone" />
      </addColumn>
      <createIndex tableName="users" columnNames="last_seen" />
    </operations>
  </changeset>

  <changeset id="track_users" author="Michael de Jong">
    <description>Track when users are created and updated.</description>
    <operations>
      <addColumn tableName="users">
        <column name="created_at" type="timestamp with time zone" defaultExpression="NOW()" nullable="false" />
      </addColumn>
      <addColumn tableName="users">
        <column name="updated_at" type="timestamp with time zone" />
      </addColumn>
    </operations>
  </changeset>
</changelog>

Schema operations

QuantumDB currently supports the following schema operations:

ADD COLUMN

SchemaOperations.addColumn("users", "admin", PostgresTypes.bool(), "false", Hint.NOT_NULL);
<addColumn tableName="users">
  <column name="admin" type="boolean" defaultExpression="false" nullable="false" />
</addColumn>

This code will create a new admin column in the users table, of type boolean, which defaults to the false value, and cannot hold NULL values. It's not required to set a default value. You can specify zero, one, or multiple hints of the following types AUTO_INCREMENT, NOT_NULL, IDENTITY.

ADD FOREIGN KEY

SchemaOperations.addForeignKey("messages", "sender_id")
        .named("message_sender_id_fk")
        .onDelete(Action.CASCADE)
        .referencing("users", "id");
<addForeignKey tableName="messages" columnNames="sender_id"
               referencesTableName="users" referencesColumnNames="id"
               name="message_sender_id_fk" onDelete="CASCADE" />

This code will create a new foreign key called message_sender_id_fk on the messages table, which references the id column of the users table and which will cascade deletes to the messages table when an entry in the users table is deleted. Although this example only uses one column (sender_id) which references one other column (id), you can also specify composite foreign key constraints by stating multiple column names.

ALTER COLUMN

SchemaOperations.alterColumn("users", "admin")
        .rename("superuser")
        .addHint(Hint.NOT_NULL)
        .modifyDefaultExpression("false");
<alterColumn tableName="users" columnName="admin" newColumnName="superuser" nullable="false" />
<alterDefaultExpression tableName="users" columnName="admin" defaultExpression="false" />

Using the ALTER COLUMN operation you can alter the name, data type, default value/expression, and add and remove hints, all in one go.

COPY TABLE

SchemaOperations.copyTable("users", "customers");
<copyTable sourceTableName="users" targetTableName="customers" />

This will create a structural copy of the users table with the name customers.

Please note that this operation does not copy the contents of the original table to the new table. For that you'll have to use a DataOperation.

CREATE INDEX

SchemaOperations.createIndex("users", true, "email");
<createIndex tableName="users" columnNames="email" unique="true" />

This example will create a new index on the email column of the users table. In this particular case an index which enforces that every value in the email column must be unique. If you wish to allow duplicate entries, you'll need to pass false instead of true. You can create indices based on multiple columns as well by specifying multiple column names.

CREATE TABLE

SchemaOperations.createTable("users")
        .with("id", PostgresTypes.bigint(), Hint.IDENTITY, Hint.AUTO_INCREMENT)
        .with("first_name", PostgresTypes.text(), Hint.NOT_NULL)
        .with("last_name", PostgresTypes.text(), Hint.NOT_NULL));
<createTable tableName="users">
  <column name="id" type="bigint" primaryKey="true" autoIncrement="true" />
  <column name="first_name" type="text" nullable="false" />
  <column name="last_name" type="text" nullable="false" />
</createTable>

This example will create a new users table with three columns id, first_name, and last_name.

DROP COLUMN

SchemaOperations.dropColumn("users", "last_name");
<dropColumn tableName="users" columnName="last_name" />

This example code drops an existing column from a table. In this particular case it will drop the last_name column from the users table.

DROP FOREIGN KEY

SchemaOperations.dropForeignKey("users", "user_referral_fk");
<dropForeignKey tableName="users" foreignKeyName="user_referral_fk" />

This example code drops an existing foreign key constraint, named user_referral_fk from the users table.

DROP INDEX

SchemaOperations.dropIndex("users", "email");
<dropIndex tableName="users" columnNames="email" />

This example code drops an existing index, based on the email column from the users table.

DROP TABLE

SchemaOperations.dropTable("users");
<dropTable tableName="users" />

This example code drops the existing users table from the database.

Note that this operation is essentially "free" provided that the table is still in use by an older version of the database schema. When the last database schema still using this table is dropped, this table will be actually dropped from the database.

RENAME TABLE

SchemaOperations.renameTable("users", "customers");
<renameTable oldTableName="users" newTableName="customers" />

This operation renames an existing users table to customers.

It's worth noting that this operation is essentially "free", as the current version of QuantumDB doesn't actually change the database structure at all. The rename is handled by adding an alias to QuantumDB's table mapping, pointing to the same physical table as the original. The quantumdb-driver will use this table mapping to ensure your queries are addressed to the same physical table even though you're using the new table name in your queries.

If you mix a "rename table" operation with any other "non-free" operation on the same table, this operation will no longer be "free" and require the creation of a ghost table during the migration.

CREATE VIEW

SchemaOperations.createView("admin_users")
        .as("SELECT * FROM users WHERE admin = true");
<createView viewName="admin_users">
  <sql>SELECT * FROM users WHERE admin = true</sql>
</createView>

This example will create a new admin_users view which executes the specified query on the users table.

DROP VIEW

SchemaOperations.dropView("admin_users");
<dropView viewName="admin_users" />

This example code drops the existing admin_users view from the database.

Data operations

QuantumDB currently supports simple data operations during the migration. These types of operations are useful (if not necessary) to perform certain migrations. Here's an example:

SchemaOperations.execute("INSERT INTO users" +
        "(first_name, last_name) VALUES ('Michael', 'de Jong');");
<sql>INSERT INTO users (first_name, last_name) VALUES ('Michael', 'de Jong');</sql>

Please note that this operation should only be used to execute Data Modification Language operations, such as UPDATE, DELETE, and INSERT.

Command-line interface

The command-line interface can perform various tasks and migrations for you. These can be performed by executing commands in the following manner:

$ quantumdb <command> [<parameters...>]

The command-line interface can also list all supported commands by executing the help command:

$ quantumdb help

==> Available commands:
    > init: Initializes and prepares the database for use with QuantumDB.
    > changelog: Lists changes recorded in the changelog.
    > status: Display currently available versions of the database schema.
    > fork: Forks an existing database schema, and applies a set of operations to the fork.
    > nuke: Drops everything in the database.
    > drop: Drops the specified version of the database schema.
    > query: Execute a query on a specific version of the database schema.

init

When you start using QuantumDB for the first time in a new PostgreSQL database you'll need to initialize QuantumDB first. This is done with the init command. After executing this command:

$ quantumdb init --host=<host(s)> --database=<database> --username=<username> [--password=<password>]

QuantumDB will scan the database and create tables used to track changes, and store the changelog in the database. If you're running a master/slave setup with hot failover, you should specify all your servers' hostnames and ports, comma separated.

$ quantumdb init --host=localhost:5432 --database=quantumdb --user=quantumdb --password=quantumdb
==> Scanning database...
    > Vendor: PostgreSQL 9.6.5
    > Found: 0 tables
    > Found: 0 foreign keys
    > Found: 0 sequences
==> Persisting current state to database...
==> Database is operating at version(s):
    > 8fb3b4e71a: Initial state of the database.

changelog

When calling the changelog command, QuantumDB will retrieve the currently known changelog from the database and display it. If a changelog.xml file is present in the working directory, it will be taken into account as well.

$ quantumdb changelog
==> 8fb3b4e71a (active) - initial
    > Date: 2018-04-24 21:05:57.074
    > Author: QuantumDB
    > Description: Initial state of the database.

==> ed9eb9be2e - add_last_seen_column
    > Date: Tue Apr 24 21:13:29 CEST 2018
    > Author: Michael de Jong
    > Operations: 1
    > Description: Track the time the user was last seen.

==> cc92b7b121 - track_users
    > Date: Tue Apr 24 21:13:29 CEST 2018
    > Author: Michael de Jong
    > Operations: 1
    > Description: Track when users are created and updated.

If this output is too verbose you can use the --short argument to produce a collapsed list of changesets.

$ quantumdb changelog --short
==> 8fb3b4e71a (active) - initial
==> ed9eb9be2e - add_last_seen_column
==> cc92b7b121 - track_users

In addition to --short you can also limit which changesets should be returned by using one of, or a combination of the following arguments: --from=<version>, --until=<version>, --limit=<number>

$ quantumdb changelog --from=8fb3b4e71a --until=ed9eb9be2e --limit=1
==> 8fb3b4e71a (active) - initial
    > Date: 2018-04-24 21:05:57.074
    > Author: QuantumDB
    > Description: Initial state of the database.

status

When calling the status command, QuantumDB will display the currently active versions of the changelog. In other words the versions of the changelog that can be used to query the database.

$ quantumdb status
==> Database is operating at version(s):
    > 8fb3b4e71a: Initial state of the database.

fork

Entering a Mixed-State (ie. multiple versions of the database schema being active), can be done by executing the fork command. This effectively means QuantumDB will try to find a migration path from the currently active database schema version to the new version, and execute it.

It might be that you're already in a Mixed-State and have multiple active database schemas. Although forking even more versions into existence is possible, this feature should be considered experimental for the time being, and require more hardware resources from your database server, than expected. Should you want to perform such a fork, you'll have to specify from which other version you'd like to fork. Usually this should be the "closest" active version.

$ quantumdb fork [--from=<version>] <version>
$ quantumdb fork ed9eb9be2e
==> Scanning database...
==> Forking database from: 8fb3b4e71a to: ed9eb9be2e...
==> Database is operating at version(s):
    > 8fb3b4e71a: Initial state of the database.
    > ed9eb9be2e: Track the time the user was last seen.

nuke

Executing the nuke command will drop everything inside the database. This not only includes the QuantumDB meta-info tables, but also your own tables. This command has been specifically added for testing purposes.

$ quantumdb nuke
==> Successfully dropped everything!

Since this command removes all tables and data from the database, it is not recommended to use this command in production!

drop

When you're in Mixed-State, you can move back to a Single-State by dropping everything but one version of the database schema. This can be done with the drop command, which will drop everything inside the database that's associated with the specified database schema version (and not used by another active database schema version.

$ quantumdb drop <version>

Here's an example when we want to drop the older database schema version:

$ quantumdb drop 8fb3b4e71a
==> Scanning database...
==> Checking how many clients are still connected to: 8fb3b4e71a
    > 0 clients are using this version of the database schema.
==> Dropping database schema version: 8fb3b4e71a...
==> Database is operating at version(s):
    > ed9eb9be2e: Track the time the user was last seen.

Please note that before dropping a database schema, QuantumDB will first ensure no client is still using it. If there is a client that is still using the specified database schema version through the QuantumDB driver, it will not perform the operation.

query

query is another debugging command that can be used. The query will execute a specified query on the database, and if applicable return the results from that query. If there's only one active database schema, it will default to that version and you won't have to specify a version. However if multiple database schema versions are active, you'll need to specify the version of the database schema you want to execute the query on.

$ quantumdb query [--version=<version>] "<query>"

For instance, to insert a new record we can execute the following command:

$ quantumdb query "INSERT INTO users (email, first_name, last_name) VALUES ('michael@example.org', 'Michael', 'de Jong') RETURNING *;"
 id | email               | first_name | last_name | last_seen
--------------------------------------------------------------------------------
  1 | michael@example.org | Michael    | de Jong   | 2018-04-28 19:03:56.876755
(1 rows)

But later on when we're in a Mixed-State, and we want to query the database we'll have to provide a version to apply the query to:

$ quantumdb query --version=ed9eb9be2e "SELECT * FROM users;"
 id | email               | first_name | last_name | last_seen
--------------------------------------------------------------------------------
  1 | michael@example.org | Michael    | de Jong   | 2018-04-28 19:03:56.876755
(1 rows)