QuantumDB is not ready for production! No guarantees are made for migration paths between 0.x.x versions.
The easiest method at the moment to manage your PostgreSQL database with QuantumDB is to use the QuantumDB CLI tool. With this tool you'll be able to perform a couple of actions to support your database schema migrations workflow. You can install the command-line tool by executing the following commands (on OS X and Linux):
$ curl https://quantumdb.io/getting-started/install.sh | bash
Note that we require a Java 8 JRE or JDK to be installed on your system.
Next we'll need to let QuantumDB inspect the current database, to see which tables, and relations are currently present. It doesn't if this database is empty, or if is has pre-existing tables and data stored in it already. It will store this information in meta tables dedicated for use by QuantumDB. We can do this with the command-line tool by executing the following command:
$ quantumdb init --host=localhost:5432 --database=<database> --username=<username> --password=<password> ==> 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): > 4c00c31ff0: Initial state of the database.
Once you've executed this operation, you'll be able to define and execute schema migrations.
This command will create a file called
.quantumdb in the current directory.
Please note that this file contains the database login credentials you specified in plain-text.
Do not commit this file to any version control system!
QuantumDB shifts your database into and out of Mixed-State while performing database schema
migrations. To abstract all this complexity away from your application, you'll need to put the
quantumdb-driver on your application's classpath. Unfortunately we have yet to publish
it on Maven Central, so you'll have to download it manually, and add it as a dependency to your
Java project yourself. You can download it here:
Note that you still need the PostgreSQL JDBC driver to be on the classpath of your project. QuantumDB's driver acts as an interceptor. It receives queries to be executed from your application, alters them so they work in the Mixed-State, and delegates the transformed query to the real PostgreSQL JDBC driver.
Next you'll need to alter the connection string you use to connect to your PostgreSQL database so it will use the QuantumDB database driver, and connect to a specific version of your database schema:
If you're using a master/slave setup for your PostgreSQL database, you can alter the connection string to something like this:
QuantumDB has not yet been tested in any master/slave setup.
QuantumDB stores all database schema migrations into a changelog. This changelog contains a sequence of changesets which are groupings of schema operations which should be executed together. We can define such schema operations in an XML file like this:
<?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.4.xsd"> <changeset id="create_users_table" author="Michael de Jong"> <description>Creates a table to store users in.</description> <operations> <createTable tableName="users"> <columns> <column name="id" type="bigint" primaryKey="true" autoIncrement="true" /> <column name="email" type="text" nullable="false"/> <column name="first_name" type="text" nullable="false" /> <column name="last_name" type="text" nullable="false" /> <column name="registered_at" type="timestamp" nullable="false" defaultExpression="NOW()" /> </columns> </createTable> <createIndex tableName="users" columnNames="email" unique="true" /> </operations> </changeset> <changeset id="create_messages_table" author="Michael de Jong"> <description>Creates a table where we can store all messages between users.</description> <operations> <createTable tableName="messages"> <columns> <column name="id" type="bigint" primaryKey="true" autoIncrement="true" /> <column name="sender_id" type="bigint" nullable="false" /> <column name="receiver_id" type="bigint" nullable="false" /> <column name="body" type="text" nullable="false" /> <column name="created_at" type="timestamp" nullable="false" defaultExpression="NOW()" /> </columns> </createTable> <addColumn tableName="users"> <column name="email_on_new_message" type="boolean" nullable="false" defaultExpression="true" /> </addColumn> </operations> </changeset> </changelog>
Since the CLI only checks the file called
changelog.xml, you must define your schema
changes in this file. All other files are ignored.
For a comprehensive list of schema operations that are currently supported by QuantumDB, and how to define and use them, please visit the documentation.
Then by executing the following command in the same folder as the XML file, we can store these change in the database, and prepare to perform a schema migration.
$ quantumdb changelog ==> 4c00c31ff0 (active) - initial > Date: 2018-04-24 21:05:57.074 > Author: QuantumDB > Description: Initial state of the database. ==> fa149e6194 - create_users_table > Date: 2018-04-26 20:46:00.366 > Author: Michael de Jong > Operations: 2 > Description: Creates a table to store users in. ==> 1463e4d8ca - create_messages_table > Date: 2018-04-26 20:46:00.367 > Author: Michael de Jong > Operations: 2 > Description: Creates a table where we can store all messages between users.
Schema migrations in QuantumDB always follow the expand/contract approach. Meaning you will first instruct QuantumDB to expand your database with whatever the new schema needs (tables, etc). Then once the deployment of your application / service is a success, you'll instruct QuantumDB to contract your database, which drops everything your old schema was using and your new schema is not using.
Now that we've prepared two changesets, we can perform these schema changes in one go, by executing a
fork command. This process will create new tables where needed, copy over data, and
install database triggers to keep data in sync. You can execute such a command like this:
$ quantumdb fork fa149e6194 ==> Scanning database... ==> Forking database from: 4c00c31ff0 to: fa149e6194... ==> Database is operating at version(s): > 4c00c31ff0: Initial state of the database. > fa149e6194: Creates a table to store users in.
Once this process has completed, QuantumDB has successfully transitioned the database into a Mixed-State where both the old and the new schema exist in the database.
Please note that, although it is technically speaking possible to have more than 2 database schemas operating side by side in the same database through the forking process, this mode is not actively tested, and will be a lot more demanding on your server's hardware.
Depending on your method of deployment and its result, you can now either choose to run these two versions side by side for a while, or contract the database by dropping one of these versions. For instance if the migration process failed you can drop the newer version and try again later, or if everything went well, and you'd like to commit to the new database schema version, you can drop the old schema version. You can drop a schema version by executing the following command:
$ quantumdb drop 4c00c31ff0 ==> Scanning database... ==> Checking how many clients are still connected to: 4c00c31ff0 > 0 clients are using this version of the database schema. ==> Dropping database schema version: 4c00c31ff0... ==> Database is operating at version(s): > fa149e6194: Creates a table to store users in.
You have now successfully performed your first zero-downtime migration with QuantumDB!
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.