We're currently using ServiceStack with Entity Framework and are investigating moving to ServiceStack.OrmLite.
One major concern we have with it is how best to manage production deployments.
We use AppVeyor/Octopus for continuous deployment. With pure code-first EF we can use Migrations. With a DB-first approach, we've used DB Projects, SSDT & MsDeploy or tools like DbUp. But with OrmLite, we're not sure what the smoothest setup is to handle deployments to our test, staging & production DBs respectively. Should we go code-first and roll our own migration logic? Should we go DB-first and use T4 templates to generate POCO's?
I would be very interested to hear some success stories from people who've used OrmLite effectively in a continuous deployment setup.
Whilst a crude approach, the way I've been handling schema migrations recently is by keeping all migrations in a Test Fixture that I manually run before deployment which just uses OrmLite to execute Custom SQL DDL statements to modify table schemas or populate any table data.
To switch between environments I just uncomment out the environment I want to run it on, e.g. here's an example of what my DatabaseMigrations test class looks like:
[TestFixture, Explicit]
public class DatabaseMigrations
{
IDbConnectionFactory LocalDbFactory = ...;
IDbConnectionFactory TestDbFactory = ...;
IDbConnectionFactory LiveDbFactory = ...;
private IDbConnection Db;
public DatabaseMigrations()
{
//DbFactory = LocalDbFactory;
//DbFactory = TestDbFactory;
DbFactory = LiveDbFactory;
Db = DbFactory.Open();
}
//...
[Test]
public void Add_ExternalRef_to_Subscription()
{
Db.ExecuteNonQuery("ALTER TABLE subscription ADD COLUMN external_ref text");
var subIds = Db.Column<int>(Db.From<Subscription>().Where(
x => x.ExternalRef == null).Select(x => x.Id));
foreach (var subId in subIds)
{
Db.UpdateOnly(new Subscription {
ExternalRef = Guid.NewGuid().ToString("N")
},
where: x => x.Id == subId,
onlyFields: x => new { x.ExternalRef });
}
}
}
It doesn't support roll backs but it's quick and easy to create and keeps all schema changes in sequence in source control and runnable in code.
Other custom migration solutions which I've used previously successfully involve a bespoke solution maintaining a custom table in an RDBMS e.g. Migrations that has a list of all the migrations that have been run on that database. The CI Task would compare the rows in the database with the files in a local folder, e.g:
/Migrations
01_Add_ExternalRef_to_Subscription.sql
and automatically run any missing custom sql migration scripts. This approach did a great job keeping the RDBMS table in-sync with the migration scripts where the state of what migrations were run on each db is kept in the DB itself.
The primary draw back of this solution was the migrations were kept in custom .sql files which lacked the flexibility of a proper programming language.
I think the ideal solution would be a combination of these approaches, i.e. custom DB Migrations Table but instead running C# DB Migration NUnit tests instead. The DB Connection settings would also need to moved out into external configuration and potentially include a solution for rolling back schema migrations (e.g. tests ending with '_Rollback'), although the very few times I've needed to rollback, there's less effort in manually rolling back when needed then having to maintain rollback scripts for every schema change that's done.