@auctionfrontier/db-util

DB utility

View Latest Test Results

The purpose of this utility is to provide a simplified node interface for upgrading or initilizing the database structure and data. It will process .sql or .js files in alphabetical order, from each root folder in the array order provided to a profile.

Install

# install via npm
npm install @auctionfrontier/db-util

Basic Usage

Currently this can only be used as a node module in a script. We would like to create an interactive cli in the future.

Provide database connection info

const Evolver = require('@auctionfrontier/db-util').Evolver;
const mysqlConfig: {
        port: '3306',
        host: '127.0.0.1',
        user: 'root',
        password: 'root',
        multipleStatements: true
    }
let velocicastDBEvolver = new Evolver(testConfig.mysqlConfig);

Initialise velocicast database, ignoring all versions with "force":true

let velDBinit = [
    {
        "legacyVersioning": true,
        "force":true,
        "database": "velocicast",
        "scripts": [
            {
                "root": "./sql/tables"
            },
            {
                "root": "./sql/procs/"
            },
            {
                "root": "./sql/scripts/"
            }
        ]
    },
];
velocicastDBEvolver.run(velDBinit);   

upgrade velocicast database tables to version 0_54

let velDBupgrade = [
    {
        "legacyVersioning": true,
        "targetGeneration": "0_54",
        "database": "velocicast",
        "scripts": [
            {
                "root": "./sql/tables"
            }
        ]
    },
];
velocicastDBEvolver.run(velDBupgrade);   

There are a few main concepts important to using the tool

• Versioning
• Profiles
• Scripts
• JS

Versioning

The library was written to support multiple versioning paradigms, and currently has the existing Velocicast versioning scheme implemented, with a new semver scheme in development. These version numbers are stored in every database schema that is touched by this utility, to ensure consistency across profiles and databases.

the existing scheme has a sequence of numbers prepended to the file name, which are parsed into a float to make comparisons, i.e.

let filename = "0_11.myscript.sql";
let version = parseFloat(filename.split(.)[0].replace('_', '.'));
// version = 0.11

If a file does not contain the _ as part of a version string, it will be run regardless of version settings.

The future versioning scheme will have a semver string appended to the beginning of each filename, and will be validated and compared using the semver npm module.

let getVersionFromFilename = function(fileName){
        let semverRegex = /^v(\d+\.\d+\.\d+).*$/;
        return semverRegex.exec(fileName)[1];
    }

Profiles

profiles are the logical groups of a set of .sql or .js scripts that should be run against a specific database, within a specific range of versions. Each profile object takes a set of properties to control which scripts are run. Each profile is run as a self contained set of work, with the scripts array run in the provided order, and the database version written at the end of all operations.

Profile Properties

• legacyVersioning Boolean which indicates if the legacy versioning should be used instead of semver
• targetGeneration The version string which the database should be upgraded to
• baseGeneration The low version number to use instead of checking the existing database version. This allows upgrading a database that does not have the version number previously set in it's lookup table.
• database The database name
• scripts An array of script objects describing the location of files to be processed
• force Setting true will skip all version checking logic, and process all files found in the provided script locations

Scripts

Each script object requires a root property, which specifies where the lib should look for files. This library uses node-glob to find the files for each profile. the default glob pattern it uses will find any .js or .sql files nested in folders under the provided root. It also supports a customGlob property which will override the default glob pattern.

//Default glob pattern
"/**/@(*.js||*.sql)"

//path passed into glob
let fullGlob = script.root + (script.customGlob || "/**/@(*.js||*.sql)")
let files = glob.sync(fullGlob)

Glob will start it's search from the process cwd. You can override this by passing a cwd property into the profile object.

JS

the .js script files you create should export a run function. This part still needs work. we can have the util pass in the mysql or knex library.

module.exports = {
    run(){
        console.log("I ran some JS!")
        return {msg:"success"}
    }
}

TODO

• implement the legacy versioning db processor
• implement the semver db processor
• fix up support for JS files, and picking the mysql library to use
• write more tests

Development

to run tests:

npm run test