Leonardo D'Ippolito
Leonardo D'Ippolito

Leonardo D'Ippolito

How to start a docker container with SQL Server and EF migrations

Leonardo D'Ippolito's photo
Leonardo D'Ippolito
·Sep 11, 2022·

2 min read

In our team we share a development SQL Server database for each of our services. It works well, in general. It frees developers from having to keep a SQL server running locally. In spite of that, every time I need to make changes to the code that involve modifying the DB schema, I prefer to start a local SQL server, to prevent breaking changes in the shared database.

Docker makes this process easier. With some PowerShell commands I can create and start up a docker container with SQL server and have all my EF migrations automatically applied to it. When the database is no longer needed, I just kill the container.

Folder structure of sample app

I have created a LocalDatabase folder to keep the scripts and Dockerfile.

2022-09-11 13_25_12-Window.png

create_database.ps1

This script will create (or recreate) the SQL Server docker image that will be used to start the SQL server instance.

[string]$name = "DockerDatabaseDemoDB"

Write-Output "Generating setup.sql script"
dotnet ef migrations script -p ../DockerDatabaseDemo.csproj --output ./sql/setup.sql --context StoreContext --idempotent

@("CREATE DATABASE [$name];", "GO", "USE [$name];") + (Get-Content ./sql/setup.sql) | Set-Content ./sql/setup.sql

get-content ./sql/setup.sql

Write-Output "Starting docker build"
docker build -t demo_db --file .\Dockerfile --progress=plain .

docker images

Dockerfile

FROM mcr.microsoft.com/mssql/server:2019-latest

ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=Password@123
ENV MSSQL_PID=Developer
ENV MSSQL_TCP_PORT=1433 

USER root

COPY ./sql/setup.sql /tmp/setup.sql

RUN ( /opt/mssql/bin/sqlservr & ) | grep -q "Service Broker manager has started" && sleep 2s \
 && echo 'Will apply script setup.sql...' \ 
 && /opt/mssql-tools/bin/sqlcmd -U sa -P Password@123 -i /tmp/setup.sql

start_database.ps1

[string]$name = "DockerDatabaseDemoDB"
[string]$cID = $(docker ps -aqf "name=$name")
[string]$dbPassword = "Password@123"

if($cID)
{
    $cID = $(docker ps -qf "name=$name")
    if($cID)
    {
        Write-Output "The container $name is already running with ID: $cID";
    }
    else
    {
        Write-Output "The container $name already exists, but is stopped. Will start it.";
        docker start $name
    }        
}
else 
{
    Write-Output "Starting container $name";
    docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=$dbPassword' --name $name -p 14331:1433 -d demo_db
}

$newline = [Environment]::NewLine

Write-Output $newline
docker ps

Write-Output $newline
Write-Output "Connection string:"
Write-Output "server=tcp:localhost,14331; database=$name; Persist Security Info=false; User Id=sa; Password=$dbPassword; MultipleActiveResultSets=false; TrustServerCertificate=true;"

stop_database.ps1

[string]$name = "DockerDatabaseDemoDB"

docker ps

docker stop DockerDatabaseDemoDB

docker ps

delete_database.ps1

[string]$name = "DockerDatabaseDemoDB"

docker ps

docker stop $name

docker rm $name

docker ps -a

Source code

DockerDatabaseDemo

 
Share this