What is Database DevOps ?
Database DevOps, as we see at the Lean Apps, is continuous integration and continuous delivery of database schema changes, along with the code. Continuous Integration and delivery needs database version control and deployment rollback. But, most of the software development companies are not doing version controlling their database, neither do they have automated database changes. Database being an integral part of any software application, DevOps without databases is incomplete.

Need for Database DevOps
Other than to complete the DevOps process, Database DevOps is the need of the hour for the following reasons :

(a) Supporting multiple developers: In increasing numbers of teams and third-party, modules is a challenge. Database configuration drifts are a constant risk due to multiple contributors to the change process, with the quality becoming increasingly unpredictable. Audit trails in these circumstances are often insufficient for regulatory compliance.

(b) Merging code for deployment means handling dependencies between the different teams, while maintaining enough flexibility to respond to changing deployment plans.

(c) Dealing with developers and production personnel, as well as acting as arbiter between them at times, is very taxing. Each role requires a different mindset.

(d) With Manual database work required during development and deployment, human error is all but inevitable. And a mistake in the database usually results in cross-application errors, accidental overwrites or downtime in the entire system.

These challenges only become more acute with the current demand for ever-faster time-to-market and consistent customer satisfaction. For strategic business reasons, companies sometimes want multiple features under development at the same time.

With all of the development teams making changes to the same database objects, there are ultimately a lot of coupling problems in deployment. This can get even more disruptive, and costly, when a feature has to be rolled back at or after deployment. Add to this the general customer expectation of minimal-to-zero downtime

Liquibase
Liquibase is an open source product for database version control. It is an open source database-independent library for tracking, managing and applying database schema changes. It was started in 2006 to allow easier tracking of database changes, especially in an agile software development environment.

All changes to the database are stored in text files (XML, YAML, JSON or SQL) and identified by a combination of an “id” and “author” tag as well as the name of the file itself. A list of all applied changes is stored in each database which is consulted on all database updates to determine what new changes need to be applied. As a result, there is no database version number but this approach allows it to work in environments with multiple developers and code branches.

Liquibase supports all major relational database management systems and converts the changes written by us into native DB’s syntax and executes them. Liquibase automatically creates DatabaseChangeLog Table and DatabaseChangeLogLock Table when you first execute a changeLog File, which have the information regarding database version control.

Configuring Liquibase

(a) For new projects: For new projects, the developers only need to create the database and start writing changesets and evaluate them, either through their terminal, or through their CI/CD pipeline.

(b) For ongoing projects: For ongoing projects, there is an additional step required before the developers begin to write changesets. It is to import existing schema changes from the database into a changelog file. Liquibase facilitates this through the generateChangeLog command. In the terminal, enter the following command after downloading the liquibase jar file and the database connector jar file, and filling the appropriate placeholders.

java -jar liquibase.jar –driver=com.mysql.jdbc.Driver
–classpath=mysqlconnector/mysql-connector-java-5.0.8/mysql-connector
-java-5.0.8-bin.jar –changeLogFile=changelogfile.xml
–url=”jdbc:mysql://DATABASE_URL:3306/DATABASE_NAME”
–username=DATABSE_USERNAME –password=DATABASE_PASSWORD
generateChangeLog

When you run this command, it creates a changelogfile.xml file in the current directory and imports all the existing database schema into it in the form of changesets. After this command, you need to synchronise those changesets you have imported. For that, you run the following command.

java -jar liquibase.jar –driver=com.mysql.jdbc.Driver
–classpath=mysqlconnector/mysql-connector-java-5.0.8/mysql-connector
-java-5.0.8-bin.jar –changeLogFile=changelogfile.xml
–url=”jdbc:mysql://DATABASE_URL:3306/DATABASE_NAME”
–username=DATABSE_USERNAME –password=DATABASE_PASSWORD
changeLogSync

Writing Changesets
These are atomic changes that would be applied to the database. Each changeset is uniquely identified by id, author and package of the file where changeset is defined. You may want to do as little as one change in a changeset or multiple changes. Each changeset is run as a single transaction by Liquibase.

An example of changeset may look like

<changeSet id=”01″ author=”yash”>
<createTable tableName=”books”
                   remarks=”A table to contain all books”>
                  <column name=”id” type=”int”>
                             <constraints nullable=”false” unique=”true”
                             primaryKey=”true”/>
                 </column>
                <column name=”name” type=”varchar(255)”>
                            <constraints nullable=”false”
                            unique=”true”/>
                </column>
               <column name=”author” type=”int”>
                           <constraints nullable=”false”/>
              </column>
</createTable>
</changeSet>

Evaluating Changesets through terminal in Linux

To evaluate changesets means to execute the database changes. To do that, we just need to run the following command in the terminal:

java -jar liquibase.jar –driver=com.mysql.jdbc.Driver
–classpath=mysqlconnector/mysql-connector-java-5.0.8/mysql-connector
-java-5.0.8-bin.jar –changeLogFile=changelogfile.xml
–url=”jdbc:mysql://DATABASE_URL:3306/DATABASE_NAME”
–username=DATABSE_USERNAME –password=DATABASE_PASSWORD update

Evaluating Changesets in Jenkins as a part of CI/CD Pipeline
To execute Liquibase Changesets as a part of the CI/CD Pipeline in Jenkins, follow these steps :
1. Install the Liquibase Plugin through Jenkins Plugin Manager.
2. After the Build Step where the application is build, Add another Build Step and from the options choose “Evaluate liquibase changesets” and fill the input fields with appropriate details.