Databases: SQL and oxmysql

When a player disconnects from your server, everything that lives only in memory vanishes: their money, their inventory, their cars in the garage, their identity. If you want that data to still be there next time they join, you need to save it somewhere permanent. That somewhere is a database.

A database is like a giant, very orderly spreadsheet: you have tables (users, vehicles, inventory) with rows (each player) and columns (each piece of data). Persisting means exactly that: the data survives a server restart and closing the game.

The stack: MySQL/MariaDB + oxmysql#

FiveM doesn't ship its own database. The de facto standard is MySQL (or its free twin MariaDB, nearly identical) as the engine where the data lives, and a resource called oxmysql as the «driver»: the bridge your Lua scripts use to talk to the database without wrestling with low-level connections.

• MySQL / MariaDB: the database server. Stores and returns the data.
• oxmysql: the FiveM resource that connects your scripts to that server.
• Your resources (economy, garage, inventory) ask oxmysql for data, not MySQL directly.

Installing and configuring oxmysql#

Download the latest oxmysql release from its official repository and unzip it into your resources/ folder. Then configure the connection string in your server.cfg and make sure oxmysql starts BEFORE any resource that uses it.

# server.cfg

# Connection string: user:password@host/database
set mysql_connection_string "mysql://root:myPassword@localhost/fivem?charset=utf8mb4"

# oxmysql MUST start before the resources that need it
ensure oxmysql
ensure my_economy
ensure my_garage

For a resource to use the oxmysql API, declare its library in the resource's fxmanifest.lua as a server_script (the database is always touched from the server, never from the client):

fx_version 'cerulean'
game 'gta5'

-- Import the oxmysql API into this resource
server_script '@oxmysql/lib/MySQL.lua'

server_scripts {
  'server.lua'
}

The oxmysql API#

oxmysql offers several methods depending on what you expect back. The `.await` version waits for the result synchronously inside a thread (it must run inside a server function or callback), which makes the code far more readable than nested callbacks.

local identifier = 'license:abc123'

-- query: returns ALL rows (a table of rows)
local vehicles = MySQL.query.await('SELECT * FROM vehicles WHERE owner = ?', { identifier })
for _, v in ipairs(vehicles) do print(v.plate) end

-- single: returns ONE single row (or nil)
local user = MySQL.single.await('SELECT * FROM users WHERE identifier = ?', { identifier })
print(user.name, user.money)

-- scalar: returns ONE single value (the first column of the first row)
local money = MySQL.scalar.await('SELECT money FROM users WHERE identifier = ?', { identifier })
print('Balance:', money)

-- insert: creates a row and returns the auto-generated id (insertId)
local newId = MySQL.insert.await(
  'INSERT INTO vehicles (owner, plate, model) VALUES (?, ?, ?)',
  { identifier, 'CRX 4321', 'adder' }
)
print('Vehicle saved with id', newId)

-- update: modifies rows and returns how many changed (affectedRows)
local rows = MySQL.update.await(
  'UPDATE users SET money = ? WHERE identifier = ?',
  { 5000, identifier }
)
print('Rows updated:', rows)

Parameterized queries: your shield against SQL injection#

Notice the `?` symbol in the previous examples. Each `?` is a slot that oxmysql fills with the values from the `{ ... }` table, escaping them safely. This is NOT optional: it's the only correct way to put data into a query. Never, ever build SQL by gluing player text with `..`.

-- ☠️ ANTI-PATTERN: concatenating user values. SQL INJECTION.
local name = source_input -- e.g. "x'; DROP TABLE users; --"
local bad = MySQL.query.await("SELECT * FROM users WHERE name = '" .. name .. "'")

-- ✅ CORRECT: the value travels as a parameter, never as code.
local good = MySQL.query.await('SELECT * FROM users WHERE name = ?', { name })

In the bad example, a player could enter as their name something that closes your query and runs their own, deleting entire tables or stealing data. With the `?`, that text is always treated as a literal value, never as instructions.

Designing a table#

Before saving anything, define the table. Use CREATE TABLE IF NOT EXISTS so the resource can create it automatically on startup if it doesn't exist. Every table needs a primary key (id), columns typed according to what they store, and an index on the column you usually filter by (typically identifier or citizenid).

CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `owner` VARCHAR(60) NOT NULL,       -- owner's identifier
  `plate` VARCHAR(8) NOT NULL,        -- license plate
  `model` VARCHAR(60) NOT NULL,       -- the car's spawn name
  `stored` TINYINT(1) DEFAULT 1,      -- 1 = in garage, 0 = out
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plate` (`plate`),       -- plates are never repeated
  INDEX `idx_owner` (`owner`)         -- we filter by owner: we index it
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The `idx_owner` index is the difference between an instant query and one that walks the whole table row by row. If you filter by `owner`, index `owner`. It's the cheapest, highest-impact optimization you'll ever make.

Advanced queries: filter, sort and limit#

SELECT * is fine to start with, but in production you rarely want the whole table. The WHERE (which rows), ORDER BY (in what order) and LIMIT (how many) clauses let you ask for exactly what you need. Asking for only what's needed is the first rule of performance: less data traveling, less memory, faster responses.

-- The 10 richest players, from most to least
SELECT identifier, name, money
FROM users
WHERE money > 0
ORDER BY money DESC
LIMIT 10;

-- A player's stored vehicles, most recent first
SELECT plate, model, created_at
FROM vehicles
WHERE owner = ? AND stored = 1
ORDER BY created_at DESC
LIMIT 50;

Also ask only for the columns you're going to use (identifier, name, money) instead of `*`. If your users table has a huge JSON blob with the inventory, fetching it when you only want the balance is pure waste.

JOINs: crossing related tables#

A RP server's data is spread across several tables linked by a common key: the `identifier` (ESX) or the `citizenid` (QBCore). A JOIN links two tables by that key to return a combined view in a single query, instead of querying users and then querying vehicles.

-- INNER JOIN: only players who HAVE at least one vehicle
SELECT u.name, u.identifier, v.plate, v.model
FROM users u
INNER JOIN vehicles v ON v.owner = u.identifier
WHERE u.identifier = ?;

-- LEFT JOIN: ALL players, whether they have cars or not.
-- Those who don't return NULL in the vehicles columns.
SELECT u.name, u.identifier, v.plate
FROM users u
LEFT JOIN vehicles v ON v.owner = u.identifier
ORDER BY u.name;

The key difference: INNER JOIN discards rows with no match (a player with no cars doesn't appear); LEFT JOIN keeps all rows from the left table and fills with NULL the columns that find no match. For a JOIN to be fast, the column you join by (`v.owner`) must be indexed.

Aggregates: COUNT, SUM and GROUP BY#

Sometimes you don't want the rows, you want a summary: how many cars does each player have? how much money is there in total on the server? That's what aggregation functions are for, usually paired with GROUP BY, which groups rows by a value before counting or summing them.

-- How many cars does EACH owner have? (garage ranking)
SELECT owner, COUNT(*) AS total_cars
FROM vehicles
GROUP BY owner
ORDER BY total_cars DESC;

-- Total money and average per player across the whole server
SELECT COUNT(*) AS players,
       SUM(money) AS total_money,
       AVG(money) AS average
FROM users;

-- Only owners with MORE than 3 cars (HAVING filters on the aggregate)
SELECT owner, COUNT(*) AS total
FROM vehicles
GROUP BY owner
HAVING total > 3;

Indexes in depth: why your server flies or crawls#

Without an index, MySQL does a «full table scan»: it reads the whole table row by row to find the matching ones. With 50 rows you won't notice; with 200,000 vehicles, each unindexed query can take hundreds of milliseconds and block the thread. An index is like the alphabetical index of a book: instead of reading the whole thing, it goes straight to the page.

• Simple index: on one column. Speeds up filters and JOINs by that column (idx_owner on vehicles).
• Composite index: on several columns in order. Serves queries that filter by that prefix. An index (owner, stored) helps WHERE owner = ? AND stored = ?, and also WHERE owner = ? alone, but NOT WHERE stored = ? alone.
• Unique key (UNIQUE): besides indexing, it prevents duplicates (plates, identifiers).
• Cost: each index takes up space and slightly slows INSERT/UPDATE (it has to be maintained). Index what you truly filter by, not everything «just in case».

-- Composite index for the garage query (owner + stored)
CREATE INDEX `idx_owner_stored` ON `vehicles` (`owner`, `stored`);

-- Is my index being used? EXPLAIN tells you.
EXPLAIN SELECT plate FROM vehicles WHERE owner = 'license:abc' AND stored = 1;

EXPLAIN in front of any SELECT shows you the execution plan without actually running it. Look at the `type` column: «ref» or «eq_ref» mean it's using an index (good); «ALL» means a full table scan (bad, a missing index). The `key` column tells you which index it chose, and `rows` how many rows it estimates it will examine. Fewer is better.

Transactions: all or nothing#

Imagine transferring €1,000 from one player to another: you subtract 1,000 from the first and add 1,000 to the second. That's two UPDATEs. What happens if the server crashes right between the first and the second? The first player loses their money and the second never receives it: money evaporated. A transaction groups several queries into one atomic unit: either they ALL apply, or NONE does.

-- Atomic transfer between two players with oxmysql
local function transfer(fromSource, toTarget, amount)
  local ok = MySQL.transaction.await({
    { 'UPDATE users SET money = money - ? WHERE identifier = ? AND money >= ?',
      { amount, fromSource, amount } },
    { 'UPDATE users SET money = money + ? WHERE identifier = ?',
      { amount, toTarget } },
  })

  -- transaction.await returns true only if ALL queries succeeded.
  -- If any fails, oxmysql does a ROLLBACK: nobody loses or gains money.
  if ok then
    print('Transfer completed')
  else
    print('Transfer reverted (rollback)')
  end
  return ok
end

SQL functions (stored functions)#

A stored function is a piece of logic that lives INSIDE the database, takes parameters and returns a single value. It's useful for centralizing a calculation you use in many queries. They're created with DELIMITER so MySQL doesn't confuse the internal `;` with the end of the statement, and they're marked DETERMINISTIC if the same inputs always return the same result.

DELIMITER //

-- Calculates a player's level from their XP
CREATE FUNCTION level_by_xp(xp INT)
RETURNS INT
DETERMINISTIC
BEGIN
  -- 1000 XP per level, minimum level 1
  RETURN GREATEST(1, FLOOR(xp / 1000) + 1);
END //

DELIMITER ;

-- It's used like any native function, inside a SELECT:
SELECT name, xp, level_by_xp(xp) AS level
FROM users
ORDER BY level DESC;

The advantage is that the «1000 XP per level» rule lives in a single place. If it changes tomorrow, you touch it once and every query that uses level_by_xp() is updated. The drawback: game logic split between Lua and SQL is harder to follow, so reserve functions for purely data-based calculations.

Stored procedures: multi-step routines#

A procedure is like a function, but it can run several statements, doesn't necessarily return a value (it uses OUT parameters) and can modify data. You use it when a data operation has several steps that always go together and you want to run it with a single call (CALL).

DELIMITER //

CREATE PROCEDURE buy_vehicle(
  IN p_owner   VARCHAR(60),
  IN p_plate   VARCHAR(8),
  IN p_model   VARCHAR(60),
  IN p_price   INT,
  OUT p_ok     TINYINT     -- 1 = bought, 0 = insufficient funds
)
BEGIN
  DECLARE balance INT;
  SELECT money INTO balance FROM users WHERE identifier = p_owner;

  IF balance >= p_price THEN
    UPDATE users SET money = money - p_price WHERE identifier = p_owner;
    INSERT INTO vehicles (owner, plate, model) VALUES (p_owner, p_plate, p_model);
    SET p_ok = 1;
  ELSE
    SET p_ok = 0;
  END IF;
END //

DELIMITER ;

-- Call (from another SQL or a client):
CALL buy_vehicle('license:abc', 'CRX 0001', 'adder', 100000, @result);
SELECT @result;

Procedure or logic in Lua? In FiveM, you'll almost always prefer Lua: it's where the rest of your game logic lives, you debug it with prints and you version it easily with Git. Reserve procedures for data-intensive operations (bulk cleanups, maintenance, migrations) where moving that logic to the SQL server avoids pulling thousands of rows into Lua just to rewrite them.

Triggers: automatic reactions to changes#

A trigger is SQL code that fires by itself when an INSERT, UPDATE or DELETE happens on a table, without anyone calling it. They're ideal for auditing (recording who changed what), maintaining derived counters or setting automatic timestamps, because the rule is ALWAYS enforced, whether the change comes from your economy resource, another script or an admin touching the database by hand.

-- Audit table for money movements
CREATE TABLE IF NOT EXISTS `money_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(60) NOT NULL,
  `old_money` INT NOT NULL,
  `new_money` INT NOT NULL,
  `diff` INT NOT NULL,
  `changed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER //

-- Every time a player's money changes, we record it
CREATE TRIGGER trg_money_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.money <> OLD.money THEN
    INSERT INTO money_log (identifier, old_money, new_money, diff)
    VALUES (NEW.identifier, OLD.money, NEW.money, NEW.money - OLD.money);
  END IF;
END //

DELIMITER ;

Inside the trigger, OLD refers to the row before the change and NEW to the row after. Here we only record if the money actually changed (NEW.money <> OLD.money), avoiding noise. With this you detect money duplication (a huge, unexplained diff) by reviewing money_log.

Migrations and schema versioning#

Your schema (the tables and their columns) evolves: today you add a `level` column, tomorrow a `factions` table. A migration is a .sql file with those changes, numbered and kept in Git, so anyone can rebuild the database step by step and you never have changes «in your head» that get lost.

• Number the files: 0001_init.sql, 0002_add_vehicles.sql, 0003_add_level.sql. The order matters.
• Idempotency: use CREATE TABLE IF NOT EXISTS and, for columns, check before adding. That way re-running a migration doesn't break anything.
• Never edit a migration already applied in production: create a new one. History is immutable.
• Destructive changes (DROP COLUMN, DROP TABLE) require extra care: once applied, the data is gone.
• BACKUP before migrating production. Always. A 30-second mysqldump saves you from losing the server's entire economy.

-- 0003_add_level.sql, add a column without breaking existing data
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `level` INT NOT NULL DEFAULT 1;

-- Backups before touching production (in the terminal, not in SQL):
-- mysqldump -u root -p fivem > backup_2026_06_29.sql

Best practices and performance#

• ALWAYS parameterize with ?. No exceptions: any data coming from the player is hostile until proven otherwise.
• Don't query inside loops or every frame. Instead of one SELECT per element, read everything at once with a WHERE ... IN (?) or a JOIN and process the result in Lua.
• Batch inserts: if you're going to save 100 items, use a transaction or a single INSERT with multiple rows instead of 100 separate inserts. It reduces round trips to the database.
• Use .await without fear: it doesn't block the whole server, it only pauses the current thread while the rest of the server keeps running.
• Index the columns you filter and join by (identifier, citizenid, owner). Without an index, queries become painfully slow as the table grows.
• Don't store a giant JSON if you need to filter inside it: if you look for «everyone who has item X», that should be its own column or table, not a blob you have to walk through in Lua.
• Don't store secrets in plain text: passwords, tokens or API keys are never stored as-is; hash them or don't even put them in the database.
• Write to the database only when necessary (on disconnect, on purchase), not every frame. Over-saving hurts performance.