A set of files that can be used to set up a MS SQL Server instance and populate a table with test data

1-Dockerfile

# Install base image
FROM microsoft/mssql-server-linux

# Copy over the SQL file to set up the database
COPY setup.sql /

# Copy over the file with sample data. 
# This data is from https://people.sc.fsu.edu/~jburkardt/data/csv/csv.html
COPY data.csv /

# Temporary SA password to do database setup
ENV MSSQL_SA_PASSWORD={your_password}
ENV ACCEPT_EULA=Y

# Set up the database and populate with sample data
RUN ( /opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" \
    && /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '{your_password}' -i /setup.sql

2-setup.sql

-- Create a new table in the database
USE master ;  
GO  
DROP DATABASE IF EXISTS TestDB
CREATE DATABASE TestDB
GO 
EXEC sp_configure 'CONTAINED DATABASE AUTHENTICATION', 1
GO
RECONFIGURE
GO

-- Create a new table
USE TestDB;
CREATE TABLE addresses (
    [LatD] DECIMAL(38, 0) NOT NULL, 
    [ "LatM"] DECIMAL(38, 0) NOT NULL, 
    [ "LatS"] DECIMAL(38, 0) NOT NULL, 
    [ "NS"] VARCHAR(max) NOT NULL, 
    [ "LonD"] DECIMAL(38, 0) NOT NULL, 
    [ "LonM"] DECIMAL(38, 0) NOT NULL, 
    [ "LonS"] DECIMAL(38, 0) NOT NULL, 
    [ "EW"] VARCHAR(max) NOT NULL, 
    [ "City"] VARCHAR(max) NOT NULL, 
    [ "State"] VARCHAR(max) NOT NULL
);
GO

-- Insert test data
BULK INSERT addresses
FROM '/data.csv'
WITH (
    DATAFILETYPE = 'char',
    FIRSTROW = 1,
    FIELDTERMINATOR = ',' ,
    ROWTERMINATOR = '0x0a'
)

-- Create a new user with required privileges for that table
USE TestDB;
ALTER DATABASE TestDB SET CONTAINMENT = PARTIAL
GO 
CREATE LOGIN TestDev WITH PASSWORD = 'your-password'
CREATE USER TestDev FOR LOGIN TestDev
GO
GRANT INSERT, SELECT TO TestDev
GO

3-data.csv

Setup

Make sure have Docker installed.

$ git clone https://gist.github.com/fe4fd074cef7c705b94d23458510ad1f.git

Edit the password placeholders in the Dockerfile and setup.sql file.

$ docker build -t {name}/sql-server-test -f Dockerfile .
$ docker run -p 1433:1433 {name}/sql-server-test

Usage

You can then connect to the Docker instance using any tool you like for working with MS SQL Server. I prefer the VS Code mssql extension for this.

Note - Don't forget to change the SQL Server SA password after set up.

Any CSV file can be used for the bulk load operation. If you want to use a different file, download it and get the structure for creating the table prior to insert using csvsql:

$ csvsql -i mssql your-file.csv

Then use the output to update the CREATE TABLE section of the setup.sql file.

Install Entity Framework CLI tools:

$ dotnet tool install --global dotnet-ef --version 3.0.0

Reverse engineer a database:

$ dotnet ef dbcontext scaffold "Server={host};Database=TestDB;Trusted_Connection=True;User Id={id};Password={password};Integrated Security=false;" Microsoft.EntityFrameworkCore.SqlServer -o Models -c AddressContext -d

or

$ dotnet ef dbcontext scaffold 'Server=localhost;Database=TestDB;User Id={id};Password={password}' Microsoft.EntityFrameworkCore.SqlServer -o Models -c AddressContext -d