Category Archives: Database

Database, DevOps

Announcing NSchemer 1

1 Comment

If you already know all about NSchemer, you can jump straight to the Version 1 release notes.

What is NSchemer?

Database schema management has been an interest of mine for a very long time. I’ve seen all sorts of approaches tried: folders full of .sql files, schema version tracking in Excel, and of course the tried-and-true1 manual approach using schema diffing tools. I’m a keen proponent of automated schema management. Automated deployment is all the rage these days, and if you can’t automate your schema updates, you can’t automate your releases.

I prefer to go one step further: I like to aim towards single code path schema management. Any database, whether a brand-new one to support a new installation, or an ancient database restored from a backup for a returning client, should get to the current version using the same code path – or at least, as close as possible.

When I started pushing this idea – that developers should write their own SQL migrations as they went, rather than leaving it to the designated DBA to do in the lead-up to a release – I got some push-back. Some of my team didn’t want to write SQL. Thus, NSchemer was born2. The framework languished in alpha status for many years, despite being actively used in a number of production systems. Recently, I finally decided to tidy up the API, add a few new features I’d been meaning to for a while, and bump it up to version 1.

Why Automated Schema Management?

The number one reason for automating your schema management is testing and reliability. Assuming, for a moment, that you have test environments, automated schema management means your test environments should go through the same migrations as your production environments will – automatically, with no opportunity for a manual step to get skipped or done incorrectly. This gives you a lot of confidence that when you hit the Big Red Button to go live with a new version, your schema migrations will work: the same automated set of steps which have run against all of your other environments will run against production.

You get a lot of other nice bonuses, as well. When you merge master into your own branch, not only do you get all of the new code; you also get the migrations that update your database schema to match. No more pulling in another branch, only to have to manually update your local database schema to match.

Digging up a backup from a couple of years ago? No worries, NSchemer will bring it up to current without any hassle at all.

Installation

While you can install NSchemer into an existing assembly, I typically create a new assembly just for managing schema transitions (I use a console app, so I can run the transitions from a script during deployment). Once you’ve created YourProjectName.Schema, just 

install-package NSchemer

and you’re ready to go.

Show me the code!

NSchemer uses a single class which inherits from SqlClientDatabase to represent a versioned schema. Just create one, implement the Versions collection, and start writing transitions (beginning from 1 – NSchemer uses version 0 internally). If you’re starting with an existing schema, just use your favourite SQL tool to generate a full CREATE script, and drop it in as version 1 (use the embedded resource transition mentioned below).

public class TestSchema : SqlClientDatabase
{
    public TestSchema(string connectionString) : base(connectionString) {}
    public override List<ITransition> Versions
    {
        get
        {
            return new List<ITransition>
            {
                new CodeTransition(1, "Initial Schema", BuildTheWorld),
                new CodeTransition(2, "Add Widget Table", "This script adds a very important table", AddWidgets)
            };
        }
    }
    private bool BuildTheWorld()
    {
        CreateTable("Thing",
            new Column("ThingId", DataType.BIGINT).AsIdentity(1, 1).AsPrimaryKey(),
            new Column("ThingName", DataType.STRING, 50)                
        );
        CreateTable("ThingAnnotation",
            new Column("AnnotationId", DataType.BIGINT).AsIdentity(1, 1).AsPrimaryKey(),
            new Column("Text", DataType.STRING, 50),
            new Column("ThingId", DataType.BIGINT, false).AsForeignKey("Thing", "ThingId")
        );
        return true;
    }
    private bool AddWidgets()
    {
        RunSql(@"CREATE TABLE DBO.Widget (WidgetId [int],WidgetName [nvarchar](50)) ON [PRIMARY]");
        return true;
    }
}

The core of your schema class is the Versions collection, which contains a numbered list of transitions to be run in order. NSchemer will automatically create a table to track which versions have and haven’t been run, and whenever you call Update() on your class, it will work out which transitions haven’t run yet, and apply them. Assuming your transitions all ran without exceptions and returned true, your schema should now be up-to-date, and the version history table updated. If any of your transitions either returned false, or threw an exception, Update() will throw an exception.

Why not just SQL?

You’ll notice, in the sample above, that as well as being able to write SQL transitions, there are helper methods like CreateTable. These are here for four reasons:

  1. Some developers refuse to write SQL.
  2. Some developers write terrible SQL.
  3. They’re actually pretty convenient.
  4. If/when NSchemer officially supports non-MSSQL databases, your transitions should be cross-platform.

If you don’t want to use these convenience methods at all and you’re happy just using SQL, you could also look at some of the SQL-only frameworks which do the same thing as NSchemer, such as DbUp (which has explicit support for a range of other databases as well as Microsoft SQL Server).

If you have larger blocks of SQL to run, don’t put them all into a string like in the example above: NSchemer also supports resource files. You can create a transition like this:

new SqlScriptTransition(3, "Add another table", "NSchemer.SystemTests.EmbeddedFile.sql")

It will look in the same assembly for an embedded resource file with that name. There is also an overload which allows you to specify a different assembly for the resource file.

NSchemer uses a similar format to SQL Server Management Studio: it uses GO on a line by itself as a command separator, allowing you to submit multiple chunks of the file as separate commands.

Configuration

NSchemer has a couple of options you can use to control its behaviour. I’m afraid the API is inconsistent and the options are limited: I plan to address this when I do the overhaul in 2.0 (see below).

  • VersionTable
    Override this property to change the table NSchemer uses. Default: NSCHEMER_VERSION
  • SchemaName
    Set this property to use a schema other than dbo (use with caution: this has limited test coverage).

Can I rely on NSchemer?

You should be testing all of your migrations in testing and staging environments before they make it to production. These tests will also ensure NSchemer is behaving itself in your environment. Should you start using NSchemer in production without ensuring it goes through a testing pipeline? No, but you shouldn’t be running your own code that way either.

If you find any bugs or problems in NSchemer, please report them on the NSchemer GitHub repository. I use NSchemer myself, and I’m keen to fix any bugs you find as soon as possible. I’m also open to pull requests.

Implementation Advice

I like to run NSchemer from two different places: one in development environments, and another in deployed environments (testing, staging, UAT, production, whatever you prefer to call them).

To run in development environments, I throw some guards in (to make really sure it never runs in a deployed environment), and put it somewhere it will run on application start-up. It might look like this:

if (Debugger.IsAttached && _connectionString.Contains(".\sqlexpress")) {
    new MySchemaClass(_connectionString).Update();
}

For deployed environments, I make sure the assembly containing my transitions is a console app, run the transitions from there, and return or output a success/failure message so my deployment tool knows whether to continue or alert me that something went wrong.

Upgrading from 0.x

The only changes you should need are to sprinkle a few using NSchemer.Sql statements at the top of your files.

You should notice some API improvements:

  • When you create columns, there are new options you can provide using a fluent syntax:
    • .AsPrimaryKey()
      Only allowed when creating a new table. Specifies that this column is part of the primary key. Supports composite keys.
    • .AsForeignKey(…)
      Allowed when either creating a table or adding columns to an existing table. Allows you to indicate the referenced table, column, and (optionally) cascade options.
    • .Identity(…)
      Allows you to create the column as an IDENTITY (auto-increment) column. You can specify the initial seed, and increment value.
  • Description is no longer required on transitions.
  • CreateTable(…) now has a params syntax, so you don’t need to create a List every time you use it.
  • You can now specify the nullable status of a column for data types which don’t require a length.

The Future: NSchemer 2.0

As I’ve used NSchemer, I’ve discovered a few short-comings of the existing API. I made some minor breaking changes when I bumped NSchemer to 1.0, mostly just relating to namespaces, but 2.0 is likely to be a more significant overhaul of the API.

I also want to split Microsoft SQL Server support out into a separate package, and provide official support for other database servers. If you have strong knowledge of MySQL, PostgreSQL, Oracle, or another database platform and would like to help maintain support for that platform, please get in touch.

License

I’ve chosen to release NSchemer under the LGPL because I want it to be generally usable, but I want to make sure any improvements are available to everyone who uses it. If you want to use NSchemer but the LGPL is a problem for you, please let me know. I’m sure we can work something out.