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.
Top comments (0)