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:

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:

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:

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:

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!