Engineering
Using oxmysql in FiveM: setup, queries and good habits
Set up oxmysql for FiveM: the mysql_connection_string convar (URI and key=value), including the library in fxmanifest, MySQL.query/single/scalar/insert/update/prepare/transaction with await, placeholders, slow query warnings and debugging.
Overview
Every persistent thing on a FiveM server — characters, money, vehicles, houses — lives in a MySQL or MariaDB database, and nearly every modern resource talks to it through oxmysql. The API is small and friendly, but a few habits (placeholders, the right function for the job, not querying in loops) make the difference between a database that hums and one that lags the whole server.
Connecting
# URI form
set mysql_connection_string "mysql://fivem:[email protected]:3306/fivem?charset=utf8mb4"
# or key=value form
# set mysql_connection_string "user=fivem;password=StrongPass;host=127.0.0.1;port=3306;database=fivem"
set mysql_slow_query_warning 150
ensure oxmysqlWhere the connection line sits among the rest of your settings is shown in the server.cfg guide on the minimap studio.
Using it in a resource
fx_version 'cerulean'
game 'gta5'
server_scripts {
'@oxmysql/lib/MySQL.lua',
'server.lua',
}
dependency 'oxmysql'The query functions
| Function | Returns | Use for |
|---|---|---|
MySQL.query | Array of rows (or result info for writes) | Selecting several rows |
MySQL.single | The first row or nil | Selecting one row |
MySQL.scalar | The first column of the first row | Counts, a single value |
MySQL.insert | The inserted row’s id | INSERT |
MySQL.update | Number of affected rows | UPDATE / DELETE |
MySQL.prepare | Depends on the query | Hot queries run many times (prepared statements) |
MySQL.transaction | true/false | Several writes that must all succeed or all fail |
-- One row
local char = MySQL.single.await('SELECT * FROM characters WHERE citizenid = ?', { citizenid })
-- One value
local count = MySQL.scalar.await('SELECT COUNT(*) FROM vehicles WHERE owner = ?', { citizenid })
-- Insert, returns the new id
local id = MySQL.insert.await('INSERT INTO fines (citizenid, amount, reason) VALUES (?, ?, ?)', { citizenid, 500, 'Speeding' })
-- Update, returns affected rows
local changed = MySQL.update.await('UPDATE characters SET bank = bank + ? WHERE citizenid = ?', { 250, citizenid })
-- Several rows
local cars = MySQL.query.await('SELECT plate, model FROM vehicles WHERE owner = ?', { citizenid })
for _, car in ipairs(cars) do print(car.plate, car.model) endawait or callbacks
Each function has an .await form that pauses the current thread until the result arrives, and a callback form. .await reads top to bottom and is what most modern code uses; it must run inside a thread or an event handler (which already is one).
-- await
local row = MySQL.single.await('SELECT bank FROM characters WHERE citizenid = ?', { cid })
-- callback
MySQL.single('SELECT bank FROM characters WHERE citizenid = ?', { cid }, function(row)
print(row and row.bank)
end)Placeholders and SQL injection
Always pass values through placeholders — ? in order, or named parameters. Building SQL by gluing strings together lets a player’s name or chat message change your query, which is SQL injection. Placeholders make the database treat input purely as data.
-- NEVER
MySQL.query.await("SELECT * FROM users WHERE name = '" .. name .. "'")
-- ALWAYS
MySQL.query.await('SELECT * FROM users WHERE name = ?', { name })More on this in preventing SQL injection.
Transactions
local ok = MySQL.transaction.await({
{ query = 'UPDATE characters SET bank = bank - ? WHERE citizenid = ?', values = { 500, fromCid } },
{ query = 'UPDATE characters SET bank = bank + ? WHERE citizenid = ?', values = { 500, toCid } },
})
print('transfer committed:', ok)If either statement fails, neither is applied — so money is never created or destroyed by a half-finished transfer.
Performance habits
- Never query inside a loop over players; fetch once with
WHERE id IN (…)or cache. - Keep frequently read data (a player’s job, their inventory) in memory while they are online and write it back on change or on a timer.
- Add indexes on columns you search by (
citizenid,owner,plate). - Watch the slow-query warnings and fix the worst query first.
- Use
mysql_debugtemporarily to log queries from a specific resource while investigating.
Designing the tables themselves is covered in database design for FiveM.
Frequently asked questions
How do I connect oxmysql to my database?
Add set mysql_connection_string "mysql://user:password@host:3306/database" to server.cfg before ensure oxmysql.
What is the difference between MySQL.query and MySQL.single?
query returns all matching rows as an array; single returns only the first row (or nil).
Should I use .await or callbacks?
Either works. .await is easier to read; it must run inside a thread or event handler.
How do I find slow queries?
Set mysql_slow_query_warning (for example 150 ms) and oxmysql prints a warning for any slower query.
Why do I get a connection error?
Wrong credentials, the database server not running, or special characters in the password breaking the connection string.
Need this built, not just explained?
Ten years of FiveM work, from Lua to NUI
Custom resources, React NUI, ESX / QBCore / Qbox integration, OneSync performance audits and security reviews — plus the websites and SEO around your server brand.
Related guides
- EngineeringDesigning a database for a FiveM serverGive every table a primary key, reference characters by one stable identifier (citizenid on QBCore, identifier on ESX), index every column you search or join on, use utf8mb4 so names with emoji work, store small flexible data as JSON but put anything you search or count in real columns, and add created_at/updated_at timestamps.
- EngineeringWriting server events that cannot be abusedAny client can call any event you registered with RegisterNetEvent, with any arguments. In each handler: copy source into a local, check argument types and ranges, re-derive everything from server state (prices, amounts, rewards), verify the player can do this now (distance, job, item, cooldown), and log refusals. Events only other server scripts should use are registered with AddEventHandler alone, so clients cannot trigger them.