How do you deal with DB migrations?
Assuming you've got a CI in place, making the migrations a separate, testable commit will let you do this easily. We did this at my last company with a small GitHub bot and a CODEOWNERS file.
This process lets you validate and rollback/recover if needed.
In general what I have found is that database changes are unique to your situation. When an app is small it's fine for them to automatically run with the code commits. The system I deal with now has some very large tables, and running database migrations often requires planning. A column addition might be added weeks before the code is written to use the new column. It's just the nature of dealing with large tables.
Working solo, typically also means smaller, so there is a lot more leeway. I would do whatever works for you, and realize it's a good thing if you ever large enough to need to address other problems.
My deployment have a optional Target on the migration for rollbacks and I have never had any problems.
Depends on the tech you use ofc. But for. Net the entity framework make it easy.
Your comment is only used as a excuse for imperfect deployments and creates too much additional problems.
You should really do it like this, so the code decides how the connected database should.
Otherwhise the human overhead to execute the correct scripts will have a problem sooner or later ( eg. If a separate team/person handjes deployments). It's also vague to know if someone else has configured everything correct ( at my current job, they put all the scripts in a separate branch. One application connects to another DB and ofc the DB deployments where not configured yet).
Urgh
That's just another way of saying "you need to make sure you can roll forwards and backwards".
The question was how do you do it. The answer should include the phrase "we test it".
If it does, we take the app down first (with a maintenance message) so that errors won't bubble up to the users and APIs.
If we need to roll back a deploy that has migrations, we just roll back the migration. Every migration requires a "down" step that reverses itself. Our Go apps use Goose [1], but there are many other solutions.
For database migrations, we (1) design them so they can be applied without breaking the existing app and (2) make a "pre-launch" commit that adds those migrations to the codebase but doesn't have any code that uses them yet.
To deploy, we merge the "pre-launch" into "prod" and deploy, and since the app doesn't use the new db changes yet, it will happily continue working fine. Then, at our leisure we can manually run the migration (either through the framework built-in migration tools or manually through the db shell). Then, we can merge the full "launch" branch into "prod" and deploy again, which will push the code that starts using the db changes.
To roll back, we move "prod" back to "pre-launch" and deploy, which moves the app back to the state where the code isn't using the db changes, but the changes are still expected to be in place. Then, we manually roll back the migrations using the reverse of whatever migration method you used originally, which is fine since nothing in the codebase in the "pre-launch" commit is using the db changes. Then, we move "prod" back to whatever commit we need to roll back to and deploy again.
It takes a bit of planning and forethought, but it means no downtime and you have all the time you need to manually apply and roll-back db changes that can take a while (adding indexes to huge tables, etc.).