Best Practices

Follow these best practices to get the most out of Sleet ORM and maintain clean, efficient code.

Schema Design

Use Meaningful Names

-- Good
local players = sl.table('players', {
    player_id = sl.serial().primaryKey(),
    steam_identifier = sl.varchar(64).notNull().unique(),
    display_name = sl.varchar(255).notNull()
})

-- Avoid
local tbl = sl.table('p', {
    id = sl.serial().primaryKey(),
    sid = sl.varchar(64),
    n = sl.varchar(255)
})

Add Comments to Everything

local vehicles = sl.table('vehicles', {
    id = sl.serial().primaryKey().comment('Vehicle unique identifier'),
    plate = sl.varchar(8).notNull().unique().comment('License plate number'),
    model = sl.varchar(50).notNull().comment('Vehicle model name'),
    owner_id = sl.int().notNull().comment('Player ID who owns this vehicle'),
    stored = sl.boolean().default(true).comment('Whether vehicle is in garage'),
    fuel = sl.int().default(100).comment('Fuel level (0-100)'),
    engine_health = sl.int().default(1000).comment('Engine condition'),
    created_at = sl.timestamp().defaultNow().comment('Purchase timestamp')
})

Use Appropriate Column Types and Sizes

-- Right-sized columns save space and improve performance
local users = sl.table('users', {
    id = sl.serial().primaryKey(),
    
    -- Use appropriate varchar lengths
    username = sl.varchar(50),      -- Not varchar(255) for usernames
    email = sl.varchar(100),        -- Sufficient for most emails
    phone = sl.varchar(20),         -- International phone numbers
    
    -- Use smallest appropriate integer type
    age = sl.tinyint(),            -- 0-255 is enough for age
    score = sl.int(),              -- For larger numbers
    
    -- Use decimal for money to avoid floating point issues
    balance = sl.decimal(10, 2),   -- Up to 99,999,999.99
    
    -- Use appropriate text types
    bio = sl.text(),               -- For longer text
    settings = sl.json()           -- For structured data
})

Design for Soft Deletes When Appropriate

-- For data you might need to recover
local players = sl.table('players', {
    id = sl.serial().primaryKey(),
    identifier = sl.varchar(64).notNull().unique(),
    name = sl.varchar(255).notNull(),
    
    -- Soft delete support
    deleted_at = sl.timestamp().softDelete().comment('Soft delete timestamp'),
    
    -- Auto-update timestamp
    updated_at = sl.timestamp().defaultNow().onUpdate(sl.sql('NOW()'))
})

Query Optimization

Use Specific Column Selection

-- Good - only select what you need
local playerNames = db.select({ s.players.id, s.players.name })
    .from(s.players)
    .where(sl.eq(s.players.active, true))
    .execute()

-- Avoid - selecting everything wastes resources
local players = db.select()
    .from(s.players)
    .where(sl.eq(s.players.active, true))
    .execute()

Use LIMIT for Large Datasets

-- Always limit potentially large result sets
local recentPlayers = db.select()
    .from(s.players)
    .orderBy(s.players.last_seen, 'desc')
    .limit(50)  -- Prevent huge result sets
    .execute()

-- Implement pagination for user-facing queries
local page = 1
local pageSize = 20
local players = db.select()
    .from(s.players)
    .orderBy(s.players.name, 'asc')
    .limit(pageSize)
    .offset((page - 1) * pageSize)
    .execute()

Use Indexes Wisely

-- Add indexes for frequently queried columns
local players = sl.table('players', {
    id = sl.serial().primaryKey(),
    identifier = sl.varchar(64).notNull().unique(),  -- Automatic index
    name = sl.varchar(255).notNull(),
    last_seen = sl.timestamp().defaultNow(),
    
    -- Consider adding indexes for:
    -- - Foreign key columns
    -- - Columns used in WHERE clauses
    -- - Columns used in ORDER BY
})

Error Handling

Always Handle Database Errors

-- Good - proper error handling
local function getPlayer(identifier)
    local success, result = pcall(function()
        return db.select()
            .from(s.players)
            .where(sl.eq(s.players.identifier, identifier))
            .limit(1)
            .execute()
    end)
    
    if not success then
        print('Database error:', result)
        return nil
    end
    
    return result[1]
end

-- Use in your code
local player = getPlayer('steam:123')
if player then
    -- Process player data
else
    -- Handle error case
end

Validate Input Data

local function createPlayer(data)
    -- Validate required fields
    if not data.identifier or data.identifier == '' then
        return false, 'Identifier is required'
    end
    
    if not data.name or data.name == '' then
        return false, 'Name is required'
    end
    
    -- Validate data types and ranges
    if data.money and (type(data.money) ~= 'number' or data.money < 0) then
        return false, 'Money must be a positive number'
    end
    
    -- Insert with error handling
    local success, playerId = pcall(function()
        return db.insert(s.players)
            .values({
                identifier = data.identifier,
                name = data.name,
                money = data.money or 1000
            })
            .execute()
    end)
    
    if success then
        return true, playerId
    else
        return false, 'Database error: ' .. tostring(playerId)
    end
end

Performance Tips

Use Transactions for Multiple Operations

-- When performing multiple related operations
local function transferMoney(fromId, toId, amount)
    return db.transaction(function(tx)
        -- Subtract from sender
        local senderUpdated = tx.update(s.players)
            .set({ money = sl.sql('money - ?', { amount }) })
            .where(sl.and_(
                sl.eq(s.players.id, fromId),
                sl.gte(s.players.money, amount)  -- Ensure sufficient funds
            ))
            .execute()
            
        if senderUpdated == 0 then
            error('Insufficient funds or sender not found')
        end
        
        -- Add to receiver
        local receiverUpdated = tx.update(s.players)
            .set({ money = sl.sql('money + ?', { amount }) })
            .where(sl.eq(s.players.id, toId))
            .execute()
            
        if receiverUpdated == 0 then
            error('Receiver not found')
        end
        
        return true
    end)
end

Cache Frequently Accessed Data

-- Cache static or semi-static data
local itemCache = {}
local cacheExpiry = 0

local function getItems()
    local now = GetGameTimer()
    
    if now > cacheExpiry then
        itemCache = db.select()
            .from(s.items)
            .where(sl.eq(s.items.active, true))
            .execute()
        
        cacheExpiry = now + (5 * 60 * 1000)  -- Cache for 5 minutes
    end
    
    return itemCache
end

Use Batch Operations

-- Instead of multiple single inserts
local function logMultipleActions(actions)
    local values = {}
    for _, action in ipairs(actions) do
        table.insert(values, {
            player_id = action.playerId,
            action = action.type,
            data = json.encode(action.data),
            timestamp = os.time()
        })
    end
    
    -- Single batch insert
    db.insert(s.action_logs)
        .values(values)
        .execute()
end

Code Organization

Structure Your Schema Files

-- server/schemas/players.lua
local sl = Sleet

return sl.table('players', {
    -- Player schema definition
})

-- server/schemas/vehicles.lua  
local sl = Sleet

return sl.table('vehicles', {
    -- Vehicle schema definition
})

-- server/schemas/init.lua
return {
    players = require 'server.schemas.players',
    vehicles = require 'server.schemas.vehicles',
    items = require 'server.schemas.items'
}

Create Data Access Layer

-- server/dao/players.lua
local sl = Sleet
local s = require 'server.schemas'
local db = sl.connect()

local PlayersDAO = {}

function PlayersDAO.getById(playerId)
    return db.select()
        .from(s.players)
        .where(sl.eq(s.players.id, playerId))
        .limit(1)
        .execute()[1]
end

function PlayersDAO.getByIdentifier(identifier)
    return db.select()
        .from(s.players)
        .where(sl.eq(s.players.identifier, identifier))
        .limit(1)
        .execute()[1]
end

function PlayersDAO.create(playerData)
    return db.insert(s.players)
        .values(playerData)
        .execute()
end

function PlayersDAO.updateMoney(playerId, amount)
    return db.update(s.players)
        .set({ money = sl.sql('money + ?', { amount }) })
        .where(sl.eq(s.players.id, playerId))
        .execute()
end

return PlayersDAO

Security Considerations

Never Trust User Input

-- Always validate and sanitize
local function searchPlayers(searchTerm)
    -- Validate input
    if type(searchTerm) ~= 'string' or searchTerm:len() < 2 then
        return {}
    end
    
    -- Sanitize for LIKE queries
    searchTerm = searchTerm:gsub('%%', '\\%%'):gsub('_', '\\_')
    
    return db.select()
        .from(s.players)
        .where(sl.like(s.players.name, '%' .. searchTerm .. '%'))
        .limit(50)  -- Always limit search results
        .execute()
end

Use Proper Authorization

-- Check permissions before database operations
local function deletePlayer(source, targetId)
    -- Verify admin permissions
    if not IsPlayerAceAllowed(source, 'admin.players.delete') then
        return false, 'Insufficient permissions'
    end
    
    -- Perform operation
    return db.delete(s.players)
        .where(sl.eq(s.players.id, targetId))
        .execute()
end

Debugging and Monitoring

Use Query Logging in Development

-- Enable query logging for debugging
local function debugQuery(query)
    if GetConvar('sleet_debug', '0') == '1' then
        local sql, params = query.toSQL()
        print('[SLEET DEBUG]', sql)
        print('[SLEET PARAMS]', json.encode(params))
    end
    return query.execute()
end

-- Usage
local results = debugQuery(
    db.select()
        .from(s.players)
        .where(sl.eq(s.players.active, true))
)

Monitor Query Performance

local function timedQuery(query, operation)
    local start = GetGameTimer()
    local result = query.execute()
    local duration = GetGameTimer() - start
    
    if duration > 100 then  -- Log slow queries
        local sql, params = query.toSQL()
        print(('[SLOW QUERY] %s took %dms: %s'):format(
            operation or 'Unknown',
            duration,
            sql
        ))
    end
    
    return result
end

Migration Strategies

Version Your Schema Changes

-- migrations/001_create_players.lua
return function(sl)
    return sl.table('players', {
        id = sl.serial().primaryKey(),
        identifier = sl.varchar(64).notNull().unique(),
        name = sl.varchar(255).notNull()
    })
end

-- migrations/002_add_money_to_players.lua  
return function(sl)
    -- Add money column to existing players table
    return sl.alterTable('players')
        .addColumn('money', sl.int().default(1000))
end

Following these best practices will help you build robust, maintainable, and performant FiveM resources with Sleet ORM.