Tag Archives: Migration

An Introduction to DbUp in .Net

Managing databases is difficult – it’s difficult because the changes to them are different than software changes; for example, if I have a method, and I want to change the name of the method, when I deploy that, the new method takes the place of the old. With a database, if you rename a column, the deploy may fail if the column doesn’t have the name that you expect.

There’s essentially two ways to deal with this problem. The first is the target state based approach that tools like SqlPackage uses – you tell the tool what you want the DB to look like, and it looks at it now, and then generates a script to get from here to there. I’ve found this to be a very nice approach in the past: however, it does mean that an automated tool is responsible for generating this code.

The second is what we’re discussing in this post: it’s the approach of maintaining a kind of master script. Typically this script must be idempotent (that is, you can run it twice and it will not have any adverse effects). The idea here being that, when you want to add a table, you add a line to the script that checks if the table exists, and if not, you add it. There’s only one golden rule here: you can never go back – if you’ve added a table and want to delete it, you must do the check and add the table, then do the check and delete the table. You don’t need any specific technology for this: after all, it’s just a sql script. However, there are tools available, and in this, I’m talking about DbUp.

What does DbUp do?

DbUp allows you to spread your SQL script, that we’ve mentioned, over many files; and it will track which ones you have run (in the target database). It also provides some tools to run the script.

Getting Started – A Basic Application

In this example, we’re dealing with MySql (although DbUp does support most of the relational databases).

Step One – Create a Console Application

The first step is to create a console application. Once you’ve done so, create a directory called Scripts (this will be where your scripts will go). Finally, you’ll need the following packages:

    <PackageReference Include="dbup-core" Version="4.5.0" />
    <PackageReference Include="dbup-mysql" Version="4.5.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="5.0.0" />
    <PackageReference Include="microsoft.Extensions.Configuration.Binder" Version="5.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="5.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="5.0.0" />

See here if you’re interested in the configuration packages. I won’t go over that again here, but you’ll need an appsettings.json with the following:

{
  "ConnectionStrings": {
    "Default": "Server=localhost;Port=3309;Database=TestDb;Uid=user;Pwd=pass;"
  }
}

Code

Now you have the basic console application, you’ll need some code – this is a slightly modified version of the code in the Getting Started link above.

        static int Main(string[] args)
        {
            IConfiguration configuration = new ConfigurationBuilder()
               .AddJsonFile("appsettings.json", true, true)
               .Build();

            string connectionString =
                args.FirstOrDefault()
                ?? configuration.GetValue<string>("ConnectionStrings:Default");

            EnsureDatabase.For.MySqlDatabase(connectionString);

            var upgrader =
                DeployChanges.To
                    .MySqlDatabase(connectionString)
                    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(), a => {
                        // You can filter scripts here
                        if (eachscript.StartsWith('--')) return false;
                        return true
                    })
                    .LogToConsole()
                    .LogScriptOutput()
                    .Build();

            var scripts = upgrader.GetScriptsToExecute();
            foreach (var script in scripts)
            {
                Console.WriteLine(script.Name);
                Console.WriteLine(script.Contents);
            }

#if DEBUG
            Console.WriteLine("Apply changes? (Y/N)");
            var response = Console.ReadKey();
            if (response.Key != ConsoleKey.Y) return -1;
#endif

            var result = upgrader.PerformUpgrade();                        

            if (!result.Successful)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(result.Error);
                Console.ResetColor();
                return -1;
            }

            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("Success!");
            Console.ResetColor();
            return 0;
        }

The method above simply scans the Scripts folder for anything to run. It ignores files with a double dash (–). If you’re running it locally, it will ask for confirmation, otherwise it will simply apply the changes.

Scripts

The next step is to add your migration scripts. This is almost just a matter of dragging them into the Scripts folder; with two exceptions:

1. The scripts must be idempotent; for example:

CREATE TABLE IF NOT EXISTS `customer` (

2. The scripts must be flagged as an Embedded Resource:

The Journal Table

When you run this, you’ll get a line that says:

Checking whether journal table exists..

The Journal table is actually schemaversions and it holds the data about the migrations that have run. You could, for example, interrogate it:

select * 
from schemaversions

Change the Primary Key Type in Entity Framework

Imagine that you’ve created a Primary Key in Entity Framework (AFAIK, this applies to EF Core and EF 6), and it’s the wrong type: perhaps you created it as a Guid and decided it should be a string; obviously, if you created it as a string and you want it as an integer, then you have the same issue you have with any such change: EF doesn’t know how to change the text “Donald” into an integer any more than you do !

The Problem

Imagine you have a primary key PK_Duck on the table: Duck. The table has the following data:



Id Name Description
80c983c9-14d0-49b7-889d-a08b874d4629 Donald White with a blue coat
5f2178fa-2605-4f70-bdfd-a7f6e924aab0 Jemima White with a bonnet and shawl

If you just change the model / entity type from a Guid to a string, EF will generate something like the following migration:

migrationBuilder.AlterColumn<string>(
                name: "Id",
                table: "Duck",
                nullable: false,
                oldClrType: typeof(Guid));

If you try to update the DB, you’ll get the following error:

The object ‘PK_Duck’ is dependent on column ‘Id’.
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.

The Solution

The solution here is actually quite straightforward, and exactly what you would do in raw SQL; you just drop the primary key. Manually change the migration to drop the key first, and then re-create it afterwards:

migrationBuilder.DropPrimaryKey("PK_Duck", "Duck");

migrationBuilder.AlterColumn<string>(
                name: "Id",
                table: "Duck",
                nullable: false,
                oldClrType: typeof(Guid));

migrationBuilder.AddPrimaryKey("PK_Duck", "Duck", "Id");

Setting up Entity Framework Core for a Console Application – One Error at a Time

Entity Framework can be difficult to get started with: especially if you come from a background of accessing the database directly, it can seem like there are endless meaningless errors that appear. In this post, I try to set-up EF Core using a .Net Core Console application. In order to better understand the errors, we’ll just do the minimum in each step; and be guided by the errors.

The first step is to create a .Net Core Console Application.

NuGet Packages

To use Entity Framework, you’ll first need to install the NuGet packages; to follow this post, you’ll need these two (initially) 1:

PM> Install-Package Microsoft.EntityFrameworkCore
PM> Install-Package Microsoft.EntityFrameworkCore.Tools

Model / Entities

The idea behind Entity Framework is that you represent database entities, or tables as they used to be known, with in memory objects. So the first step is to create a model:

namespace ConsoleApp1.Model
{
    public class MyData
    {
        public string FieldOne { get; set; }
 
    }
}

We’ve created the model, so the next thing is to create the DB:

PM> Update-Database

In the package manager console.

First Error – DbContext

The first error you get is:

No DbContext was found in assembly ‘ConsoleApp1’. Ensure that you’re using the correct assembly and that the type is neither abstract nor generic.

Okay, so let’s create a DbContext. The recommended pattern (as described here) is to inherit from DbContext:

namespace ConsoleApp1
{
    public class MyDbContext : DbContext
    {
    }
}

Okay, we’ve created a DbContext – let’s go again:

PM> Update-Database

Second Error – Database Provider

The next error is:

System.InvalidOperationException: No database provider has been configured for this DbContext. A provider can be configured by overriding the DbContext.OnConfiguring method or by using AddDbContext on the application service provider.

So we’ve moved on a little. The next thing we need to do is to configure a provider. Because in this case, I’m using SQL Server, I’ll need another NuGet package:

PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer

Then configure the DbContext to use it:

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string cn = @"Server=.\SQLEXPRESS;Database=test-db;User Id= . . .";
        optionsBuilder.UseSqlServer(cn);
 
        base.OnConfiguring(optionsBuilder);
    }
}

And again:

PM> Update-Database

Third Error – No Migrations

Strictly speaking this isn’t an actual error. It’s more a sign that nothing has happened:

No migrations were applied. The database is already up to date.
Done.

A quick look in SSMS shows that, whilst it has created the DB, it hasn’t created the table:

So we need to add a migration? Well, if we call Add-Migration here, we’ll get this: 2

That’s because we need to tell EF what data we care about. So, in the DbContext, we can let it know that we’re interested in a data set (or, if you like, table) called MyData:

public class MyDbContext : DbContext
{
    public DbSet<MyData> MyData { get; set; }

Right – now we can call:

PM> Add-Migration FirstMigration

Fourth Error – Primary Key

The next error is more about EF’s inner workings.:

System.InvalidOperationException: The entity type ‘MyData’ requires a primary key to be defined.

Definitely progress. Now we’re being moaned at because EF wants to know what the primary key for the table is, and we haven’t told it (Entity Framework, unlike SQL Server insists on a primary key). That requires a small change to the model:

using System.ComponentModel.DataAnnotations;
 
namespace ConsoleApp1.Model
{
    public class MyData
    {
        [Key]
        public string FieldOne { get; set; }
 
    }
}

This time,

PM> Add-Migration FirstMigration

Produces this:

    public partial class FirstMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "MyData",
                columns: table => new
                {
                    FieldOne = table.Column<string>(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MyData", x => x.FieldOne);
                });
        }
 
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "MyData");
        }
    }

Which looks much more like we’ll get a table – let’s try:

PM> update-database
Applying migration '20180224075857_FirstMigration'.
Done.
PM> 

Success

And it has, indeed, created a table!

References

https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/powershell

https://docs.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext

https://www.learnentityframeworkcore.com/walkthroughs/console-application