Support environment for Liquibase & MSSQL using Docker

Goal

To run liquibase against Postgres quickly on your local system to reproduce and work support issues.

Methodology

Will be using Docker to create an mssql container. Install the version of liquibase you want to use (switch to the correct version if you have a side by side setup of multiple liquibase versions). Git clone lb_support projects and docker run to run a docker registered sqlserver image.

 

Install liquibase

…. on your local system.

You can download the software here. I will be using side x side installs on a mac. So I downloaded the last few versions:

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Ronaks-MacBook-Pro:opt root# pwd /usr/local/opt Ronaks-MacBook-Pro:opt root# mkdir liquibase-3.10.1 Ronaks-MacBook-Pro:opt root# tar -xzvf liquibase-3.10.1.tar.gz -C liquibase-3.10.1 ... drwxr-xr-x 9 root wheel 288B Jul 9 11:17 ./ drwxr-xr-x 6 root wheel 192B Jul 8 10:21 ../ lrwxr-xr-x 1 root wheel 16B Jul 9 11:17 liquibase@ -> liquibase-3.10.1 -rw-r--r--@ 1 ronak staff 7.0M Jun 29 17:08 liquibase-3.10.0.tar.gz drwxr-xr-x 14 root wheel 448B Jul 9 11:16 liquibase-3.10.1/ -rw-rw-rw-@ 1 ronak staff 7.1M Jul 8 14:44 liquibase-3.10.1.tar.gz -rw-rw-rw-@ 1 ronak staff 7.0M Jul 9 11:07 liquibase-3.8.9.tar.gz -rw-rw-rw-@ 1 ronak staff 7.0M Jul 9 11:06 liquibase-3.9.0.tar.gz -rw-rw-rw-@ 1 ronak staff 6.4M Jul 9 11:02 liquibase-4.0.0-beta2.tar.gz Ronaks-MacBook-Pro:opt root# which liquibase /usr/local/opt/liquibase/liquibase Ronaks-MacBook-Pro:opt root# liquibase --version Starting Liquibase at Thu, 09 Jul 2020 11:17:42 CDT (version 3.10.1 #17 built at Wed Jul 01 06:58:05 UTC 2020) Liquibase Version: 3.10.1 Liquibase Community 3.10.1 by Datical Running Java under /Library/Java/JavaVirtualMachines/jdk1.8.0_251.jdk/Contents/Home/jre (Version 1.8.0_251)

Also note I downloaded the tar.gz not the dmg. I had some issues with that when switching versions so I just went for more control using tar.gz. and symlinks.

Running MSSQL via Docker.

I used these instructions from Docker Hub. The instructions are not version specific, I give an example below of provisioning out a 12.1.0.2 Oracle DB server container

  1. Run: docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrongPassword1$' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-CU8-ubuntu

  2. Note, this will run with the tag 2017-CU8-ubuntu.

Connect to Database

You can use sqlplus or a SQL Browser. I used DBeaver, and here was my connection config:

DBeaver

 

The password will be what you specified in the -e 'SA_PASSWORD=yourStrongPassword1$' on docker image start in the previous section. From the above example it would be: yourStrongPassword1$

Prepare sqlserver with Databases/Schemas/objects

Now that we have a Docker container that is running MS SQLSERVER, we want to have a Database with schemas to run liquibase against. Or you can continue by using the default master db ; dbo schema.

Configure Liqubase Project

You can use any liquibase project you want, i use one I have committed to source

  1. again, make sure you are in the root of ronak_lb_projects

  2. cd ronak_lb_projects/mssql

  3. verify liquibase.properties file has URL and passwords correct, mine looked like this:

    1.  

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 # Enter the path for your changelog file. changeLogFile=changelog.sql # Enter the URL of the source database url=jdbc:sqlserver://localhost:1433;database=master; # Enter the username for your source database. username: SA password: 'yourStrongPassword1$' driver=com.microsoft.sqlserver.jdbc.SQLServerDriver classpath: ../drivers/mssql-jdbc-8.2.2.jre8.jar #### Target Database Information #### ## The target database is the database you want to use to compare to your source database. logLevel: ERROR
  4. test by running liquibase updateSQL, I used the following command:

    1. liquibase updateSQL

    2. note because of https://datical.atlassian.net/browse/LB-379 the above command does not work, use instead: liquibase --username=SA --password='yourStrongPassword1$' updateSQL

Destroying the Docker Image

find your container by listing it:

docker container ls -a

Which should return something like this:

1 2 CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 4d5674c2da35 mssql "docker-entrypoint.s…" 2 days ago Up 2 days 0.0.0.0:5432->5432/tcp some-sqlserver

Use the container id to delete the image if you are done

1 2 docker stop <containerid> docker container rm <containerid>