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.
- Part 01 - Introduction
- Part 02 - Routing and Rendering
- Part 03 - Forms, Logins
- Part 04 - Database schemas with DBIx::Class
- Part 05 - DBIx::Class Integration in Mojolicious
- Part 06 - Schema Versioning with DBIx::Class::Migration
- Part 07 - Relationships with DBIx::Class
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):
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
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
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
If you want to create migrations for SQLite and PostreSQL, you simply
run the following command (you may need to add
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
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
In the next post, we'll create relationships with DBIx::Class and actually get to use the database versioning feature of this post.
Code at GitHub
You can browse the app at the current state as well as its history on Github.