Persistence with oxmysql

Restart your server and every player's cash, position, and progress vanishes. That's not a bug — it's because your data lives in RAM, and RAM forgets the moment the process dies. This lesson wires your scripts to a real database so the world you build survives reboots, crashes, and Tuesday-night updates. By the end you'll save and load player data without freezing your server for a single frame.

Why persistence is its own skill

Up to now your scripts have held state in Lua tables. That's fast and fine — until the resource restarts. A roleplay server is a promise: the apartment you bought yesterday is still yours today. Keeping that promise means writing data somewhere durable and reading it back later.

The standard tool in the modern FiveM stack is oxmysql — a resource that lets your scripts talk to a MySQL or MariaDB database. Frameworks like Qbox and QBCore already depend on it, so learning it directly means you understand what they're doing under the hood instead of treating it as magic.

Setting up the database

You need a database server running next to FiveM. MariaDB is the usual pick (it's a drop-in MySQL fork, free, and what most tutorials assume). Install it, create an empty database — call it roleplay — and create a user with access to it.

Then tell oxmysql how to connect. In your server.cfg, set the connection string convar before oxmysql starts:

set mysql_connection_string "mysql://rpuser:yourpassword@localhost/roleplay"
ensure oxmysql

Read that string left to right: protocol, then user:password, then @host, then /database. If oxmysql can't connect, it prints a clear error on boot — check the username, password, and that the database actually exists. Don't paste your real password into a public repo; that's how servers get wiped.

How oxmysql talks to the database

oxmysql gives you a global MySQL object with a few methods. The two you'll use constantly:

• MySQL.query.await(sql, params) — runs any SQL and, for a SELECT, returns an array of row objects. Great for reads.
• MySQL.prepare.await(sql, params) — runs a prepared statement. The database caches the query plan, so running the same statement repeatedly (saving 50 players on a loop) is cheaper. Ideal for hot-path writes.

Always pass values as the second argument with ? placeholders — never glue user input into the SQL string. The ? form lets the driver escape values for you, which is your defense against SQL injection. This is non-negotiable, especially since players control things like character names.

Not blocking the thread

Here's the part beginners trip on. A database lives on disk; a query takes milliseconds, which is an eternity to a server running at 60+ ticks. If you blocked the whole server every time you read a row, players would rubber-band on every save.

That .await suffix is the trick. It pauses only the current coroutine while the query runs and lets every other thread keep ticking. Your code reads top-to-bottom like it's synchronous, but the server never freezes. The rule: .await is fine inside an event handler or a CreateThread; never run it in a tight loop with no Wait.

A working save/load script

This server-side script creates a table, loads a player when they join, and gives new players a starting balance. Server-side only — the client never touches the database. That's the core security rule: a trusted server reads and writes; clients only ask.

-- server/persistence.lua  (runs on the server, never the client)

-- Ensure the table exists. Runs once when the resource starts.
MySQL.query.await([[
    CREATE TABLE IF NOT EXISTS player_data (
        identifier VARCHAR(64) PRIMARY KEY,
        cash INT NOT NULL DEFAULT 500,
        pos_x FLOAT, pos_y FLOAT, pos_z FLOAT
    )
]])

-- Return a player's saved row, or nil if they're brand new.
local function loadPlayer(identifier)
    -- .await pauses THIS coroutine only; the rest of the server keeps running.
    local rows = MySQL.query.await(
        'SELECT cash, pos_x, pos_y, pos_z FROM player_data WHERE identifier = ?',
        { identifier }
    )
    return rows and rows[1]
end

-- Insert a new player, or update them if they already exist (an "upsert").
local function savePlayer(identifier, cash, coords)
    -- prepare caches the statement, so repeated autosaves stay cheap.
    MySQL.prepare.await([[
        INSERT INTO player_data (identifier, cash, pos_x, pos_y, pos_z)
        VALUES (?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            cash = VALUES(cash),
            pos_x = VALUES(pos_x), pos_y = VALUES(pos_y), pos_z = VALUES(pos_z)
    ]], { identifier, cash, coords.x, coords.y, coords.z })
end

AddEventHandler('playerJoining', function()
    local src = source
    local identifier = GetPlayerIdentifierByType(src, 'license')

    local data = loadPlayer(identifier)
    if not data then
        -- First time we've seen this license: seed a starting balance.
        savePlayer(identifier, 500, vector3(-269.0, -955.0, 31.2))
        data = { cash = 500 }
    end

    print(('%s loaded with $%d'):format(GetPlayerName(src), data.cash))
end)

When the client needs that data — say, to draw a HUD — don't broadcast it on join and hope. Use lib.callback from ox_lib so the client requests it and the server answers with a return value. That's the modern pattern; the old TriggerServerEvent callback dance is deprecated.

Where AI gets this wrong

Ask an AI to "add a database to my script" and you'll often get plausible-looking nonsense: invented oxmysql methods (MySQL.fetchAll from an old fork), ESX syntax bolted onto a Qbox server, or — worst — a client-side script writing money straight to the database. That last one is the classic security hole: the client is hostile, so never let it write. Three habits keep you safe:

1. Pin one framework. Decide Qbox or QBCore and reject any snippet mixing them.
2. Verify every native and export against the real oxmysql README — if AI uses a method you can't find documented, assume it's hallucinated.
3. Validate server-side. The server decides what a player can afford, not the client.

Treat AI as a fast first draft you check against the official reference, then prove in the PlayDeck sandbox.

Practice

In the PlayDeck browser sandbox, no real database is needed — we mock the row. Paste this and make the test pass by finishing applyLoad, which should return saved data or fall back to defaults for a new player:

local function applyLoad(row)
    -- TODO: if row is nil, return { cash = 500 }
    -- otherwise return the row unchanged
end

print(applyLoad(nil).cash)            -- expect 500
print(applyLoad({ cash = 9000 }).cash) -- expect 9000

Run it. If both lines print the right numbers, your load logic correctly handles new and returning players — the exact branch the real loadPlayer relies on.

Recap

• Player data must live in a database (MariaDB/MySQL) to survive restarts.
• oxmysql connects via the mysql_connection_string convar in server.cfg.
• Use MySQL.query.await for reads and MySQL.prepare.await for repeated writes; the .await suffix pauses one coroutine, never the whole server.
• Always pass values via ? placeholders — never string-concatenate SQL.
• Do all database work server-side, deliver it to clients with lib.callback, and verify any AI-generated query against the official oxmysql docs before trusting it.