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.
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:
- Install SQL Server, create an instance, set up JDBC driver.
- Install Liquibase.
- Take a database backup from the reference environment.
- Install Windocks.
- Create a Windocks image of the baseline database.
- Create and start a Windocks container.
- Upgrade the database with Liquibase.
My goal is to allow each developer to have its own development environment including the database.
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.
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.
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:
- Update the project from Git to the desired version.
- 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.
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.