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()
npm i like-mysql
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
// 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')
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)
// 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) // => 0
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 }, ...]
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) // => undefined
const exists = await db.exists('ips', 'addr = ?', req.ip)
console.log(exists) // => true
const total = await db.count('ips', 'addr = ?', req.ip)
console.log(total) // => 2
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) // => 1
const affectedRows = await db.delete('ips', 'addr = ?', req.ip)
console.log(affectedRows) // => 1
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'
await db.end()
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
Code released under the MIT License.