Applications with Mojolicious – Part Six: Schema Versioning with DBIx::Class::Migration

In this post, we start versioning our database for upcoming changes in the requirements of our app. To avoid replacing the schema and data alltogether, we'll employ DBIx::Class::Migration to create seamless migration paths for the database.

Mojolicious Series

Migrations

Requirements over applications change over time, thus their data and databases need to adapt. Changing the database by hand is a pain, so instead we need a reliable tool providing an upgrade path for production databases with data.

For the DBIx::Class ecosystem, there is the outstanding DBIx::Class::Migration.

Moving Moblo to DBIx::Class::Migration

As we already use the DBIx::Class schema format, we won't have much effort to prepare the first migration. In the schema modules, we only need to add the current database version (which is 1):

lib/Moblo/Schema.pm

1
2
3
4
5
6
7
8
9
10
11
12
13

use utf8;
package Moblo::Schema;

use strict;
use warnings;

use base 'DBIx::Class::Schema';

our $VERSION = 1;

__PACKAGE__->load_namespaces;

1;

Creating the Migration

After installing DBIx::Class::Migration, a command-line tool named dbic-migration is available. We can use that to display the curent status of the schema and database. In the root directory of Moblo, create a directory called share (which the default migration storage directory for dbic-migration) and execute the following command:

1
2
3

$> dbic-migration -S 'Moblo::Schema' -Ilib --database SQLite status
 Schema is 1
 Database is not currently installed

It reads directly from our Moblo::Schema module and reports that no versioned SQLite database was found. We can prepare a migration for our initial version with the following command:

1
2
3

$> dbic-migration -S 'Moblo::Schema' -Ilib --database SQLite prepare
 There is no current database deployed, so I can't prepare upgrades
 or downgrades

This output means dbic-migration wanted to generate up- and downgrades automatically for the new schema version, but it can only do that when a database is deployed in the current version. Again, for the first version, this is just fine.

This command has generated several files under share/ for this version:

1
2
3
4
5
6
7
8
9
10
11
12

├─ fixtures                           # Fixture directory, not yet relevant
│  └── ...
├─ migrations
│  ├── SQLite
│  │   └─ deploy
│  │     └─ 1
│  │        ├─ 001-auto-__VERSION.sql # Internal versioning metatable.
│  │        └─ 001-auto.sql           # Generated DDL that is used to deploy to SQLite
│  │
│  └── _source                        # Internal representation of this version, ignore
│      └─ ...
└─ moblo-schema.db                    # SQLite database (empty, so far)

The file of particular interest is migrations/SQLite/deploy/1/001-auto.sql. It is the SQL version of our schema as created by SQL::Translator. Review the generated SQL and adapt to your database if necessary (e.g., performance optimizations or other syntax specific features).

1
2
3
4
5
6
7
8
9
10
11
12

BEGIN TRANSACTION;
--
-- Table: posts
--
CREATE TABLE posts (
  id INTEGER PRIMARY KEY NOT NULL,
  author text NOT NULL,
  title text NOT NULL,
  content text NOT NULL,
  date_published datetime NOT NULL
);
COMMIT;

This file is generated for each database you enable in dbic-migration. If you want to create migrations for SQLite and PostreSQL, you simply run the following command (you may need to add --force_overwrite if the share directory is not empty).

1

$> dbic-migration -S 'Moblo::Schema' -Ilib --database SQLite --database PostgreSQL prepare

Deploying to Database

The last step is to deploy the migration to database. Again, for the first migration, this is a no-brainer.

1
2
3

$> dbic-migration -S 'Moblo::Schema' -Ilib --database SQLite install
 Since this database is not versioned, we will assume version 1
 Reading configurations from share/fixtures/1/conf

The remaining step is to copy data from your previous database. In a production database, I would suggest to manually select, revise and insert the data. Just for fun in this series, you can copy the data in your SQLite database using ATTACH:

1
2
3
4

$> sqlite3 share/moblo-schema.db

sqlite> ATTACH "/path/to/moblo.db" AS OLD;
sqlite> INSERT INTO posts(author, title, content, date_published) SELECT author, title, content, date_published FROM OLD.posts;

Last but not least, update the reference to your database in Moblo.pm to share/moblo-schema.db.

In the next post, we'll create relationships with DBIx::Class and actually get to use the database versioning feature of this post.

Additional Documentation

Code at GitHub

You can browse the app at the current state as well as its history on Github.