From mysql-async to oxmysql: Safe Migration & Query Patterns

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://fivemx.com/blog/how-to-optimize-fivem-server-performance
• Adapter Patterns: ESX↔QBCore↔QBOX Exports, Events & Player Models — https://fivemx.com/blog/adapter-patterns

Credits Maintained by fivemx.com

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