Database development environment with Windocks and Liquibase

Database development environment with Windocks and Liquibase

small

I cannot imagine a project in a reputable company without a development environment. A place where developers could deploy unfinished code, test it, play with it or do ad-hoc manual changes without making any trouble to their colleagues. All projects have such place you could say. Not really I would respond.

 

 

Such environment should be used for a single development at each moment. It means that each developer should have its own. Usually, Java/.NET programmers can build and run the application locally on their machines but if it requires a database, they use a shared one.

semi local env

It is fine if the database structure and code do not change often but surely it sometimes does. Then the programmers collide with each other as they have a single shared database and mutually exclusive code to be deployed there.

That is the case in many projects that need a database. A solution is an entirely local environment with the application and the database set up on a developer's machine. If it is a laptop, it could be brought home and be fully ready for software development and testing even with a poor network connection.

 

Challenges of local databases

There are some reasons why the first scenario with the shared database seems to be more common among software companies.

  • Java/.NET developers do not want to bother administering their database servers,
  • when the database gets broken during the development process, the owner (and the only user) would have to fix it; when the server is shared, someone more experienced with the database would do that for all or would restore from a backup,
  • when someone else makes a change in the database code, all other developers would need to somehow apply it on their servers; usually, upgrading a database code is at least partially manual.

I think those are three main reasons why I have not noticed many local database installations on developer's laptops. Fortunately, there is a solution for all of them.

 

Solution with Windocks and Liquibase

If you use SQL Server as a database engine, the first two problems can be solved with Windocks which delivers Docker SQL Server containers. The third one can be taken care of by Liquibase. Before we start with detailed steps, let me draft the plan.

In my case, I have a Java application which I develop along with a SQL Server database. Java and database code is stored in the same Git repository. The database part is stored as Liquibase changesets. I already have a fully working Java development environment which uses a shared database for now so I will not focus on the Java part. I have some work to be done in the database piece.

These are the high level steps that I will follow to configure my local environment:

  1. Install SQL Server, create an instance, set up JDBC driver.
  2. Install Liquibase.
  3. Take a database backup from the reference environment.
  4. Install Windocks.
  5. Create a Windocks image of the baseline database.
  6. Create and start a Windocks container.
  7. Upgrade the database with Liquibase.

My goal is to allow each developer to have its own development environment including the database.

fully local env

As the plan is known, let's do it.

 

SQL Server installation and set up

I already have SQL Server installed so it is the first step. Mind the version, it should be the same as your application requires. I also have a SQL Server instance created. My application uses Windows authentication to connect to the database, so I enabled it. Do not bother creating a database, Windocks will take over the instance, which will be cloned to deliver SQL Server containers.

As I will use Liquibase as well, I need a JDBC driver on the disk which can be downloaded from Microsoft's website. I copied it to c:\\Program Files\\sqljdbc\\sqljdbc4.jar.

 

Liquibase installation

I download Liquibase. I extract it to a local directory and add the directory path to the PATH environment variable. If you need more information, check Liquibase - how to use it.

 

Create a baseline database

I need a database to start from. It should contain some data, configuration and be ready to be used by the application. If you already have a shared development database, you can take a backup of it. If you are lucky and the production database do not contain secret or PII data, it would work as well.

For the purpose of this article, I created an empty database - mydb and I took a backup of it - mydb.bak. It is empty so not ready to be used by the Java application but that is not a problem - I have a Liquibase script to upgrade it. I will come back to it later. Anyway, if you can, it is better to use a database with some data.

 

Windocks installation

If you want to test it first, download Community Edition. It has some limitations like 500 MB limit per database file and is not allowed for commercial use but it is great for testing. Once you decide to go serious with it, you can upgrade to a full version.

Before you install it, make sure you have supported OS and SQL Server versions. It requires Windows Server or Windows Pro/Enterprise Edition. A range of supported SQL Server versions is very wide - from 2008 to 2017. Installation is very simple, and the automated install completes in minutes. You just need to remember to install it from the Administrator account. More information can be found in the installation guide.

When it is installed, you may go to Services in Windows and see that SQL Server processes have been disabled - it is normal, Windocks manages the instance to create containers.

 

Windocks image creation

An image is a template that will be used to create containers. I will create an image of a baseline database environment but before I do that, let's check what images are already there. I open Windows CMD and use docker images command.

D:\>docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             VIRTUAL SIZE
dotnet-4.5          none                dotnet-4.5          4 years ago         0 B
windows             none                windows             4 years ago         0 B
mssql-2012          none                mssql-2012          4 years ago         0 B
mssql-2016          none                mssql-2016          4 years ago         0 B

There are four of them. I am especially interested in SQL Server images. There are two because my laptop has 2012 and 2016 version installed. I will need the latter.

If you have ever used docker, you are probably familiar with a concept of a dockerfile. Basically, it is a script that can be used to create an image. It may contain commands that will be executed during the image creation. I created the following one - the filename is dockerfile

FROM mssql-2016
SETUPCLONING FULL mydb d:\mydb\mydb.bak

As you can see, it is very simple. The first line instructs Windocks to use an already existing image - mssql-2016. The second one tells it to restore the baseline database backup on that image. That is it. After those two commands I should have a new image with SQL Server 2016 instance and mydb database in it.

Let's keep it rolling. I run docker build -t mydb d:\mydb on Windows CMD. It creates an image named mydb in d:\mydb directory. The same directory must contain dockerfile.

D:\>docker build -t mydb d:\mydb\
Sending build context to Docker daemon  3.24 MB
Sending build context to Docker daemon
Step 0 : FROM mssql-2016
Step 1 : SETUPCLONING FULL mydb d:\mydb\mydb.bak
ContainerId = 0f80dfb493ea40311aeb81b9f4d7fd31b11f0c42362497c0e180b95938e98245 & ContainerPort = 10001 & ContainerUserName = prison_oo_m4gZg1Y & MSSQLPort = 10001 & MSSQLServerUserName = sa & MSSQLServerSaPassword = Pr!530Ohc16N1v & DockerFile output: & Image mydb created. Please run docker create  mydb OR docker run -d mydb to get fresh containers from the image you just created

The above output states that the image was created and provides a series of useful information.

I check the available images once again.

D:\>docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             VIRTUAL SIZE
mydb                none                2ee8a538-a06        19 seconds ago      0 B
dotnet-4.5          none                dotnet-4.5          4 years ago         0 B
windows             none                windows             4 years ago         0 B
mssql-2012          none                mssql-2012          4 years ago         0 B
mssql-2016          none                mssql-2016          4 years ago         0 B

There are five not four images now. The new one is mydb. That is what I wanted. Now, I have an image of a SQL Server instance with a baseline database.

 

Windocks container creation

A container is a product of executing an image. In this case, it should act like a SQL Server instance with a database so I should be able to connect to it, run SQL queries and connect my Java application to it.

Expectations are already high so I create a container and start it by executing docker run -d mydb command. mydb is the image name in this context.

D:\mydb>docker run -d mydb
ContainerId = cff00aa8d7bd4e05ec937aaead60c63891a1297479e52020ac7c507ad4d656f7 & ContainerPort = 10001 & ContainerUserName = prison_oo_08fzwo6 & MSSQLPort = 10001 & MSSQLServerUserName = sa & MSSQLServerSaPassword = Pr!5XbjaZddAn7 & clones  =  mydb^\\DBAPRESENTS\0d0581da-78a0-41ce-a3f3-c1686b109269mydb\mydbmydb.mdf^\\DBAPRESENTS\0d0581da-78a0-41ce-a3f3-c1686b109269mydb\mydbmydb_log.ldf

It seems like the container was created. I verify its status with docker ps command. It lists all containers.

D:\mydb>docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES
cff00aa8d7bd        mydb                ""                  3 minutes ago       Started             10001/              dreamy_goldstine/Windocks-id:0d0581da-78a0-41ce-a3f3-c1686b109269

It says that my container is started and available on port 10001.

 

Database upgrade

If you used a shared development database as a baseline, you can already connect the application to this database and use it. As I created an empty database, I have to do one more step - upgrade it to a proper version.

I clone a Git repository with the source code of my database so there is d:\Git\mydb\db-change-log-master.xml on my disk. The XML file contains Liquibase changesets that defines the structure of my database. Here is the content.

<databaseChangeLog
       
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
       
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"
>

    <
changeSet id="Create USERS table" author="DBA presents">
        <
createTable tableName="USERS">
            <
column name="Usr_Id" type="int" autoIncrement="true">
                <
constraints nullable="false" primaryKey="true" primaryKeyName="PK_Users"/>
            </
column>
            <
column name="Usr_Email" type="varchar(255)"/>
            <
column name="Usr_Password" type="varchar(255)"/>
        </
createTable>
    </
changeSet>

</
databaseChangeLog>

For simplicity purposes, it contains only one changeset with a table creation. Now, I upgrade the database to the version of the XML file.

D:\mydb>liquibase.bat --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\\Program Files\\sqljdbc\\sqljdbc4.jar" --url="jdbc:sqlserver://localhost:10001;databaseName=mydb;integratedSecurity=true" --changeLogFile="d:\Git\mydb\db-change-log-master.xml" update
Starting Liquibase at Mon, 22 Apr 2019 20:24:29 CEST (version 3.6.3 built at 2019-01-29 11:34:48)
Liquibase: Update has been successful.

Notice, that I provided the port number (10001) returned by docker ps.

I connect to the database with SQL Server Management Studio.

upgraded1

The whole operation was successful. The database exists, I can connect to it and it was upgraded by USERS table creation. That is pretty much all as for creating a truly local database environment. Although there are a few steps, they have to be performed only once. See belolw what happens during common development scenarios.

 

Scenario 1 - incoming changes to the database

While I am working with this local database, another developer made a change in the code repository that modifies the database. I am developing a new feature so I want to work with the latest code version. I need to take two simple actions. Obviously, I update the project from the Git repository. Afterwards, db-change-log-master.xml has the following content.

<databaseChangeLog
       
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
       
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"
>

    <
changeSet id="Create USERS table" author="DBA presents">
        <
createTable tableName="USERS">
            <
column name="Usr_Id" type="int" autoIncrement="true">
                <
constraints nullable="false" primaryKey="true" primaryKeyName="PK_Users"/>
            </
column>
            <
column name="Usr_Email" type="varchar(255)"/>
            <
column name="Usr_Password" type="varchar(255)"/>
        </
createTable>
    </
changeSet>

    <
changeSet id="Create TRANSACTIONS table" author="DBA presents">
        <
createTable tableName="TRANSACTIONS">
            <
column name="Trn_Id" type="int" autoIncrement="true">
                <
constraints nullable="false" primaryKey="true" primaryKeyName="PK_Transactions"/>
            </
column>
            <
column name="Trn_Usr_Id" type="int"/>
            <
column name="Trn_Type" type="char(1)"/>
            <
column name="Trn_Amount" type="money"/>
        </
createTable>
    </
changeSet>

</
databaseChangeLog>

A changeset with TRANSACTIONS table creation was added by the other developer. The second action is to upgrade the database with the very same Liquibase command as before.

D:\mydb>liquibase.bat --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\\Program Files\\sqljdbc\\sqljdbc4.jar" --url="jdbc:sqlserver://localhost:10001;databaseName=mydb;integratedSecurity=true" --changeLogFile="d:\Git\mydb\db-change-log-master.xml" update

I go back to SQL Server Management Studio to confirm the TRANSACTIONS table was created.

upgraded2

Yes, it is there. Now, I have my local database updated with a change made by someone other than me. Yey.

 

Scenario 2 - downgrading to an old version

Developers do not always work with the latest code. It often happens that some changes have to be made in an old code. For example bug fixing. That is not a problem either. This is what I have Windocks for. First, I update the project from Git to the desired checkin. Let's assume that after the update, db-change-log-master.xml has only one changeset again - creation of USERS table. As the container contains a database that is upgraded too far (two changesets), I remove the container by checking the container id with docker ps and removing it with docker rm.

D:\mydb>docker rm cff00aa8d7bd
cff00aa8d7bd

Then I create a container from the image.

docker run -d mydb

And upgrade to the desired version.

liquibase.bat --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\\Program Files\\sqljdbc\\sqljdbc4.jar" --url="jdbc:sqlserver://localhost:10001;databaseName=mydb;integratedSecurity=true" --changeLogFile="d:\Git\mydb\db-change-log-master.xml" update

By following these simple steps, I can create a database compatible with any version I want.

 

Scenario 3 - having two databases in different versions at the same time

Let's assume I have a container with the latest database version available on port 10001. I need it as I am working on a new feature but I need to quickly veryfy something in an old version. As I do not want to lose uncommitted changes, I quickly create a separate container with the old version. I follow these steps:

  1. Update the project from Git to the desired version.
  2. Create a new container from the image
    D:\mydb>docker run -d mydb
    ContainerId = 962ec78d4a2b2d5da9185c81f4ebc0f9e2e88ef4282550bf9575dfcae9ab47d8 & ContainerPort = 10002 & ContainerUserName = prison_oo_Xy7ezn0 & MSSQLPort = 10002 & MSSQLServerUserName = sa & MSSQLServerSaPassword = Pr!5uo6pQPk8pf & clones  =  mydb^\\DBAPRESENTS\5884efbd-62f0-45b5-8b7f-0ef0297d97c2mydb\mydbmydb.mdf^\\DBAPRESENTS\5884efbd-62f0-45b5-8b7f-0ef0297d97c2mydb\mydbmydb_log.ldf
    Notice that the new database is available on 10002 port.

That is all. I can connect to the instance and verify that the database is accessible.

upgraded3

 

Summary

The combination of Windocks and Liquibase allowed me to set up a local database environment which is easily upgradable. Windocks made resetting and cloning a database very easy. I encourage you to try them yourself on your project.

If you like what I do, consider buying me a coffee :)

Buy me a coffeeBuy me a coffee