Run MSSQL on Mac using Docker
Photo by Tj Holowaychuk on Unsplash
One of the main problem I have with tests (until today) is that the database is remote: an AWS instance running windows with MSSQL. That had several consequences.
Slow tests... sounds familiar? #
First of all, tests were slow (about 5 seconds each). Given that I have more than 100 tests, running them sequentially takes about 8 minutes. So I use parallel testing execution (jest default, by the way), that reduced the time to 30 seconds, but leads to more (and deeper) problems.
Slow tests made the concurrency problem become worse because the chances of data collision were higher. Data collision is a very bad problem because it makes tests fail randomly. And random errors are the worst errors of all.
Finally, I also had some false negatives because of connection errors.
Don't do this #
My first attempt was to use sqlite3 for the tests and resulted in a very bad idea. Not only some things that passed the tests failed in development. Moreover, I had to write some code to support some specific sqllite use cases. Not anymore.
Lesson learned: make test, development and production environments as similar as possible (devs are the only animals that fall into the same hole N times with N → ∞)
Microsoft 💘 Linux (WTF!) #
We were using a remote database because the client wanted to use MSSQL, and MSSQL only run on windows... but not anymore! Since last year MSSQL is capable of running on Linux machines, and that opens a new door: run MSSQL locally using docker. In fact, a Microsoft tutorial made me aware of this possibility.
Docker to the rescue #
I'm not a docker expert, but every time I use it I got amazed by its capabilities (and performance). Run MSSQL on docker was very easy:
1. Download docker
Here: https://www.docker.com/community-edition
2. Download a Linux image with MSSQL preinstalled
docker pull microsoft/mssql-server-linux
3. Run the docker instance from the image
docker run -d --name mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=d@ZLgum3' -e 'MSSQL_PID=Developer' -p 1433:1433 microsoft/mssql-server-linux
At this point, the only problem I had was that the password was too sort (and made docker instance to raise the error and stop). I used Kitematic to watch the logs and discover the problem. It requires an 8 or more character password with letters, numbers, and symbols.
4. Download mssql command line utilities
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=y brew install --no-sandbox msodbcsql mssql-tools
5. Test everything works 🎉
sqlcmd -S 127.0.0.1 -U sa -P d@ZLgum3 -Q "SELECT @@VERSION"
(You should see the MSSQL version on the console)
6. Create a database
sqlcmd -S 127.0.0.1 -U sa -P d@ZLgum3 -Q "CREATE DATABASE wfetest;"
7. Migrate the database
yarn db:reset:test
(A custom npm/yarn script)
8. Run tests 🏎
jest
Now all my tests run in 7 seconds but, more important, all and the concurrency problems disappeared. Yay!
🖖