@auctionfrontier/db-util NPM

DB utility

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 a specified order, from each root folder in the array order provided to a profile. Semver'd script files will be sorted by the semver before being run. Incremental script files will be sorted by number

Install

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

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);

Configuration Examples (Semver)

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

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

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

Initialise full velocicast database structure

let velDBinit = [{
    "name":"velocicast",
    "targetVersion": "1.0.0",
    "database": "velocicast",
    "scripts": [
        {
            "root": "./sql/tables"
        },
        {
            "force": true,
            "root": "./sql/procs/"
        },
        {
            "root": "./sql/scripts/"
        }
    ]
},
{
    "name":"velocicast_archive",
    "targetVersion": "1.0.0",
    "database": "velocicast_archive",
    "scripts": [
        {
            "root": "./sql/tables_archive"
        },
    ]
},
{
    "name":"velocicast_staging",
    "targetVersion": "1.0.0",
    "database": "velocicast_staging",
    "scripts": [
        {
            "root": "./sql/tables_staging"
        },
    ]
}
];
velocicastDBEvolver.run(velDBinit);

upgrade velocicast database tables to version 0.1.0. this will only run scripts newer than the version stored from the last run.

let velDBupgrade = [
    {
        "name":"veloBase",
        "targetVersion": "0.1.0",
        "database": "velocicast",
        "scripts": [
            {
                "root": "./sql/tables"
            }
        ]
    },
];
velocicastDBEvolver.run(velDBupgrade);

if you leave out the targetVersion property, it will run all files with a greater semver than the database version.

let velDBupgrade = [
    {
        "name":"veloBase",
        "database": "velocicast",
        "scripts": [
            {
                "root": "./sql/tables"
            }
        ]
    },
];
velocicastDBEvolver.run(velDBupgrade);

upgrade velocicast database tables to version 0.1.0, ignoring the current version of the database by specifying a base vesrion

let velDBupgrade = [
    {
        "name":"veloBase",
        "targetVersion": "0.1.0",
        "baseVersion": "0.0.18",
        "database": "velocicast",
        "scripts": [
            {
                "root": "./sql/tables"
            }
        ]
    },
];
velocicastDBEvolver.run(velDBupgrade);

Configuration Examples (Legacy Versioning)

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

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

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

run a specific single file

let velDBprofiles = [{
    "force":true,
    "database": "velocicast",
    "scripts": [
        {
            "root": "./sql/",
            "customGlob":"data-intl-test.sql"
        }
    ]
}
]
velocicastDBEvolver.run(velDBprofiles);

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, including semver like so: v0.1.2.filename.sql and incremental filenames like so: 1.sql.

Semver scheme

The new semver scheme creates it's own database called afdb and stores each version and profile run under the log table. Each profile is versioned independently based on the name property provided in the config, and each run is inserted as a new row with a timestamp. The version of the profile is determined by the results of the following query:

SELECT semver FROM afdb.log WHERE name = '${profile.name}' ORDER BY UNIX_TIMESTAMP(timestamp) DESC LIMIT 1;

The default semver scheme requires 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];
    }

if a version number is not found in the filename, the file will NOT be run unless the force option is passed with the config.

Incremental scheme

The incremental scheme creates it's own database called afdb and stores each version and profile run under the log table. Each profile is versioned independently based on the name property provided in the config, and each run is inserted as a new row with a timestamp. The version of the profile is determined by the results of the following query:

SELECT version FROM afdb.log WHERE name = '${profile.name}' ORDER BY UNIX_TIMESTAMP(timestamp) DESC LIMIT 1;

To use it, set the versionType property of the profile to the value 'increment'.

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. The semver scheme stores versions for each profile, while the legacy scheme only supports a single version for the whole database.

Example with all profile options:

profile = {
    "name":"velocicast",
    "versionType":"semver",
    "targetVersion": "1.0.0",
    "baseVersion":"0.0.0",
    "database": "velocicast",
    "scripts": [
        {
            "customGlob: "/**/@(*.js||*.sql)",
            "force": true,
            "root": "./sql/procs/"
        }
    ],
    "force":true,  //this overrides the script force property
    "skipVersionUpdate": false,
}

Profile Properties

name required property for the semver scheme, which is used when storing the version of that named profile
versionType String, which when set to 'increment', will use the incrementing verison method. By default, this is set to use semver versioning.
targetVersion The version string which the database should be upgraded to. Omitting this will run all files newer than the datbase version.
baseVersion 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 this to true will skip all version checking logic, and process all files found in the provided script locations. can be set at the profile level or in an individual script object
skipVersionUpdate if true the util won't write the db version at the end of the profile run

by default, if a the tool is run in interactive mode, the user will be prompted to confirm they want to run each profile. This behavior can be bypassed with the --skipConfirm command line argument.

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. The run function is passed a sql connection class that has some helper functions. see the example below:

module.exports = {
    run(sqlHelper){

        sqlHelper.mysql
            .then((mysql)=>{
                return mysql.queryAsync();
            })
            .then(()=>{
                return sqlHelper.end();
            })
        
        sqlHelper.prepare('');

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