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()

// 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')

// 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

await db.transaction(async function (conn) {
  const id = await conn.insert('users', { username: 'lukks', ... })
  await conn.insert('profiles', { owner: id, ... })
})

const result = await db.transaction(async function (conn) {
  await conn.insert(...)
  return 'custom value'
})

console.log(result) // => 'custom value'

await db.end()