Ok, so here is the situation: you work in a team, and each developer works with a local environment. Or you have multiple environments. Or... We don't care. The only thing is, you have to handle different versions of databases, and you don't know how to do this safely efficiently. and Different versions of a database may differ in terms of or in terms of structure. In this article, we will focus on the management of different versions of a database with different structures by using relational database vocabulary with MySQL examples. However, this solution can work for any kind of database, like NoSQL. data A potential solution Here we assume you work in a team, and you have a tool to share the code between people (ex: GIT, SVN). Let's create a script in the project where you put all of the database evolution. That can be a SQL script, a code in any programming language that you connect to the database, or another solution. This is not important, the only thing is you may execute database requests.When a developer wants to change the database structure, he will write a block of code (one or multiple requests) at the end of this file, and share the file with other people. Is important to keep this file up to date and to regularly run it on all environment. At this point, if you execute the file several times, you will certainly encounter some errors. This is problematic cause you need to run this file each time it is updated. So you have to add condition on each block, and store into the database which block is already executed. To do so, yo can create a table in your database to store each evolution with a unique ID. Finally, you will run the block only if the ID is not already stored on this table. That's all. The following example provides an illustration of the process. Example This example is based on a development project that uses MySQL database to store some application data. There are two developers that develop in local environments on their laptop (sso 2 development environments), and there is one production environment. For each environment there is one database. Databases already exist, but we want to be sure that we have the same version (the same structure) of the database on each environment. Generally in a project, the database evolves with the code of the project. So if you update the database you have to update the code, and vice versa. First, let's create a table to store evolution. We have to execute this request on all databases: db_evol( id_evol ( ) PRIMARY ); CREATE TABLE VARCHAR 10 KEY NOT NULL Now, let's create a file db_evol.sql in the project, with our first evolution. Here we use MySQL procedure, but we could use others. delimiter // evol() @id_evol := ; @ := ( (*) db_evol id_evol= @id_evol); if @count = 0 then table1 new_col ( ); table1 new_col = ; db_evol(id_evol) (@id_evol); (@id_evol, ); else (@id_evol, ); ; // evol()// evol// -- evol1 create procedure begin set "evol1" SET count select COUNT FROM WHERE -- This 2 lines correspond to the DB evolution ALTER TABLE ADD VARCHAR 255 UPDATE set 'default value' INSERT INTO VALUES SELECT CONCAT " executed" SELECT CONCAT " skipped" end if end call drop procedure Next step is the second developer who wants to do update the database. First, I check if the file is up to date. If there are changes, I will run the file. After that, I will write a second evolution at the end of the file and test the script. If it's ok, I will update the file of the project. db_evol.sql After several evolutions, all developers agree to update the production environment. So they will update the code and run the script db_evol.sql on the production. Conclusion This article provides an overview on how to handle database versions on multiple environments. As we have seen, there is not only one solution: you will face different cases. In order to comeback to the initial state in case of errors, you can add transaction or error handling in your script. Previously published at https://4sushi.github.io/#articles/how-to-handle-database-versions
