Database Migrations

As you develop an application, the database schema will change. New tables and columns may be added, lengths of VARCHARs may be increased, and so on.

Comparing two schemas

When you have finished working with a development database you will want to apply the changes to a test or acceptance database.

In the DatabaseSchemaReader, you can load the schemas of both database as normal:

var devSchema = new DatabaseReader(connectionStringToDev, providername).ReadAll();
var tstSchema = new DatabaseReader(connectionStringToTest, providername).ReadAll();

Then use the CompareSchemas tool. See Database Comparisons

var comparison = new CompareSchemas(tstSchema, devSchema);
var script = comparison.Execute();

The script is SQL script which you can run against the test schema to transform it (save it to a text file).

The comparison isn't magic. If you changed a column from a VARCHAR to an INT, the ALTER COLUMN statement will be generated but most databases will throw an exception because they can't implicitly change the data in the table.

Migrations In Code

You can also describe the individual changes to a database in code.

var table = new DatabaseReader(connectionStringToDev, SqlType.SqlServer)
  .Table("Categories");
//the migration
var migration = new DdlGeneratorFactory(SqlType.SqlServer)
  .MigrationGenerator();
var script = migration.AddTable(table);

When you run this code, you receive a string that contains the SQL "CREATE TABLE" script.

You can also build the schema in memory, so there is no database connection at all.

var schema = new DatabaseSchema(null, SqlType.SqlServer)
var table = schema.AddTable("Categories")
.AddColumn("CategoryId", DbType.Int32).AddPrimaryKey().AddIdentity()
.AddColumn("CategoryName", DbType.String).AddLength(50).AddNullable()
.AddColumn("Cost", DbType.Decimal).AddPrecisionScale(15,4).AddNullable();
//the migration
var migration = new DdlGeneratorFactory(SqlType.SqlServer)
  .MigrationGenerator();
var script = migration.AddTable(table);

If you want to add a column to a table, you don't have to define the every existing column. Just create a table (var table = schema.AddTable(name)), create a column (var column = table.AddColumn(colname, DbType.String).AddLength(20)) and use the migration (var script = migration.AddColumn(table, column)) which will return an "ALTER TABLE name ADD COLUMN colname VARCHAR (20) NOT NULL"

Migration Frameworks

The DatabaseSchemaReader MigrationGenerator is a lower level library than frameworks like Ruby on Rails Migrations.

• It works with simple operations producing string SQL which can be concatonated together to form a script. It does not have Up/Down migration classes.
• It does not run scripts against a database- it just produces the scripts and you must run it manually with your database tools.
• It supports more databases than many frameworks, and more schema objects.

Available Migrations

You can build the entire schema model that the DatabaseSchemaReader uses, from tables, columns and constraints to indexes, triggers, views and stored procedures, and most of it can be written out in any of the supported database platforms (SqlServer, SqlServerCe, Oracle, SQLite, MySQL, PostgreSql, DB2). Not all databases support all features, so don't expect a SQLite database to have stored procedures.

• AddTable, DropTable, RenameTable
• AddColumn, DropColumn, RenameColumn, AlterColumn
• AddConstraint, DropConstraint (with a DatabaseConstraint of ConstraintType PrimaryKey, ForeignKey, UniqueKey or Check)
• AddIndex, DropIndex, AddTrigger, DropTrigger, AddSequence, DropSequence
• AddView, DropView
• AddProcedure, DropProcedure, AddFunction, DropFunction, AddPackage, DropPackage
• IncludeSchema indicates whether to write schema names in script (eg dbo.TableName)

Building The Model

The DatabaseSchema model is composed of simple .Net classes. There are a set of "Add" extensions that allow it to be constructed in chained commands. Each has various overloads, and adds the full cross relationships that make the model easy to navigate.

• AddTable
• AddColumn
• AddLength, AddNullable, AddPrecisionScale
• AddPrimaryKey, AddIdentity, AddForeignKey, AddUniqueKey For fuller control, just create the model objects, set the properties and add them to the schema collections.

Database types can be added as strings or the System.Data.DbType enumeration. These types are automatically converted to the database appropriate type (so "VARCHAR" will be "VARCHAR2" in Oracle and "TEXT" in SQLite, "INT" will be "NUMERIC(9)" in Oracle and "INTEGER" in DB2).

var schema = new DatabaseSchema(null, SqlType.Oracle);
schema
  .AddTable("Categories")
   .AddColumn("CategoryId", "INT").AddPrimaryKey().AddIdentity()
   .AddColumn("CategoryName", "VARCHAR").AddLength(10).AddUniqueKey("UK_NAME")

  //chaining from a column to the next table
 .AddTable("Products")
  .AddColumn("ProductId", DbType.Int32).AddPrimaryKey("PK_PRODUCTS")
  .AddColumn("CategoryId", "INT")
    .AddNullable()
    .AddForeignKey("FK_CATEGORY", "Categories");