Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

mysql2-cache

Package Overview
Dependencies
Maintainers
1
Versions
5
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

mysql2-cache - npm Package Compare versions

Comparing version 1.0.1 to 1.3.0

.github/workflows/node.js.yml

195

index.js

@@ -0,6 +1,16 @@

const CONNECTION_IDLE_TIMEOUT = 60 * 1000 // ms
const TCP_KEEPALIVE_TIMEOUT = 2 * 60 * 1000 // ms
const TCP_IDLE_TIMEOUT = 2 * 60 * 1000 + 1 * 10 * 1000 // ms
const DEFAULT_QUEUE_LIMIT = 300
const DEFAULT_CONNECTION_LIMIT = 45
const DEFAULT_CONNECT_TIMEOUT = 2 * 1000 // ms
const DEFAULT_CACHE_TTL = 5 * 60 // s
const DEFAULT_CACHE_CHECKPERIOD = 1 * 60 // s
const DEFAULT_INSECURE_AUTH = true
const debug = require('debug')('mysql2-cache')
const mysql = require('mysql2')
const crypto = require('crypto')
const crypto = require('node:crypto')
const NodeCache = require('node-cache')
const queryCache = new NodeCache({ stdTTL: 5 * 60, checkperiod: 1 * 60 })
const queryCache = new NodeCache({ stdTTL: DEFAULT_CACHE_TTL, checkperiod: DEFAULT_CACHE_CHECKPERIOD })

@@ -10,3 +20,3 @@ const { Console } = require('console')

const ts = new Transform({ transform (chunk, enc, cb) { cb(null, chunk) } })
const logger = new Console({ stdout: ts, stderr: ts, colorMode: true })
const logger = new Console({ stdout: ts, stderr: ts, colorMode: true, inspectOptions: { depth: Infinity, breakLength: Infinity, compact: true } })
function getTable (data) {

@@ -18,2 +28,3 @@ logger.table(data)

debug('init')
debug.inspectOpts = { depth: Infinity, breakLength: Infinity, compact: true }

@@ -23,4 +34,12 @@ module.exports = mysql

module.exports.connect = (config = {}) => {
config.connectionLimit = config.connectionLimit || 15
config.queueLimit = config.queueLimit || 100
// queueLimit shouldn't be 0 as it leads to long pool of lost queries
// in case of zombie sockets instead of throwing error
// https://github.com/sidorares/node-mysql2/blob/master/lib/pool_config.js
config.queueLimit = config.queueLimit || DEFAULT_QUEUE_LIMIT
// default mysql max_connections=151
config.connectionLimit = config.connectionLimit || DEFAULT_CONNECTION_LIMIT
// should be less then TCP_KEEPALIVE_TIMEOUT
config.idleTimeout = config.idleTimeout || CONNECTION_IDLE_TIMEOUT
config.connectTimeout = config.connectTimeout || DEFAULT_CONNECT_TIMEOUT
config.insecureAuth = config.insecureAuth || DEFAULT_INSECURE_AUTH

@@ -32,15 +51,14 @@ const pool = mysql.createPool(config).promise()

qid++
const id = qid
const log = debug.extend(qid)
log(sql, params, {cache: cache, ttl: ttl ? ttl : DEFAULT_CACHE_TTL})
// https://medium.com/@chris_72272/what-is-the-fastest-node-js-hashing-algorithm-c15c1a0e164e
const hash = crypto.createHash('sha1').update(sql + JSON.stringify(params)).digest('base64')
const log = debug.extend(id)
log('%s %j', sql, params)
if (cache && queryCache.has(hash)) {
log('cache hit %s %j %j', hash, queryCache.getStats(), queryCache.keys())
log('Cache hit', hash, queryCache.getStats() /*, queryCache.keys() */)
return queryCache.get(hash)
} else if (cache) {
log('cache missed %j %j', queryCache.getStats(), queryCache.keys())
log('Cache missed', queryCache.getStats() /*, queryCache.keys() */)
}
const [rows, fields] = await pool.query(sql, params).catch(error => {
console.error('[MYSQL] query_error %s %j', sql, params, error)
console.error('[MYSQL] query', sql, params, error)
if (error.message === 'Queue limit reached.') {

@@ -54,5 +72,3 @@ // @todo Graceful server and mysql connections exit

const result = Array.isArray(rows) && rows.length ? rows : false
if (debug.enabled) {
log(getTable(rows))
}
log(getTable(rows))
if (cache) {

@@ -64,3 +80,3 @@ queryCache.set(hash, result, ttl)

pool.qRow = async (sql, params = [], cache = false, ttl = undefined) => {
pool.qRow = pool.selectRow = async (sql, params = [], cache = false, ttl = undefined) => {
const rows = await pool.q(sql, params, cache, ttl)

@@ -70,3 +86,150 @@ return Array.isArray(rows) && rows.length ? rows[0] : false

// @todo insert array of objects
pool.insert = pool.i = async (table, row) => {
qid++
const log = debug.extend(qid)
log('INSERT INTO', table)
log(row)
const [rows, fields] = await pool.query('INSERT INTO ?? SET ?', [table, row])
.catch(error => {
console.error('[MYSQL] insert', table, row, error)
throw error
})
log(rows)
return rows || false
}
pool.update = async (table, row, where = false) => {
qid++
const log = debug.extend(qid)
log('UPDATE', table, row, where)
const _where = where ? 'WHERE ' + Object.keys(where).map(key => key + '=' + pool.escape(where[key])).join(' AND ') : ''
const [rows, fields] = await pool.query(`UPDATE ?? SET ? ${_where}`, [table, row])
.catch(error => {
console.error('[MYSQL] update', table, [row, where], error)
throw error
})
log(rows)
return rows || false
}
pool.delete = pool.del = async (table, where = false) => {
qid++
const log = debug.extend(qid)
log('DELETE FROM', table, where)
const _where = where ? 'WHERE ' + Object.keys(where).map(key => key + '=' + pool.escape(where[key])).join(' AND ') : ''
const [rows, fields] = await pool.query(`DELETE FROM ?? ${_where}`, [table])
.catch(error => {
console.error('[MYSQL] delete', table, where, error)
throw error
})
log(rows)
return rows || false
}
pool.stat = () => {
return {
ALL: pool.pool._allConnections.toArray().length,
// USE: pool.pool._allConnections.toArray().length - pool.pool._freeConnections.toArray().length,
FRE: pool.pool._freeConnections.toArray().length,
QUE: pool.pool._connectionQueue.toArray().length
}
}
pool.cacheFlush = (sql, params) => {
const hash = crypto.createHash('sha1').update(sql + JSON.stringify(params)).digest('base64')
const deleted = queryCache.del(hash)
debug('Cache flush', sql, params, { deleted }, queryCache.getStats())
return deleted
}
pool.cacheFlushAll = () => {
queryCache.flushAll()
debug('Cache flush all', queryCache.getStats())
return true
}
exports.cacheStat = () => {
return queryCache.getStats()
}
pool.on('acquire', (connection) => {
debug('Connection #%s acquired', connection.threadId, pool.stat())
})
pool.on('connection', (connection) => {
debug('Connected #%s to %s:%s', connection.threadId, connection.config.host, connection.config.port, pool.stat())
/**
* tcp_keepalive and ESTABLISHED zombie sockets bug
* https://blog.cloudflare.com/when-tcp-sockets-refuse-to-die/
* https://github.com/mysqljs/mysql/issues/835
*
* tcp_keepalive is off in Node by default
* https://nodejs.org/dist/latest-v20.x/docs/api/net.html#net_socket_setkeepalive_enable_initialdelay
*
* _socket.setKeepAlive(true, 1000 * 60 * 2); // ms
* https://github.com/mysqljs/mysql/issues/1939#issuecomment-365715668
*
* TCP_TIMEOUT = TCP_KEEPIDLE + TCP_KEEPINTVL * TCP_KEEPCNT
* 130 = 120 + 1 * 10
*/
connection.stream.setKeepAlive(true, TCP_KEEPALIVE_TIMEOUT)
/**
* _socket.setTimeout is an alternative:
* https://github.com/nodejs/node/issues/4560#issuecomment-302008479
*
* Set socket idle timeout in milliseconds
* https://nodejs.org/api/net.html#socketsettimeouttimeout-callback
* _socket.setTimeout(1000 * 60 * 15); // ms
*
* Wait for timeout event (node will emit it when idle timeout elapses)
* socket.on('timeout', function () {
* socket.destroy();
* });
*
* Recently added param idleTimeout is also used in mysql.createPool()
* but they both used as there is no guarantee one will help with the bug
*/
connection.stream.setTimeout(TCP_IDLE_TIMEOUT)
connection.stream.on('timeout', () => {
connection.stream.destroy()
connection.destroy()
debug('Connection #%s socket timeout', connection.threadId, pool.stat())
})
/**
* No events emitted on connection close => listen on sockets
* https://github.com/sidorares/node-mysql2/blob/68cc3358121a88f955c0adab95a2d5f3d2b4ecb4/lib/connection.js#L770
*/
connection.stream.on('error', (error) => {
debug('Connection #%s socket error', connection.threadId, pool.stat(), error)
})
connection.stream.on('close', (hadError) => {
debug('Connection #%s socket closed%s', connection.threadId, hadError ? ' on error' : '', pool.stat())
})
connection.on('error', (error) => {
console.error('[MYSQL] Connection error', error) // 'ER_BAD_DB_ERROR'
})
})
pool.on('enqueue', (connection) => {
debug('Connection queued', pool.stat())
})
pool.on('release', (connection) => {
debug('Connection #%d released', connection.threadId, pool.stat())
})
pool.on('error', (...args) => {
console.error('[MYSQL]', ...args)
})
return pool
}
/*
process.on('unhandledRejection', (reason) => { // , promise
console.error('Unhandled rejection:', reason)
})
process.on('uncaughtException', (error) => {
console.error('Uncaught exception:', error)
})
*/

12

package.json
{
"name": "mysql2-cache",
"version": "1.0.1",
"version": "1.3.0",
"description": "✔ MySQL2 upgrade: cache queries, easy shortcuts, logging and debugging.",
"main": "index.js",
"scripts": {
"test": "DEBUG=* node ./test/test.js"
"test": "node ./test/test.js"
},

@@ -30,8 +30,10 @@ "repository": {

"homepage": "https://github.com/2naive/node-mysql2-cache#readme",
"engines": {
"node": ">=14"
},
"dependencies": {
"crypto": "^1.0.1",
"debug": "^4.3.3",
"mysql2": "^2.3.3",
"debug": "^4.3.4",
"mysql2": "^3.2.0",
"node-cache": "^5.1.2"
}
}

33

README.md

@@ -5,6 +5,6 @@ # mysql2-cache

![GitHub release (latest by date)](https://img.shields.io/github/v/release/2naive/node-mysql2-cache)
![GitHub release (latest by date)](https://img.shields.io/github/v/release/2naive/mysql2-cache)
![node-current](https://img.shields.io/node/v/mysql2-cache)
![GitHub Workflow Status](https://img.shields.io/github/workflow/status/2naive/node-mysql2-cache/Node.js%20Package)
![Coveralls github](https://img.shields.io/coveralls/github/2naive/node-mysql2-cache)
![GitHub Workflow Status](https://img.shields.io/github/actions/workflow/status/2naive/mysql2-cache/node.js.yml?branch=master)
![Coveralls github](https://img.shields.io/coveralls/github/2naive/mysql2-cache)
![Standard - JavaScript Style Guide](https://img.shields.io/badge/code_style-standard-brightgreen.svg)

@@ -43,13 +43,12 @@

})
db.q('SELECT * FROM test_table WHERE id=?', 1, true) // use cache with default ttl
db.q('SELECT * FROM test_table WHERE id=?', 1, true) // use cache with default ttl=300s
db.q('SELECT * FROM test_table WHERE id=?', 1, true, 300) // ttl in seconds
```
## Debugging easy
### Debugging easy
Pass `DEBUG=mysql2-cache*` environment variable to pretty debug.
```bash
mysql2-cache:1 SELECT * FROM test_table undefined +0ms
mysql2-cache:1 SELECT * FROM test_table WHERE age > ? [1] +0ms
mysql2-cache:1 ┌─────────┬─────────┬─────┐

@@ -64,2 +63,22 @@ mysql2-cache:1 │ (index) │ name │ age │

## API
You may use all [MySQL2](https://github.com/sidorares/node-mysql2) methods plus:
### async q(sql, params = [], cache = false, ttl = undefined)
### async insert(table, row)
### async update(table, row, where = false)
### async delete(table, row, where = false)
### stat()
### cacheFlush(sql, params)
### cacheFlushAll()
### cacheStat()
## Getting help

@@ -66,0 +85,0 @@

150

test/test.js

@@ -1,3 +0,5 @@

// @todo Node default test runner
// @todo Node default test runner or Jest
const mysql = require('../index.js')
const debug = require('debug')
debug.enable('mysql2-cache*')
// https://github.com/sidorares/node-mysql2/blob/master/examples/server.js

@@ -8,66 +10,94 @@ const server = mysql.createServer()

const id = Math.floor(Math.random() * 100)
conn.serverHandshake({
protocolVersion: 10,
serverVersion: '5.6.10',
connectionId: id,
statusFlags: 2,
characterSet: 8,
authCallback: (params) => {
conn.writeOk()
try {
conn.serverHandshake({
protocolVersion: 10,
serverVersion: '5.6.10',
connectionId: id,
statusFlags: 2,
characterSet: 8,
authCallback: (params) => {
conn.writeOk()
conn.sequenceId = 0
},
capabilityFlags: 2181036031
})
} catch (error) {
console.error(error)
}
conn.on('query', query => {
try {
// https://github.com/sidorares/node-mysql2/issues/528#issuecomment-944949065
// https://github.com/sidorares/node-mysql2/blob/master/lib/constants/types.js
conn.sequenceId = 1
conn.writeColumns([
{
catalog: 'def',
schema: 'test',
table: 'test_table',
orgTable: 'test_table',
name: 'name',
orgName: 'name',
characterSet: 33,
columnLength: 384,
columnType: 253,
flags: 0,
decimals: 0
},
{
catalog: 'def',
schema: 'test',
table: 'test_table',
orgTable: 'test_table',
name: 'age',
orgName: 'age',
characterSet: 33,
columnLength: 384,
columnType: 2,
flags: 0,
decimals: 0
}
])
conn.writeTextRow(['Alice', id])
conn.writeTextRow(['Bob', 42])
conn.writeEof()
conn.sequenceId = 0
},
capabilityFlags: 2181036031
// conn.close()
} catch (error) {
console.log('MySQL server on.query error', error)
}
})
conn.on('query', query => {
// https://github.com/sidorares/node-mysql2/issues/528#issuecomment-944949065
// https://github.com/sidorares/node-mysql2/blob/master/lib/constants/types.js
conn.sequenceId = 1
conn.writeColumns([
{
catalog: 'def',
schema: 'test',
table: 'test_table',
orgTable: 'test_table',
name: 'name',
orgName: 'name',
characterSet: 33,
columnLength: 384,
columnType: 253,
flags: 0,
decimals: 0
},
{
catalog: 'def',
schema: 'test',
table: 'test_table',
orgTable: 'test_table',
name: 'age',
orgName: 'age',
characterSet: 33,
columnLength: 384,
columnType: 2,
flags: 0,
decimals: 0
}
])
conn.writeTextRow(['Alice', id])
conn.writeTextRow(['Bob', 42])
conn.writeEof()
conn.sequenceId = 0
conn.close()
})
})
const db = mysql.connect()
db.q('SELECT * FROM test_table').then(res => console.dir)
db.q('SELECT * FROM test_table', {}, true).then((res) => {
db.q('SELECT * FROM test_table', {}, true).then((res) => {
server.close()
process.exit(0)
const db = mysql.connect({
connectionLimit: 2,
maxIdle: 1,
idleTimeout: 2000
});
(async () => {
await db.q('DROP TABLE IF EXISTS test')
await db.q('CREATE TABLE test (`name` VARCHAR(50) NULL DEFAULT NULL, `age` INT(10) NULL DEFAULT NULL)')
db.insert('test', { name: 'Alice', age: 92 })
db.insert('test', { name: 'Bob', age: 42 })
// no cache
db.q('SELECT * FROM test LIMIT 1').then(res => console.dir)
// cache
db.q('SELECT * FROM test LIMIT 1', [], true).then((res) => {
db.q('SELECT * FROM test LIMIT 1', [], true)
})
console.log('✅ ', res)
})
// cache, flush, flush all
db.q('SELECT * FROM test WHERE 1=0', [], true).then((res) => {
db.cacheFlush('SELECT * FROM test LIMIT 1', [])
db.cacheFlushAll()
})
db.del('test', { age: 92 })
await db.i('test', { name: 'Mark', age: 36 })
await db.update('test', { age: 13 }, { age: 36 })
await db.delete('test', { age: 13 })
await db.insert('test', { name: 'Mark', age: 11 })
await db.q('SELECT * FROM test')
process.exit(0)
})()
process.on('exit', code => {
console.log(`About to exit with code: ${code}`)
})
// db.q('Unhandled rejection')
// throw(new Error('Unhandled error'))
SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc