Database Migration Tool

🧐 About

The Database Migration Tool was designed for those looking to migrate their data from one database to another. Additionally type casting is supported if you need to change column type in new version. Our main goal is to make data migration possible between different database servers. For example mysql, postgresql.

Currently only mysql and postgresql is supported. Future planning is to support migration from/to other databases.

---

Documentation: Documentation

🏁 Getting Started

Installing

pip install madmigration

Usage

madmigration -f migration_schema.yaml

Configuration

After installation you should define YAML file where configuration will be taken in order to apply data to target database. Yaml file with list and dictionaries may contain following structures:

Connection Config

• SourceConfig is intented to be data from source database
• DestinationConfig is intented to be transfered data to target database

version: 0.1.6
Configs:
  - SourceConfig:
       dbURI: "postgres://admin:[email protected]/oldDB"
  - DestinationConfig:
       dbURI: "mysql://admin:[email protected]/newDB"

migrationTables:
  - migrationTable:
      SourceTable:
        name: users
      DestinationTable:
        name: persons
        create: True

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

        - sourceColumn:
            name: name
          destinationColumn:
            name: fname
            options:
              type_cast: varchar
              length: 32
      
        - sourceColumn:
            name: surname
          destinationColumn:
            name: lname
            options:
              type_cast: varchar
              length: 32
              index: true

        - sourceColumn:
            name: age
          destinationColumn:
            name: age
            options:
              type_cast: int

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

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

Configs section

• SourceConfig set the source database configurations
  • dbURI source database URI
• DestinationConfig set the destination database configurations
  • dbURI destination database URI

Configs:
  - SourceConfig:
       dbURI: "postgres://root:[email protected]/oldDB"  # set source database uri
  - DestinationConfig:
       dbURI: "mysql://root:[email protected]/newDB"  # set destination database uri

migrationTables section

• migrationTables in this configuration, you will write the source of the table that you have to migrate and the destination tables that will migrate the data.
  • migrationTable specify the source and destination table name
    • SourceTable information about source table
      • name source table name
    • DestinationTable information about destination table
      • name destination table name
      • create bool value. This parameter tells the program whether it should create a table or not. (default false)

migrationTables:
  - migrationTable:
      SourceTable:
        name: users
      DestinationTable:
        name: persons
        create: True

MigrationColumns section

• MigrationColumns specify source and destination column
  • sourceColumn information about source column
    • name source column name
  • destinationColumn information about destination column
    • name destination column name
    • options column options
      • type_cast destination column type name varchar,integer etc. (when we convert data we use this parameter)

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

If you want to create a foreign key you can specify it in the column parameters

- sourceColumn:
    name: USERID
  destinationColumn:
    name: user_id
    options:
      type_cast: uuid
      foreign_key:
        table_name: users
        column_name: id
        ondelete: CASCADE

You can split your .yaml files or import .json file into .yaml file.

You must create the main .yaml file and importing other files into main .yaml file.

main.yaml file

version: 1.1
Configs:
  - SourceConfig:
      dbURI: "mysql://admin:[email protected]/old"
  - DestinationConfig:
      dbURI: "postgresql://admin:[email protected]/new"

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

company.yaml file

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

{
    "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
          }
        }
      }
    ]
  }

See also the list of contributors who participated in this project.

Contributing

We are open to new ideas, additions. If you have any we would be happy to recieve and diccuss.

• Contributing