Mindscape Blog » Blog Archive » Getting started with LightSpeed migrations

Getting started with LightSpeed migrations

One of the big new features of LightSpeed 3 is migrations support. We’ll be providing more detailed how-tos on this over the next few weeks, but I thought it would be useful to kick off with a quick overview of the what and how of LightSpeed migrations, starting with the question on everybody’s lips…

Just what are migrations anyway?

Let’s suppose you have an application, and it uses a database. If you’re wise, you’ll have a SQL script for setting up that database, probably consisting of a truckload of CREATE TABLE statements and maybe a few INSERTs for reference data. By running these scripts, you can easily and reliably create a new database for your application to run on.

Now let’s suppose you upgrade the application, and the upgrade requires you to store some new data in the database. Maybe a new field (column) for one kind of entity, and a couple of new kinds of entities (tables). You can update your create script, no problem… but what about existing databases?

Well, okay, instead of updating your create script, you can create an update script, which does only the new bits (an ALTER TABLE and a couple of CREATE TABLEs). For new databases, you’ll run the create script followed by the update script; for existing databases, you’ll just run the update script. No worries.

But then it happens again! (Thanks a bunch, Marketing.) Sure, you can create another update script. But now for new databases, you have to run the create script and two update scripts, but for existing databases, you might have to run both update scripts or just the second, depending on whether the original update script had already been applied. So now you need to detect what level an existing database is at before you run your scripts. Well, maybe you can do that now. But what about after the fifth upgrade? The tenth? And what if someone doesn’t want to go straight to your latest and greatest — they want the same version you installed in their Ruritanian office three years ago.

That’s where migrations come in. A migrations framework automates tracking and applying database version changes — typically schema changes but could also include data changes such as if a new column needs to be defaulted or new reference data needs to be added. You write each migration step — the create and update scripts in the story above — and the migration framework figures out which steps need to be applied in any given situation, and applies them. For example, if you tell the migration framework to upgrade the Podunk office to version 9, which is what they’ve got in the Ruritanian office, the migrations framework can figure out that Zimiamvia is currenly on version 6, and apply only steps 7, 8 and 9.

How do I write migration scripts?

You might think the obvious way to write your migration steps would be as the SQL statements to be executed for each step — the CREATE TABLE and ALTER TABLE statements mentioned above. This can work okay but is fairly labour-intensive, and gets plain vexing when multiple databases enter the picture — for example, if some of your customers use an Oracle back-end, but others use SQL Server or MySQL. Many migration frameworks, including LightSpeed’s, therefore get you to write your migration scripts using a higher-level API; the migration framework will then generate the low-level SQL scripts when you run a migration.

So in LightSpeed, the way to write a migration (by which I mean a single version-to-version step) is to create a class in C# or Visual Basic which derives from the Migration base class:

public class AddProductColumns : Migration
{
}

This migration doesn’t do anything yet. The actual migration logic goes in your override of the Up() method:

public override void Up()
{
  AddColumn("Products", "Name", ModelDataType.String);
  AddColumn("Products", "Description", ModelDataType.String);
}

Now when you run this migration, it’ll add two columns, Name and Description, to the Products table, of data type NVARCHAR(MAX) or VARCHAR2(MAX) or however your database likes to spell Big Honking String.

This sounds dangerously like work. Can’t you write the code for me?

If you’re using the LightSpeed designer, you can get it to create migrations for you. To do this, you first need to associate a migrations project with your model. This tells the designer where to create the migration class files. A migrations project is just an ordinary C# or Visual Basic class library project (it does have to be in the same solution as the project containing your model though). To associate a migrations project with a model, open the model and choose Set Migrations Project from the Migrations menu.

Once you’ve done this, you can choose Create Migration from the Migrations menu, and LightSpeed will compare your model with the previous version (the last one you created a migration for), and suggest the steps that should go in the migration. Uncheck anything that you don’t want, give the migration a name, and LightSpeed will create the migration class and implementation for you.

You’re not restricted by what the designer gives you, though. Once the migration is created, it’s just C# or Visual Basic source code, and you can edit it to your heart’s content.

How do I run migrations?

There are three ways of running migrations: from the command line, from your own programs and from the LightSpeed designer. We’ll talk about the first two options in a future post: for today, I just want to describe how to run migrations from the designer.

Again, open your model and go to the Migrations menu. Choose Run Migrations to display the migrations window.

There are a number of things you can do in this window, but I want to concentrate on a specific workflow, of applying migrations to a connected database. You can connect to a database, or choose another workflow such as generating SQL scripts for a DBA to run, via the Change button. Once you’re connected, LightSpeed will show you the current database version (if any). Click on the desired target version and choose Apply Now. And that’s it. LightSpeed figures out which migrations to apply, and applies the changes specified in your migration code for each migration that’s needed.

This has been a whistlestop tour of migration concepts and practice and we’ll have a lot more to say about this over time. If it’s been enough to whet your appetite, why not download the free Express edition and give it a go!