Project Kura | High Performance FiveM Server Core

Recommended style

For Lua resources that own their schema, prefer generated table and column references from kura.db.tables.

The examples below use users as an illustration. In a real server, use whatever table names and field names your schema.ts generates.

local users = kura.db.tables.users

local rows = kura.db.select({ users.id, users.username, users.createdAt })
  :from(users)
  :where(op.eq(users.id, 42))
  :await()

String-based calls are still supported:

local rows = kura.db.select({ 'id', 'username' })
  :from('users')
  :where(op.eq('id', 42))
  :await()

String inputs are identifiers

Plain string table and column inputs in the builder are quoted as SQL identifiers for compatibility and convenience. They are not raw SQL fragments or expression slots. For custom SQL expressions or aliases, use the explicit raw SQL APIs.

Basic select

Select all rows and all schema-known columns:

local users = kura.db.tables.users

local rows = kura.db.select()
  :from(users)
  :await()

const rows = await db.select().from(users);

When you select from a generated table, KuraDB aliases SQL column names back to your Lua field names. If your schema defines createdAt = defineColumn('created_at', 'date'), builder results come back as row.createdAt.

Partial select

Pick specific columns:

local users = kura.db.tables.users

local rows = kura.db.select({ users.id, users.username })
  :from(users)
  :await()

const rows = await db.select({ id: 'id', name: 'username' }).from(users);

Filtering

Use operators to filter results. op is available globally.

local users = kura.db.tables.users

local rows = kura.db.select({ users.id, users.username })
  :from(users)
  :where(op.eq(users.id, 42))
  :await()

import { eq } from './queryBuilder/operators';

const rows = await db.select().from(users).where(eq('id', 42));

Combining filters

local users = kura.db.tables.users

local rows = kura.db.select()
  :from(users)
  :where(op.and_(
    op.eq(users.username, 'john'),
    op.gt(users.id, 3),
    op.isNull(users.deletedAt)
  ))
  :await()

import { and, eq, gt, isNull } from './queryBuilder/operators';

const rows = await db.select()
  .from(users)
  .where(and(eq('username', 'john'), gt('id', 3), isNull('deleted_at')));

Order, limit, offset

local users = kura.db.tables.users

local rows = kura.db.select({ users.id, users.createdAt })
  :from(users)
  :where(op.lt(users.id, 100))
  :orderBy({ [users.createdAt] = 'desc' })
  :limit(10)
  :offset(20)
  :await()

String order keys still work:

local rows = kura.db.select()
  :from('users')
  :orderBy({ created_at = 'desc' })
  :await()

Result helpers

Use helpers to avoid manual rows[1] boilerplate:

local users = kura.db.tables.users

local firstUser = kura.db.select():from(users):first():await()
local maybeUser = kura.db.select():from(users):where(op.eq(users.id, 42)):maybeSingle():await()
local exactUser = kura.db.select():from(users):where(op.eq(users.id, 42)):single():await()
local hasUsers = kura.db.select():from(users):exists():await()

Helper behavior:

:first() returns the first row or nil
:maybeSingle() returns one row or nil, and errors if more than one row matches
:single() returns exactly one row, and errors if zero or more than one row matches
:exists() returns true or false
:exists() compiles an efficient SELECT EXISTS(SELECT 1 ...) AS "exists" query and ignores the selected column list

Row locking

Use :forUpdate() inside a transaction-bound builder when you need row-level locks:

local users = kura.db.tables.users

kura.db.transaction.await(function(query, tx)
  local user = tx.select()
    :from(users)
    :where(op.eq(users.id, 42))
    :forUpdate()
    :single()
    :await()

  return user
end)

Outside a transaction context, :forUpdate() raises an error. Use it through kura.db.transaction(function(query, tx) ... end) or kura.db.transaction.await(...), and call tx.select():forUpdate().

Inspect the generated SQL

local users = kura.db.tables.users

local sql, params = kura.db.select({ users.username, users.createdAt })
  :from(users)
  :where(op.eq(users.username, 'john'))
  :toSQL()

-- sql:
-- SELECT "username" AS "username", "created_at" AS "createdAt"
-- FROM "public"."users"
-- WHERE "username" = $1
--
-- params: { 'john' }

const { sql, parameters } = db.select()
  .from(users)
  .where(eq('username', 'john'))
  .toSQL();

Select

On this page