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.
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:
An alternative approach is just cloning from Github.
You can start configuring the options for DBV by creating a copy of the
config.php.sample file and renaming it to
The most important things to update here are the first two sections. Just substitute the values for
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:
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
- stored procedures
- functions (user-defined functions)
You can fire up dbv from your browser by accessing the following URL:
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:
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.
Next tell your team mate to visit the dbv page (
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.
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
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:
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 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!
SitePoint WordPress Restaurant Theme
SitePoint WordPress Ecommerce Theme
SitePoint WordPress Portfolio Theme
Zend Framework 2: The Basics
Local Development Environments for Designers and Developers
Jump Start PHP Environment
Jump Start MySQL
Jump Start PHP
Jump Start PHP
- 1 Versioning Show, Episode 0, with M. David Green and Tim Evko
- 2 Versioning Show, Episode 1, with Craig Buckler
- 3 CSS Animation, and Creating Art with Code, with Rachel Smith
- 4 How to Properly Deploy Web Apps via SFTP with Git
- 5 Tech Stacks, Frameworks, Being Creative, and Being Real, with Tim Holman