Develop against SQL Server on a Mac with Docker and VS Code

- 3 minutes read - 564 words

Developing and testing web apps that target Microsoft SQL Server are a breeze on Windows because you can leverage SQL Server Express LocalDB. This is not the case when developing on a Mac as LocalDB or SQL Server full are not available natively. The options are then to either target a remote database, either on-prem or cloud, which quickly becomes expensive and pushes out the inner loop development time. Another option could be to use SQLite when in development, as this is supported on a Mac, but then you risk having unforeseen problems when deploying to production as the two databases are not equivalent.

However, with the advent of Docker and some VS Code extensions, we can develop and test against SQL Server, giving us more confidence our apps will work once deployed to production. In this post we’ll go through how to get SQL Server running on a Mac and how we can use VS Code extensions to view and interact with the database.

Prerequisities

Docker

Firstly, we’ll need to install Docker. If you haven’t heard of Docker before, it allows OS level virtualisation allowing you to run services inside lightweight containers on any operating system. In our scenario, Docker enables us to run the Linux version of SQL Server in a Linux container on our Mac.

SQL Server VS Code extension

Next, we install the SQL Server extension for VS Code.

Alt Text

This will allow us to connect, view and query our database.

SQL Server setup

Once we have our prerequisites completed, we can setup our SQL Server. I have created the following PowerShell script to automate this:

docker pull mcr.microsoft.com/mssql/server:2017-CU20-ubuntu-16.04
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=yourStrong(!)Password" `
   -p 1433:1433 --name mssqldev `
   -d `
   mcr.microsoft.com/mssql/server:2017-CU20-ubuntu-16.04`

Start-Sleep -s 15

docker exec -i mssqldev /opt/mssql-tools/bin/sqlcmd `
    -S localhost -U SA -P 'yourStrong(!)Password' `
    -Q 'CREATE DATABASE mydb'

Let’s break this script down. Firstly, we’re pulling the 2017-CU20-ubuntu-16.04 image of mssql to the local machine. Then, we’re creating and running a container, named mssqldev, and setting a password for SQL Server. Then, we’re idling for 15 seconds to allow the Server to start up. Then we’re finally executing an SQL query inside the container to create a database called mydb.

We are now in a position to connect to the database and view tables and so forth.

Connecting to SQL Server

Open SQL Server extension in VS Code, then follow these steps:

Click on Add Connection:

Alt Text

Enter localhost as the hostname:

Alt Text

Enter your database name:

Alt Text

Select SQL Login as authentication type:

Alt Text

Enter SA as username:

Alt Text

Enter your password; the same one you setup in the script:

Alt Text

Select Yes to save password:

Alt Text

Enter a connection name e.g. mssqldev-mydb:

Alt Text

The connection should then show up in the extension:

Alt Text

Great, SQL Server is now setup and a database is ready for local development on a Mac!

Summary

In this post, we went through the process to get SQL Server running on a local Mac development machine. We also configured the SQL Server VS Code extension to connect to it to allow viewing of the tables and so forth. This workflow has helped me when developing apps on a Mac that require SQL Server, I hope it can help you too! How do you handle local development with databases? Let me know in the comments!

Resources

comments powered by Disqus