Managing database schemas across multiple environments can be a chore if you don’t have a schema management tool (sometimes referred to as database migrations, but not to be confused with data migrations) in place.
I first became familiar with the notion of database migrations with Ruby on Rails. Rails Active Record uses its own domain-specific-language (DSL) so you don’t have to write SQL by hand. A typical piece of migration code looked like this:
class CreateProducts < ActiveRecord::Migration[5.0] def change create_table :products do |t| t.string :name t.text :description t.timestamps end end end
The code above would create a products table with an autonumbered ID column, name, text and autopopulated timestamps for update and creation time. The equivalent SQL in Postgresql for the above would be:
create table products ( id bigserial, name varchar(255), description text, created_at timestamp, updated_at timestamp )
So why not just use SQL instead of a DSL? That’s a good question. In the particular case of Rails, ActiveRecord is database platform agnostic. It will create the table using the specific datatypes used by the particular database you’re using. Raw SQL doesn’t offer you this advantage, because there are always some datatypes that are specific to particular vendor.
Having said that, for any given project, you’re using one particular database platform anyways. Unless you’re making a cross-platform product, the need for a database agnostic migration tool probably isn’t a deal breaker.
Since Rails database migrations are really for Rails applications, it obviously doesn’t make sense to use Rails database migrations for non-Rails applications.
Why Use A Database Migration Tool?
Database migration tools let you version your database easily. Ideally, your changes are coded into text files like SQL files, so that they work well with commonly used version control systems like Git. Every change you apply to the database is essentially a “version”.
With most migration tools, every time you apply a change to a database by running the migration, a special table in that database is updated so that version state is tracked. This way, the next time the migration tool is run, it will skip over any changes that were applied to the database already.
Promotion Across Environments
Because each database has its own version tracking, deployment across multiple environments (i.e., development, testing, and production) is a snap.
For example, let’s say your development database is on version 5, test database is on version 3 and production is on version 1.
Now that your test environment has been validated by QA, you want to promote production to version 3.
Assuming you tagged version 3 in your source control system, all you need to do is a pull of version 3 onto your production system and then run the migration.
When you run the migration for version 3 on production, the changes for version 2 and version 3 are applied to production to bring it from version 1 to 3. Once the promotion is complete, the local database version is updated to note what changes were made to the database.
A Note on Forward Only Migrations
Most migration tools have a mechanism to allow you to roll-back database changes to a previous version, but rolling-back can have negative side effects.
Stack Overflow’s Nick Craver has an excellent explanation for the rationale for forward-only database migrations in a blog post he wrote explaining how Stack Overflow handled deployments.
Generally speaking, it’s easier to fix a problem with a new migration than it is to roll-back and reapply a fixed migration.
While there are database migration libraries for every major coding platform, the first big standalone migrations tool I used was Liquibase. Liquibase is a freemium tool built on Java. It’s fairly straightforward if you’re technically minded, and it’s powerful. It also gives you the flexibility of defining your database changes in XML, YAML, JSON or SQL. And, it gives you the added benefit of reverse engineering a database. It’s an excellent tool, but there’s a little bit of a learning curve with it.
Flyway is also a Java based freemium tool. The thing I like about Flyway is that it’s easy to install, and easy to pick up. There’s less lingo to learn compared to Liquibase, and if you were writing SQL scripts for table creation, there’s not a lot you have to do to make it work with Flyway.
To get started with Flyway, you basically just make sure you have Java 6+ installed, install the Flyway command line app, make sure your table creation SQL files are named properly, and run the migration tool. It’s really that easy. For most common databases, you don’t even need to install drivers or know where they are located.
I have been building a demo data warehouse for Extrata and using SQL files to create and manage the schema. To get those SQL files to work with Flyway, all I had to do was rename and order my files using Flyway’s naming convention, and I was done in a matter of minutes.
I highly recommend that you give Flyway a shot, especially if you are not using a tool to handle the versioning of your database schemas. It’s really easy to integrate into your toolchain, and it’s a piece of cake to use.
Be sure to subscribe to our RSS feed, as I plan to do more in-depth writeups on how to use Flyway.
Other articles by this author:
- Set up your own Business Intelligence/Data Warehousing/Reporting solution on the cheap
- Coming Soon: Action Mailbox in Rails 6
- Get To Know Extrata, Episode 2: Database Writeback
- Make Your Life Easier With This Software On Your Windows Cognos Server
- Quick Hit: Chef Extends Open Source Licensing to All Its Software