Category Archives: Database

MySql Auto-Increment

While playing around with MySql recently, I discovered a strange little quirk. An automatically incremented field cannot be reset – that is, it cannot be reset to a lower value than it currently is. For example, say that you insert a lot of records into a table, or that you manually add a key that’s very high:

MySql.Data.MySqlClient.MySqlException: ‘Duplicate entry ‘2147483647’ for key ‘test_table.PRIMARY”

In my case, I was simply playing around with some settings – if you have a table with more records than that then you may have other issues than this. However, my initial idea was to reset the count:

ALTER TABLE myschema.test_table AUTO_INCREMENT = 1

It turns out that, whilst you can do this, you can only do it for numbers higher than it currently is. Presumably this is to prevent conflicts or whatever. Anyway, the way around it for me was to simply insert a new record, but to override the setting of the auto-increment:

INSERT INTO `myschema`.`test_table`

You would then need to remove this record. Not ideal, but the only way I could get this to work.


A few weeks ago, I was looking into making a change to a project in work that uses DbUp. For some reason, I took away from that the overwhelming urge to write my own data generator. It’s far from finished, but I came up with datagen. This currently only comes in a MySql flavour, but my plan is to add a few more database engines.

The idea behind this is that you can generate pseudo data in your database. It’s not a tool in its own right, because I wanted to allow it to be customisable. To install , simply reference the package:

<PackageReference Include="datagen.MySql" Version="1.0.0" />

You can then populate the data in an entire schema. Just create a console app (this works with any app type that can physically access the database):

using datagen.Core;
using datagen.MySql;
using datagen.MySql.MySql;

var valueGenerator = new ValueGenerator(

string connectionString = "Server=;Port=3306;Database=datagentest;Uid=root;Pwd=password;AllowUserVariables=True";

var mySqlDefaults = new MySqlDefaults(connectionString);

var generate = new Generate(
    mySqlDefaults .UniqueKeyGenerator);
await generate.FillSchema(20, "datagentest");

The code above allows you to create a ValueGenerator – there is a default one in the package, but you can easily write your own. FillSchema then adds 20 rows to every table in the schema.


There are currently a few limitations – the main two being that this will only work with MySql currently, and that it does not deal with foreign keys (it will just omit that data).

Feel free to contribute, offer suggestions, or contibute.

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;"


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)

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


            var upgrader =
                    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(), a => {
                        // You can filter scripts here
                        if (eachscript.StartsWith('--')) return false;
                        return true

            var scripts = upgrader.GetScriptsToExecute();
            foreach (var script in scripts)

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

            var result = upgrader.PerformUpgrade();                        

            if (!result.Successful)
                Console.ForegroundColor = ConsoleColor.Red;
                return -1;

            Console.ForegroundColor = ConsoleColor.Green;
            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.


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:


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

Designing and Debugging Database Unit Tests

There are many systems out there in the wild, and some new ones being written, that use database logic extensively. This article discusses how and why these pieces of logic should be tested, along with whether they should exist at all.

In general, for unit tests, it’s worth asking the question of what, exactly, is being tested, before starting. This is especially true in database tests; for example, consider a test where we update a field in a database, and then assert that the field is what it has been set to. Are you testing your trigger logic, or are you simply testing Microsoft SQL Server works?

The second thing to consider is whether or not it makes any sense to use testable database logic in new code. That is, say we have a stored procedure that:
– Takes a product code
– Looks up what the VAT is for that product
– Calculates the total price
– Writes the result, along with the parameter and the price to a new table

Does it make sense for all that logic to be in the stored procedure, or would it make more sense to retrieve the values needed via one stored procedure, do the calculation in a testable server-side function, and call a second procedure to write the data?


Unit testing a database is a tricky business. First of all, if you have business logic in the database then it, almost by definition, depends on the state of the data. You obviously can simply run unit tests against the database and change the data, but let’s have a look at the FIRST principles, and see where database tests are inherently difficult.


It depends exactly what is meant by fast, but in comparison to a unit test that asserts some logic in C# code, database tests are slow (obviously, in comparison to conducting the test manually, they are very fast). Realistically, they are probably going to be sufficiently slow to warrant taking them out of your standard unit test suite. A sensible test project (that is, one that tests some actual code) may contain a good few hundred tests, let’s assume they all take 200ms – that means that 300 tests take a total of 60 seconds!

One thing that conducting DB tests does give you is an idea as to how fast (or slow) they actually are:


It’s incredibly difficult to produce a database unit test that is isolated because, by its nature, a database had dependencies. Certainly, if anything you’re testing is dependent on a particular data state (for example, in the case above, the product that we are looking for must exist in a table, and have a VAT rate) then, unless this state is set-up in the test itself, this rule is broken.


Again – this isn’t a small problem with databases. Should I change Column A to test a trigger on the table, am I then able to change it again. What if the data is in a different state when I run the unit tests from the last time – I might get rogue fails, or worse, rogue passes. What happens if the test crashes half way through, how do we revert?


In my example before, I changed Column A in order to test a trigger, and I’ll maybe check something that is updated by the trigger. Providing that the assertion is inside the test, the test is self-verifying. Obviously, this is easier to do wrong in a database context, because if I do nothing, the data is left in a state that can be externally verified.


This refers to when a test is written. There’s nothing inherent about database tests that prevent them from being written before, or very shortly after the code is written. However, see the comment above as to whether new code written like this makes sense.

Problems With A Database Test Project

Given what we’ve put above, let’s look at the outstanding issues that realistically need to be solved in order to use database tests:

1. Deployment. Running a standard code test will run the code wherever you are; however, a database test, whichever way you look at it, needs a database before it runs.

2. Rollback. Each test needs to be isolated, and so there needs to be a way to revert to the database state before the tests began.

3. Set-up. Any dependencies that the tests have, must be inside the test; therefore, if a table needs to have three rows in it, we need to add those rows within the test.

4. Assertion. What are we testing, and what makes sense to test; each test needs a defined purpose.

Example Project

In order to explore the various possibilities when setting up a database project, I’m going to use an example project:

Let’s start with some functionality to test. I’m going to do it this way around for two reasons: having code to test better illustrates the problems faced by database tests, and it is my belief that much of the database logic code is legacy and, therefore, already exists.

Here’s a new table, and a trigger that acts upon it:

CREATE TABLE [dbo].[SalesOrder]
    [ProductCode] NCHAR(10) NOT NULL, 
    [NetAmount] DECIMAL(18, 2) NULL, 
    [Tax] DECIMAL(18, 2) NULL, 
    [TotalAmount] DECIMAL(18, 2) NULL, 
    [Comission] DECIMAL(18, 2) NULL
CREATE TRIGGER SalesOrderAfterInsert ON SalesOrder
	DECLARE @CalcTax Decimal(18,2) 
	DECLARE @CalcComission Decimal(18,2) 
	SELECT @CalcComission = INSERTED.NetAmount * 0.10 FROM INSERTED
    SET S.Tax = @CalcTax,
		S.Comission = @CalcComission,
		S.TotalAmount = S.NetAmount + S.Tax

This is for the purpose of illustration, so obviously, there are things here that might not make sense in real life; however, the logic is very testable. Let’s deploy this to a database, and do a quick manual test:

Once the database is published, we can check and test it in SSMS:

Quick edit the rows:

And test:

At first glance, this seems to work well. Let’s create a test:

public void CheckTotalAmount()
    using (SqlConnection sqlConnection = new SqlConnection(
        @"Data Source=TLAPTOP\PCM2014;Initial Catalog=MySqlDatabase;Integrated Security=SSPI;"))
        using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
            sqlCommand.CommandText = "INSERT INTO SalesOrder (Id, ProductCode, NetAmount) " +
                "VALUES (2, 'test', 10)";
        using (SqlCommand sqlCommandCheck = sqlConnection.CreateCommand())
            sqlCommandCheck.CommandText = $"SELECT TotalAmount FROM SalesOrder WHERE Id = 1";
            decimal result = decimal.Parse(sqlCommandCheck.ExecuteScalar().ToString());

Okay – there are a number of problems with this test, but let’s pretend for a minute that we don’t know what they are; the test passes:

Let’s run it again, just to be sure:


Let’s firstly check this against the test principles that we discussed before.
1. Is it fast? 337ms means that we can run 3 of these per second. So that’s a ‘no’.
2. Is it Isolated? Does is have a single reason to fail – and can it live independently? If we accept that the engine itself is a reason to fail, but ignore that, then we can look specifically at the test, which asserts nothing. What’s more, it is doing two separate things to the DB, so both can fail realistically.
3. Is it Repeatable? Clearly not.
4. Is it self-verifying? No – it isn’t, because we have no assertions in it. Although we know that on the first run, both queries worked, we don’t know why.
5. Timely – well, we did write it directly after the code, so that’s probably a tick.

So, we know that the second run didn’t work. A quick look at the DB will tell us why:

Of course, the test committed a transaction to the database, as a result, any subsequent runs will fail.

The Solution

What follows is a suggested solution for this kind of problem, along with the beginnings of a framework for database testing. The tests here are using MSTest, but the exact same concept is easily achievable in Nunit and, I imagine, every other testing framework.

Base Test Class

The first thing is to create a deployment task:

The deployment task might look a little like this:

public static bool DeployDatabase(string projectFile)
    ILogger logger = new BasicFileLogger();
    Dictionary<string, string> globalProperties = new Dictionary<string, string>()
        { "Configuration", "Debug" },
        { "Platform", "x86" },
        { "SqlPublishProfilePath", @"MySqlDatabase.publish.xml" }
    ProjectCollection pc = new ProjectCollection(
        globalProperties, new List<ILogger>() { logger }, ToolsetDefinitionLocations.Registry);
    BuildParameters buildParameters = new BuildParameters(pc);            
    BuildRequestData buildRequestData = new BuildRequestData(
        projectFile, globalProperties, null, new string[] { "Build", "Publish" }, null);
    BuildResult buildResult = BuildManager.DefaultBuildManager.Build(
        buildParameters, buildRequestData);
    return (buildResult.OverallResult == BuildResultCode.Success);

Publish Profiles

This uses a publish profile. These are basically XML files that tell the build how to publish your database; here’s an example of one:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="">
    <TargetConnectionString>Data Source=TLAPTOP\PCM2014;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>

You can get Visual Studio to generate this for you, by selecting to “Deploy…” the database, and then selecting “Save Profile As…”:

Database Connection

Now that we’ve deployed the database, the next step is to connect. One way of doing this is to configure the connection string in the app.config of your test project:

<?xml version="1.0" encoding="utf-8" ?>
    <add name="MySqlDatabase" 
         connectionString="Data Source=TLAPTOP\PCM2014; Initial Catalog=MySqlDatabase; Integrated Security=true" />

You can then connect using the following method:

ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
_sqlConnection = new SqlConnection(connectionString.ConnectionString);

This sort of functionality could form the basis of a base test class; for example:

public class BaseTest
    protected SqlConnection _sqlConnection;
    public virtual void SetupTest()
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
    public virtual void TearDownTest()


So, we now have a deployment task, and a connection, the next step is to run the tests in a way in which they are repeatable. The key here is to use transactions. Going back to the base class, we can wrap this functionality into a method that can simply be inherited by all unit tests.

public class BaseTest
    protected SqlConnection _sqlConnection;
    protected SqlTransaction _sqlTransaction;
    public virtual void SetupTest()
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
        _sqlTransaction = _sqlConnection.BeginTransaction();
    public virtual void TearDownTest()

Refactor The Base Class

Let’s put all this together, and remove some parts that can be separated into a common helper class:

public class ConnectionHelper
    SqlConnection _sqlConnection;
    SqlTransaction _sqlTransaction;
    public SqlConnection OpenTestConnection()
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
        _sqlTransaction = _sqlConnection.BeginTransaction();
        return _sqlConnection;
    public SqlCommand UseNewTestCommand()
        SqlCommand sqlCommand = _sqlConnection.CreateCommand();
        sqlCommand.Transaction = _sqlTransaction;
        return sqlCommand;
    public void CloseTestConnection()

The base test now looks like this:

public class BaseTest
    protected ConnectionHelper _connectionHelper;
    public virtual void SetupTestClass()
    public virtual void SetupTest()
        _connectionHelper = new ConnectionHelper();
    public virtual void TearDownTest()

In Summary

We now have a base test class that will deploy the database, establish a new connection, and transaction; and then, on completion of the test, will roll back the transaction. Here’s what the above test now looks like:

public class UnitTest2 : BaseTest
    public void CheckTotalAmount3()
        // Arrange
        using (SqlCommand sqlCommand = _connectionHelper.UseNewTestCommand())
            sqlCommand.CommandText =
                "INSERT INTO SalesOrder (Id, ProductCode, NetAmount) " +
                "VALUES (2, 'test', 10)";
        // Act
        using (SqlCommand sqlCommand = _connectionHelper.UseNewTestCommand())
            sqlCommand.CommandText = $"SELECT TotalAmount FROM SalesOrder WHERE Id = 2";
            decimal result = decimal.Parse(sqlCommand.ExecuteScalar().ToString());

            // Assert
            Assert.AreEqual(12, result);

Debugging Unit Tests

The idea behind the framework described above is that the data is never committed to the database; as a consequence of this, the tests are repeatable, because nothing ever changes. The unfortunate side-effect here is that debugging the test is made more difficult as, if it fails, it is not possible to see directly which changes have been made. There’s a couple of ways around this. One of which is to simply debug the test, and then manually fire a commit, look at the data and continue. However, a SQL expert recently introduced me to a concept of “Dirty Reads”.

Dirty Reads

Dirty reads are achieved by issuing the following command the SQL Server:


This allows you to see changes in the database which are still pending (that is, they have yet to be committed). What this means is that you can see the state of the data as it currently is, it also doesn’t place a lock on the data. One of the big issues with using this methodology is that you can see half committed transactions; of course, in this instance, that’s exactly what you want! Let’s debug our unit test:

Now let’s have a look at the SalesOrder table:

Not only does this not return anything, it doesn’t return at all. We’ve locked the table, and held it in a transaction. Let’s apply our dirty read and see what happens:

Instantly, we get the SalesOrder. If we now complete the test and run the query again, the data is gone: