From mysql-async to oxmysql: Safe Migration & Query P...

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

Also read:

FiveM Server Optimization: The Definitive 2025 Playbook — https://fivemx.com/fivem-server-optimization/

Adapter Patterns: ESX↔QBCore↔QBOX Exports, Events & Player Models — https://fivemx.com/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

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 `oxmysql`

2.1 server.cfg

# 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-async disabled but available in your resources folder during the staging phase (for rapid rollback).

3) API Mapping: mysql‑async → oxmysql

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

-- 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

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

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

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

-- mysql-async

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

INSERT (get insertId)

-- mysql-async (execute)

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

UPDATE/DELETE (affectedRows)

-- mysql-async (execute)

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

Transactions (manual)

-- 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 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

-- 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 })

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

-- 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.

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

5.2 ESX Indexes (safe performance wins)

MySQL 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+

-- 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+

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+

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)

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 use inventories / items).

6) Rollback Plan (Zero‑Panic)

6.1 Code rollback

Revert your resource changes (keep a legacy-mysql-async branch).
In server.cfg swap: # 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:

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_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'
game 'gta5'
server_script 'bench.lua'

bench.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))))
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

Put the resource in a folder (e.g., ox-bench/), add ensure ox-bench to server.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 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 2025 Playbook — https://fivemx.com/fivem-server-optimization/
Adapter Patterns: ESX↔QBCore↔QBOX Exports, Events & Player Models — https://fivemx.com/adapter-patterns/

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

From mysql-async to oxmysql: Safe Migration & Query P…

TL;DR

1) Pre‑flight Safety Checklist

2) Install & Wire Up `oxmysql`

2.1 server.cfg

3) API Mapping: mysql‑async → oxmysql

3.1 Common replacements

4) Prepared Statements Cheat‑Sheet

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

5.1 Normalize Charset & Collation (UTF‑8 everywhere)

5.2 ESX Indexes (safe performance wins)

5.3 QBCore/QBOX Indexes

5.4 Optional: ox_inventory (if installed)

6) Rollback Plan (Zero‑Panic)

6.1 Code rollback

6.2 SQL rollback

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

8) Micro‑Benchmarks (Bring Your Own Numbers)

9) Troubleshooting

10) Code Review Checklist (copy/paste)

Internal Links

Luke

TL;DR

1) Pre‑flight Safety Checklist

2) Install & Wire Up oxmysql

2.1 server.cfg

3) API Mapping: mysql‑async → oxmysql

3.1 Common replacements

4) Prepared Statements Cheat‑Sheet

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

5.1 Normalize Charset & Collation (UTF‑8 everywhere)

5.2 ESX Indexes (safe performance wins)

5.3 QBCore/QBOX Indexes

5.4 Optional: ox_inventory (if installed)

6) Rollback Plan (Zero‑Panic)

6.1 Code rollback

6.2 SQL rollback

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

8) Micro‑Benchmarks (Bring Your Own Numbers)

9) Troubleshooting

10) Code Review Checklist (copy/paste)

Internal Links

Luke

Related Posts

FiveM server.cfg Guide

FiveM Error Codes & Fixes – Mega-Guide

FiveM Server Optimization: The Definitive 2025 Playbook

Trending now

2) Install & Wire Up `oxmysql`