Saving to multiple DBs with different schemas

ServiceStack.OrmLite SQL Server
1

Hello! We have an application that was developed in ServiceStack whose database is slowly being migrated to a new one. The old database used the dbo schema for all its ServiceStack tables, however the new database is using a custom schema for the same tables. We need to be able to save the data in both databases simultaneously as the data is read by multiple other services, which will be migrated to the new DB at different times.

Is there an easy way to define the schema other than duplicating all the classes and adding the Schema tag? Can we define a schema when we call a Save, etc? Or would it just be most efficient to do a replication in SQL?

Thanks.

2

You can use the same approach of modifying the table metadata at runtime to use a custom schema, e.g:

public static class GenericTableExtensions 
{
    static object ExecWithSchema<T>(string schema, Func<object> fn)
    {
        var modelDef = typeof(T).GetModelMetadata();
        lock (modelDef) {
            var hold = modelDef.Schema;
            try {
                modelDef.Schema = schema;
                return fn();
            }
            finally {
                modelDef.Schema = hold;
            }
        }
    }
}

Which you could further make more ergonomic by creating extension methods around all the OrmLite APIs you want to use it with, e.g:

public static class GenericTableExtensions 
{
    //...
    public static void DropAndCreateTable<T>(this IDbConnection db, string schema) {
        ExecWithSchema<T>(schema, () => { db.DropAndCreateTable<T>(); return null; });
    }

    public static long Insert<T>(this IDbConnection db, string schema, T obj, bool selectIdentity = false) {
        return (long)ExecWithSchema<T>(schema, () => db.Insert(obj, selectIdentity));
    }

    public static List<T> Select<T>(this IDbConnection db, string schema, Func<SqlExpression<T>, SqlExpression<T>> expression) {
        return (List<T>)ExecWithSchema<T>(schema, () => db.Select(expression));
    }

    public static int Update<T>(this IDbConnection db, string schema, T item, Expression<Func<T, bool>> where) {
        return (int)ExecWithSchema<T>(schema, () => db.Update(item, where));
    }
}

Which you’ll be able to call with:

var schema = "SchemaA"'
db.DropAndCreateTable<GenericEntity>(schema);

db.Insert(schema, new GenericEntity { Id = 1, ColumnA = "A" });

var rows = db.Select<GenericEntity>(schema, q =>
    q.Where(x => x.ColumnA == "A"));

rows.PrintDump();

db.Update(schema, new GenericEntity { ColumnA = "B" },
    where: q => q.ColumnA == "A");

rows = db.Select<GenericEntity>(schema, q => 
    q.Where(x => x.ColumnA == "B"));

rows.PrintDump();

The table wide lock isn’t a suitable solution for running in a production app with multiple concurrent contentious requests, but is likely the simplest solution for table migrations.