Hi,
I have been using Entity Framework for quite a while so one thing that has thrown me quite a bit is that OrmLite doesn’t appear to have migration features.
If I change a code first DTO what is the best way to reflect those changes in the database? Do I have to write out the raw SQL for modifying it? How do I record the migration so I can roll it back if there is an issue? How do I commit that to version control?
OrmLite doesn’t contain any data migration solutions itself but you can read about different approaches taken in:
In addition to dropping and recreating table schemas, OrmLite also includes Modify Schema API’s, e.g. for adding/removing individual columns. There are additional APIs in the OrmLiteSchemaModifyApi class:
Thanks, do you have any online example of how you organise your tests for modifying database as that is not really clear to me how to do it? The API functions seem the most familiar to me to work with but I am not sure on a good way to structure it. Also having some way to roll quickly roll back would be nice in case I make a mistake.
Also as a side note the OrmLite doc is really long. Is there any version that has an index menu to navigate?
There’s nothing special about using unit tests which is just used as a harness for being able to run individual adhoc migration tasks. I don’t have a public copy of for what we use on internal projects but TechStacks.Tests shows an example of the migration tasks I was running whilst developing the new https://techstacks.io, e.g. in MigrationTasks.cs.
But it’s not an example I’d recommend following as it was developed against the single AWS RDS db instance directly. Normally there would be single flag to indicate which DB to run against and each test would be idempotent and allow multiple runs where it would check if the new column exists before adding it, etc.
I’d just use what you’re comfortable with, this approach works for me since I’m productive with it as I can create and run migration tasks from the same IDE using shared code-base. You may want to adopt a more formal approach that is run by CI instead of from an IDE / command-line.
There’s no other version of OrmLite doc (which is on the TODO list to break up), but there is an interactive OrmLite guide on Gistlyn which walks through some of the major features.
Thanks, it’s not really an issue now as I am early in project but I have had first hand experience of migrations causing issues when multiple people working on same project which is why I like EF system so much. Dbup looks pretty good but being able to manage migrations from code is my preferred method.
I guess I could build the tests in a way that they check for a schema version number in the database and the test only executes if the schema number matches the number the test was written to migrate. I will miss the automatically generated up/down methods though.
Any plans to introduce something like this in the future?
Right the migration table would just need to maintain the test that’s ran to prevent it from running again.
Any future features are on UserVoice, e.g. you can vote for this feature request to prioritize it sooner and get notified on any updates. The comments also suggest using FluentMigrator as an alternative, which looks like another a nice option.
We’ve been using DbUp for a while and it works in that it is a verbose process. FluentMigration looks good, but their intro statement of “an alternative to creating lots of sql scripts” is misleading because you’ll have to essentially maintain lots of Fluent Migrations … pick your poison.
At first glance a common problem that FM does make more user-friendly than SQL statements is this one…
Alter.Table("Bar")
.AddColumn("SomeDate")
.AsDateTime()
.SetExistingRowsTo(DateTime.Today)
.NotNullable();
Worth exploring…
Does not seem to work on SQLite…
var apData = Db.LoadSelect<AnimalPerformanceResult>();
var auditData = Db.LoadSelect<Audit>();
Db.DropColumn<Audit>(a => a.ApRowId);
Db.DropAndCreateTable<AnimalPerformanceResult>();
Db.AddColumn<Audit>(a => a.ApRowId);
Db.InsertAll(apData);
Db.UpdateAll(auditData);
The followings are the message from exception:
{Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: ‘near “DROP”: syntax error’.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC (System.Int32 rc, SQLitePCL.sqlite3 db) [0x00053] in <1f2a4e259b9a4b0480d2b09b7c367a1b>:0
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x000dd] in <1f2a4e259b9a4b0480d2b09b7c367a1b>:0
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader () [0x00000] in <1f2a4e259b9a4b0480d2b09b7c367a1b>:0
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery () [0x00042] in <1f2a4e259b9a4b0480d2b09b7c367a1b>:0
at ServiceStack.OrmLite.OrmLiteCommand.ExecuteNonQuery () [0x00000] in :0
at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.ExecuteSql (System.Data.IDbCommand dbCmd, System.String sql, System.Collections.Generic.IEnumerable1[T] sqlParams) [0x0004a] in <c6314b8708a540e0a4e595ea797afd5d>:0 at ServiceStack.OrmLite.OrmLiteWriteApi+<>c__DisplayClass2_0.<ExecuteSql>b__0 (System.Data.IDbCommand dbCmd) [0x00000] in <c6314b8708a540e0a4e595ea797afd5d>:0 at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T] (System.Data.IDbConnection dbConn, System.Func2[T,TResult] filter) [0x00025] in :0
at ServiceStack.OrmLite.OrmLiteReadExpressionsApi.Exec[T] (System.Data.IDbConnection dbConn, System.Func2[T,TResult] filter) [0x00006] in <c6314b8708a540e0a4e595ea797afd5d>:0 at ServiceStack.OrmLite.OrmLiteWriteApi.ExecuteSql (System.Data.IDbConnection dbConn, System.String sql) [0x0000d] in <c6314b8708a540e0a4e595ea797afd5d>:0 at ServiceStack.OrmLite.OrmLiteDialectProviderBase1[TDialect].DropColumn (System.Data.IDbConnection db, System.Type modelType, System.String columnName) [0x00011] in :0
at ServiceStack.OrmLite.OrmLiteSchemaModifyApi.DropColumn (System.Data.IDbConnection dbConn, System.Type modelType, System.String columnName) [0x00006] in :0
at ServiceStack.OrmLite.OrmLiteSchemaModifyApi.DropColumn[T] (System.Data.IDbConnection dbConn, System.Linq.Expressions.Expression`1[TDelegate] field) [0x0001d] in :0
Do you know a workaround…
Thanks
SQLite doesn’t support DROP COLUMN the workaround is to create a new table without the column and copy data from the old table into it, see:
If you can be forward-only (no drop or alter, and I would argue… generally you HAVE to be forward only for frequent changes at scale or you’re hand coding…) the following works for us… put a loop on your types and call this guy:
public static void CheckSchema(Type T, string connName)
{
using (var conn = GetConnection(connName)) // a System.Data.IDbConnection
{
var d = OrmLiteUtils.GetModelDefinition(T);
var tableName = T.ToString();
if (tableName.Contains("."))
{
tableName = tableName.Substring(tableName.LastIndexOf(".") + 1);
}
if (d.Alias != null)
{
tableName = d.Alias;
}
var fieldDefns = d.FieldDefinitions;
foreach (var f in fieldDefns)
{
if (!OrmLiteSchemaApi.ColumnExists(conn, f.FieldName, tableName))
{
OrmLiteSchemaModifyApi.AddColumn(conn, T, f);
}
}
}
}