Setup local MS SQL docker container
Tags: docker, mssql, local, dev
Published: 2021-12-13 14:33:59

## 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`:

```yaml
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:

```sh
#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:

```sh
#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:

```sql
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](https://www.liquibase.org/) or [FlyWay](https://flywaydb.org/) in Java world or [Entity Framework Migrations](https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/?tabs=dotnet-core-cli) 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](https://github.com/fluentmigrator/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](https://docs.microsoft.com/en-us/archive/msdn-magazine/2016/september/essential-net-command-line-processing-with-net-core-1-0)):

```cs
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:

```cs
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](https://github.com/alekshura/Ankara/blob/master/Compentio.Ankara.Database/Program.cs).

### **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`:

```cs
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](https://github.com/alekshura/Ankara/blob/master/Compentio.Ankara.Database/Extensions/TableExtensions.cs).

### 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:

```sh
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:

```cs
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](https://github.com/alekshura/Ankara).