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 = const mysql = host: '' user: '' password: '' database: '' cacheProvider: 'LRU' mysql
3. Do awesome stuff!
// Start executing SQL like you are used to using the mysql module mysql
In-depth configuration
Here you can have a overview of a more defined mysql-cache object
const mysql = // 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.debsudo dpkg -i libc6_2.17-0ubuntu4_amd64.deb
Getting make errors on yarn/npm install (ubuntu)
sudo apt-get install python-software-propertiessudo add-apt-repository ppa:ubuntu-toolchain-r/testsudo apt-get updatesudo apt-get install gcc-5 g++-5sudo update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-5 80 --slave /usr/bin/g++ g++ /usr/bin/g++-5sudo update-alternatives --config gcc //
Benchmarks
Edit the file settings.js make sure it reflects your mysql database settings Then execute in the mysql-cache root directory:
node benchmark/samequeryjs // Test same repeating select queriesnode benchmark/randomqueryjs // Test random select queriesnode benchmark/createhashjs // Test the hash speed
Example output:
Events
// When you want to know when you are connectedmysqlevent // When all the cache gets flushed, by mysql.flush() for examplemysqlevent // When a cache object was found when a query was runmysqlevent // When a cache object was NOT found when a query was runmysqlevent // When a query was run (pre-cache)mysqlevent // When a sql is fired to the databasemysqlevent // When a pool connection is accquiredmysqlevent // When a pool connection is closedmysqlevent // When a pool connection has been killedmysqlevent // When a cache object will be createdmysqlevent // When a cache object is about to be retrievedmysqlevent // When a cache object key gets deleted by mysql.delKey() for examplemysqlevent
Properties
// Get total cache missesconsole // Get total cache hitsconsole // Get total qeury requestsconsole // Get total insert queries runconsole // Get total delete queries runconsole // Get total delete queries runconsole // Get total updates queries runconsole // Get total open pool connectionsconsole // Get the configured settings for mysql-cacheconsole // Get or set the configured TTL for all future made cachesmysqlconfigTTL = 5 // TTL is always defined in SECONDSconsole // Get the mysql2 package mysql variableconsole // Get the cache providers availableconsole
API
.query (sql,params,callback,data)
sql: String // The sql you want to execute*params: Object // This is used if you want to escape valuescallback: 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
Example #2
mysql
Example with one time setting per query
// Setting the TTLmysql // Setting the cache optionmysql // Setting the cache option alternative methodmysql
Example with error handling
mysql
Example with getting some extra information from mysql-cache
mysql
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
Example #2
mysql
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 // default is display via verbose mode
Example #2
console// Returns: { poolConnections: 0, hits: 3, misses: 1 }
.flush ()
removes all keys and values from the cache, this function always expects a callback
Example
mysql
.killPool ()
Kills the connection pool
Example
mysql
Important editor notes
Refreshing cache objects
A already cached object can be refreshed (retrieve from db and then re-cache):
mysql
Contact
You can contact me at specamps@gmail.com