How to implement Continuous Delivery applications with a relational DBMS?

Question

MySQL

Hello everyone,
I'm working on a rest api application written in java (DropWizard) that stores all information in a MySQL database. The specific list of technologies probably does not matter here. But nonetheless.
I am currently working on the issue of automation: continuous integration and delivery. And in general, there are no problems with automating builds - I decided to use Team City, which, when pushing to a certain branch, will collect the build, run tests and upload it to production (well, something like this).
But I have not yet been able to solve the problem of updating the database. Let's say the next update of the service - new functionality has been added: the user can now specify several phones. To do this, in addition to changes in the code, you need to change the database schema.
How to automate all this? Do I need to start some kind of special repository where SQL scripts will be stored and when pushing, these scripts will be launched? What to do next? how to synchronize these two repositories? Let's say I changed the functionality - I immediately commit to two repositories, and then Team City first runs the scripts to update the database, and then actually builds the new application?
In general, I will be glad to any advice and personal experience. I don't have any good ideas in my head right now. Do you really need to do something manually?

Answer 1

https://en.wikipedia.org/wiki/Schema_migration aka "db migrations".