Re-running EF Migrations (after version upgrade) causes crash error on startup

[tomy2105]

Elsa 2.0, from code, latest code in git as of 2021/04/19 9 AM UTC..... :)

Had previously created database using code 4 days ago and everything worked.

Now I get Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'table "Bookmarks" already exists'.' when starting Elsa.
Works ok if no database exists, or if db created todays code exists......

I believe it is related to 76aaeb6 commit.....

[sfmskywalker]

That's expected - anytime the domain models change, I recreate the EF migrations. I provide the EF migrations as part of the library for convenience, but real-world applications are better off to maintaining their own migrations. That way, you are in control over whether or not you want to re-create the database or migrate existing ones.

[tomy2105]

I'm not sure that I fully get what you are saying :(.
Do I need to just invoke some of your code that isn't invoked by default to make this run?
Or I need to write my own code?

[sfmskywalker]

It depends: if you’re ok with deleting the database anytime Elsa’s schema changes, then there’s nothing to do except for deleting the database.

If you want to keep your database, you should generate your own EF Core migrations. The best way to do that (that I’m aware of) is to create a Console project and add to it a DB context factory that configures the ElsaContext.

Then from the command line, use the EF dotnet tool to generate migration classes.

Then, the next time you pulled in a newer version of Elsa that contains schema changes, you simply generate a second version of the migrations using the same dotnet tool and then apply these migrations to your database.

As an alternative to applying migrations manually, you can configure Elsa to run them for you automatically upon application startup.

I’ll document this entire stuff with a step by step guide.

[tomy2105]

It depends: if you’re ok with deleting the database anytime Elsa’s schema changes, then there’s nothing to do except for deleting the database.

If you want to keep your database, you should generate your own EF Core migrations. The best way to do that (that I’m aware of) is to create a Console project and add to it a DB context factory that configures the ElsaContext.

Then from the command line, use the EF dotnet tool to generate migration classes.

Then, the next time you pulled in a newer version of Elsa that contains schema changes, you simply generate a second version of the migrations using the same dotnet tool and then apply these migrations to your database.

As an alternative to applying migrations manually, you can configure Elsa to run them for you automatically upon application startup.

I’ll document this entire stuff with a step by step guide.

OK....
I'll live with documentation and doing it myself but would rather see this as part of core elsa.
Because if only other alternative is deleting old database then you could do an option to either delete or run migrations if migrations are needed.
In any case, in production environment I highly doubt anyone would go for delete so I guess migration, from my point, should be default behavior.

[craigfowler]

That's certainly an interesting problem. I suppose it's a bit of a side effect of the way people are consuming Elsa 2 at the moment. Whilst some people might be 'trying to use it like it were a stable release', in some aspects of development, things are still changing in a way which don't make it suitable for production use. Migrations are one example of that.

I have a lot of experience using DbUp for database/schema migrations and in our production project we very strictly follow a rule: Once a script has been released, unless it actually causes the upgrade process to fail, we never change it. If we need to update or correct a script, what we actually have to do is create a new script on top. That ensures that anybody can upgrade/move forwards without having the rug pulled from underneath their feet.

I think that's the kind of philosophy we would have to follow if we wanted to ship migrations with Elsa that could be used in production. I agree it would be useful (because it makes consuming/upgrading Elsa cheaper) but it would also be costly to develop & maintain. This is especially the case because Elsa supports so many persistence backends. It's not just EF Core migrations to worry about, it's also YesSQL schema (which is admittedly small, but still not zero), and also any schema-like maintenance which needs to conducted on MongoDB (again less than a SQL DB but not zero-effort).

Again, from experience I know that it's far easier to just have a project generate schema automatically; I do this with NHibernate & its hbm2ddl tool all of the time. That makes it real easy to go "from zero to a working database", but it's more effort to maintain a workable upgrade/migration path.

Anyway, all of that aside, I suppose the question is "do we ever want to do this"? If we do then we could leave this issue open with low priority. If we don't plan on doing this then I suppose it needs documenting thoroughly and then this issue could close.

[tomy2105]

I think if Elsa wants to be production ready the DB schema update should be part of the project.
As for the "philosophy" of layering one update on top of older ones that is exactly what I do in my day job even in cases when updates are written manually as sql scripts and are not auto generated by some tool. And it has served me well :).

[sfmskywalker]

Exactly. The pros & cons are clear as far as I'm concerned. My biggest worry is we mess up a migration and destroy someone's production database. As I do not wish to bear the responsibility for that, the developer themselves will have to take care of their database. Which in turn might mean it's safer for them to maintain their own migrations anyway.

That being said, what we might try is to generate a new migration step only when we are ready to push out a new release. We more or less do this for the YesSQL provider, so we could do it for EF Core as well.

In any case, we should not do this anytime we push a new package to MyGet, since this potentially generates a lot of migration steps even though the schema changes haven't even been pushed as a release. This doesn't mean that there won't be folks not using preview packages in production, which is fine, but it would be upon themselves to maintain EF core migrations.