I can tell you from personal experience that there’s nothing more frustrating than trying to figure out why your application stopped working, only to find out it is because of one small database change by your colleague. We kept running into these issues, causing us to back-track on everything we were doing; costing us precious time and money. That is why we currently utilize database versioning on every project.
In this blog, we'll explain database versioning and its benefits. We also provide our solution and describe basic principles on how it works, so you can try it yourself. In part 2 of this blog, we'll dive deeper into the logic and give you detailed instructions on how to integrate CloverDX database versioning into your project.
Database versioning allows you to track any database changes. It is no different than version controlling of source code in application development. Each new commit creates a new checkpoint and it records changes made to a database. All user defined database objects and data within those objects can be version controlled (and should be version controlled).
Versioning your database becomes extremely useful on projects that utilize agile methodologies and when there are multiple developers working within the same database. Among the biggest benefits of database versioning is that you are limiting your own risk and containing potential disasters.It is much easier to manage multiple changes in parallel to the existing database structure because there’s already a process in place to handle this scenario. Database versioning should become a part of your data governance framework.
The solution we provide is universal, so it can be deployed in any existing customer environment. At the end of the day, our solution saved us lot of headaches, time and money. If that’s not a win-win, I don’t know what is! And we hope it will be the same for you.
When a database change is needed, you won't do it directly, but you will prepare an SQL update script, and store it in the dedicated folder in CloverDX. To update database with the new script you will run jobflow, which will first check the existing database version, then run all update scripts that have not been applied on the database yet.
Here you can download our database versioning project for CloverDX. Let’s take a brief look at the key elements in the solution. I am not getting into exhausting details for now as I am working on detailed guide, that will help you implement your own database versioning. This guide will be available on our blog in near future.
All important necessary scripts for solution are in the folder DatabaseVersioning/setup and jobflows are stored in the folder DatabaseVersioning/jobflow. The "setup" folder contains two sample SQL scripts. "CreateSqlDatabase.sql" is for creating database and "InsertTestData.sql" for loading data into it. Then there is a subdirectory DatabaseVersioning/setup/UpdateScripts for storing the update scripts for the database, we include two examples as well. Jobflows, that utilize the scripts are in "jobflow" folder. The first workflow "InitDatabase.grf" creates the database by executing SQL scripts. The second "UpdateScripts.jbf" is designed to run any update scripts that have not been applied to the database yet. Connection to database is configured in "connection.cfg" in folder DatabaseVersioning/conn.
First you need to define your database in a "CreateSqlDatabase.sql" script. The very important part is on line 12 - CREATE TABLE. This version table the key to the entire solution. In this table you keep version of your database, so CloverDX can check it against update scripts and update the database to latest version. The second script "InsertTestData.sql" contains all the data that will be inserted into your database. After you had prepared those two scripts, run jobflow "InitDatabase.grf", which will create database and load data into it.
Every one of those steps is important in managing your database version. You need to make sure that all databases are on the same version (new and old). My philosophy is to let the database manage itself rather than allowing for human error.
I hope you have learned a little bit about how and why you should manage your database and how to leverage CloverDX to do this for you. We had used this approach on more than one occasion, so why not follow our lead? I know it’s not the ‘cool’ thing to do when developing an application, but it is absolutely essential for your team. With the database version controlled, developers, stakeholders, and project managers are on the same page throughout the entirety of the project.
In the following part we will provide detailed guide how to implement database versioning for your environment. We will demonstrate how to prepare custom SQL scripts, run the database initialization graph or update script jobflow, how to pre-configure your database server and much more.
Subscribe to our blog or follow us on social media to find out what comes next.