ServiceStack OrmLite how to detect change to code model and recreate database?

Question

Is there a preferred way to detect change in your code model and automatically recreate database? I don't need to migrate data, if there is a change I would be OK with just completely dropping all tables, recreate the ones from model, and populating new tables with initial data set in code.

Related to this: is there a way to get a list all tables within the Database by using ServiceStack's version of ORMLite?

Currently I'm using my own class, but would like not to invent a wheel if there is something already for this.

Web.config:

<connectionStrings>
    <add name="ApplicationServices" 
         connectionString="data source=(local);Integrated Security=SSPI;database=MyTestDatabase" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

DatabaseUtil:

public class DatabaseUtil
{
    private const int CURR_VERSION = 1;
    private static string connString = WebConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;

    public static void Init(Funq.Container container)
    {
        using (var db = connString.OpenDbConnection())
        {
            using (IDbConnection dbConn = connString.OpenDbConnection())
            {
                var createScript = !db.TableExists(typeof(ZatabaseConfig).Name.ToString());
                if (!createScript)
                {
                    var first = dbConn.FirstOrDefault<ZatabaseConfig>("");
                    createScript = first == null || first.Version < CURR_VERSION;
                }

                if (createScript)
                {
                    DropAndCreateDatabase(dbConn);
                }
            }
            // db.InsertAll(SeedData);
        }
    }

    private static void DropAndCreateDatabase(IDbConnection dbConn)
    {
        var tables = new[] { typeof(Table1), typeof(Table2), typeof(Table3), typeof(ZatabaseConfig) };

        // running drop tables only once doesn't remove tables that are referenced
        using (var dbDrop = createConnection())
            dbDrop.ExecuteSql(DROP_EVERYTHING_CONSTRAINT);

        for (int i = 0; i < 5; i++)
        {
            // dropping table 5 times to eliminate foreign constraints
            try
            {
                using (var dbNew = createConnection())
                    dbNew.ExecuteSql(DROP_EVERYTHING_TABLES);
            }
            catch
            {
            }
        }           

        //Drop and re-create all Auth and registration tables
        //var authRepo = (OrmLiteAuthRepository)container.Resolve<IUserAuthRepository>();
        //authRepo.DropAndReCreateTables();

        dbConn.CreateTables(true, tables);
        dbConn.Insert(new ZatabaseConfig { ConfigId = (int)ZatabaseConfigIds.Version, Name = CURR_VERSION });
    }

    private static string DROP_EVERYTHING_CONSTRAINT = @"
        SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id)+
        ' DROP CONSTRAINT ' + f.name 
        FROM .sys.foreign_keys AS f
        INNER JOIN .sys.foreign_key_columns AS fc
        ON f.OBJECT_ID = fc.constraint_object_id
        ";
    private static string DROP_EVERYTHING_TABLES = @"
        exec sp_MSforeachtable 'DROP TABLE ?'
        ";
}

Answer 1

There isn't a built in mechanism for this that I am aware of. But you can role your own:

Create a separate model assembly:

If you find yourself changing your model a lot then I would recommend that you create your model as a separate assembly. So create a new library project in your solution and move your model there. Then reference the project in your main project. This is good organisation practise anyway.

Then in your Properties/AssemblyInfo.cs (of the model) ensure that AssemblyVersion is set with wildcard build number and remove the [assembly: AssemblyFileVersion ...] if it exists.

[assembly: AssemblyVersion("1.0.*")]

So my model: class looks like this:

using System;
using ServiceStack.Model;
using ServiceStack.DataAnnotations;

namespace Blog
{
    public static class Model
    {
        public class Article : IHasId<int>
        {
            [AutoIncrement, PrimaryKey]
            public int Id { get; set; }
            ...

Notice that I use an outer static class Model. This makes all my tables easy to reference in my project.

Create a method to detect changes to the model assembly:

Now that we have an assembly thats version number will automatically increment when we make new builds of it, we need to be able to detect changes to the assembly in our application so we can recreate the tables.

The code below does the following:

1. Determines the type of our Model assembly. Because it can then use reflection to determine the version number of the current assembly.

2. Check the application configuration settings for the last created database model version.

3. If it doesn't find the configuration setting or the version numbers don't match a database connection is resolved.

4. The tables with the Model are then inferred from the assembly. The advantage of this is we can add more tables to our Model assembly and never have to change the drop/create code.

5. The database drops and creates the tables.

6. The new assembly version number is saved.

7. If you restart your application without altering your model, the database will persist, however make a change and restart and the database with be recreated.

public static void Init(Funq.Container container)
{
    ...
    CheckDatabaseModel(typeof(Model));
}

public void CheckDatabaseModel(Type modelType)
{
    // Get the referenced model version
    string modelVersion = Assembly.GetAssembly(modelType).GetName().Version.ToString();

    // Determine the last model version number from the configuration
    var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    var lastModelVersion = config.AppSettings.Settings["DatabaseModelVersion"];

    // Determine if the model has changed
    if(lastModelVersion == null || lastModelVersion.Value != modelVersion)
    {
        Console.WriteLine("Model has changed");
        using(var db = Resolve<IDbConnectionFactory>().OpenDbConnection())
        {
            // Drop and recreate the tables

            // Determine the tables from the model assembly
            var tables = modelType.GetNestedTypes();

            db.DropAndCreateTables(tables);

            // Repopulate database with initial data.

            // Save the model version to settings
            if(lastModelVersion == null)
                config.AppSettings.Settings.Add("DatabaseModelVersion", modelVersion);
            else 
                config.AppSettings.Settings["DatabaseModelVersion"].Value = modelVersion;     
            config.Save(ConfigurationSaveMode.Modified);
        }
    } else {
        // The model numbers matched
        Console.WriteLine("Model is current");
    }
}

Dealing with table creation order

Your database will probably have foreign key constraints and you will then find you need to create tables in a certain order, or the database won't be happy.

When you manually created the table array for your db.DropAndCreateTables before you would specify the creation order to satisfy any constraints. But because we are using modelTypes.GetNestedTypes() the order is no longer in our control. There are a couple of ways to tackle this problem.

1: Disable Key Check Constraints (Not Recommended)

The most basic, would be instruct our database to ignore constraints, while we do the table creation. In MySQL the code would be:

db.ExecuteSql("SET foreign_key_checks = 0;");
db.DropAndCreateTables(tables);
db.ExecuteSql("SET foreign_key_checks = 1;");

The code required in MSSQL or other databases will vary, and in some may not be possible. But that is ultimately a dangerous way to do things. The constraints are there for a reason, after all.

2: Define the Table Creation order in our Model (Recommended):

We can create a simple attribute that we decorate our tables with that tells our database setup code what order to do things in. The advantage of this is that we don't have to turn of constraints, and it's clear to maintainers the order things will happen in.

The Attribute:

public class TableCreationOrderAttribute : Attribute
{
    public int Order { get; private set; }

    public TableCreationOrderAttribute(int order)
    {
        Order = order;
    }
}

Decorating the Model:

public static class Model
{
    [TableCreationOrder(3)]
    public class Article : IHasId<int>
    {
        [AutoIncrement, PrimaryKey]
        public int Id { get; set; }
        ...

So now we need to tell database setup code how to use this order to create the tables correctly. Replace this line db.DropAndCreateTables(tables); with this:

var orderedTables = new Dictionary<int, Type>();
var unorderedTables = new List<Type>(); // Tables without the attribute will be created last, but in no specific order
foreach(var table in tables)
{
    var order = Attribute.GetCustomAttribute(table, typeof(TableCreationOrderAttribute)) as TableCreationOrderAttribute;
    if(order != null)
        orderedTables.Add(order.Order, table);
    else
        unorderedTables.Add(table);
}

foreach(var table in orderedTables.OrderBy(t=>t.Key))
    db.DropAndCreateTable(table.Value);

foreach(var table in unorderedTables)
    db.DropAndCreateTable(table);

Summary

It may seem a lot but it's not really, the CheckDatabaseModel method can be shrunk down to less than 35 lines of code. It's generic, so you can add it to a utilities library and reuse it again using a single call in other projects. You will never have to worry about manually causing triggering the database to refresh.

The full source code for the method can be found here.

Includes a simplified step by step guide, as this answer includes a lot of additional explaination.

---

Is there a way to get a list all tables within the Database by using ServiceStack's version of ORMLite?

• Built into ORMLite directly: No.
• You can use db.ExecSql to query the database for this information. Each database has a different command to do this. It would certainly be possible using raw SQL this way.

Hope this helps.