Setup local MS SQL docker container

Local database container

One of the organizational tasks for development team is to setup the development environment for all remote developers. Often development teams share data on one development database: due the security policy of the company or due to the complexity of the database.

During cloud native applications implementation very natural and convenient trick for developers is to setup and run database in container on the local machines. It is important also, to have the same database container with the initial snapshot: with schemas, users, data, permissions, etc. Having SQL script for database initialization it can be done creating database initialization container compentio.ankara.database in your docker-compose:

compentio.ankara.database:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: ankara-db
    command: /bin/bash ./entrypoint.sh
    networks:
      - ankara
    volumes:
      - dbdata:/var/opt/mssql/data
      - ./Compentio.Ankara.Database/CreateDatabase/docker-entrypoint.sh:/entrypoint.sh
      - ./Compentio.Ankara.Database/CreateDatabase/docker-db-init.sh:/db-init.sh
      - ./Compentio.Ankara.database/CreateDatabase/ankara-db-init.sql:/ankara-db-init.sql

were docker image entrypoint looks like:

#start SQL Server, start the script to create/setup the DB 
/db-init.sh & /opt/mssql/bin/sqlservr

30s sleep is added to allow SQL Server to start and execute SQL script:

#wait for the SQL Server to come up
sleep 30s

echo "running set up script"
#run the setup script to create the DB and the schema in the DB
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P sa@Passw0rd -d master -i ankara-db-init.sql

Above, you can see that before starting migrations all the job for creating schema, users, roles does ankara-db-init.sql script:

USE [master]
GO

IF DB_ID('ankara') IS NOT NULL
  set noexec on 

CREATE DATABASE [ankara];
GO

USE [ankara]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE LOGIN [migrator] WITH PASSWORD = 'migrator123!'
GO

CREATE SCHEMA app
GO

CREATE USER [migrator] FOR LOGIN [migrator] WITH DEFAULT_SCHEMA=[app]
GO

EXEC sp_addrolemember N'db_owner', N'migrator'
GO

CREATE LOGIN [ankaraUser] WITH PASSWORD = 'user123!'
GO

CREATE USER [ankaraUser] FOR LOGIN [ankaraUser] WITH DEFAULT_SCHEMA=[app]
GO

Schema migrations

In this way during Docker container startup we’ve created database, users and default schema. In such scripts, you can perform as much operations as you need. In the next step you can add tables, views to your schema. For this purpose, there are a number of approaches used: starting from pure SQL scripts to database migrations.

In contrast to pure SQL scripting popular are migration projects like Liquibase or FlyWay in Java world or Entity Framework Migrations in .NET. It is convenient to define subsequent database changes in XML, YAML or in code and store it in repository, while the migration framework will keep an eye on execution and versioning of appropriate database changes. Also it is handy during CI/CD process configuration.

Personally I prefer to use FluentMigrator to schema modification data load.

Migration project

First of all, in Visual Studio, let's created a database Cli project (more about creating .NET Core Cli, for example, you can read here):

static void Main(string[] args)
{
    CommandLineApplication commandLineApplication = new CommandLineApplication(throwOnUnexpectedArg: false);
    var previewOption = commandLineApplication.Option(
      "-$|-s |--script <fineName>", "Generate Sql database scripts but do not execute it on the database.", CommandOptionType.SingleValue);
    var unappliedOption = commandLineApplication.Option(
      "-$|-u |--unnaplied", "Generate Sql scripts for unapplied changes in database without executing. ", CommandOptionType.NoValue);

    commandLineApplication.HelpOption("-? | -h | --help");
    commandLineApplication.OnExecute(() =>
    {
        if (previewOption.HasValue())
        {
            PreviewOnly = true;
            PreviewScriptName = previewOption.Value();
        }

        if (unappliedOption.HasValue())
        {
            PreviewOnly = true;
            PreviewUnapplied = true;
            PreviewScriptName = previewOption.HasValue() ? previewOption.Value() : $"UnappliedMigrations_{ DateTime.UtcNow:yyyyMMddHHmm}.sql";
        }

        Run();
        return 0;
    });

    commandLineApplication.Execute(args);
}

Here, command line options are added. These options I going to discuss later.
With these options Run is executed:

private static void Run()
{
    var logger = LogManager.GetCurrentClassLogger();

    var configuration = new ConfigurationBuilder()
        .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
        .AddEnvironmentVariables()
        .Build();

    Configuration = configuration;

    try
    {
        logger.Info($"Start dabase migration. ConnectionString: {configuration.GetConnectionString("Ankara")}");

        var serviceProvider = CreateServices(configuration);
        using var scope = serviceProvider.CreateScope();
        UpdateDatabase(scope.ServiceProvider);

        logger.Info($"Migration sucessfull.");
    }
    catch (Exception exception)
    {
        logger.Error(exception, "Migration stopped because of exception");
        throw;
    }
    finally
    {
        LogManager.Shutdown();
    }
}

all the update logic executed in UpdateDatabase method, which calls FluentMigrator’s runner which performs all migrations’ Up method.
Entire code you can find here.

FluentMigrator extensions

It is convenient in FluentMigrator to extend it default methods according to you project, you can see it in example of FM InitialMigration:

namespace Compentio.Ankara.Database.Migrations
{
    using Compentio.Ankara.Database.Extensions;
    using FluentMigrator;
    using NLog;
    using System;
    using static Compentio.Ankara.Database.Extensions.TableExtensions;

    [Migration(2020_01_01_1000)]
    public class InitialMigration : Migration
    {
        private readonly Logger logger = LogManager.GetCurrentClassLogger();

        public override void Up()
        {
            logger.Info("Start initial migration");

            if (!Schema.AppSchemaExists())
            {
                Create.AppSchema();
            }

            if (!Schema.AppSchema().AppTableExists(TableNames.Cache))
            {
                Create.AppTable(TableNames.Cache)
                    .WithColumn("Id").AsString(449).NotNullable().PrimaryKey()
                    .WithColumn("Value").AsMaxVarBinary().NotNullable()
                    .WithColumn("ExpiresAtTime").AsDateTimeOffset(7).NotNullable()
                    .WithColumn("SlidingExpirationInSeconds").AsInt64().Nullable()
                    .WithColumn("AbsoluteExpiration").AsDateTimeOffset(7).Nullable();
            }

            if (!Schema.AppSchema().AppTableExists(TableNames.Logs))
            {
                Create.AppTable(TableNames.Logs)
                    .WithIdentityColumn()
                    .WithColumn("Severity").AsString()
                    .WithColumn("Message").AsMaxString()
                    .WithColumn("Timestamp").AsDateTime().Indexed()
                    .WithColumn("Logger").AsString()
                    .WithColumn("ExceptionType").AsString().Nullable()
                    .WithColumn("ExceptionMessage").AsMaxString().Nullable()
                    .WithColumn("UserLogin").AsString().Nullable()
                    .WithColumn("SourceName").AsString();
            }

            if (!Schema.AppSchema().AppTableExists(TableNames.Users))
            {
                Create.AppTable(TableNames.Users)
                   .WithIdentityColumn()
                   .WithColumn("UserId").AsInt64().Indexed()
                   .WithColumn("Roles").AsMaxString().WithDefaultValue("")
                   .WithPeriodColumns()
                   .WithActiveColumn()
                   .WithAuditColumns();
            }

            if (!Schema.AppSchema().AppTableExists(TableNames.UsersSession))
            {
                Create.AppTable(TableNames.UsersSession)
                  .WithColumn("SessionKey").AsString(449).NotNullable().PrimaryKey()
                  .WithColumn("SessionId").AsString().NotNullable()
                  .WithColumn("Login").AsString().NotNullable()
                  .WithColumn("UserId").AsInt64().NotNullable().Indexed()
                  .WithColumn("TimeStamp").AsDateTimeOffset(7).NotNullable().WithDefaultValue(DateTimeOffset.UtcNow);
            }


            Execute.AppScript("./Scripts/Example.sql");

            logger.Info("Initial migration completed");
        }

        public override void Down()
        {
            logger.Warn("Initial migration rollback.");

            Delete.AppSchema();

            Delete.Table(TableNames.Logs);
            Delete.Table(TableNames.Users);
            Delete.Table(TableNames.UsersSession);
            Delete.Table(TableNames.Cache);
        }
    }
}

here we've used Create.AppTable extension method, which creates table for defined scheme or .WithIdentityColumn() extension method for identity columns.
All used table extensions, can be found here.

Generate SQL from Migration project

Sometimes one of requirements coming from database administrators or DevOps team is to have SQL scripts for database update. For this purpose database Cli can be used. For generating of unapplied migrations, we can write:

compentio.ankara.database.exe -u | --unnaplied

it will generate SQL script with can be used to update database to its last version.

The trick here, is to configure FluentMigrator only to log generated SQL without execution of migrations, which is shown in code snippet:

private static IServiceProvider CreateServices(IConfiguration configuration)
{
    var connectionString = configuration.GetConnectionString("Ankara");

    return new ServiceCollection()
        .AddFluentMigratorCore()
        .ConfigureRunner(rb => rb
            .AddSqlServer()
            .ConfigureGlobalProcessorOptions(o => o.PreviewOnly = PreviewOnly)
            .WithGlobalConnectionString(connectionString)
            .ScanIn(typeof(InitialMigration).Assembly).For.Migrations())
                .AddLogging(loggingBuilder => {
                    loggingBuilder.ClearProviders();
                    loggingBuilder.SetMinimumLevel(Microsoft.Extensions.Logging.LogLevel.Trace);
                    loggingBuilder.AddNLog("NLog.config");
                    loggingBuilder.AddFluentMigratorConsole();
                    if (PreviewOnly)
                    {
                        loggingBuilder.Services.AddSingleton<ILoggerProvider, LogFileFluentMigratorLoggerProvider>();
                        loggingBuilder.Services.Configure<LogFileFluentMigratorLoggerOptions>(
                            opt =>
                            {
                                opt.OutputFileName = PreviewScriptName;
                                opt.OutputGoBetweenStatements = true;
                                opt.ShowSql = true;
                            });
                    };
                })

        .BuildServiceProvider(false);
}

Now with use of created database Cli Admin can control all changes in generated file and manually execute it or use in CI/CD pipeline.

The full code example can be found on Github.