From mysql-async to oxmysql: Safe Migration & Query Patterns
August 16, 2025
Last Updated: February 26, 2026
10 min read
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/en/blog/how-to-optimize-fivem-server-performance
- Adapter Patterns: ESX↔QBCore↔QBOX Exports, Events & Player Models — https://vertexmods.com/en/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; replaceMySQL.Async.*calls withMySQL.*/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
- Full backup:
mysqldump --single-transaction yourdb > backup.sql.- Staging env mirroring production schema + data subset.
- Artifact & deps: Current FXServer build, latest
oxmysql.- Downtime window for prod switch (usually < 5 minutes).
- Health probes ready:
/players, login flow, economy ops, garage ops, inventory ops, ban checks.2) Install & Wire Up
oxmysql2.1 server.cfg
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# Stop using mysql-async
default_prio 500
# ensure mysql-async # ← comment out or remove
# Start oxmysql
default_prio 50
ensure oxmysql
# Connection string consumed by oxmysql
set mysql_connection_string "mysql://user:pass@127.0.0.1:3306/yourdb?charset=utf8mb4"
# Optional diagnostics
set mysql_slow_query_warning 200 # log queries slower than 200ms
set mysql_debug false # true for verbose logging during staging
Keep
mysql-asyncdisabled 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:
@paramstyle 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:namevia object
3.1 Common replacements
SELECT many
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 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
text
1
2
3
4
5
6
7
-- mysql-async (fetchAll + rows[1])
-- oxmysql
local row = MySQL.single.await(
'SELECT * FROM users WHERE identifier = ?',
{ identifier }
)
SELECT scalar (e.g., count, id)
text
1
2
3
4
5
6
7
-- mysql-async
-- oxmysql
local count = MySQL.scalar.await(
'SELECT COUNT(*) FROM owned_vehicles WHERE owner = ?',
{ owner }
)
INSERT (get insertId)
text
1
2
3
4
5
6
7
-- mysql-async (execute)
-- oxmysql
local insertId = MySQL.insert.await(
'INSERT INTO notes (owner, text) VALUES (?, ?)',
{ cid, text }
)
UPDATE/DELETE (affectedRows)
text
1
2
3
4
5
6
7
-- mysql-async (execute)
-- oxmysql
local changed = MySQL.update.await(
'UPDATE users SET job = ?, job_grade = ? WHERE identifier = ?',
{ job, grade, identifier }
)
Transactions (manual)
text
1
2
3
4
5
6
7
8
9
10
11
12
-- 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 addMySQL.ready,.transaction, etc. The calls above are safe without extra wrappers.
4) Prepared Statements Cheat‑Sheet
Param styles
- mysql‑async (legacy):
@nameplaceholders with a table:{ ['@name']=value } - oxmysql (positional):
?placeholders with an array:{ value1, value2 } - oxmysql (named):
:nameplaceholders with an object:{ name = value }
Examples
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 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
text
1
2
3
4
5
6
7
8
-- 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+
text
1
2
3
4
5
6
7
8
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`);
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+
text
1
2
3
4
5
6
7
8
9
10
11
12
-- 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+
text
1
2
3
4
5
6
7
8
9
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`);
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+
text
1
2
3
4
5
6
7
8
9
10
11
12
13
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)
text
1
2
3
4
5
6
ALTER TABLE `ox_inventory`
ADD INDEX IF NOT EXISTS `idx_inv_owner` (`owner`),
ADD INDEX IF NOT EXISTS `idx_inv_type` (`type`);
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
- Revert your resource changes (keep a
legacy-mysql-asyncbranch). - In
server.cfgswap:# ensure oxmysql ensure mysql-async - 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:
text
1
2
3
4
5
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)
- Freeze deploys, back up DB.
- Apply Section 5 “UP” SQL on staging → verify → prod.
- Commit code refactor: replace calls (Section 3) + parameter styles (Section 4).
- Deploy,
ensure oxmysql, restart server. - Run smoke tests (login, paychecks, inventory, vehicle spawn/despawn, bans, society money, job duty toggles).
- Watch logs for 15–30 minutes (
mysql_slow_query_warninghelps); 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
text
1
2
3
fx_version 'cerulean'
game 'gta5'
server_script 'bench.lua'
bench.lua
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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))))
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
- Put the resource in a folder (e.g.,
ox-bench/), addensure ox-benchtoserver.cfg. - Tail server console; results print as lines like:
[bench] SELECT single: 134.21 ms. - For a before/after comparison, run once with
mysql-async(adjust the calls if necessary), then withoxmysql.
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/.scalarwithLIMIT 1when only one row/value is required. - Batch
IN (...)reads for collections. - Transactions around multi‑step money/inventory ops.
- Index present for every hot
WHERE/JOINcolumn. - Avoid
SELECT *in hot paths. - Log slow queries; track top offenders weekly.
Internal Links
- FiveM Server Optimization: The Definitive 2026 Playbook — https://vertexmods.com/en/blog/how-to-optimize-fivem-server-performance
- Adapter Patterns: ESX↔QBCore↔QBOX Exports, Events & Player Models — https://vertexmods.com/en/blog/adapter-patterns
Credits Maintained by vertexmods.com
Credits
Maintained by vertexmods.com. Contributions welcome (send diffs of additional safe indexes or wrapper helpers).
Stay in the Loop
Get the latest FiveM tutorials, mod releases, and exclusive updates delivered to your inbox.
No spam. Unsubscribe anytime.
Share Article