mysql-cache

What it does

Automatically caches SELECT sql's in memory, you have serveral cache providers at your disposal and it can even work in clustered mode via redis or mmap!!

This module is wrapping some functions of the mysql2 module for ease of use

Changelog

Getting Started

1. Start by installing the package:

yarn add mysql-cache

2. Load the code

const MysqlCache = require('mysql-cache')

const mysql = new MysqlCache({
    host:            '',
    user:            '',
    password:        '',
    database:        '',
    cacheProvider:   'LRU',
})

mysql.connect(err => {
    if (err) {
        throw err // Catch any nasty errors!
    }
    console.log('W00t! i\'m connected!!')

    // Lets run some queries now!
})

3. Do awesome stuff!

// Start executing SQL like you are used to using the mysql module

mysql.query('SELECT ? + ? AS solution', [1, 5], (err, res, cache) => {
    if (err) {
        throw new Error(err)
    }
    // Some extra information
    console.log(cache.hash + ' is the cache key')
    console.log(cache.sql + ' was the sql generated and run (if not cached)')
    console.log(cache.isCache + ' boolean if the result was from cache or not')

    // The actual sql result
    console.log(res)

    // This sql was not in the cache and was cached
    // for future references

    // Do something with the output of the sql

    // Later in your code if this exact sql is run again
    // It will be retrieved from your choosen
    // cacheProvider instead of the Mysql database.

    // Can also use a configuration object if you like that :)
    mysql.query({
        sql:    'SELECT ? + ? AS solution',
        params: [1, 5],
    }, (err, res, cache) => {
        if (err) {
            throw new Error(err)
        }
        // This query was retrieved from the cache because it was the
        // exact same sql code, which was much faster call!

        console.log(cache.isCache === true) // Should be true :)

        // Do something with the results
    })
})

In-depth configuration

Here you can have a overview of a more defined mysql-cache object

const mysql = new MysqlCache({
    // You can put any configuration settings from the mysql package here, they are compatible!

    // Nice error formatting display
    prettyError: true,

    // Do you want to show errors at all when found?
    stdoutErrors: true,

    // Time To Live for a cache key in SECONDS
    // 0 = infinite
    // MMAP is not supported for TTL
    TTL: 0,

    // Mysql connection pool limit
    // Increase value if you are having problems with a lot of queries
    connectionLimit: 100,

    // You can choose a hashing method for the cache key
    // To avoid conflicts sha512 should be really safe, but it's slow!
    // You can choose all the nodejs supported hashing methods as defined
    // In the native crypto module of nodejs itself.

    // Extra hashing methods are also available:
    // farmhash32     https://github.com/lovell/farmhash#hash32input
    // farmhash64     https://github.com/lovell/farmhash#hash64input
    // xxhash         https://cyan4973.github.io/xxHash/

    // Default hashing algorithm is farmhash64
    hashing: 'farmhash64',

    // Do you want console.log's about what the program is doing?
    verbose: true,

    // Do you want to enable caching?
    caching: true,

    // You can choose different cache providers of your liking
    // memcached    https://www.npmjs.com/package/memcached
    // LRU          https://www.npmjs.com/package/lru-cache
    // mmap         https://www.npmjs.com/package/mmap-object works in clustered mode but is using IO!
    // redis        https://www.npmjs.com/package/redis using default 127.00.1 database 1)
    // node-cache   https://www.npmjs.com/package/node-cache
    // file         https://www.npmjs.com/package/cacheman-file
    // native       local variable assignment
    // You can also use mysql.cacheProviders this is a array with strings of the avaliable cacheProviders
    cacheProvider: 'memcached',

    // cacheProviders can be supplied with additional configurations via this variable!
    cacheProviderSetup: {
        // For example when we use memcached (checking the module configuration object) we can do this:
        serverLocation: '127.0.0.1:11211',
        options: {
            retries:10,
            retry:10000,
            remove:true,
            failOverServers:['192.168.0.103:11211'],
        }
    }
})

Automatic package installer

mysql-cache can use some packages that will optimise the performance. This is dependent on your settings but if mysql-cache wants to use a module that is not installed (for example mmap-object, farmhash or xxhash) then it will be Automatically installed, so the first time might run a bit slow. Any warnings or errors are printed to the console, fatal errors will throw a exception

Promises

mysql-cache uses bluebird to create promises. If you would like to use them just append the word 'Async' to any api call of mysql-cache that you would like to return promises.

Example

mysql.connectAsync().then(() => {
    mysql.flushAsync().then(() => {
        mysql.queryAsync({
            sql: 'SELECT from test where name = ?',
            nestTables: true,
            params: [
                'Joe'
            ]
        }).then(result => {
            // Do something with result

            // To get the cache object for extra info:
            console.log(result[1])

            // To get the database result:
            console.log(result[0])
        }).catch(e => {
            // Do something with the error, if it happened
            throw e
        }).finally(() => {
            // this will be always executed
        })
    }).catch(e => {
        // Do something with the error, if it happened
        throw e
    })
}).catch(e => {
    // Do something with the error, if it happened
    throw e
})

Clustered mode or Persistent mode

Want cached data to persist on restarts in your application? OR Running a application in clustered mode but want to share the cache? check this list below for compatibility for the cacheProviders:

• LRU
• mmap
• redis
• node-cache
• file
• native

Troubleshooting

Glibc errors on yarn/npm install (ubuntu)

wget http://launchpadlibrarian.net/130794928/libc6_2.17-0ubuntu4_amd64.deb
sudo dpkg -i libc6_2.17-0ubuntu4_amd64.deb

Getting make errors on yarn/npm install (ubuntu)

sudo apt-get install python-software-properties
sudo add-apt-repository ppa:ubuntu-toolchain-r/test
sudo apt-get update
sudo apt-get install gcc-5 g++-5
sudo update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-5 80 --slave /usr/bin/g++ g++ /usr/bin/g++-5
sudo update-alternatives --config gcc

// (choose gcc-5 from the list)

Benchmarks

Edit the file settings.js make sure it reflects your mysql database settings Then execute in the mysql-cache root directory:

node benchmark/samequery.js    // Test same repeating select queries
node benchmark/randomquery.js  // Test random select queries
node benchmark/createhash.js   // Test the hash speed

Example output:

Events

// When you want to know when you are connected
mysql.event.on('connected', () => {
    console.log('We are now connected to the mysql database')
})

// When all the cache gets flushed, by mysql.flush() for example
mysql.event.on('flush', () => {
    console.log('mysql-cache cache was flushed!')
})

// When a cache object was found when a query was run
mysql.event.on('hit', (query, hash, result) => {
    // query  = the sql code that was used
    // hash   = the hash that was generated for the cache key
    // result = the result that was found in the cache
    console.log('mysql-cache hit a cache object!', query, hash, result)
})

// When a cache object was NOT found when a query was run
mysql.event.on('miss', (query, hash, result) => {
    // query  = the sql code that was used
    // hash   = the hash that was generated for the cache key
    // result = the result that will be cached
    console.log('mysql-cache got a miss on a cache object!', query, hash, result)
})

// When a query was run (pre-cache)
mysql.event.on('query', sql => {
    console.log('mysql-cache is going to run a query, it might be cached or not we dont know yet: ' + sql)
})

// When a sql is fired to the database
mysql.event.on('dbQuery', obj => {
    console.log(obj)
})

// When a pool connection is accquired
mysql.event.on('getPool', connection => {
    console.log('Pool connection aqquired!')
    // connection = mysql2 module variable
})

// When a pool connection is closed
mysql.event.on('endPool', connection => {
    console.log('Pool connection was dropped!')
    // connection = mysql2 module variable
})

// When a pool connection has been killed
mysql.event.on('killPool', () => {
    console.log('Pool connection was killed!')
})

// When a cache object will be created
mysql.event.on('create', (hash, val, ttl) => {
    console.log('Creating cache object: ', hash, val, ttl)
})

// When a cache object is about to be retrieved
mysql.event.on('get', hash => {
    console.log('Retrieving cache object: ', hash)
})

// When a cache object key gets deleted by mysql.delKey() for example
mysql.event.on('delete', hash => {
    console.log('this cache object was deleted from cache: ', hash)
})

Properties

// Get total cache misses
console.log(mysql.misses)

// Get total cache hits
console.log(mysql.hits)

// Get total qeury requests
console.log(mysql.queries)

// Get total insert queries run
console.log(mysql.inserts)

// Get total delete queries run
console.log(mysql.deletes)

// Get total delete queries run
console.log(mysql.selects)

// Get total updates queries run
console.log(mysql.updates)

// Get total open pool connections
console.log(mysql.poolConnections)

// Get the configured settings for mysql-cache
console.log(mysql.config)

// Get or set the configured TTL for all future made caches
mysql.config.TTL = 5  // TTL is always defined in SECONDS
console.log(mysql.config.TTL)

// Get the mysql2 package mysql variable
console.log(mysql.mysql)

// Get the cache providers available
console.log(mysql.cacheProviders)

API

.query (sql,params,callback,data)

sql:        String    // The sql you want to execute
*params:    Object    // This is used if you want to escape values
callback:   Function  // For getting the (err, res, cache) back of the query.
data:       Object    // One time settings for this query, check below for more

* More about escaping values by using params

Will execute the given SQL and cache the (err, res, cache) if it's a SELECT statement. If the SQL was executed before, it will skip the database request and retrieve it from the cache straight away. Invalid queries will throw a error

Example #1

mysql.query('SELECT id,username,avatar FROM accounts WHERE id = ?', [530], (err, res, cache) => {
    if (err) {
        throw new Error(err)
    }
    console.log(result)
})

Example #2

mysql.query({
    sql:'SELECT 6 + ? AS solution',
    params: [4],
}, (err, res, cache) => {
    if (err) {
        throw new Error(err)
    }
    console.log(result)
})

Example with one time setting per query

// Setting the TTL
mysql.query('SELECT id, username, avatar FROM accounts WHERE id = ?', [530], (err, res, cache) => {
    if (err) {
        throw new Error(err)
    }
    console.log(result)
}, {
    TTL: 6 // Will set TTL to 6 seconds only for this query
})

// Setting the cache option
mysql.query('SELECT id, username, avatar FROM accounts WHERE id = ?', [530], (err, res, cache) => {
    if (err) {
        throw new Error(err)
    }
    console.log(result)
}, {
    cache: false // Will not cache this query
})

// Setting the cache option alternative method
mysql.query({
    sql:'SELECT 6 + 6 AS solution',
    cache: false, // Do not cache this query
}, (err, res, cacheMysql, mysqlCache) => {
    // Do something with your results
})

Example with error handling

mysql.query('SELECT id, username, avatar FROM accounts WHERE id = ?', [530], (err, res, cache) => {
    if (err) {
        throw new Error(err)
    }
    console.log(result)
})

Example with getting some extra information from mysql-cache

mysql.query('SELECT 6 + 6 AS solution', (err, res, cache) => {
    const mysqlCacheHash = cache.hash.slice(0, 12)
    if (mysqlCache.isCache) {
        console.log(mysqlCacheHash + ': is from the cache!')
    } else {
        console.log(mysqlCacheHash + ': is NOT from the cache!')
    }
    console.log('The result of the sql ' + cache.sql + ' = ' + mysqlResult[0].solution)
})

The mysql.query function is using node-mysql for querying. It's wrapping the sql function, check the mysql2 documentation for more information about escaping values

mysql-cache only supports the use of questionmarks in sql at the moment for escaping values

.delKey (id,params)

    id:         String    // The sql in string format of the cache key you are trying to delete
    params:     Object    // This is required if the cache key had any questionmarks (params) in the sql

Deletes a cache key in the cache. You will need to supply a SQL format, this function always expects a callback

Example #1

mysql.delKey('SELECT id,username,avatar FROM accounts WHERE id = ?', [530], err => {
    if (err) {
        throw new Error(err)
    }
    console.log('key deleted!')
})

Example #2

mysql.delKey({
    sql:    'SELECT id,username,avatar FROM accounts WHERE id = ?',
    params: [530],
}, err => {
    if (err) {
        throw new Error(err)
    }
    console.log('key deleted!')
})

This exact SQL is now removed from the cache. Making sure the next time this query is executed it will be retrieved from the database.

.stats (object)

    object:         boolean    // Print in verbose mode or return as a object

Will console.log() some statistics regarding mysql-cache

Example #1

mysql.stats() // default is display via verbose mode

Example #2

console.log(mysql.stats(true))
// Returns: { poolConnections: 0, hits: 3, misses: 1 }

.flush ()

removes all keys and values from the cache, this function always expects a callback

Example

mysql.flush(err => {
    if (err) {
        throw new Error(err)
    }
    console.log('cache flushed!')
})

.killPool ()

Kills the connection pool

Example

mysql.killPool(err => {
    if (err) {
        throw new Error(err)
    }
    console.log('Pool killed!')
})

Important editor notes

Refreshing cache objects

A already cached object can be refreshed (retrieve from db and then re-cache):

mysql.query({
    sql:          'select 1 + 1 as solution',
    refreshCache: true,
},  (err, res, cache) => {
    if (err) {
        throw new Error(err)
    }
    console.log(result) // Even though the query was cached, it will be retrieved from the database for this call and then be re-cached
})

Contact

You can contact me at [e-mail]