like-mysql
Simple and intuitive ORM for MySQL
const mysql = require('like-mysql')
// create a pool easily with good defaults
const db = mysql('127.0.0.1:3306', 'root', 'secret', 'myapp')
// wait until a connection is established
await db.ready()
// INSERT INTO `ips` (`addr`, `hits`) VALUES (?, ?)
const id = await db.insert('ips', { addr: req.ip, hits: 0 })
// SELECT `addr`, `hits` FROM `ips` WHERE addr = ?
const rows = await db.select('ips', ['addr', 'hits'], 'addr = ?', req.ip)
// SELECT `addr`, `hits` FROM `ips` WHERE addr = ? LIMIT 1
const row = await db.selectOne('ips', ['addr', 'hits'], 'addr = ?', req.ip)
// SELECT EXISTS(SELECT 1 FROM `ips` WHERE addr = ? LIMIT 1)
const exists = await db.exists('ips', 'addr = ?', req.ip)
// SELECT COUNT(1) FROM `ips` WHERE addr = ?
const count = await db.count('ips', 'addr = ?', req.ip)
// UPDATE `ips` SET `hits` = ? WHERE addr = ? LIMIT 1
await db.update('ips', { hits: 1 }, 'addr = ? LIMIT 1', req.ip)
// UPDATE `ips` SET `hits` = hits + ? WHERE addr = ?
await db.update('ips', [{ hits: 'hits + ?' }, 1], 'addr = ?', req.ip)
// DELETE FROM `ips` WHERE addr = ? LIMIT 1
await db.delete('ips', 'addr = ? LIMIT 1', req.ip)
// getConnection, beginTransaction, callback, commit/rollback, release
await db.transaction(async function (conn) {
const id = await conn.insert('users', { username: 'lukks', ... })
await conn.insert('profiles', { owner: id, ... })
})
// execute
const [res, fields] = await db.execute('SELECT * FROM `ips` WHERE `addr` = ?', [req.ip])
// query
const [res, fields] = await db.query('SELECT * FROM `ips` WHERE `addr` = "8.8.8.8"')
// end pool
await db.end()Install
npm i like-mysql
Description
node-mysql2 is used to create the MySQL pool.
like-sql is used to build the SQL queries.
Operations are prepared statements made by execute.
Promise version. All custom methods are also promised.
Automatic WHERE when find argument doesn't start with:
ORDER BY, LIMIT or GROUP BY
Examples
constructor
// host:port
const db = mysql('127.0.0.1:3306', 'root', 'secret', 'mydb')
// socketPath
const db = mysql('/var/lib/mysql/mysql.sock', 'root', 'secret', 'mydb')ready
Wait for database started by docker-compose, etc.
// default timeout (15s)
await db.ready() // will throw in case is not able to connect
// custom timeout
await db.ready(5000)insert
// with autoincrement id:
const insertId = await db.insert('ips', { addr: req.ip, hits: 0 })
console.log(insertId) // => 1336
// otherwise it always returns zero:
const insertId = await db.insert('config', { key: 'title', value: 'Database' })
console.log(insertId) // => 0select
const rows = await db.select('ips', ['*'], 'addr = ?', req.ip)
console.log(rows) // => [{ id: 2, addr: '8.8.4.4', hits: 2 }]
const rows = await db.select('ips', ['addr', 'hits'], 'ORDER BY hits DESC')
console.log(rows) // => [{ addr: '8.8.8.8', hits: 6 }, { addr: '8.8.4.4', hits: 2 }, ...]selectOne
const row = await db.selectOne('ips', ['addr', 'hits'], 'addr = ?', req.ip)
console.log(row) // => { addr: '8.8.4.4', hits: 2 }
const row = await db.selectOne('ips', ['addr', 'hits'], 'addr = ?', '0.0.0.0')
console.log(row) // => undefinedexists
const exists = await db.exists('ips', 'addr = ?', req.ip)
console.log(exists) // => truecount
const total = await db.count('ips', 'addr = ?', req.ip)
console.log(total) // => 2update
const changedRows = await db.update('ips', { hits: 1 }, 'addr = ?', req.ip)
console.log(changedRows) // => 1
const changedRows = await db.update('ips', [{ hits: 'hits + ?' }, 1], 'addr = ?', req.ip)
console.log(changedRows) // => 1delete
const affectedRows = await db.delete('ips', 'addr = ?', req.ip)
console.log(affectedRows) // => 1transaction
Normally with a pool you do something like:
conn = pool.getConnection()conn.beginTransaction()conn.execute('INSERT INTO users (username, password) VALUES (?, ?)')conn.execute('INSERT INTO profile (owner, 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) {
const id = await conn.insert('users', { username: 'lukks', ... })
await conn.insert('profiles', { owner: id, ... })
})You can also return a custom value:
const result = await db.transaction(async function (conn) {
await conn.insert(...)
return 'custom value'
})
console.log(result) // => 'custom value'end
await db.end()Tests
Start a database instance
docker run --rm -p 3305:3306 -e MYSQL_ROOT_USER=root -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0
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.