Query Builder API

Sleet ORM provides a fluent query builder interface that allows you to construct SQL queries in a chainable, readable manner.

Database Connection

First, create a database connection:

local sl = require 'sleet'
local schema = require 'schema'
local db = sl.connect()

SELECT Queries

Basic Selection

-- Select all columns from a table
local players = db.select()
    .from(schema.players)
    .execute()

-- Select specific columns
local playerNames = db.select({ schema.players.id, schema.players.name })
    .from(schema.players)
    .execute()

WHERE Conditions

-- Simple equality
local player = db.select()
    .from(schema.players)
    .where(sl.eq(schema.players.identifier, 'steam:123456'))
    .execute()

-- Multiple conditions with AND
local richAdmins = db.select()
    .from(schema.players)
    .where(sl.and_(
        sl.eq(schema.players.is_admin, true),
        sl.gte(schema.players.bank, 10000)
    ))
    .execute()

-- OR conditions
local activeOrRich = db.select()
    .from(schema.players)
    .where(sl.or_(
        sl.eq(schema.players.job, 'police'),
        sl.gte(schema.players.money, 50000)
    ))
    .execute()

Ordering and Limiting

-- Order by single column
local topPlayers = db.select()
    .from(schema.players)
    .orderBy(schema.players.bank, 'desc')
    .execute()

-- Order by multiple columns
local sortedPlayers = db.select()
    .from(schema.players)
    .orderBy(schema.players.job, 'asc')
    .orderBy(schema.players.bank, 'desc')
    .execute()

-- Limit results
local firstTenPlayers = db.select()
    .from(schema.players)
    .limit(10)
    .execute()

-- Pagination with offset
local page2Players = db.select()
    .from(schema.players)
    .orderBy(schema.players.created_at, 'desc')
    .limit(10)
    .offset(10)
    .execute()

Soft Delete Support

-- Normal queries exclude soft-deleted records
local activePlayers = db.select()
    .from(schema.players)
    .execute()

-- Include soft-deleted records
local allPlayers = db.select()
    .from(schema.players)
    .withDeleted()
    .execute()

-- Only soft-deleted records
local deletedPlayers = db.select()
    .from(schema.players)
    .withDeleted()
    .where(sl.isNotNull(schema.players.deleted_at))
    .execute()

WHERE Operators

Comparison Operators

-- Equality and inequality
.where(sl.eq(schema.players.job, 'police'))
.where(sl.ne(schema.players.job, 'unemployed'))

-- Numeric comparisons
.where(sl.gt(schema.players.money, 1000))    -- greater than
.where(sl.gte(schema.players.bank, 5000))    -- greater than or equal
.where(sl.lt(schema.players.money, 100))     -- less than
.where(sl.lte(schema.players.bank, 1000))    -- less than or equal

NULL Checks

-- Check for NULL values
.where(sl.isNull(schema.players.deleted_at))
.where(sl.isNotNull(schema.players.last_seen))

Pattern Matching

-- LIKE patterns
.where(sl.like(schema.players.name, '%john%'))
.where(sl.notLike(schema.players.identifier, 'discord:%'))

Logical Operators

-- AND conditions
.where(sl.and_(
    sl.eq(schema.players.is_admin, true),
    sl.gt(schema.players.money, 10000)
))

-- OR conditions  
.where(sl.or_(
    sl.eq(schema.players.job, 'police'),
    sl.eq(schema.players.job, 'ambulance')
))

-- Complex nested conditions
.where(sl.and_(
    sl.eq(schema.players.is_admin, true),
    sl.or_(
        sl.gt(schema.players.money, 50000),
        sl.gt(schema.players.bank, 100000)
    )
))

INSERT Operations

-- Insert single record
local playerId = db.insert(schema.players)
    .values({
        identifier = 'steam:123456',
        name = 'John Doe',
        money = 5000,
        bank = 10000
    })
    .execute()

-- Insert multiple records
local playerIds = db.insert(schema.players)
    .values({
        {identifier = 'steam:111111', name = 'Alice'},
        {identifier = 'steam:222222', name = 'Bob'},
        {identifier = 'steam:333333', name = 'Charlie'}
    })
    .execute()

UPDATE Operations

-- Update with static values
local affected = db.update(schema.players)
    .set({
        name = 'New Name',
        job = 'police'
    })
    .where(sl.eq(schema.players.identifier, 'steam:123456'))
    .execute()

-- Update with SQL expressions
local affected = db.update(schema.players)
    .set({
        money = sl.sql('`money` - ?', { 1000 }),
        bank = sl.sql('`bank` + ?', { 1000 })
    })
    .where(sl.and_(
        sl.eq(schema.players.identifier, 'steam:123456'),
        sl.gte(schema.players.money, 1000)
    ))
    .execute()

-- Update all records
local affected = db.update(schema.players)
    .set({
        bank = sl.sql('`bank` + 500')  -- Give everyone $500
    })
    .execute()

DELETE Operations

-- Soft delete (if table has deleted_at column)
local affected = db.delete(schema.players)
    .where(sl.eq(schema.players.identifier, 'steam:123456'))
    .execute()

-- Restore soft-deleted record
local affected = db.update(schema.players)
    .set({ deleted_at = nil })
    .where(sl.and_(
        sl.eq(schema.players.identifier, 'steam:123456'),
        sl.isNotNull(schema.players.deleted_at)
    ))
    .execute()

Raw SQL

For complex queries that need raw SQL:

-- Raw SQL expressions in SELECT
local result = db.select({
    schema.players.name,
    sl.sql('`money` + `bank` AS total_wealth')
})
.from(schema.players)
.execute()

-- Raw WHERE conditions
local result = db.select()
    .from(schema.players)
    .where(sl.sql('YEAR(created_at) = ?', { 2023 }))
    .execute()

Debugging Queries

Use toSQL() to debug your queries without executing them:

local sql, params = db.select()
    .from(schema.players)
    .where(sl.eq(schema.players.identifier, 'steam:123456'))
    .limit(1)
    .toSQL()

print(('SQL: %s | Params: %s'):format(sql, json.encode(params)))

Query Execution

All queries must end with .execute() to run and return results:

local players = db.select()
    .from(schema.players)
    .where(sl.eq(schema.players.job, 'police'))
    .orderBy(schema.players.name, 'asc')
    .execute()  -- Returns PlayersRecord[]