qwik-tern

Tern🐧 - MySQL Versioning for Python

This project is a database migration tool that helps manage and apply database schema changes. It uses a JSON file to define the changes and applies them to a MySQL database. It is named after a bird called Arctic Tern which can migrate the longest distance. Yearly migration can range from 44,000 miles to 59,000 miles.

Files

• changes.json: Contains the list of database changes to be applied.
• connection.py: Manages the database connection pool.
• main.py: Entry point for running the migration scripts.
• Makefile: Contains commands to run the project.
• migration.py: Contains functions to process migrations, calculate checksums, and insert data into the database.
• requirement.txt: Lists the Python dependencies for the project.
• venv/: Virtual environment directory.

Setup

1. Create a Virtual Environment:

   python -m venv venv
   

2. Activate the Virtual Environment:

   • On Windows:

     .\venv\Scripts\activate
     

   • On macOS/Linux:

     source venv/bin/activate
     

3. Install Dependencies:

   pip install -r requirement.txt
   

Usage

Running Migrations

To run the migrations, use the main.py script with the appropriate arguments.

• Migrate Up:

  python main.py --migrate up
  

• Migrate Down:

  python main.py --migrate down
  

Makefile

You can also use the Makefile to run the project:

• Run the Project:

  make run
  

Code Overview

connection.py

• get_connection_pool(): Returns a MySQL connection pool.
• create_internal_db(): Creates the internal database and the db_changelog table if it doesn't exist.

migration.py

• process_migration_down(cnx_pool): Processes the down migration by one change.
• process_migration(cnx_pool): Processes the up migration by applying all changes from the changes.json file.
• insert_data(cnx_pool, change): Inserts a change record into the db_changelog table.
• calculate_checksum(changeText): Calculates the SHA-256 checksum of a change text.
• compare_checksum(checksum, changeText): Compares the given checksum with the calculated checksum of a change text.
• read_migration_from_file(file_name): Reads migration data from a YAML file.

main.py

• Entry point for the migration tool.
• Parses command-line arguments to determine whether to migrate up or down.
• Calls the appropriate functions from migration.py to process the migrations.

Example changes.json

{
    "changeSet": [
        {
            "id": "1",
            "author": "John Doe",
            "description": "Initial version",
            "date": "2019-01-01",
            "comment": "Initial version",
            "migrateUp": "CREATE TABLE my_table (id INT PRIMARY KEY, name VARCHAR(255));",
            "migrateDown": "DROP TABLE my_table;"
        },
        {
            "id": "2",
            "author": "John Doe",
            "description": "Add column",
            "date": "2019-01-02",
            "comment": "Add column",
            "migrateUp": "ALTER TABLE my_table ADD COLUMN age INT;",
            "migrateDown": "ALTER TABLE my_table DROP COLUMN age;"
        }
    ]
}