Query Builder

QB is a fluent, chainable SQL query builder. The entry point is always DB.table(tableName) — you never instantiate QB directly.

Server-only

DB and QB are server-side globals. They are not available in client scripts.

Getting Started

-- SELECT all online players, ordered by name
local players = DB.table('players')
    :where('online', true)
    :orderBy('name')
    :get()

-- SELECT one player by ID
local player = DB.table('players')
    :where('id', playerId)
    :first()

---

Selecting Columns

By default, * is selected. Use :select() to choose specific columns.

DB.table('players')
    :select('id', 'name', 'job')
    :get()

---

WHERE Clauses

:where(column, value) — equality

:where('job', 'police')

:where(column, operator, value) — custom operator

:where('grade', '>=', 2)
:where('balance', '<', 0)

:orWhere(column, value|operator, value?)

:where('job', 'police'):orWhere('job', 'sheriff')

:whereNull(column) / :whereNotNull(column)

:whereNull('deleted_at')
:whereNotNull('last_seen')

:whereRaw(expr, binds?)

:whereRaw('expires_at < NOW()')
:whereRaw('amount > ?', { 100 })

---

Joins

-- INNER JOIN
DB.table('players')
    :join('accounts', 'players.id', '=', 'accounts.player_id')
    :select('players.name', 'accounts.balance')
    :get()

-- LEFT JOIN
DB.table('players')
    :leftJoin('characters', 'players.id', '=', 'characters.player_id')
    :get()

---

Ordering, Limit, Offset

DB.table('logs')
    :orderBy('created_at', 'desc')   -- or 'asc' (default)
    :orderByDesc('severity')          -- shorthand
    :limit(50)
    :offset(100)
    :get()

---

Terminal Methods

These execute the query and return a result.

:get() — all rows

local rows = DB.table('players'):where('online', true):get()
-- returns table[]

:first() — first row or nil

local player = DB.table('players'):where('id', id):first()
-- returns table|nil

:exists() — boolean

local exists = DB.table('bans'):where('player_id', id):exists()

:count() — integer

local total = DB.table('players'):where('job', 'police'):count()

---

Write Methods

:insert(data) — returns insertId

local id = DB.table('characters'):insert({
    player_id = source,
    name      = 'John Doe',
    job       = 'unemployed',
})

:update(data) — returns affected rows

DB.table('players')
    :where('id', playerId)
    :update({ job = 'police', grade = 3 })

:delete() — returns affected rows

DB.table('logs')
    :where('created_at', '<', cutoffDate)
    :delete()

:increment(column, amount?) / :decrement(column, amount?)

DB.table('accounts'):where('id', accountId):increment('balance', 500)
DB.table('accounts'):where('id', accountId):decrement('balance', 200)
-- amount defaults to 1 if not provided

:upsert(data, updateData?) — INSERT ON DUPLICATE KEY UPDATE

DB.table('player_stats'):upsert(
    { player_id = source, kills = 1, deaths = 0 },  -- insert data
    { kills = QB.raw('`kills` + 1') }               -- on conflict: update
)

If updateData is omitted, all columns from data are used for the update.

:insertIgnore(data) — silently skip duplicate rows

DB.table('unique_achievements'):insertIgnore({
    player_id    = source,
    achievement  = 'first_login',
})
-- returns insertId, or nil if the row was ignored

---

Raw Expressions

Use QB.raw() to embed literal SQL inside :insert(), :update(), or :upsert() data tables.

:update({
    last_seen  = QB.raw('NOW()'),
    playtime   = QB.raw('`playtime` + ?', { sessionSeconds }),
    deleted_at = QB.NULL,   -- set column to NULL
})

QB.NULL is a pre-built sentinel for NULL.

---

Full Example — Economy Transfer

local function transferMoney(fromId, toId, amount)
    DB.transaction(function()
        local from = DB.table('accounts'):where('player_id', fromId):first()
        if not from or from.balance < amount then
            error('insufficient funds')
        end

        DB.table('accounts')
            :where('player_id', fromId)
            :decrement('balance', amount)

        DB.table('accounts')
            :where('player_id', toId)
            :increment('balance', amount)

        DB.table('transactions'):insert({
            from_id    = fromId,
            to_id      = toId,
            amount     = amount,
            created_at = QB.raw('NOW()'),
        })
    end)
end

---

API Reference

Clause methods (all return self for chaining)

Method	Description
:select(...)	Columns to SELECT (default *)
:where(col, val)	AND WHERE col = val
:where(col, op, val)	AND WHERE col op val
:orWhere(col, val|op, val?)	OR WHERE
:whereNull(col)	AND WHERE col IS NULL
:whereNotNull(col)	AND WHERE col IS NOT NULL
:whereRaw(expr, binds?)	AND WHERE with raw SQL fragment
:join(tbl, col1, op, col2)	INNER JOIN
:leftJoin(tbl, col1, op, col2)	LEFT JOIN
:orderBy(col, dir?)	ORDER BY col ASC|DESC
:orderByDesc(col)	ORDER BY col DESC shorthand
:limit(n)	LIMIT n
:offset(n)	OFFSET n

Terminal methods (execute the query)

Method	Returns	Description
:get()	table[]	All matching rows
:first()	table|nil	First matching row (LIMIT 1)
:exists()	boolean	True if any row matches
:count()	integer	Count of matching rows
:insert(data)	integer|nil	INSERT, returns new row ID
:update(data)	integer	UPDATE, returns affected rows
:delete()	integer	DELETE, returns affected rows
:increment(col, amount?)	integer	Increment column by amount (default 1)
:decrement(col, amount?)	integer	Decrement column by amount (default 1)
:upsert(data, updateData?)	integer|nil	INSERT ON DUPLICATE KEY UPDATE
:insertIgnore(data)	integer|nil	INSERT IGNORE

Static helpers

	Description
QB.raw(expr, binds?)	Raw SQL expression sentinel for use in data tables
QB.NULL	Pre-built sentinel for SQL NULL

See Also

• Database →
• Models →
• Migrations →