Support for scripting idempotent SQLite migrations

[natemcmaster]

Currently, migration script generation attempts to create an idempotent script (aka can be run repeatedly and have the same effect.) With SQLite, this is not possible using SQL statements alone as SQLite does not support conditional

One possible way to workaround this limitation:
Generate a script using LUA or Python (or something else) that will execute the if-else logic we need. Embed the SQL statements into this higher-level scripting language.

[rowanmiller]

Concluded that we don't really need this. Idempotent scripts are generally for deployment scenarios where a script needs to be handed off... and we don't think the overlap of this scenario with SQLite is significant.

We can always revisit if we see folks asking for it.

[xleon]

So I can´t deploy my app from Visual Studio to Azure just because I´m using SQLite and getting the same error (Generating idempotent scripts for migration is not currently supported by SQLite). That makes ef sqlite option totally useless or just for development.
I know sqlite should not be used for production but it´s perfect for testing or quick deploy scenarios.

[bricelam]

Calling dbContext.Database.Migrate() during app start (or from an admin or first-run page) could be an alternative.

Another alternative is to manually apply the script generated by dotnet ef migrations script --from {currentMigration} as part of your deployment process.

[bricelam]

(Even if Web Publish could get the script, I don't think MSDeploy knows how to run it on SQLite)