Usage Examples

-- Insert a player
DB.insert('players', {
    identifier = 'license:abc123',
    name = 'John Doe',
    money = 5000
}, function(insertId, err)
    if not err then
        print("Player created:", insertId)
    end
end)

-- Select players
local players = DB.select('players', {
    where = {money = {operator = ">", value = 10000}},
    orderBy = {money = 'desc'},
    limit = 10
})

-- Update player
DB.update('players', {
    where = {identifier = license},
    set = {money = 7500, lastSeen = Date.now()}
})

-- Delete player
DB.delete('players', {
    where = {identifier = license}
})

-- Subscribe to top players (updates automatically!)
local unsubscribe = DB.subscribe('players', {
    orderBy = {money = 'desc'},
    limit = 10,
    select = {'name', 'money'}
}, function(players)
    -- This runs automatically when data changes
    TriggerClientEvent('leaderboard:update', -1, players)
    print("Leaderboard updated with", #players, "players")
end)

-- No more polling loops!
-- Old way: while true do Citizen.Wait(5000) SELECT ... end
-- New way: Automatic push when data changes

-- Cleanup on resource stop
AddEventHandler('onResourceStop', function(resourceName)
    if resourceName == GetCurrentResourceName() then
        unsubscribe()
    end
end)

function TransferMoney(from, to, amount)
    -- Get current balances
    local fromPlayer = DB.selectOne('players', {where = {identifier = from}})
    local toPlayer = DB.selectOne('players', {where = {identifier = to}})

    -- Validate
    if fromPlayer.money < amount then
        return false, "Insufficient funds"
    end

    -- Execute atomically (all succeed or all fail)
    local operations = {
        {type = "update", table = "players",
         where = {identifier = from},
         data = {money = fromPlayer.money - amount}},
        {type = "update", table = "players",
         where = {identifier = to},
         data = {money = toPlayer.money + amount}}
    }

    local p = promise.new()
    exports['cfx-db']:executeTransaction(operations, function(success, result)
        if success then p:resolve(true)
        else p:reject(result.error) end
    end)

    return Citizen.Await(p)
end

-- Get player with all vehicles and inventory
DB.relations('players', {
    where = {identifier = license},
    relations = {
        {
            name = 'vehicles',
            table = 'vehicles',
            foreignKey = 'owner',
            localKey = 'identifier',
            type = 'many',
            select = {'plate', 'model', 'mileage'}
        },
        {
            name = 'inventory',
            table = 'inventory',
            foreignKey = 'owner',
            localKey = 'identifier',
            type = 'many'
        }
    }
}, function(players)
    local player = players[1]
    print(player.name, "has", #player.vehicles, "vehicles")
    print("Inventory:", #player.inventory, "items")
end)