Best practice to update database?

Docker Hub
1

lets assume I have a fancy notes app, that uses a mysql-db to store users and notes.
Therefore the database contains the tables users and notes.

after a while i want to add categories and need a new category table.

The database runs of course outside of thr container (bare metal, another container, …)

So what’s the best practice to create the database scheme in the first place and add new tables for new versions?

Currently I have two options in my mind.

  1. special script or entrypoint to run the upgrade
  2. just one single entrypoint that checks on each start if there is something to upgrade.

first option:
pros: no need to check for update on each start
cons: someone has to manually do the Update before the start

second option:
pro: just start the container and have a ready to use app
cons: you may run into problems if you start multiple containers with the same database and update might run some time before the app gets started

are there some best practices how to handle this?

2

As someone with java development background, I am used to tools like flyway and liquibase.

Both can be integrated in java applications or run standalone as part of an entrypoint script. If you use another language, you could run them standalone as part of the entrypoint script, or check if there is something similar available for your language.

3

The db-update itself is not the problem.
I’m just looking for best practices how to do it in a containerized application.

Lets asume I have a script updatedb.sh

Should I check on each start if I have to run it.

Should it only be started with an exec command or if a environment variable is set.

4

It realy depends on your design and wether your application itself is able to address this. I just used those two examples as their mechanisms are quite clever and could be a good insipiration in case you implement something yourself.

One thing is for sure: migration scripts need to have an owner (especialy in case you have multiple replicas of your container running) and you need to know wether it is possible to add the migration functionality into the app code or wether it already expects a static schema on the database tables. And probably there are even more factors I didn’t consider so far.

It realy depends - there is no one size fits all recommendation.