Examples

Real-world examples demonstrating Sleet ORM usage in FiveM resources.

Complete Player System

A comprehensive player management system using Sleet ORM.

Schema Definition

-- server/schema.lua
local sl = Sleet

local players = sl.table('players', {
    id = sl.serial().primaryKey().comment('Player unique ID'),
    identifier = sl.varchar(64).notNull().unique().comment('Steam/Discord identifier'),
    name = sl.varchar(255).notNull().comment('Player display name'),
    money = sl.int().default(1000).comment('Cash on hand'),
    bank = sl.int().default(5000).comment('Bank balance'),
    job = sl.varchar(50).default('unemployed').comment('Current job'),
    job_grade = sl.int().default(0).comment('Job grade level'),
    is_admin = sl.boolean().default(false).comment('Admin status'),
    last_seen = sl.timestamp().defaultNow().onUpdate(sl.sql('NOW()')).comment('Last login time'),
    created_at = sl.timestamp().defaultNow().comment('Registration date')
})

return { players = players }

Player Management Functions

-- server/player.lua
local sl = Sleet
local s = require 'server.schema'
local db = sl.connect()

local Player = {}

-- Get player by identifier
function Player.getByIdentifier(identifier)
    local result = db.select()
        .from(s.players)
        .where(sl.eq(s.players.identifier, identifier))
        .limit(1)
        .execute()
    return result[1]
end

-- Create new player
function Player.create(data)
    return db.insert(s.players)
        .values({
            identifier = data.identifier,
            name = data.name,
            money = data.money or 1000,
            bank = data.bank or 5000
        })
        .execute()
end

-- Update player money
function Player.addMoney(playerId, amount)
    return db.update(s.players)
        .set({ money = sl.sql('money + ?', { amount }) })
        .where(sl.eq(s.players.id, playerId))
        .execute()
end

-- Transfer money between players
function Player.transferMoney(fromId, toId, amount)
    return db.transaction(function(tx)
        -- Check sender has enough money
        local sender = tx.select({ s.players.money })
            .from(s.players)
            .where(sl.eq(s.players.id, fromId))
            .execute()[1]

        if not sender or sender.money < amount then
            error('Insufficient funds')
        end

        -- Subtract from sender
        tx.update(s.players)
            .set({ money = sl.sql('money - ?', { amount }) })
            .where(sl.eq(s.players.id, fromId))
            .execute()

        -- Add to receiver
        tx.update(s.players)
            .set({ money = sl.sql('money + ?', { amount }) })
            .where(sl.eq(s.players.id, toId))
            .execute()

        return true
    end)
end

return Player

Vehicle System

Vehicle ownership and management system.

Schema

local vehicles = sl.table('player_vehicles', {
    id = sl.serial().primaryKey(),
    owner_id = sl.int().notNull().references(s.players.id),
    model = sl.varchar(50).notNull(),
    plate = sl.varchar(8).notNull().unique(),
    stored = sl.boolean().default(true),
    garage = sl.varchar(50).default('central'),
    fuel = sl.int().default(100),
    engine_health = sl.int().default(1000),
    body_health = sl.int().default(1000),
    mods = sl.json().comment('Vehicle modifications'),
    purchased_at = sl.timestamp().defaultNow()
})

Vehicle Functions

-- Get player vehicles
function getPlayerVehicles(playerId)
    return db.select()
        .from(s.vehicles)
        .where(sl.and_(
            sl.eq(s.vehicles.owner_id, playerId),
            sl.eq(s.vehicles.stored, true)
        ))
        .orderBy(s.vehicles.purchased_at, 'desc')
        .execute()
end

-- Spawn vehicle
function spawnVehicle(vehicleId)
    return db.update(s.vehicles)
        .set({ stored = false })
        .where(sl.eq(s.vehicles.id, vehicleId))
        .execute()
end

Shop System

Item shop with inventory management.

Schema

local items = sl.table('items', {
    id = sl.serial().primaryKey(),
    name = sl.varchar(100).notNull().unique(),
    label = sl.varchar(255).notNull(),
    price = sl.decimal(10, 2).notNull(),
    category = sl.varchar(50).notNull(),
    weight = sl.decimal(5, 2).default(0.0),
    usable = sl.boolean().default(false),
    description = sl.text(),
    active = sl.boolean().default(true)
})

local player_inventory = sl.table('player_inventory', {
    id = sl.serial().primaryKey(),
    player_id = sl.int().notNull().references(s.players.id),
    item_id = sl.int().notNull().references(items.id),
    quantity = sl.int().notNull().default(1),
    metadata = sl.json(),
    created_at = sl.timestamp().defaultNow()
})

Shop Functions

-- Get shop items by category
function getShopItems(category)
    return db.select()
        .from(s.items)
        .where(sl.and_(
            sl.eq(s.items.category, category),
            sl.eq(s.items.active, true)
        ))
        .orderBy(s.items.name, 'asc')
        .execute()
end

-- Purchase item
function purchaseItem(playerId, itemId, quantity)
    return db.transaction(function(tx)
        -- Get item info
        local item = tx.select()
            .from(s.items)
            .where(sl.eq(s.items.id, itemId))
            .execute()[1]

        if not item then
            error('Item not found')
        end

        local totalCost = item.price * quantity

        -- Check player money
        local player = tx.select({ s.players.money })
            .from(s.players)
            .where(sl.eq(s.players.id, playerId))
            .execute()[1]

        if not player or player.money < totalCost then
            error('Insufficient funds')
        end

        -- Deduct money
        tx.update(s.players)
            .set({ money = sl.sql('money - ?', { totalCost }) })
            .where(sl.eq(s.players.id, playerId))
            .execute()

        -- Add to inventory
        tx.insert(s.player_inventory)
            .values({
                player_id = playerId,
                item_id = itemId,
                quantity = quantity
            })
            .execute()

        return true
    end)
end

Statistics and Analytics

Advanced queries for server statistics.

-- Player activity stats
function getPlayerStats()
    local stats = db.select({
        sl.sql('COUNT(*) as total_players'),
        sl.sql('COUNT(CASE WHEN last_seen > DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 END) as active_24h'),
        sl.sql('COUNT(CASE WHEN last_seen > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as active_7d'),
        sl.sql('AVG(money + bank) as avg_wealth'),
        sl.sql('SUM(money + bank) as total_economy')
    })
    .from(s.players)
    .execute()[1]

    return stats
end

-- Top wealthy players
function getWealthyPlayers(limit)
    return db.select({
        s.players.name,
        sl.sql('(money + bank) as total_wealth')
    })
    .from(s.players)
    .orderBy(sl.sql('(money + bank)'), 'desc')
    .limit(limit or 10)
    .execute()
end

-- Vehicle ownership stats
function getVehicleStats()
    return db.select({
        s.vehicles.model,
        sl.sql('COUNT(*) as count'),
        sl.sql('AVG(engine_health) as avg_condition')
    })
    .from(s.vehicles)
    .groupBy(s.vehicles.model)
    .orderBy(sl.sql('count'), 'desc')
    .limit(10)
    .execute()
end

Advanced: Soft Delete Example

Implementing a ban system with soft deletes.

-- Schema with soft delete
local bans = sl.table('player_bans', {
    id = sl.serial().primaryKey(),
    player_id = sl.int().notNull().references(s.players.id),
    reason = sl.text().notNull(),
    banned_by = sl.int().notNull().references(s.players.id),
    expires_at = sl.timestamp(),
    created_at = sl.timestamp().defaultNow(),
    deleted_at = sl.timestamp().softDelete()  -- Soft delete for ban revokes
})

-- Ban player (creates record)
function banPlayer(playerId, reason, bannedBy, duration)
    local expiresAt = duration and
        sl.sql('DATE_ADD(NOW(), INTERVAL ? SECOND)', { duration }) or nil

    return db.insert(s.bans)
        .values({
            player_id = playerId,
            reason = reason,
            banned_by = bannedBy,
            expires_at = expiresAt
        })
        .execute()
end

-- Revoke ban (soft delete)
function revokeBan(banId)
    return db.delete(s.bans)  -- This becomes UPDATE deleted_at = NOW()
        .where(sl.eq(s.bans.id, banId))
        .execute()
end

-- Check if player is banned (only active bans)
function isPlayerBanned(playerId)
    local ban = db.select()
        .from(s.bans)  -- Automatically filters deleted_at IS NULL
        .where(sl.and_(
            sl.eq(s.bans.player_id, playerId),
            sl.or_(
                sl.isNull(s.bans.expires_at),
                sl.gt(s.bans.expires_at, sl.sql('NOW()'))
            )
        ))
        .limit(1)
        .execute()[1]

    return ban ~= nil
end