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.
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