Basic Usage

This guide covers the fundamental concepts and basic operations in Sleet ORM.

Schema Definition

The foundation of Sleet ORM is defining your database schema in Lua code.

Creating a Table Schema

-- server/schema.lua
local sl = Sleet

local users = sl.table('users', {
    id       = sl.serial().primaryKey().comment('User ID'),
    username = sl.varchar(50).notNull().unique().comment('Username'),
    email    = sl.varchar(100).notNull().unique().comment('Email address'),
    age      = sl.int().comment('User age'),
    active   = sl.boolean().default(true).comment('Account status'),
    created  = sl.timestamp().defaultNow().comment('Registration date')
})

return { users = users }

Column Types

Sleet supports all common MySQL column types:

-- Numeric types
id = sl.serial()                    -- AUTO_INCREMENT INTEGER
count = sl.int()                    -- INTEGER
price = sl.decimal(10, 2)          -- DECIMAL(10,2)
rating = sl.float()                -- FLOAT

-- String types
name = sl.varchar(255)             -- VARCHAR(255)
code = sl.char(3)                  -- CHAR(3)
description = sl.text()            -- TEXT

-- Date/Time types
birthday = sl.date()               -- DATE
updated = sl.timestamp()           -- TIMESTAMP
posted = sl.datetime()             -- DATETIME

-- Other types
is_admin = sl.boolean()            -- TINYINT(1)
metadata = sl.json()               -- JSON

Column Modifiers

Chain modifiers to customize column behavior:

local players = sl.table('players', {
    id = sl.serial()
        .primaryKey()                      -- PRIMARY KEY
        .comment('Player ID'),

    username = sl.varchar(50)
        .notNull()                         -- NOT NULL
        .unique()                          -- UNIQUE
        .comment('Player username'),

    money = sl.int()
        .default(1000)                     -- DEFAULT 1000
        .comment('Player money'),

    last_login = sl.timestamp()
        .defaultNow()                      -- DEFAULT CURRENT_TIMESTAMP
        .onUpdate(sl.sql('NOW()'))         -- Auto-update on change
        .comment('Last login time'),

    deleted_at = sl.timestamp()
        .softDelete()                      -- Enables soft delete
        .comment('Deletion timestamp')
})

Database Operations

Once you have a schema, you can perform database operations.

Connecting to Database

-- server/main.lua
local sl = Sleet
local s = require 'server.schema'
local db = sl.connect()  -- Uses oxmysql connection

SELECT Queries

Basic Select

-- Select all users
local users = db.select()
    .from(s.users)
    .execute()

-- Select specific columns
local names = db.select({ s.users.username, s.users.email })
    .from(s.users)
    .execute()

With Conditions

-- WHERE clause
local activeUsers = db.select()
    .from(s.users)
    .where(sl.eq(s.users.active, true))
    .execute()

-- Multiple conditions
local result = db.select()
    .from(s.users)
    .where(sl.and_(
        sl.eq(s.users.active, true),
        sl.gte(s.users.age, 18)
    ))
    .execute()

Sorting and Limiting

-- ORDER BY and LIMIT
local recent = db.select()
    .from(s.users)
    .orderBy(s.users.created, 'desc')
    .limit(10)
    .execute()

-- Pagination
local page2 = db.select()
    .from(s.users)
    .orderBy(s.users.username, 'asc')
    .limit(20)
    .offset(20)  -- Skip first 20 records
    .execute()

INSERT Operations

Single Insert

local newUserId = db.insert(s.users)
    .values({
        username = 'john_doe',
        email = 'john@example.com',
        age = 25
    })
    .execute()  -- Returns the auto-generated ID

Multiple Insert

local userIds = db.insert(s.users)
    .values({
        { username = 'alice', email = 'alice@example.com', age = 30 },
        { username = 'bob', email = 'bob@example.com', age = 28 }
    })
    .execute()

UPDATE Operations

Basic Update

local affected = db.update(s.users)
    .set({
        email = 'newemail@example.com',
        age = 26
    })
    .where(sl.eq(s.users.id, userId))
    .execute()  -- Returns number of affected rows

Atomic Operations

-- Increment a value atomically
db.update(s.players)
    .set({
        money = sl.sql('`money` + ?', { 500 }),
        last_login = sl.sql('NOW()')
    })
    .where(sl.eq(s.players.id, playerId))
    .execute()

DELETE Operations

Regular Delete

local deleted = db.delete(s.users)
    .where(sl.eq(s.users.id, userId))
    .execute()  -- Returns number of deleted rows

Soft Delete

If your table has a soft delete column, DELETE operations become UPDATE operations:

-- This becomes: UPDATE users SET deleted_at = NOW() WHERE id = ?
db.delete(s.users)
    .where(sl.eq(s.users.id, userId))
    .execute()

-- Force actual delete (bypass soft delete)
db.delete(s.users)
    .where(sl.eq(s.users.id, userId))
    .force()
    .execute()

Query Conditions

Sleet provides a comprehensive set of operators for building conditions:

Comparison Operators

-- Basic comparisons
sl.eq(column, value)        -- column = value
sl.ne(column, value)        -- column != value
sl.gt(column, value)        -- column > value
sl.gte(column, value)       -- column >= value
sl.lt(column, value)        -- column < value
sl.lte(column, value)       -- column <= value

-- String operations
sl.like(column, pattern)    -- column LIKE pattern
sl.notLike(column, pattern) -- column NOT LIKE pattern

-- NULL checks
sl.isNull(column)           -- column IS NULL
sl.isNotNull(column)        -- column IS NOT NULL

-- Array operations
sl.inArray(column, values)  -- column IN (values)
sl.notInArray(column, values) -- column NOT IN (values)
sl.between(column, min, max)  -- column BETWEEN min AND max

Logical Operators

-- Combine conditions
sl.and_(condition1, condition2, ...)  -- AND
sl.or_(condition1, condition2, ...)   -- OR
sl.not_(condition)                    -- NOT

-- Example: Complex query
local results = db.select()
    .from(s.users)
    .where(sl.or_(
        sl.and_(
            sl.eq(s.users.active, true),
            sl.gte(s.users.age, 18)
        ),
        sl.eq(s.users.username, 'admin')
    ))
    .execute()

Raw SQL

For complex expressions that can't be expressed with the query builder:

-- Raw SQL in SELECT
local stats = db.select({
    sl.sql('COUNT(*) as total'),
    sl.sql('AVG(age) as avg_age'),
    sl.sql('MAX(created) as latest')
})
.from(s.users)
.execute()

-- Raw SQL in WHERE
local recent = db.select()
    .from(s.users)
    .where(sl.sql('created > DATE_SUB(NOW(), INTERVAL 7 DAY)'))
    .execute()

-- Raw SQL in UPDATE
db.update(s.players)
    .set({
        score = sl.sql('score * 1.1'),  -- 10% bonus
        updated = sl.sql('NOW()')
    })
    .where(sl.eq(s.players.active, true))
    .execute()

Debugging Queries

Use .toSQL() to inspect generated queries:

local query = db.select()
    .from(s.users)
    .where(sl.eq(s.users.active, true))
    .orderBy(s.users.created, 'desc')
    .limit(10)

local sql, params = query.toSQL()
print('SQL:', sql)
print('Params:', json.encode(params))
-- Then execute
local results = query.execute()

Next Steps

• Check out Best Practices
• See real-world Examples