MadMigration
Tags: migration, tools, database, python
Published: 2021-02-15 10:21:36

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](https://github.com/MadeByMads/mad-migration)**.

MadMigration is written entirely in python. We used the power of the **[SQLAlchemy](https://www.sqlalchemy.org)** library. In this tutorial, we will explain the details of how to use MadMigration. 

# Installation
`pip install madmigration`
or
`pip3 install -U madmigration`

You can check version:
`madmigration --version`

Let's get start. 

For example you have old MySQL database table with name `user` :

```sql
#Mysql
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) NOT NULL,
  `lname` varchar(10) NOT NULL,
  `nick` varchar(10) NOT NULL,
  `email` varchar(15) NOT NULL,
  `pass` varchar(15) NOT NULL,
 `updated` TIMESTAMP NULL,
 `created` TIMESTAMP NOT NULL,
  `companyID` INT,
  PRIMARY KEY (`id`),
  FOREIGN KEY (companyID)
  REFERENCES company(`id`)
  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
```

And you want to migrate this table to new postgresql table with different column names:

```sql
#postgresql

CREATE TABLE public."User" (
created_at timestamp NOT NULL,
updated_at timestamp NULL,
id serial NOT NULL,
"name" varchar NOT NULL,
surname varchar NOT NULL,
nickname varchar NOT NULL,
email varchar NOT NULL,
"password" varchar NOT NULL,
CONSTRAINT "User_email_key" UNIQUE (email),
CONSTRAINT "User_pkey" PRIMARY KEY (id),
company_id integer REFERENCES company (id)
ON DELETE SET NULL
);
```

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`:

```yaml
version: 0.1.8
Configs:
  - SourceConfig:
      dbURI: "mysql://root:admin@127.0.0.1/old"
  - DestinationConfig:
      dbURI: "postgresql://root:admin@127.0.0.1/new"
```

The `migrationTables` section is intended to describe information about tables:

```yaml
migrationTables:
  - migrationTable:
      SourceTable:
        name: user
      DestinationTable:
        name: User
        create: true 
```

Nextly, write information about columns in `MigrationColumns` section. 

```yaml
MigrationColumns:
  - sourceColumn:
      name: id
    destinationColumn:
      name: id
      options:
        primary_key: true
        type_cast: bigint
```

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.

```yaml
  - sourceColumn:
      name: companyID
    destinationColumn:
      name: company_id
      options:
        type_cast: int
        nullable: true
        foreign_key:
          table_name: company
          column_name: id
          ondelete: "SET NULL"
```


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: 

```yaml
version: 0.1.8
Configs:
  - SourceConfig:
      dbURI: "mysql://root:admin@127.0.0.1/old"
  - DestinationConfig:
      dbURI: "postgresql://root:admin@127.0.0.1/new"

migrationTables:
  - migrationTable:
      SourceTable:
        name: user
      DestinationTable:
        name: User
        create: True 

      MigrationColumns:
        - sourceColumn:
            name: id
          destinationColumn: 
            name: id
            options:
              primary_key: true
              type_cast: bigint

        - sourceColumn:
            name: fname
          destinationColumn:
            name: name
            options:
              type_cast: string

        - sourceColumn:
            name: lname
          destinationColumn:
            name: surname
            options:
              type_cast: string

        - sourceColumn:
            name: email
          destinationColumn:
            name: email
            options:
              type_cast: string
              unique: true

        - sourceColumn:
            name: nick
          destinationColumn:
            name: nickname
            options:
              type_cast: string

        - sourceColumn:
            name: pass
          destinationColumn:
            name: password
            options:
              type_cast: string

        - sourceColumn:
            name: updated
          destinationColumn:
            name: updated_at
            options:
              type_cast: datetime

        - sourceColumn:
            name: created
          destinationColumn:
            name: created_at
            options:
              type_cast: datetime

        - sourceColumn:
            name: companyID
          destinationColumn:
            name: company_id
            options:
              type_cast: int
              nullable: true
              foreign_key:
                table_name: company
                column_name: id
                ondelete: "SET NULL"
```


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.

```yaml
version: 0.1.8
Configs:
  - SourceConfig:
      dbURI: "postgresql://root:admin@localhost:5432/olddb"
  - DestinationConfig:
      dbURI: "mongodb://localhost:27017/mydb"

migrationTables:
  - migrationTable:
      SourceTable:  #postgresql table name
        name: company  
      DestinationTable: #the collection name:
        name: Company

      MigrationColumns:
        - sourceColumn:
            name: id
          destinationColumn:
            name: id
            options:
              type_cast: uuid

        - sourceColumn:
            name: name
          destinationColumn:
            name: NAME
            options:
              type_cast: varchar
              
        - sourceColumn:
            name: created
          destinationColumn:
            name: CREATED
            options:    
              type_cast: datetime
        - sourceColumn:
            name: email
          destinationColumn:
            name: EMAIL
            options:     
              type_cast: string
        - sourceColumn:
            name: updated
          destinationColumn:
            name: UPDATED
            options:
              type_cast: datetime
         - sourceColumn:
            name: code
          destinationColumn:
            name: CODE
            options:      
              type_cast: string
```

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.

`main.yaml` file: 

```yml 
version: 1.1
Configs:
  - SourceConfig:
      dbURI: "mysql://root:admin@127.0.0.1/old"
  - DestinationConfig:
      dbURI: "postgresql://root:admin@127.0.0.1/new"

migrationTables:
  - migrationTable: !import company.yaml
  - migrationTable: !import op_cond.json

```

`company.yaml` file
```yml
SourceTable:
  name: company
DestinationTable:
  name: company
  create: true 

MigrationColumns:
  - sourceColumn:
      name: id
    destinationColumn: 
      name: id
      options:
        primary_key: true
        type_cast: uuid

  - sourceColumn:
      name: name
    destinationColumn:
      name: name
      options:
        length: 120
        type_cast: varchar
        nullable: false

  - sourceColumn:
      name: created
    destinationColumn:
      name: created
      options:
        type_cast: datetime
  - sourceColumn:
      name: updated
    destinationColumn:
      name: updated
      options:
        type_cast: datetime
        nullable: true
```

`op_conds.json` file
```json
{
    "SourceTable": {
      "name": "operation_conditions"
    },
    "DestinationTable": {
      "name": "operation_conditions",
      "create": true
    },
    "MigrationColumns": [
      {
        "sourceColumn": {
          "name": "id"
        },
        "destinationColumn": {
          "name": "id",
          "options": {
            "primary_key": true,
            "type_cast": "uuid"
          }
        }
      },
      {
        "sourceColumn": {
          "name": "interest"
        },
        "destinationColumn": {
          "name": "interest",
          "options": {
            "type_cast": "varchar",
            "length": 30,
            "nullable": false
          }
        }
      },
      {
        "sourceColumn": {
          "name": "FIFD"
        },
        "destinationColumn": {
          "name": "FIFD",
          "options": {
            "type_cast": "varchar",
            "length": 30,
            "nullable": false
          }
        }
      },
      {
        "sourceColumn": {
          "name": "comission"
        },
        "destinationColumn": {
          "name": "comission",
          "options": {
            "type_cast": "varchar",
            "length": 30,
            "nullable": false
          }
        }
      }
    ]
  }
```



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](https://github.com/MadeByMads/mad-migration/graphs/contributors)** for implementing this tool.