larvitdb
DB pool wrapper for node.js
This module is used to share a mysql/mariadb pool of connections between modules, classes, files etc.
It also logs with winston if there is a database error, so you do not need to fetch the database error manually each time.
Installation
npm i larvitdb
Usage
The module must first be required and then configured.
Make this in your main application file:
const winston = require('winston'),
log = winston.createLogger({'transports': [new winston.transports.Console()]}),
db = require('larvitdb');
db.setup({
'host': '127.0.0.1',
'socketPath': '/var/run/mysqld/mysqld.sock',
'connectionLimit': 10,
'user': 'foo',
'password': 'bar',
'charset': 'utf8_general_ci',
'supportBigNumbers': true,
'database': 'my_database_name',
'log': log
});
See list of native options here. Then you can just require the module in your other files for usage, like this:
A direct query
const db = require('larvitdb');
db.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
console.log('dbmodel: The solution is: ', rows[0].solution);
});
Or, if a connection is needed:
const db = require('larvitdb');
db.pool.getConnection(function(err, dbCon) {
const sql = 'SELECT * FROM users WHERE username LIKE ' + dbCon.escape(postData);
dbCon.query(sql, function(err, rows) {
dbCon.release();
if (err)
throw err;
});
});
You dont need to get a connection to escape though. You can do like this:
const db = require('larvitdb');
db.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {
});
Advanced configuration - recoverable errors
Sometimes recoverable errors happend in the database. One such example is deadlocks in a cluster. Here we'll provide an example of how to make the database layer retry a query 5 times if a deadlock happends, before giving up.
const db = require('larvitdb');
db.setup({
'connectionLimit': 10,
'socketPath': '/var/run/mysqld/mysqld.sock',
'user': 'foo',
'password': 'bar',
'charset': 'utf8_general_ci',
'supportBigNumbers': true,
'database': 'my_database_name',
'retries': 5,
'recoverableErrors': ['PROTOCOL_CONNECTION_LOST', 'ER_LOCK_DEADLOCK']
});
db.query('DELETE FROM tmpTable LIMIT 10');
Advanced configuration - long running queries
By default a warning is logged if a query runs longer than 10k ms (10 seconds). This number can be tweaked like this for 20 seconds:
db.setup({
...
'longQueryTime': 20000
});
or like this to disable the warnings:
db.setup({
...
'longQueryTime': false
});
## Custom functions
### Remove all tables from current database
This function will clean the current database from all tables.
```javascript
const db = require('larvitdb');
db.removeAllTables();
Important about time zones!
All sessions with the database will be set to UTC time!
When setting datetime stuff, use the javascript native Date object, like this:
db.query('INSERT INTO users (created, username) VALUES(?,?)', [new Date(), 'foobar']);
If you do, this library will convert the time zone info for you.
However, please note that all date time you get back from the database will be in UTC.
Version history
2.0.0
- Always set all new sessions to UTC time zone
- Convert Date objects to UTC datetimestamps that fits MariaDB and MySQL
Major from 1.x to 2.0 since this might break functionality for some implementations.