Loading content...
Database
A high-level oxmysql wrapper — idempotent migrations and a simple CRUD, server-side.
A high-level oxmysql wrapper — idempotent migrations and a simple CRUD, server-side.
lib.db wraps oxmysql with idempotent table / column creation and a small CRUD. Server-side
only — keep all your database code in server/.
lib.db.ensureTable('myscript_logs', {
{ name = 'id', type = 'INT', primary = true, autoIncrement = true },
{ name = 'identifier', type = 'VARCHAR(60)', notNull = true },
{ name = 'action', type = 'VARCHAR(64)', notNull = true },
{ name = 'created_at', type = 'TIMESTAMP', default = { raw = 'CURRENT_TIMESTAMP' } },
})
lib.db.ensureColumn('myscript_logs', { name = 'duration_ms', type = 'INT' })Both are idempotent — safe to run on every start. A column def is { name, type, primary?, autoIncrement?, notNull?, default?, unique? }.
| Function | Returns | Description |
|---|---|---|
select(table, where?, columns?) | table[] | Rows matching where (implicit AND). |
selectOne(table, where, columns?) | table? | First row, or nil. |
insert(table, data) | number | The new auto-increment id. |
update(table, data, where) | number | Affected rows. |
delete(table, where) | number | Affected rows. |
select(table, where?, columns?)table[]where (implicit AND).selectOne(table, where, columns?)table?nil.insert(table, data)numberupdate(table, data, where)numberdelete(table, where)numberlocal id = lib.db.insert('myscript_logs', { identifier = ident, action = 'kick' })
local rows = lib.db.select('myscript_logs', { identifier = ident })
local one = lib.db.selectOne('myscript_logs', { id = 42 })
lib.db.update('myscript_logs', { action = 'banned' }, { id = 42 })
lib.db.delete('myscript_logs', { identifier = ident })where = { col = nil } becomes col IS NULL. Booleans store as 0 / 1.
lib.db.query(sql, params?), lib.db.scalar(...) and lib.db.single(...) pass through to
oxmysql for anything the CRUD doesn't cover. lib.db.transaction(queries) runs several
parameterized queries atomically and returns a boolean.
local ok = lib.db.transaction({
{ query = 'UPDATE accounts SET balance = balance - ? WHERE id = ?', params = { 100, 1 } },
{ query = 'UPDATE accounts SET balance = balance + ? WHERE id = ?', params = { 100, 2 } },
})Requires oxmysql (dependencies { 'oxmysql' }). select without where returns the whole table — always filter on big ones. JSON columns need json.encode yourself. Never concatenate SQL — always pass params.