Player System Example

This example demonstrates how to implement a complete player management system using Sleet ORM in FiveM with ESX Legacy.

Schema Definition

First, define your player-related tables:

-- server/schema.lua
local sl = require 'sleet'

return {
    -- Main players table
    players = sl.table('users', {
        identifier = sl.varchar(64).primaryKey(),
        firstname = sl.varchar(16).notNull(),
        lastname = sl.varchar(16).notNull(),
        dateofbirth = sl.varchar(10).notNull(),
        sex = sl.varchar(1).notNull().default('m'),
        height = sl.integer().notNull(),
        skin = sl.longtext(),
        status = sl.longtext(),
        is_dead = sl.boolean().default(false),
        position = sl.varchar(255).default('{"x":-269.4,"y":-955.3,"z":31.2,"heading":205.8}'),
        accounts = sl.longtext(),
        job = sl.varchar(20).default('unemployed'),
        job_grade = sl.integer().default(0),
        group = sl.varchar(50).default('user'),
        loadout = sl.longtext().default('[]'),
        metadata = sl.longtext().default('[]'),
        firstname = sl.varchar(16),
        lastname = sl.varchar(16),
        dateofbirth = sl.varchar(10),
        sex = sl.varchar(1).default('m'),
        height = sl.integer()
    }),
    
    -- Player items
    user_licenses = sl.table('user_licenses', {
        id = sl.serial().primaryKey(),
        type = sl.varchar(60).notNull(),
        owner = sl.varchar(64).notNull()
    }),
    
    -- Player accounts (bank, money, etc.)
    user_accounts = sl.table('user_accounts', {
        id = sl.serial().primaryKey(),
        identifier = sl.varchar(64).notNull(),
        name = sl.varchar(50).notNull(),
        money = sl.integer().default(0)
    })
}

Player Management Module

Create a comprehensive player management system:

-- server/modules/player.lua
local Schema = require('server.schema')

local PlayerManager = {}

-- Create a new player
function PlayerManager.create(identifier, firstname, lastname, dateofbirth, sex, height)
    local playerId = Schema.players:insert({
        identifier = identifier,
        firstname = firstname,
        lastname = lastname,
        dateofbirth = dateofbirth,
        sex = sex,
        height = height,
        accounts = json.encode({
            {name = 'bank', money = 50000},
            {name = 'black_money', money = 0},
            {name = 'money', money = 5000}
        }),
        job = 'unemployed',
        job_grade = 0,
        group = 'user'
    })
    
    -- Create default accounts
    PlayerManager.createDefaultAccounts(identifier)
    
    return playerId
end

-- Get player by identifier
function PlayerManager.getByIdentifier(identifier)
    return Schema.players:where('identifier', identifier):exec()[1]
end

-- Get all online players
function PlayerManager.getOnlinePlayers()
    local players = {}
    for _, playerId in ipairs(GetPlayers()) do
        local identifier = GetPlayerIdentifier(playerId, 0)
        if identifier then
            local player = PlayerManager.getByIdentifier(identifier)
            if player then
                players[#players + 1] = {
                    serverId = playerId,
                    data = player
                }
            end
        end
    end
    return players
end

-- Update player position
function PlayerManager.updatePosition(identifier, x, y, z, heading)
    local position = {
        x = x,
        y = y, 
        z = z,
        heading = heading
    }
    
    return Schema.players:where('identifier', identifier):update({
        position = json.encode(position)
    })
end

-- Update player job
function PlayerManager.setJob(identifier, jobName, grade)
    return Schema.players:where('identifier', identifier):update({
        job = jobName,
        job_grade = grade or 0
    })
end

-- Get player job
function PlayerManager.getJob(identifier)
    local player = Schema.players:select('job', 'job_grade')
        :where('identifier', identifier)
        :exec()[1]
    
    if player then
        return player.job, player.job_grade
    end
    return nil, nil
end

-- Update player group
function PlayerManager.setGroup(identifier, group)
    return Schema.players:where('identifier', identifier):update({
        group = group
    })
end

-- Check if player has permission
function PlayerManager.hasPermission(identifier, permission)
    local player = Schema.players:select('group')
        :where('identifier', identifier)
        :exec()[1]
    
    if not player then return false end
    
    local permissions = {
        admin = {'admin', 'mod', 'user'},
        mod = {'mod', 'user'},
        user = {'user'}
    }
    
    local playerPerms = permissions[player.group] or {}
    for _, perm in ipairs(playerPerms) do
        if perm == permission then
            return true
        end
    end
    
    return false
end

-- Create default accounts for new player
function PlayerManager.createDefaultAccounts(identifier)
    local defaultAccounts = {
        {name = 'bank', money = 50000},
        {name = 'black_money', money = 0},
        {name = 'money', money = 5000}
    }
    
    for _, account in ipairs(defaultAccounts) do
        Schema.user_accounts:insert({
            identifier = identifier,
            name = account.name,
            money = account.money
        })
    end
end

-- Account management
function PlayerManager.getAccount(identifier, accountName)
    return Schema.user_accounts:where('identifier', identifier)
        :where('name', accountName)
        :exec()[1]
end

function PlayerManager.addMoney(identifier, accountName, amount)
    return Schema.user_accounts:where('identifier', identifier)
        :where('name', accountName)
        :update({
            money = Schema.raw('money + ?', {amount})
        })
end

function PlayerManager.removeMoney(identifier, accountName, amount)
    return Schema.user_accounts:where('identifier', identifier)
        :where('name', accountName)
        :where('money', '>=', amount)
        :update({
            money = Schema.raw('money - ?', {amount})
        })
end

function PlayerManager.setMoney(identifier, accountName, amount)
    return Schema.user_accounts:where('identifier', identifier)
        :where('name', accountName)
        :update({money = amount})
end

function PlayerManager.getMoney(identifier, accountName)
    local account = PlayerManager.getAccount(identifier, accountName)
    return account and account.money or 0
end

-- Get all accounts for a player
function PlayerManager.getAccounts(identifier)
    return Schema.user_accounts:where('identifier', identifier):exec()
end

-- License management
function PlayerManager.addLicense(identifier, licenseType)
    return Schema.user_licenses:insert({
        type = licenseType,
        owner = identifier
    })
end

function PlayerManager.removeLicense(identifier, licenseType)
    return Schema.user_licenses:where('owner', identifier)
        :where('type', licenseType)
        :delete()
end

function PlayerManager.hasLicense(identifier, licenseType)
    local license = Schema.user_licenses:where('owner', identifier)
        :where('type', licenseType)
        :exec()[1]
    return license ~= nil
end

function PlayerManager.getLicenses(identifier)
    return Schema.user_licenses:where('owner', identifier):exec()
end

-- Player statistics
function PlayerManager.getPlayerStats()
    local stats = {}
    
    -- Total players
    stats.totalPlayers = Schema.players:count()
    
    -- Players by job
    stats.jobDistribution = Schema.players:select('job', Schema.raw('COUNT(*) as count'))
        :groupBy('job')
        :orderBy('count', 'DESC')
        :exec()
    
    -- Players by group
    stats.groupDistribution = Schema.players:select('group', Schema.raw('COUNT(*) as count'))
        :groupBy('group')
        :exec()
    
    -- Total money in economy
    stats.totalMoney = Schema.user_accounts:sum('money')
    
    return stats
end

-- Cleanup inactive players
function PlayerManager.cleanupInactivePlayers(daysSinceLastSeen)
    local cutoffDate = os.date('%Y-%m-%d', os.time() - (daysSinceLastSeen * 24 * 60 * 60))
    
    -- Note: This assumes you have a last_seen column
    -- return Schema.players:where('last_seen', '<', cutoffDate):delete()
end

return PlayerManager

Usage in Game Logic

Here's how to use the PlayerManager in your gamemode:

-- server/main.lua
local PlayerManager = require('server.modules.player')

-- When a player connects
RegisterServerEvent('esx:onPlayerJoined')
AddEventHandler('esx:onPlayerJoined', function()
    local _source = source
    local identifier = GetPlayerIdentifier(_source, 0)
    
    -- Check if player exists
    local player = PlayerManager.getByIdentifier(identifier)
    if not player then
        -- New player - create character
        TriggerClientEvent('esx:showCharacterCreation', _source)
    else
        -- Existing player - load data
        TriggerClientEvent('esx:playerLoaded', _source, player)
    end
end)

-- Create new character
RegisterServerEvent('esx:createCharacter')
AddEventHandler('esx:createCharacter', function(data)
    local _source = source
    local identifier = GetPlayerIdentifier(_source, 0)
    
    PlayerManager.create(
        identifier,
        data.firstname,
        data.lastname,
        data.dateofbirth,
        data.sex,
        data.height
    )
    
    local player = PlayerManager.getByIdentifier(identifier)
    TriggerClientEvent('esx:playerLoaded', _source, player)
end)

-- Save player position periodically
CreateThread(function()
    while true do
        Wait(30000) -- Save every 30 seconds
        
        local players = PlayerManager.getOnlinePlayers()
        for _, player in ipairs(players) do
            local ped = GetPlayerPed(player.serverId)
            if DoesEntityExist(ped) then
                local coords = GetEntityCoords(ped)
                local heading = GetEntityHeading(ped)
                
                PlayerManager.updatePosition(
                    player.data.identifier,
                    coords.x,
                    coords.y,
                    coords.z,
                    heading
                )
            end
        end
    end
end)

-- Admin commands
RegisterCommand('setjob', function(source, args, rawCommand)
    if not PlayerManager.hasPermission(GetPlayerIdentifier(source, 0), 'admin') then
        return
    end
    
    local targetId = tonumber(args[1])
    local jobName = args[2]
    local grade = tonumber(args[3]) or 0
    
    if targetId and jobName then
        local targetIdentifier = GetPlayerIdentifier(targetId, 0)
        if targetIdentifier then
            PlayerManager.setJob(targetIdentifier, jobName, grade)
            TriggerClientEvent('esx:showNotification', source, ('Set %s job to %s grade %d'):format(GetPlayerName(targetId), jobName, grade))
        end
    end
end, true)

RegisterCommand('givemoney', function(source, args, rawCommand)
    if not PlayerManager.hasPermission(GetPlayerIdentifier(source, 0), 'admin') then
        return
    end
    
    local targetId = tonumber(args[1])
    local account = args[2] or 'money'
    local amount = tonumber(args[3])
    
    if targetId and amount then
        local targetIdentifier = GetPlayerIdentifier(targetId, 0)
        if targetIdentifier then
            PlayerManager.addMoney(targetIdentifier, account, amount)
            TriggerClientEvent('esx:showNotification', source, ('Gave $%d %s to %s'):format(amount, account, GetPlayerName(targetId)))
        end
    end
end, true)

-- Money transfer between players
RegisterServerEvent('esx:transferMoney')
AddEventHandler('esx:transferMoney', function(targetId, amount)
    local _source = source
    local sourceIdentifier = GetPlayerIdentifier(_source, 0)
    local targetIdentifier = GetPlayerIdentifier(targetId, 0)
    
    if not targetIdentifier then return end
    
    local sourceMoney = PlayerManager.getMoney(sourceIdentifier, 'money')
    if sourceMoney >= amount then
        PlayerManager.removeMoney(sourceIdentifier, 'money', amount)
        PlayerManager.addMoney(targetIdentifier, 'money', amount)
        
        TriggerClientEvent('esx:showNotification', _source, ('Sent $%d to %s'):format(amount, GetPlayerName(targetId)))
        TriggerClientEvent('esx:showNotification', targetId, ('Received $%d from %s'):format(amount, GetPlayerName(_source)))
    else
        TriggerClientEvent('esx:showNotification', _source, 'Not enough money')
    end
end)

Key Features

This player system example demonstrates:

1. Complete Player Lifecycle: Creation, loading, and data persistence
2. Account Management: Multiple account types (bank, cash, black money)
3. Job System: Setting and managing player jobs
4. Permission System: Group-based permissions for admin commands
5. License System: Managing player licenses
6. Position Tracking: Automatic position saving
7. Statistics: Player and economy statistics
8. Money Transfers: Secure player-to-player transactions
9. Admin Commands: Administrative tools for player management

The system is designed to be modular, efficient, and easy to extend with additional features as your FiveM server grows.