I would like to introduce a recently started open source project designed for database migration. Why would we need such a tool when there are many database migration tools that exist? Let’s dive into details. We were asked to add some new features to a project which was made back in time. Upon entering the project we realized that the database structure was not designed very well, had complicated issues on tables. We decided to redesign the database structure and keep data as it was.
We looked at many tools to produce solution for this case, however, they did operate almost the same. Basically, the procedure of the tools, obtaining old database structure and convert data types,change structure of database and transfer it to another database. We have designed new database structure and our main goal to keep data. We needed to take data from different tables and transfer them to a new table. For sure, another option could be dumping old database and change everthing there and import it into new a database. Fortunately we did not apply this tecnnique, we decided to make a tool that does all of it. That is where story begins MadMigration.
MadMigration is written entirely in python. We used the power of the SQLAlchemy library. In this tutorial, we will explain the details of how to use MadMigration.
pip install madmigration
pip3 install -U madmigration
You can check version:
Let’s get start.
For example you have old MySQL database table with name user :
And you want to migrate this table to new postgresql table with different column names:
Then create yaml file schema.yml for declaring tables and columns. At first stage add Configs section. This section is for connecting to database. Configs section have two subsections SourceConfig,DestinationConfig:
The migrationTables section is intended to describe information about tables:
Nextly, write information about columns in MigrationColumns section.
In case of creating a foreign key you can specify it in the column parameters. By defining foreign key options, such as ondelete,onupdate. The table_name parameter is a reference to the table, column_name is the name of the referenced column.
No options added sourceColumn section, however in destinationColumn section we could have primary_key,nullable,default,index,unique,autoincrement,foreign_key,length,type_cast options. The final yaml sturtcure will look like this:
In addition, you can migrate your data from RDBMS to NOSQL. Below a small example for migration data from PostgreSQl to MongoDB. Work on PostgreSQL to MongoDB still goes, we will add other features as soon as possible.
Upon finishing declaration of yaml file run the following command.
madmigrate -f schema.yaml
Alternatively, you can separate migration logic into multiple .yaml or .json files. Importantly, one main .yaml file should exist, other files should be imported into this main file.
We are not currently fully implemented all functionality for SQL and NoSQL, therefore we are still developing and adding new features on it.
Thank you for amazing work to our team for implementing this tool.