DbUp | NET library that helps you to deploy changes | SQL Database library

What works best heavily depends on how deployment pipeline works - how many environments are there before production, release cycle, what parts of deployment are automated. There are no universal "best practices" - each way of handling migrations has its own set of tradeoff to be concious about. Pick upgrade procedure according to what your needs and expectations are.

When setting up EF Core migrations for a mid-sized project (around 70 tables), I tried out few potential approaches. My observations from the process and what worked out in the end:

1. You want to get a migration SQL somewhere between changing your models and deploying to production, if only to look at it in case there are any breaking changes that may cause issues on rollback. We decided on having migrations directly in project with dbcontext, and have a migration script (using dotnet ef migrations script --idempotent) be generated for every build that can potentially be deployed to any environment - in our case, a CI step for each push to trunk or release branch.
2. Putting migration SQL in version control and treating SQL as a source of truth in regards to database structure gives an ability to manually modify scripts when you want to keep some columns for backup or backwards compatibility purposes. Another option would be to consider your data model as a reference for database schema and treat migration SQL as intermediate step that is not preserved, which makes it easier to automate whole process, but requires you to handle special cases directly in your datamodel.
3. Using --idempotent flag when generating migration script gives you a script you can reapply to a database schema regardless of what schema version it was at, having it execute only steps that were not yet executed. This means you can reapply same migration script to already migrated database without breaking schema. If you have different versions of your application running in parallel in separate environments (development, staging and production environment), it can save issues with tracking manually what migration scripts version you need to apply and in what order.
4. When you have migration SQL, you can use native for your database tools in order to apply them to target environment - such as sqlcmd for SQL Server, psql for postgres. This also has a benefit of having separate user with higher privileges (schema modification) handle migrations, while your application works on limited privileges, that often can't touch the schema.
5. Applying database migrations is part of application deployment, not application startup - if you have deployment automation of some sorts, it's probably the best place to put executing migrations against target database, again - database native client is a good alternative to DbUp, pick whichever you prefer. Separating migrations from application startup also gives you ability to run an application against mismatched, but still compatible database schema - which comes handy when e.g. you're doing rollout deployments.
6. Most problems with schema upgrades come from breaking schema compatibility between versions - avoiding that requires being concious about backwards/forward compatibility when working on data model and splitting breaking changes into separate versions that keep at least single step of backwards/forwards compatibility - whether you need it depends on your project, it's something you should decide on. We run full integration test suite for previous version against current database schema and for current version against previous database schema to make sure no breaking changes are introduced between two subsequent versions - any deployment that moves multiple versions will roll out migrations one by one, with assumption that migration script or application startup can include data transformation from old to new model.

To sum up: generating migration SQL and using either native tools or DbUp on version deploy gives you a degree of manual control over migration process, and ease of use can be achieved by automating your deployment process. For development purposes, you may as well add automatic migrations on application startup, preferably applied only if environment is set to Development - as long as every person on a team has its own development database (local SQL, personal on a shared server, filedb if you use SQL) there are no conflicts to worry about.

The issue was in fact due to the FscToolPath evaluating to an empty string.

Existing error message accurately conveys the issue; it’s not F#-specific. Something in the .props/.targets files evaluates to dotnet $(PathToFsc) some/file.rsp and the variable $(PathToFsc) (or whatever is in your build scripts) is evaluating to an empty string. The final command that’s executed is then dotnet some/file.rsp and the normal dotnet behavior is to look for dotnet- as an executable.

The second factor was that the location of FSC did change due to an update of Visual Studio on the VM Image.

Not an answer, but I wonder if it's related to this: stackoverflow.com/questions/67800998/… - it seems things may have moved between VS 16.9 and 16.10.

Finally why it impacted me was because I was setting the FscCompilerPath manually due to a TypeProvider that did not support the dotnet core pipeline due to a dependency on System.Data.SqlClient.

It depends on whether you are pushing content via the SDK or if you are pulling content from a supported content source using one of the pre-built indexers. Using the SDK you can add new properties to your model as explained in this post: Update Azure search document schema

Note: Changes in your data model may require either an update or a rebuild of the index. For example, adding new properties only requires and update. But, if you change a setting like searchable, filterable or sortable you will need a rebuild. For details, see How to rebuild an index in Azure Cognitive Search

COMPATIBILITY PROBING VIA PUSH

My preferred solution is to use push indexing for everything. To test if the data model in the index is compatible, I create a sample item and submit to the index. If the model used in the index is incompatible, and error is thrown by the Azure Search SDK. I then delete the index and create it from scratch using my new model.

Here is a simplified method to test if any item is compatible with a named index:

The table schemaversions was added correctly but the problem is that you drop the database in your initial script. DbUp does not add it again.

I can't test UpdateDatabaseWithDbUp@2 but I have sth what explain how you can achieve your goal. First define templeate.yaml

Found the problem, DBUP project needs to be published isolated from other projects.

My bad was, trying to run/use dbupproject.dll file as a reference from other projects.

Problem happens with 4.3.0 release. Downgrade to 4.0.0 and it won't happen anymore (as a temporary fix till a stable one)