How to keep your database schema updated with Simple.Migrations

Do you email SQL scripts to a coworker to update your database schema? Do you update your database schema by hand? I’ve being there. Let’s find out about database migrations with Simple.Migrations.

A database migration is a uniquely identified operation to create, update or delete database objects. Migrations are a more scalable and maintainable alternative to running SQL scripts directly into your database to update its schema.

Let’s migrate

Migrations allow you to create and setup testing and development environments easily.

Have you ever needed to create a new environment to reproduce an issue? But, you forgot to run one or two scripts to create columns in a table and your application couldn’t even start. Then, you realized you had two problems.

Migrations help you to keep your database schema and your SQL scripts under version control in sync.

No more emails with database scripts!

Simple.Migrations

Simple.Migrations “is a simple bare-bones migration framework for .NET Core”. It provides “a set of simple, extendable, and composable tools for integrating migrations into your application”.

Simple.Migrations has out-of-the-box database providers for SQL Server, SQLite, PostreSQL and MySQL. But, you can create your own provider too.

Let’s create our first migration for an employees database using SQL Server.

1. Create a new Employees table

First, in a Console application, install Simple.Migrations NuGet package. Then, create a class CreateEmployees inheriting from the Migration base class. Don’t forget to add the using SimpleMigrations; statement.

With Simple.Migrations, all migrations should override two methods: Up and Down.

The Up() method should contain database operation to apply. For example, creating a new table, adding a new column to an existing table, etc. And, the Down() method should contain the steps to rollback that operation. Remember, we want to apply and rollback migrations.

For our first migration, the Up() method will have the SQL statement to create the Employees table. And, the Down() method, the statement to remove it.

You can use the Execute() method from the Migration class to run your SQL statements. But, you have a Connection property of type DbConnection to bring your own database layer or ORM of choice.

A migration should use be uniquely identified.

You need to annotate your migration with a version number using the [Migration] attribute. Either a consecutive number or a timestamp-like number is fine.

Make sure to not to repeat the same version number. Otherwise, you will get a MigrationLoadFailedException.

This is our CreateEmployees migration with CREATE TABLE and DROP TABLE statements.

using SimpleMigrations;

[Migration(1)]
public class CreateEmployees : Migration
{
    protected override void Up()
    {
        Execute(@"CREATE TABLE Employees
                (
                    [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
                    [SSO] [varchar](24) NOT NULL,
                    [FirstName] [varchar](255) NOT NULL,
                    [MiddleName] [varchar](255) NOT NULL,
                    [LastName] [varchar](255) NOT NULL,
                    [Salary] [decimal](18) NOT NULL,
                    [CreatedDate] [datetime] NULL,
                    [UpdatedDate] [datetime] NULL,
                )");
    }

    protected override void Down()
    {
        Execute(@"DROP TABLE Employees");
    }
}

I know, I know…Yes, I copied the SQL statement from SQL Server Management Studio Database Designer.

2. Apply our first migration

The next step is to update the Console application to run this migration.

Inside the Main() method of your console application, create a connection to your database and use the SimpleMigrator class. Its constructor needs the assembly containing the migrations and a database provider.

For our example, the MssqlDatabaseProvider is the appropriate provider.

With the SimpleMigrator class, you can use two methods: MigrateTo() and MigrateToLatest().

MigrateTo() applies an specific version into your database. And MigrateToLatest(), all versions up to the latest one. Before using any of these two methods, make sure to call the Load method.

The Main() method of our console application looks like this.

class Program
{
    static void Main(string[] args)
    {
        var connString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Payroll;Integrated Security=True;";
        using (var connection = new SqlConnection(connString))
        {
            var databaseProvider = new MssqlDatabaseProvider(connection);
            var migrator = new SimpleMigrator(typeof(AssemblyWithYourMigrations).Assembly, databaseProvider);
            migrator.Load();
            migrator.MigrateToLatest();
        }
    }
}

Run your console application to apply your first migration.

Simple.Migrations creates a dbo.VersionInfo table on your database. This table keeps track of the applied migrations. It should look like this one.

3. Add a column to an existing table

Now, suppose you need to add a Type column to the Employees table.

This time, create an AddTypeToEmployee class with the SQL statements needed. Remember, you need a different version number.

For example, the AddTypeToEmployee will look like this.

[Migration(2)]
public class AddTypeToEmployee : Migration
{
    protected override void Up()
    {
        Execute(@"ALTER TABLE Employees
                  ADD Type VARCHAR(8) NULL");
    }

    protected override void Down()
    {
        Execute(@"ALTER TABLE Employees
                  DROP COLUMN Type");
    }
}

Again, run the console application. Notice how the Employees and VersionInfo tables have changed on your database.

4. A runner

Finally, you can create a runner to apply your migrations. Simple.Migrations has a predefined console runner.

We have a Console application that always applies all the migrations to the latest. We need more flexibility to apply any migration or rollback any other.

Let’s use .NET Core configuration options to move the connection string to a settings file. We have ours hardcoded into our Console application.

For this, you need two install two NuGet packages:

• Microsoft.Extensions.Configuration, and
• Microsoft.Extensions.Configuration.Json

Then, create an appsettings.json file with your connection string. Mine looks like this.

{
  "ConnectionStrings": {
    "YourConnString": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Payroll;Integrated Security=True;"
  }
}

If you’re using different environments (QA, Staging, for example), you can read the environment name from an environment variable.

Then, with the ConfigurationBuilder class, you can load the appropiate json file with our connection string per environment.

After using the console runner, our console application looks like this.

class Program
{
    static void Main(string[] args)
    {
        var connString = Configuration().GetConnectionString("YourConnString");
        using (var connection = new SqlConnection(connString))
        {
            var databaseProvider = new MssqlDatabaseProvider(connection);
            var migrator = new SimpleMigrator(typeof(AssemblyWithYourMigrations).Assembly, databaseProvider);

            var consoleRunner = new ConsoleRunner(migrator);
            consoleRunner.Run(args);

            Console.ReadKey();
        }
    }

    public static IConfigurationRoot Configuration()
    {
        var environmentName = Environment.GetEnvironmentVariable("DOTNET_ENVIRONMENT");

        var configurationBuilder = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .AddJsonFile($"appsettings.{environmentName}.json", optional: true);
        return configurationBuilder.Build();
    }
}

Simple.Migrations default ConsoleRunner reads the commands up to migrate to the latest version, to to migrate to an specific version and down to revert back to a version. If the command arguments you provide are invalid, you will get a help message.

Conclusion

Voilà! That’s how we can keep our database schema up to date with migrations and Simple.Migrations.

Migrations are a better alternative to running scripts directly into your database. You can use migrations to create constraints and indexes too. With migrations, your database structure is under version control and reviewed as it should be.

Your mission, Jim, should you decide to accept it, is to add a Payments table with a relation to the Employees table. It should contain an id, a date, a paid value and the employee id. This post will self-destruct in five seconds. Good luck, Jim.

To learn more about reading configuration files in ASP.NET Core, read Configuration and the Options pattern in ASP.NET Core. Speaking of ASP.NET Core and databases, check How to create a CRUD API with Insight.Database.

Also, check how to simplify your migrations by squashing old migration files.

Happy migration time!