like-mysql v2.0.2
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 [rows, cols] = await db.execute('SELECT * FROM `ips` WHERE `addr` = ?', [req.ip])
// query
const [rows, cols] = 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) // => 0
select
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) // => undefined
exists
const exists = await db.exists('ips', 'addr = ?', req.ip)
console.log(exists) // => true
count
const total = await db.count('ips', 'addr = ?', req.ip)
console.log(total) // => 2
update
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
delete
const affectedRows = await db.delete('ips', 'addr = ?', req.ip)
console.log(affectedRows) // => 1
transaction
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.
2 years ago
2 years ago
2 years ago
3 years ago
3 years ago
3 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago