simple-mysql-orm v1.2.7
like-mysql
Simple and intuitive ORM for MySQL
const mysql = require('like-mysql');
const db = mysql.createPool({ /*config*/ });
// wait until a connection is established
db.waitConnection(); // => Promise as all methods
// INSERT INTO ip (addr, hits) VALUES (?, ?)
db.insert('ip', { addr: req.ip, hits: 0 });
// SELECT addr, hits FROM ip WHERE addr = ?
db.select('ip', ['addr', 'hits'], 'addr = ?', req.ip);
// SELECT addr, hits FROM ip WHERE addr = ? LIMIT 1
db.selectOne('ip', ['addr', 'hits'], 'addr = ?', req.ip);
// SELECT EXISTS(SELECT 1 FROM ip WHERE addr = ? LIMIT 1)
db.exists('ip', 'addr = ?', req.ip);
// SELECT COUNT(1) FROM ip WHERE addr = ?
db.count('ip', 'addr = ?', req.ip);
// UPDATE ip SET hits = ? WHERE addr = ?
db.update('ip', { hits: 1 }, 'addr = ?', req.ip);
// UPDATE ip SET hits = hits + ? WHERE userid = ?
db.update('ip', [{ hits: 'hits + ?' }, 1], 'addr = ?', req.ip);
// DELETE FROM ip WHERE addr = ?
db.delete('ip', 'addr = ?', req.ip);
// getConnection, beginTransaction, callback, commit/rollback, release
db.transaction(async function (conn) {
await conn.insert('users', { id: 'lukks', password: 'hwy' });
await conn.insert('profile', { id: 'lukks', name: 'Lucas' });
});
Install
npm i like-mysql
Features
MySQL
sidorares/node-mysql2 as client, it's already well documented.\
Operations are prepared statements made by execute
.\
Promise version. All custom methods are also promised.
Properties
After every execution the next variables are overwritten for immediate usage:
// on every operation:
db.sql: String
db.values: Array
// only on select, selectOne, exists or count:
db.rows: Array
db.fields: Array
// only on insert, update or delete:
db.insertId: Number
db.fieldCount: Number
db.affectedRows: Number
// only on update:
db.changedRows: Number
Methods
db.insert(table: String, data: Object): Object
db.select(table: String, cols: Array, find?: String, ...any): Array
db.selectOne(table: String, cols: Array, find?: String, ...any): Object | undefined
db.exists(table: String, find?: String, ...any): Boolean
db.count (table: String, find?: String, ...any): Number
db.update(table: String, data: Object, find?: String, ...any): Object
db.update(table: String, data: Array[Object, ...any], find?: String, ...any): Object
db.delete(table: String, find?: String, ...any): Object
db.transaction(callback: Function): any | undefined
db.waitConnection(retry = 5: Number, time = 500: Number): undefined
transaction
and waitConnection
methods only available on pool instances.
Automatic WHERE
when find
argument doesn't start with:\
ORDER BY
, LIMIT
or GROUP BY
You still can use all other node-mysql2 methods like execute
, query
, etc.
Examples
insert
// INSERT INTO ip (addr, hits) VALUES (?, ?)
let a = await db.insert('ip', { addr: req.ip, hits: 0 });
console.log(db.insertId); // 1336
let b = await db.insert('ip', { addr: req.ip, hits: 0 });
console.log(db.insertId); // 1337
console.log(a); // { fieldCount: 0, affectedRows: 1, insertId: 1336, ... }
console.log(b); // { fieldCount: 0, affectedRows: 1, insertId: 1337, ... }
select
// SELECT addr, hits FROM ip ORDER BY hits DESC
let a = await db.select('ip', ['addr', 'hits'], 'ORDER BY hits DESC');
console.log(db.rows); // [{ addr: '8.8.8.8', hits: 6 }, ...]
// SELECT addr, hits FROM ip WHERE addr = ?
let b = await db.select('ip', ['addr', 'hits'], 'addr = ?', req.ip);
console.log(db.rows); // [{ addr: '8.8.4.4', hits: 2 }]
console.log(a); // [{ addr: '8.8.8.8', hits: 6 }, ...]
console.log(b); // [{ addr: '8.8.4.4', hits: 2 }]
selectOne
// SELECT addr, hits FROM ip WHERE addr = ? LIMIT 1
let a = await db.selectOne('ip', ['addr', 'hits'], 'addr = ?', req.ip);
console.log(db.rows); // [{ addr: '8.8.4.4', hits: 2 }]
// SELECT addr, hits FROM ip WHERE addr = ? LIMIT 1
let b = await db.selectOne('ip', ['addr', 'hits'], 'addr = ?', '0.0.0.0');
console.log(db.rows); // []
console.log(a); // { addr: '8.8.4.4', hits: 2 }
console.log(b); // undefined
exists
// SELECT EXISTS(SELECT 1 FROM ip WHERE addr = ? LIMIT 1)
let a = await db.exists('ip', 'addr = ?', req.ip);
console.log(a); // true
count
// SELECT COUNT(1) FROM ip WHERE addr = ?
let a = await db.count('ip', 'addr = ?', req.ip);
console.log(a); // 2
update
// UPDATE ip SET hits = ? WHERE addr = ?
let a = await db.update('ip', { hits: 1 }, 'addr = ?', req.ip);
console.log(db.affectedRows); // 1
// UPDATE ip SET hits = hits + ? WHERE userid = ?
let b = await db.update('ip', [{ hits: 'hits + ?' }, 1], 'addr = ?', req.ip);
console.log(db.affectedRows); // 1
console.log(a); // { fieldCount: 0, affectedRows: 1, insertId: 0, changedRows: 1, ... }
console.log(b); // { fieldCount: 0, affectedRows: 1, insertId: 0, changedRows: 1, ... }
delete
// DELETE FROM ip WHERE addr = ?
let a = await db.delete('ip', 'addr = ?', req.ip);
console.log(db.affectedRows); // 1
console.log(a); // { fieldCount: 0, affectedRows: 1, insertId: 0, ... }
transaction
Normally with a pool you do something like:
conn = pool.getConnection
conn.beginTransaction
conn.execute 'INSERT INTO users (id, password) VALUES (?, ?)'
conn.execute 'INSERT INTO profile (id, name) VALUES (?, ?)'
conn.commit
conn.release
Also checking different catchs to release and/or rollback.
This method simplifies all that and you just do the important part:
await db.transaction(async function (conn) {
await conn.insert('users', { id: 'lukks', password: 'hwy' });
await conn.insert('profile', { id: 'lukks', name: 'Lucas' });
});
The callback has the connection as context, so can avoid the first argument:
await db.transaction(async function () {
await this.insert(...);
await this.insert(...);
});
You can also return a custom value:
let result = await db.transaction(async function () {
await this.insert(...);
return 'custom value';
});
console.log(result); // => 'custom value'
waitConnection
Very useful to wait the database connection started by docker-compose.
db.waitConnection().then(main);
async function main () {
// ...
await db.query(...);
// ...
}
Tests
Start a database instance
docker run --rm -p 3306:3306 -e MYSQL_ROOT_USER=root -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0
Wait a few seconds for instance creation and run tests
npm test
Stop container and due --rm will be auto deleted
docker ps
docker stop cc6
License
Code released under the MIT License.