Database Versioning with DBV

It’s good practice to always use a version control system in any of your projects. Be it a side-project in which you are the only developer, or a team project where five or more people are working on it together. But the idea of putting your database into version control isn’t really that widespread. Often times we take the database for granted.

But like the source files in our project, the database is constantly changing too. That’s why we also need a way to track the changes that we have made and easily share it to other members of our team.

In this article we will take a look at DBV, a database version control system written in PHP for MySQL databases so you need to have PHP and MySQL installed before you can use it, along with a web server like Apache or Nginx.

An important note about this software is that it is not a stand-alone database version control system, because it needs a version control system such as Git, Mercurial or SVN for syncing changes with your team.

Installing DBV

To start working with DBV, first you have to download the installer from their website, extract it into your project directory then rename the resulting folder to dbv. This will give you the following path:

my_project/dbv

An alternative approach is just cloning from Github.

DBV Configuration

You can start configuring the options for DBV by creating a copy of the config.php.sample file and renaming it to config.php.

The most important things to update here are the first two sections. Just substitute the values for my_username, my_password, my_database for the values in your current database configuration:

<?php
/**
 * Your database authentication information goes here
 * @see http://dbv.vizuina.com/documentation/
 */
define('DB_HOST', 'localhost');
define('DB_PORT', 3306);
define('DB_USERNAME', 'my_username');
define('DB_PASSWORD', 'my_password');
define('DB_NAME', 'my_database');

/**
 * Authentication data for access to DBV itself
 * If you leave any of the two constants below blank, authentication will be disabled
 * @see http://dbv.vizuina.com/documentation/#optional-settings
 */
define('DBV_USERNAME', 'my_username');
define('DBV_PASSWORD', 'my_password');
?>

The first section in the configuration file above is all about the MySQL database details in your machine.

The second section are the login details for dbv itself.

Next open up the .gitignore file. By default it contains the following:

config.php
.buildpath
.project
.settings

These are the files that will be ignored by Git. But if you know that you have the same database information (user, password, database name) as your team mates then you can remove config.php from the .gitignore file. If not then they will have to create their own configuration file and exclude it from source control.

Next you also have to add the data/meta/revision file to .gitignore as that is where dbv puts information about your local copy of the database. It might be different for your team mates so it needs to be excluded from source control.

Once you’re done with the configuration you can now add dbv into version control:

git add dbv
git commit -m "add dbv into project"

And then push it to your central repository for the other members of your team to pull:

git push

What Changes To Keep Track Of?

Before we move on into actually using dbv. I’d like to touch a bit on which changes to keep track of. In the database world pretty much any change can be put into source control. This includes the following:

  • new tables
  • renamed tables
  • dropped tables
  • new fields
  • updated field
  • deleted field
  • new table row (default table data)
  • updated table row
  • deleted table row
  • views
  • stored procedures
  • triggers
  • functions (user-defined functions)

DBV Workflow

You can fire up dbv from your browser by accessing the following URL:

http://localhost/your_project/dbv

Or if you defined a virtual host, by accessing its URL.

This will give you an interface similar to the following:

dbv

From the screenshot above you will see the tables that are currently in the database that you supplied in the config.php earlier. There’s also an In DB field that displays whether a specific table is currently in the database and the On disk which displays whether the current table is saved in your filesystem. With this information you’re pretty much aware whether you currently have the latest copy of the database.

An important thing to remember when working with dbv is that any changes you make to your local copy of your database should have a local copy that you can commit into your source control.

That means that if you create a new table in the database you have to export it to disk. All the tables that are exported to disk are saved in the data/schema directory of your dbv installation. You can see from the screenshot below that we currently do not have the tbl_leadinfo table in our filesystem:

disk copy

After exporting the newly created table to disk you have to commit it into version control:

git add data/schema/tbl_tasks.sql
git commit -m "add tbl_task table"

Then you can just push it to your central repository:

git push

At this point you can just tell to your team mates that you have created a new table in the database and that you have already pushed it to the central repo. Now they can just pull it down to their local copy.

git pull

Next tell your team mate to visit the dbv page (http://localhost/your_project/dbv).
Your team mate will probably have a screen similar to the following:

dbv team mate

At this point he can just tick the checkbox next to the tbl_tasks table and click on the ‘Push to database’ button. This will create the tbl_tasks table in the database.

And that’s the workflow for working with dbv. Pretty easy, isn’t it? But what if you need to make changes to the current database schema? Maybe you missed a field or forgot to add it on a specific table before you distributed it to your team. That’s where revisions come in.

Revisions

If you’re like me and you tried updating the schema (added a new field, remove a field, updated the data type, etc.) for a specific table you might have noticed that dbv isn’t aware of it by default. For those changes you need to create a revision file. You can do that by creating a new folder in the data/revisions directory in your dbv installation. The convention for naming the folder is making use of a number. So the first time you make a revision the folder name would be 1 and then the next time it would be 2 and so on. Note that revisions are changes that can be applied to the whole database. This means that you have the freedom to modify the structure of more than one table, but its good practice to only make changes to a single table and make a revision for it. This is for you and your team to easily manage the changes and make sense of it later on. The only exception to this practice is when the changes are related to each other. In that case it would make sense to put those changes together in one revision.

Let’s try creating a new field inside the tbl_users table and name it email:

ALTER TABLE `tbl_users`
ADD `email` varchar(160) COLLATE 'latin1_swedish_ci' NOT NULL

Next create a new file in the data/revisions/1 directory in your dbv installation and put the query that you just executed as the contents. Name the file tbl_users.sql. The convention here is using the name of the modified table as the name for the revision file.
If you are making modifications to more than one table then create a separate file for each table.

After that you can commit the new file into your source control:

git add data/revisions/1/tbl_users.sql
git commit -m "add email field to tbl_users"

And then push it to your central repository:

git push

Again you can inform your team mates about the particular change. Communication is key when making changes to the database. You want to make sure that everyone on your team is on the same page as you.

Now if they access dbv from the browser they can now see the revision. All they have to do now is to tick the checkbox beside the revision and then click on the ‘Run selected revisions’ button. This will commit your changes to their local database copy:

dbv revisions

Conclusion

DBV is a nice way to easily manage your database version control needs. It lets you and your team easily keep track of the changes made in your database. It also allows for easy sharing of your changes with the rest of your team through the use of Git. This ensures that everyone always has the latest copy of the database.

In this article we have looked at using DBV with Git but you can pretty much use any version control system of your choice. Feedback? Please leave it in the comments below!

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Taylor Ren

    It is a handy tool. Next step is to make the “delta” generation automatically. DB admin somehow shall be centralized so it is not a mission impossible.

  • Moist Von Lipwig

    Seems like quite a lot of trouble to do something so simple. There is a great db admin tool called SQLyog which we use. It has tool which creates a diff between 2 databases and the SQL needed to execute it. So before each release we run a diff between the live and development databases. The generated sql is then comitted with the release notes. always been a good way of working for us.

    • http://www.bitfalls.com/ Bruno Skvorc

      Nice, cheers. If someone on your team would like to do a workflow writeup, let me know.

    • mogosselin

      It depends on the kind of projects. If it’s a product anybody can install, this might not work. Also, if you need to “diff” data, it’s possible that it won’t work either depending on the solution (ex: big ecommerce site with transactions each minute). But that’s true that if the “compare” solution works for you, it’s an easy way to manage that kind of changes.

  • Martin

    Migrations would be a better tool for this.

    Most popular PHP ORMs I know support migrations out-of-the-box, it would easily help you version-control your schema changes and synchronize those changes throughout the entire development team.

    • Sviatoslav Danylenko

      +1
      dbv looks for me like standalone migration tool (without version rollback) with web interface… and if it can’t be run in console (I haven’t seen any info about console mode on the dbv site), than it is not suitable for automating production deploy

  • Steven Orr

    I thought I would give this a go but your example, and the one on the owner’s website, only show DBV already loaded with a schema. When I start I get “no schema objects” and “no revisions” found messages. It would be nice to be shown what the real first steps will be to get it going. (I type this only after 15 minutes and am still fumbling around.) Consequently, I don’t know yet if this is for our team.

  • JohnNiice

    Tracking schema changes is one thing. However, I would rather have a central database that the whole team develops against. Most of the time there are issues it is with specific data and not the structure. If each member has their own copy with its own data it is difficult to reproduce some problems.