TravisCI: Setup MySQL Tables+Data before running Tests

test, mysql, mariadb, travis, continuous integration, before_install

In case your projects make use of external databases like MySQL/MariaDB you need to setup your continuous integration tests with dedicated testcases including application specific database structures. This requires some initial steps to load the database dump before starting the tests. Thanks to travisci.org you do’t need to do this kind of stuff within your application – just use the test configuration!

Travis+MySQL Server#

First of all, we add MySQL Server as service within our .travis.yml file. This initializes a dedicated database instance for testing. Additionally we hook into the before_install action to initialize our database structure. In this example all SQL commands are loaded from an external file located in our test directory.

language: node_js
node_js:
  - "7"
  - "7.6"
  - "8"
services:
  - mysql
before_install:
  - mysql -u root --password="" < test/travis.sql

Initial Database Setup#

Our Test Database structure is definied within a dedicated SQL file in test/travis.sql. It contains all necessary commands to add a new user, create demo database, create demo tables and finally add some test-data.

# Create Testuser
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'dev'@'localhost';

# Create DB
CREATE DATABASE IF NOT EXISTS `demo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `demo`;

# Create Table
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL,
  `created_on` timestamp NULL DEFAULT NULL,
  `modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(50) DEFAULT NULL,
  `salt` varchar(20) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

# Add Data

Node.js: Log static file requests with expressjs serve-static middleware

nodejs, express, static, logfile, analytics, statistics, download counter

In most cases, every web-application requires some kind of request logging. Especially package downloads will be counted for statistic purpose. By using expressjs, static content is served by the middleware module serve-static.

To count the successfull requests handled by this module, you can hook into the setHeaders callback which is invoked each time a file is ready for delivering (file exists, file is accessible).

Example#

// utility
const _path = require('path');

// expressjs
const _express = require('express');
let _webapp = _express();

// your statistic module
const _downloadStats = require('./download-counter');

// serve static package files
_webapp.use('/downloads', _express.static(_path.join(__dirname, 'downloads'), {
    // setHeaders is only called on success (stat available/file found)
    setHeaders: function(res, path, stat){
        // count request: full-path, file-stats, client-ip
        _downloadStats(path, stat, res.req.connection.remoteAddress);
    }
}));

 

Sometimes it can be very useful to have magical constants like __FILENAME__ or __LINE__ available within your sourcecode – especially for debugging or in merged files. Unfortunately, such feature is missing in javascript but it is possible to implement it by yourself using a file-postprocessing filter in your gulp build script. Thanks to gulp-concat-util, it’s […]

Prevent Errors from breaking Gulp watch

gulp-plumber, custom error handler, gulp-prettyerror

As an intermediate javascript developer, you may using gulp these days – a great and straightforward streaming build system with a lot of advantages compared to grunt. For example, i’ve switched from a bunch of custom, ANT based scripts to gulp for the next EnlighterJS major version and it saves a lot of time! Especially […]

Node.js Simple Command Line Confirm Messages

user confirmation, terminal actions, yes, no

Sometime, special terminal commands can be dangerous for your users. To ensure that they are really want to run the command the proven “best practise” is to wait for an explicit user confirmation by typing yes/no into the terminal.

Install “prompt” using NPM#

First of all, you have to install prompt – a powerfull package for command line prompts & user interactions. The “–save” option will add this package to your package.json file.

npm install prompt --save

Confirm Dialog#

After installing the prompt package you can use the following code to show a confirm dialog to your users.

var _prompt = require('prompt');

// user confirmation required!
_prompt.start();

// disable prefix message & colors
_prompt.message = '';
_prompt.delimiter = '';
_prompt.colors = false;

// wait for user confirmation
_prompt.get({
    properties: {
        
        // setup the dialog
        confirm: {
            // allow yes, no, y, n, YES, NO, Y, N as answer
            pattern: /^(yes|no|y|n)$/gi,
            description: 'Do you really want to format the filesystem and delete all file ?',
            message: 'Type yes/no',
            required: true,
            default: 'no'
        }
    }
}, function (err, result){
    // transform to lower case
    var c = result.confirm.toLowerCase();

    // yes or y typed ? otherwise abort
    if (c!='y' && c!='yes'){
        console.log('ABORT');
        return;
    }
    
    // your code
    console.log('Action confirmed');
    
});

 

Are you using a Yubikey and want to create your custom Keyserver written in Node.js ? In this case this piece of code might be useful :)

/**
 * Convert the Yubico MODHEX encoded Strings to hex
 * @param modhex String
 * @returns hex String
 */
var modhex2hex = function(modhex){
    // strip whitespaces and string cleanup - all non matching characters are 0x00 (c in modhex)
    modhex = modhex.replace(/\s*/g, '').replace(/[^cbdefghijklnrtuv]/g, 'c');

    // even length ?
    if (modhex.length%2 !== 0){
        return null;
    }

    // modhex mapping base; c.....v => 0x0 ... 0xF
    var modhexBase = 'cbdefghijklnrtuv'.split('');

    // tmp
    var output = '';

    // convert
    for (var i=0;i<modhex.length;i++){
        // convert index to hex
        output += modhexBase.indexOf(modhex.charAt(i)).toString(16);
    }

    return output;
};

console.log(modhex2hex('te vt hh fg ue dk gv rt lv hb lu gf nk ge ng cv'));