Database Versioner (dbv)

Database versioner to be used to version Postgres & Redshift

Features

• Able to run db migrations in a single command
• Manages current version of the database
• Able to randomize password and store in AWS SSM
• Useful for CICD deployments
• Better than sharing data dump exports

Installation

This is a python module available through the pypi registry.

Before installing, download and install python. python 3 or higher is required.

Installation is done using the pip install command:

$ pip install syngenta_digital_dbv

or

$ pipenv install syngenta_digital_dbv

Basic Usage

Postgres/Redshift

import syngenta_digital_dbv

def some_func():
    syngenta_digital_dbv.version(
        engine='postgres',
        endpoint='localhost',
        database='dbv-postgis',
        port=5433,
        user='root',
        password='Lq4nKg&&TRhHv%7z',
        ssm_param='local-postgres-config',
        versions_directory='application/db_versions/version_number_files',
        reset_root=True
    )

Postgres/Redshift Example File Contents

CREATE TABLE users_a (
    id SERIAL PRIMARY KEY,
    "user" text,
    password text
);
CREATE TABLE foods_a (
    id SERIAL PRIMARY KEY,
    name text,
    calories text
);
ALTER TABLE foods_a ADD COLUMN user_id int;
ALTER TABLE foods_a ADD CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES users_a (id) MATCH FULL;

NOTE: File names must be unique and in order (i.e. 1.sql, 2.sql, etc or a.sql, b.sql, etc), this is how the package knows what order to run the files in

Mongo

import syngenta_digital_dbv

def some_func():
    syngenta_digital_dbv.version(
        engine='mongo',
        database='unit',
        user='root',
        password='Lq4nKg&&TRhHv%7z',
        endpoint='mongodb://localhost:27017/',
        versions_directory='tests/mocks/mongo/version_number_files',
        seed=True,
        seed_directory='tests/mocks/mongo/seed'
    )

Mongo Example File Contents

[
    {
        "database": "unit",
        "collection": "test",
        "operation": "create_index",
        "query": "user_email",
        "params": {"background": true, "unique": true}
    },
    {
        "database": "unit",
        "collection": "test",
        "operation": "create_index",
        "query": [["first", 1], ["last", -1]],
        "params": {"name": "first_last"}
    }
]

NOTE: Full list of collection operations found here: https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html

NOTE: Databases and collections are automatically by mongo; no need to have specific commands to create them

NOTE: File names must be unique and in order (i.e. 1.json, 2.json, etc or a.json, b.json, etc), this is how the package knows what order to run the files in