Introduction to Audience: FiveM server owners, scripters, maintainers

Audience: FiveM server owners, scripters, maintainers
Goal: Replace mysql-async with oxmysql safely, speed up queries, and modernize your SQL usage.

This guide is part of our complete FiveM frameworks guide, where we compare ESX, QBCore, and QBOX in depth and help you choose the right one.

Also read:

• FiveM Server Optimization: The Definitive 2026 Playbook — https://vertexmods.com/blog/how-to-optimize-fivem-server-performance
• Adapter Patterns: ESX↔QBCore↔QBOX Exports, Events & Player Models — https://vertexmods.com/blog/adapter-patterns

---

TL;DR

• Use oxmysql: prepared statements, promise/await API, better diagnostics, strong performance.
• Minimal code changes: swap @param → ? (positional) or :name (named) params; replace MySQL.Async.* calls with MySQL.*/exports.oxmysql:*.
• Run the SQL “UP” scripts below (charset/index fixes) and keep the rollback handy.
• Verify with the micro‑benchmark harness at the end to confirm wins on your hardware.

---

1) Pre‑flight Safety Checklist

1. Full backup: mysqldump --single-transaction yourdb > backup.sql.
2. Staging env mirroring production schema + data subset.
3. Artifact & deps: Current FXServer build, latest oxmysql.
4. Downtime window for prod switch (usually < 5 minutes).
5. Health probes ready: /players, login flow, economy ops, garage ops, inventory ops, ban checks.

2) Install & Wire Up oxmysql

2.1 server.cfg

# Stop using mysql-async default_prio 500

ensure mysql-async # ← comment out or remove

Start oxmysql

default_prio 50

```lua
```lua
ensure oxmysql

Connection string consumed by oxmysql

```lua
```lua
set mysql_connection_string "mysql://user:pass@127.0.0.1:3306/yourdb?charset=utf8mb4"

Optional diagnostics

```lua
```lua
set mysql_slow_query_warning 200   # log queries slower than 200ms
set mysql_debug false              # true for verbose logging during staging

Keep mysql-async disabled but available in your resources folder during the staging phase (for rapid rollback).

3) API Mapping: mysql‑async → oxmysql

mysql-async (legacy):

mysql-async (legacy):

• Async: MySQL.Async.fetchAll, MySQL.Async.fetchScalar, MySQL.Async.execute
• Sync: MySQL.Sync.fetchAll, MySQL.Sync.fetchScalar, MySQL.Sync.execute
• Parameters: @param style tables like { ['@identifier']=identifier }

oxmysql (modern):

• Callback style via export: exports.oxmysql:query|scalar|single|insert|update(sql, params, cb)
• Promise/await via global: MySQL.query|scalar|single|insert|update.await(sql, params) and non-await callbacks without .await
• Parameters: positional ? via array, or named :name via object

3.1 Common replacements

SELECT many

```lua
```lua
-- mysql-async
MySQL.Async.fetchAll(
  'SELECT * FROM users WHERE identifier = @id',
  { ['@id'] = identifier },
  function(rows) ... end
)

-- oxmysql (callback via export)
exports.oxmysql:query(
  'SELECT * FROM users WHERE identifier = ?',
  { identifier },
  function(rows) ... end
)

-- oxmysql (await)
local rows = MySQL.query.await(
  'SELECT * FROM users WHERE identifier = ?',
  { identifier }
)

SELECT single row

```lua
```lua
-- mysql-async (fetchAll + rows[1])

-- oxmysql
local row = MySQL.single.await(
  'SELECT * FROM users WHERE identifier = ?',
  { identifier }
)

SELECT scalar (e.g., count, id)

```lua
```lua
-- mysql-async

-- oxmysql
local count = MySQL.scalar.await(
  'SELECT COUNT(*) FROM owned_vehicles WHERE owner = ?',
  { owner }
)

INSERT (get insertId)

```lua
```lua
-- mysql-async (execute)

-- oxmysql
local insertId = MySQL.insert.await(
  'INSERT INTO notes (owner, text) VALUES (?, ?)',
  { cid, text }
)

UPDATE/DELETE (affectedRows)

```lua
```lua
-- mysql-async (execute)

-- oxmysql
local changed = MySQL.update.await(
  'UPDATE users SET job = ?, job_grade = ? WHERE identifier = ?',
  { job, grade, identifier }
)

Transactions (manual)

```lua
```lua
-- oxmysql manual transaction
MySQL.query.await('START TRANSACTION')
local ok = true

local r1 = MySQL.update.await('UPDATE users SET bank = bank - ? WHERE identifier = ? AND bank >= ?', { amount, fromId, amount })
local r2 = MySQL.update.await('UPDATE users SET bank = bank + ? WHERE identifier = ?', { amount, toId })

if r1 == 1 and r2 == 1 then
  MySQL.query.await('COMMIT')
else
  MySQL.query.await('ROLLBACK')
end

Some frameworks expose wrappers (e.g., ox_lib ) that

Some frameworks expose wrappers (e.g., ox_lib) that add MySQL.ready, .transaction, etc. The calls above are safe without extra wrappers.

4) Prepared Statements Cheat‑Sheet

Param styles

• mysql‑async (legacy): @name placeholders with a table: { ['@name']=value }
• oxmysql (positional): ? placeholders with an array: { value1, value2 }
• oxmysql (named): :name placeholders with an object: { name = value }

Examples

```lua
```lua
-- Named params (recommended for readability)
local row = MySQL.single.await(
  'SELECT * FROM users WHERE identifier = :id',
  { id = identifier }
)

-- IN (...) list
-- Build placeholders dynamically and pass a flat array
local ids = { 'cid1','cid2','cid3' }
local qs = ('?,' ):rep(#ids):sub(1,-2) -- "?, ?, ?"
local rows = MySQL.query.await('SELECT * FROM players WHERE citizenid IN ('..qs..')', ids)

-- JSON fields (MySQL 5.7+/MariaDB 10.2+)
local name = MySQL.scalar.await('SELECT JSON_UNQUOTE(JSON_EXTRACT(data, "$.name")) FROM players WHERE citizenid = ?', { cid })

Do

• Use prepared statements everywhere (never string‑concatenate user input).
• Prefer named params for clarity in complex statements.
• Add LIMIT 1 when reading a single entity.

Avoid

• Wildcard SELECT * in hot paths (project needed columns).
• Per‑row N+1 queries; batch with IN (...).

5) Database “UP” Migration Scripts (Ready‑to‑Run)

Choose the blocks that match your framework (ESX/QBCore) and server (MySQL 8+ or MariaDB 10.4+). Run on staging first.

5.1 Normalize Charset & Collation (UTF‑8 everywhere)

(A) MySQL 8+ — replace yourdb once

```sql
```sql
-- Force database default to utf8mb4 (emoji‑safe)
ALTER DATABASE \`yourdb\` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Convert common tables (extend list as needed)
ALTER TABLE \`users\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE \`owned_vehicles\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE \`players\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE \`player_vehicles\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

(B) MariaDB 10.4+ — same statements are valid.

(B) MariaDB 10.4+ — same statements are valid.

Add other hot tables (inventory, billing, phone, society, jobs) as present in your server.

5.2 ESX Indexes (safe performance wins)

MySQL 8+

```sql
```sql
ALTER TABLE \`users\`

ADD INDEX IF NOT EXISTS `idx_users_identifier` (`identifier`), ADD INDEX IF NOT EXISTS `idx_users_job` (`job`), ADD INDEX IF NOT EXISTS `idx_users_name` (`name`);

```sql
```sql
ALTER TABLE \`owned_vehicles\`

ADD UNIQUE INDEX IF NOT EXISTS `ux_owned_vehicles_plate` (`plate`), ADD INDEX IF NOT EXISTS `idx_owned_vehicles_owner` (`owner`);

MariaDB 10.4+

```lua
```lua
-- Drop first to be idempotent where IF NOT EXISTS is unavailable
DROP INDEX IF EXISTS \`idx_users_identifier\` ON \`users\`;
DROP INDEX IF EXISTS \`idx_users_job\` ON \`users\`;
DROP INDEX IF EXISTS \`idx_users_name\` ON \`users\`;
CREATE INDEX \`idx_users_identifier\` ON \`users\` (\`identifier\`);
CREATE INDEX \`idx_users_job\` ON \`users\` (\`job\`);
CREATE INDEX \`idx_users_name\` ON \`users\` (\`name\`);

DROP INDEX IF EXISTS \`ux_owned_vehicles_plate\` ON \`owned_vehicles\`;
DROP INDEX IF EXISTS \`idx_owned_vehicles_owner\` ON \`owned_vehicles\`;
CREATE UNIQUE INDEX \`ux_owned_vehicles_plate\` ON \`owned_vehicles\` (\`plate\`);
CREATE INDEX \`idx_owned_vehicles_owner\` ON \`owned_vehicles\` (\`owner\`);

5.3 QBCore/QBOX Indexes

MySQL 8+

```sql
```sql
ALTER TABLE \`players\`

ADD UNIQUE INDEX IF NOT EXISTS `ux_players_citizenid` (`citizenid`), ADD INDEX IF NOT EXISTS `idx_players_license` (`license`), ADD INDEX IF NOT EXISTS `idx_players_steam` (`steam`), ADD INDEX IF NOT EXISTS `idx_players_last_name` (`lastname`);

```sql
```sql
ALTER TABLE \`player_vehicles\`

ADD UNIQUE INDEX IF NOT EXISTS `ux_player_vehicles_plate` (`plate`), ADD INDEX IF NOT EXISTS `idx_player_vehicles_citizenid` (`citizenid`);

MariaDB 10.4+

```lua
```lua
DROP INDEX IF EXISTS \`ux_players_citizenid\` ON \`players\`;
DROP INDEX IF EXISTS \`idx_players_license\` ON \`players\`;
DROP INDEX IF EXISTS \`idx_players_steam\` ON \`players\`;
DROP INDEX IF EXISTS \`idx_players_last_name\` ON \`players\`;
CREATE UNIQUE INDEX \`ux_players_citizenid\` ON \`players\` (\`citizenid\`);
CREATE INDEX \`idx_players_license\` ON \`players\` (\`license\`);
CREATE INDEX \`idx_players_steam\` ON \`players\` (\`steam\`);
CREATE INDEX \`idx_players_last_name\` ON \`players\` (\`lastname\`);

DROP INDEX IF EXISTS \`ux_player_vehicles_plate\` ON \`player_vehicles\`;
DROP INDEX IF EXISTS \`idx_player_vehicles_citizenid\` ON \`player_vehicles\`;
CREATE UNIQUE INDEX \`ux_player_vehicles_plate\` ON \`player_vehicles\` (\`plate\`);
CREATE INDEX \`idx_player_vehicles_citizenid\` ON \`player_vehicles\` (\`citizenid\`);

5.4 Optional: ox_inventory (if installed)

```sql
```sql
ALTER TABLE \`ox_inventory\`

ADD INDEX IF NOT EXISTS `idx_inv_owner` (`owner`), ADD INDEX IF NOT EXISTS `idx_inv_type` (`type`);

```sql
```sql
ALTER TABLE \`ox_inventory_items\`

ADD INDEX IF NOT EXISTS `idx_items_inv_owner_name` (`inventory`, `owner`, `name`);

Adjust table names if your schema differs (some setups

Adjust table names if your schema differs (some setups use inventories / items).

6) Rollback Plan (Zero‑Panic)

6.1 Code rollback

1. Revert your resource changes (keep a legacy-mysql-async branch).
2. In server.cfg swap: # ensure oxmysql ensure mysql-async
3. Restart FXServer or the affected resources in dependency order.

6.2 SQL rollback

• If you only added indexes: drop them (see the MariaDB blocks above — use DROP INDEX IF EXISTS).
• If you changed charset/collation and must undo, revert the DB and tables:

```sql
```sql
ALTER DATABASE \`yourdb\` CHARACTER SET = utf8 COLLATE = utf8_general_ci;
ALTER TABLE \`users\` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE \`owned_vehicles\` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE \`players\` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE \`player_vehicles\` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Prefer restoring from backup.sql instead of mass charset reversals when possible.

7) End‑to‑End Migration Procedure (Scriptable)

1. Freeze deploys, back up DB.
2. Apply Section 5 “UP” SQL on staging → verify → prod.
3. Commit code refactor: replace calls (Section 3) + parameter styles (Section 4).
4. Deploy, ensure oxmysql, restart server.
5. Run smoke tests (login, paychecks, inventory, vehicle spawn/despawn, bans, society money, job duty toggles).
6. Watch logs for 15–30 minutes (mysql_slow_query_warning helps); address any missed params or schema mismatches.

8) Micro‑Benchmarks (Bring Your Own Numbers)

A tiny resource you can drop in to compare hot‑path queries on your hardware and dataset.

fxmanifest.lua

fx_version 'cerulean'

```lua
```lua
game 'gta5'

server_script 'bench.lua'

bench.lua

```lua
```lua
local COUNT = 2000  -- adjust for your server

local function bench(name, fn)
  local t0 = os.clock()
  local ok, err = pcall(fn)
  local dt = (os.clock() - t0) * 1000.0

print(('[bench] %s: %.2f ms %s'):format(name, dt, ok and '' or ('ERR: '..tostring(err))))

```lua
```lua
end

-- Hot path 1: ownership lookup
bench('SELECT single', function()
  for i=1,COUNT do
    local row = MySQL.single.await('SELECT owner FROM owned_vehicles WHERE plate = :p LIMIT 1', { p = ('TEST%04d'):format(i % 500) })
  end
end)

-- Hot path 2: batched fetch
bench('SELECT batch IN', function()
  local ids = {}
  for i=1,100 do ids[#ids+1] = ('cid%04d'):format(i) end
  local qs = ('?,' ):rep(#ids):sub(1,-2)
  local rows = MySQL.query.await('SELECT citizenid, firstname, lastname FROM players WHERE citizenid IN ('..qs..')', ids)
end)

-- Hot path 3: update with guard
bench('UPDATE guarded', function()
  for i=1,COUNT do
    local changed = MySQL.update.await('UPDATE users SET bank = bank + :d WHERE identifier = :id AND bank >= 0', { d = 1, id = ('lic:%04d'):format(i % 500) })
  end
end)

How to run

How to run

1. Put the resource in a folder (e.g., ox-bench/), add ensure ox-bench to server.cfg.
2. Tail server console; results print as lines like: [bench] SELECT single: 134.21 ms.
3. For a before/after comparison, run once with mysql-async (adjust the calls if necessary), then with oxmysql.

What to look for

• Lower total ms per section after migration.
• Lower P95/P99 latency on gameplay actions tied to queries.
• Fewer slow‑query warnings over an hour of live play.

9) Troubleshooting

Q: I get “no such export: query/single/…”.
A: oxmysql isn’t started early enough. Ensure ensure oxmysql is above resources that use it.

Q: Parameter errors or empty results.
A: You likely kept @param placeholders. Replace with ? or :name and pass an array/object accordingly.

Q: Deadlocks or partial writes.
A: Wrap multi‑step balances/transfers in a transaction (see Section 3), add indexes from Section 5.

Q: JSON path returns NULL.
A: Confirm your engine supports JSON functions (MySQL ≥5.7/MariaDB ≥10.2) and that the column type is JSON (not LONGTEXT).

Q: Slow after migration.
A: Check missing indexes, EXPLAIN your query, project only needed columns, and review the Server Optimization Playbook.

10) Code Review Checklist (copy/paste)

• No string‑concatenated SQL; all queries parameterized.
• Use .single/.scalar with LIMIT 1 when only one row/value is required.
• Batch IN (...) reads for collections.
• Transactions around multi‑step money/inventory ops.
• Index present for every hot WHERE/JOIN column.
• Avoid SELECT * in hot paths.
• Log slow queries; track top offenders weekly.

---

Internal Links

• FiveM Server Optimization: The Definitive 2026 Playbook — https://vertexmods.com/blog/how-to-optimize-fivem-server-performance
• Adapter Patterns: ESX↔QBCore↔QBOX Exports, Events & Player Models — https://vertexmods.com/blog/adapter-patterns

---

Credits Maintained by vertexmods.com

Credits
Maintained by vertexmods.com. Contributions welcome (send diffs of additional safe indexes or wrapper helpers).