How to build and run a SQL container using a DACPAC file
Introduction
Ok so recently I was working on a code project which had a hard dependency on a SQL database. Now normally I’d try and mock things and add abstractions etc. but for what was a relatively simple service, I wondered if there might be a better way to approach this.
The project I was working on already contained a .sqlproj project which generated a dacpac file to enable developers to easily keep their local or remote SQL instances in sync (if you are unfamiliar with either of these concepts please checkout the dacpac docs here and the sqlproj docs here). This meant that if I built the sqlproj I got a dacpac… but how to get that to deploy into a container?
Where to start
So I know about dockerfiles, and I know about dacpac files, but how can I munge these two things together?
Basically what I wanted was a way of doing a docker build, using the Microsoft provided base image for SQL2019, throw into that database my dacpac spec so my tables etc were created, and then have that container definition on my machine ready to spin up and spin down whenever I wanted.
Luckily I stumbled across a post by Ken Muse on Wintellect and while it didn’t work for my use case (because it was SQL2017 and had a few bugs) it did give me a good place to start. So huge kudos to Ken 👏👏👏👏👏
The Container Definition
Breaking it down
Ok so there are a number of steps here, let me break it down:
- Firstly I use the base SQL2019 image provided by MSFT
- Next I elevate my permissions to
rootto enable me to do someapt-getpackage installations - Ok so on line 11, I go to the
aka.mslink for the linux distro ofsqlpackage. If you are not familiar with this tool, please check the docs here but essentially it is a CLI tool that allows you to do dacpac deployments into SQL. - Line 19 I copy in my dacpac file to the container layers
- Lines 22–27 setup some of the default options required for the SQL container to startup. More info can be found in the docs here
- Line 31+ is where the real magic happens.
- Firstly we start the sqlserver process, and wait for it to inform us that the broker has started.
- Next we invoke our downloaded sqlpackage cli binary and tell it to do a Publish to localhost and to use our dacpac file.
- Line 33> tidy up the image.
Running the container
We can easily build and then run this container by invoking the following commands:
$ podman build ../. --build-arg PASSWORD="<YourStrong@Passw0rd>" -t mydatabase:1.0 --no-cache$ podman run -p 1433:1433 --name sqldb -d mydatabase:1.0
I’m using podman here as my container engine, but the same commands should work perfectly in docker too 😃
(Notice I used the --no-cache flag here because i ran into problems where podman would cache the dacpac file, and not notice if it had been changed/rebuilt 🙈)
Differences between mine and Ken’s solutions
For those of you interested in the differences between the blog Ken wrote and my version:
- I had to elevate permissions to root because the apt-get didn’t work without it. I could have probably dropped them back down, but didn’t bother.
- The
sqlpackageuri used by Ken didn’t seem to want to run properly in the container with a dacpac built innetstandard2.0. It kept complaining about openssl not being the correct version, bumping it to this never link fixed. - Did some tidying up of the
sqlpackagecli to keep the image a bit smaller.
Conclusion
I really love the isolation and protection that containers give me. Especially when running service tests because i can pollute my local database container, tear it down and start again in a few minutes. I can also see a future where I get these containers running on my build agents so I don’t have to have infra hanging around for CI/CD builds.
I hope this maybe helps someone else in the future 👋
