DEV Community

Kevin Hicks
Kevin Hicks

Posted on

How do you deploy your database changes?

When working with websites that are likely to have active users at any time and you can't have downtime, deploying database changes with your code changes can be an issue. You need to either deploy changes in a way that they can go out at different times without causing issues or deploy database changes that take effect at the same time as the code changes.

Concerns during database deploys

  • No downtime
  • Possible bugs
  • Rolling back code
  • Data changes
  • Possible long-running tasks (import data, big data updates, etc.)

Possible deployment solutions

  • Deploy with the code as part of the code CI (Continous Integration process) using something like migrations or deploy scripts
  • Deploy database changes ahead of time in a way that won't break existing code.
  • Deploy database changes with their own CI
  • Manually deploy database changes

How do you handle your database deployments? Do you use one of the above solutions or something else?

Do you have any tips or tricks for database deployment or handling issues that may arise?

Top comments (1)

kevinhickssw profile image
Kevin Hicks • Edited

For me, there are two different primary ways I handle database deployments depending on the project's traffic and how important it is to avoid any possible bugs or downtime.

The first and my favorite is to make changes to the database to start saving data to the correct new spot using triggers, sprocs saving the same data to multiple columns, or other solutions so the database changes can go out ahead of time. For renames, I add a new column, table, sproc so the old one can stick around until the code is updated to use the new name. For deletes, the database changes go after the code is no longer using it. This leaves some clean-up to do after the deployment, though, but it can work really well to avoid any downtime or bugs during deployments.

The second way I'll use for lower traffic sites that potential issues may not be a big deal is deploying the change as part of the code's CI with migrations. There is a slight potential for downtime or bugs, but I offset it by having two servers running the code and taking one server offline to update the code. The database changes are then deployed while switching which server is online at the same time. There is still a few second window of potential issues, but it is a lot simpler and cleaner for sites where deployment issues may not matter much.

Whichever way I am deploying database changes, I prefer to keep the database changes with the code as migrations and deploy using the code's CI. However, for the first one, there may be a code deployment that only consists of database changes so that the database changes can be deployed first.

One thing I still need to figure out the best way to handle is when there are issues that require a rollback that could potentially lose data. This usually turns into an emergency to fix the problem or finding a way to save the data somewhere else quickly.