startup-in | 스타트업 개발자 채용 | Frontend Framework 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.