Introduction to Use‑case: You’re moving from ESX to QBCore or QBOX

Use‑case: You’re moving from ESX to QBCore or QBOX (qbx_core) and need a clean, auditable migration of player identifiers and balances. This guide gives you production‑ready SQL, a reversible plan, and validation steps.

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.

Related reads:

• Adapter Patterns: ESX↔QBCore↔QBOX (Exports, Events & Player Models) — https://vertexmods.com/blog/adapter-patterns
• Converting FiveM Scripts – ESX, QBCore, QBOX (Framework Guide) — https://vertexmods.com/blog/converting-fivem-scripts/ (Pillar)

---

What changes between ESX and QBCore/QBOX

Topic	ESX (common)	QBCore / QBOX (common)
Primary player key	identifier (e.g., license:xxx or legacy steam:xxx)	citizenid (server‑generated token)
Alt identifiers	users.identifier, sometimes a separate identifiers table	columns like license, steam, fivem stored alongside citizenid
Money model	Separate accounts (cash/bank/black_money) via users.accounts (JSON) or user_accounts rows	Single money JSON on players (e.g., { "cash": 0, "bank": 5000 }); optional extra wallets
Vehicles	owned_vehicles.owner refers to ESX identifier	player_vehicles.citizenid (or license on some forks)

QBOX generally follows QB’s DB shape. Treat QBOX as “QB schema + qbx additions.” Always diff your live schema.

---

Golden rules (don’t skip)

1. Freeze writes during migration (stop the game server + any external bots touching DB).
2. Full backup and a dump of table structures. Store both with timestamps.
3. Work in a transaction per table if possible; keep steps idempotent.
4. Create a crosswalk (old_identifier → citizenid) you can reuse or roll back to.

Target you’re aiming for (QB/QBOX baseline)

A typical players table (columns vary by fork):

-- Inspect your actual schema and adjust.

DESCRIBE players; -- Expect columns like: citizenid, license, name, money, charinfo, job, gang, metadata

• citizenid: primary key used across QB/QBOX.
• license/steam: keep for forensics and re‑linking.
• money (JSON): e.g. {"cash":123,"bank":456}. Some servers add crypto, dirty, etc.

---

Step 0 — Snapshot & staging

# MySQL/MariaDB backup mysqldump -u root -p --routines --triggers yourdb > yourdb_$(date +%F_%H%M).sql

Optional: structure‑only snapshot

mysqldump -u root -p --no-data yourdb > yourdb_schema_$(date +%F_%H%M).sql

Spin up a staging copy

Spin up a staging copy. Run everything there first

Step 1 — Build the crosswalk table

We’ll map every ESX identifier to a new citizenid. If you already have a players table with citizenids, you’ll invert the mapping (see Existing QB players note below).

-- 1) Create crosswalk
CREATE TABLE IF NOT EXISTS identifier_crosswalk (

old_identifier VARCHAR(60) PRIMARY KEY, citizenid VARCHAR(20) NOT NULL, license VARCHAR(60) NULL, steam VARCHAR(60) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2) Seed from ESX users (adjust table/column names to your ESX flavor)
-- Common ESX has \`users.identifier\` holding license:xxx or steam:xxx
INSERT IGNORE INTO identifier_crosswalk (old_identifier, license, steam, citizenid)

SELECT u.identifier AS old_identifier, CASE WHEN u.identifier LIKE 'license:%' THEN u.identifier ELSE NULL END AS license, CASE WHEN u.identifier LIKE 'steam:%' THEN u.identifier ELSE NULL END AS steam, UPPER(SUBSTRING(REPLACE(UUID(),'-',''),1,10)) AS citizenid FROM users u;

-- 3) If you have a separate \`identifiers\` table, merge best‑known values
-- Example (optional): prefer license when available
UPDATE identifier_crosswalk x

JOIN (

  SELECT i1.identifier AS old_identifier,
         MAX(CASE WHEN i1.type='license' THEN i1.value END) AS license,
         MAX(CASE WHEN i1.type='steam'   THEN i1.value END) AS steam

FROM identifiers i1 GROUP BY i1.identifier

) i ON i.old_identifier = x.old_identifier

SET x.license = COALESCE(i.license, x.license),

    x.steam   = COALESCE(i.steam,   x.steam);

-- 4) Uniqueness & indexes
ALTER TABLE identifier_crosswalk

ADD UNIQUE KEY ux_cid (citizenid), ADD KEY ix_license (license), ADD KEY ix_steam (steam);

Existing QB players? If you already have players rows, create the crosswalk by selecting their license/steam and existing citizenid instead of generating new ones. Your crosswalk must never assign a new citizenid to an existing QB player.

---

Step 2 — Normalize/prepare target players rows

Create any missing players rows based on ESX users

Create any missing players rows based on ESX users.

-- Ensure \`players\` exists and inspect its columns first.
-- We’ll insert shells for missing citizens only.

INSERT INTO players (citizenid, license, name, money, charinfo, metadata)

SELECT x.citizenid, COALESCE(NULLIF(x.license,''), NULLIF(x.steam,'')) AS license_like, COALESCE(u.firstname, '') || ' ' || COALESCE(u.lastname, '') AS name_like,

  '{"cash":0,"bank":0}' AS money,

JSON_OBJECT(

    'firstName', COALESCE(u.firstname,''),
    'lastName',  COALESCE(u.lastname,''),
    'birthdate', COALESCE(u.dateofbirth,''),
    'gender',    COALESCE(u.sex,'')
  ) AS charinfo,

JSON_OBJECT('esx_identifier', u.identifier) AS metadata FROM users u JOIN identifier_crosswalk x ON x.old_identifier = u.identifier LEFT JOIN players p ON p.citizenid = x.citizenid WHERE p.citizenid IS NULL;

Note: Use your SQL flavor’s string concat (CONCAT in MySQL) and JSON functions accordingly. For MySQL 5.7, replace JSON_OBJECT with manual string building if needed.

MySQL‑safe variant:

INSERT INTO players (citizenid, license, name, money, charinfo, metadata)

SELECT x.citizenid, COALESCE(NULLIF(x.license,''), NULLIF(x.steam,'')) AS license_like, TRIM(CONCAT(COALESCE(u.firstname,''), ' ', COALESCE(u.lastname,''))) AS name_like,

  '{"cash":0,"bank":0}' AS money,
  CONCAT('{',
    '"firstName":"', REPLACE(COALESCE(u.firstname,''),'"','\\"'), '",',
    '"lastName":"',  REPLACE(COALESCE(u.lastname,''),'"','\\"'),  '",',
    '"birthdate":"', REPLACE(COALESCE(u.dateofbirth,''),'"','\\"'),'",',
    '"gender":"',    REPLACE(COALESCE(u.sex,''),'"','\\"'),       '"',
  '}') AS charinfo,
  CONCAT('{',
    '"esx_identifier":"', REPLACE(u.identifier,'"','\\"'), '"',
  '}') AS metadata

FROM users u JOIN identifier_crosswalk x ON x.old_identifier = u.identifier LEFT JOIN players p ON p.citizenid = x.citizenid WHERE p.citizenid IS NULL;

---

Step 3 — Migrate Accounts → Money

There are two common ESX patterns:

A) ESX stores balances inside users.accounts JSON

-- Example: users.accounts = '{"bank":5000, "money":750, "black_money":200}'

-- 1) Extract from ESX JSON safely
-- Create a temp view/table with parsed numbers
CREATE TEMPORARY TABLE esx_balances AS

SELECT u.identifier, COALESCE(JSON_EXTRACT(u.accounts, '$.money'), 0) AS esx_cash, COALESCE(JSON_EXTRACT(u.accounts, '$.bank'), 0) AS esx_bank, COALESCE(JSON_EXTRACT(u.accounts, '$.black_money'), 0) AS esx_black FROM users u;

-- 2) Merge into QB/QBOX money JSON
-- Decide how to handle black_money (see Options below)
UPDATE players p

JOIN identifier_crosswalk x ON x.citizenid = p.citizenid JOIN esx_balances b ON b.identifier = x.old_identifier SET p.money = JSON_OBJECT(

  'cash', CAST(b.esx_cash AS UNSIGNED),
  'bank', CAST(b.esx_bank AS UNSIGNED)
);

If MySQL without native JSON operations (or old

If MySQL without native JSON operations (or old version): build JSON strings using CONCAT.

B) ESX stores balances in user_accounts rows

-- Example: user_accounts(identifier, account, money)
CREATE TEMPORARY TABLE esx_balances AS
SELECT ua.identifier,
       SUM(CASE WHEN ua.account='money'        THEN ua.money ELSE 0 END) AS esx_cash,
       SUM(CASE WHEN ua.account='bank'         THEN ua.money ELSE 0 END) AS esx_bank,
       SUM(CASE WHEN ua.account='black_money'  THEN ua.money ELSE 0 END) AS esx_black

FROM user_accounts ua GROUP BY ua.identifier;

UPDATE players p

JOIN identifier_crosswalk x ON x.citizenid = p.citizenid JOIN esx_balances b ON b.identifier = x.old_identifier SET p.money = JSON_OBJECT(

  'cash', CAST(b.esx_cash AS UNSIGNED),
  'bank', CAST(b.esx_bank AS UNSIGNED)
);

Handling black_money (choose one)

• Option 1 (recommended): Create a dedicated wallet key in QB money JSON, e.g. "dirty".
• Option 2: Convert to items (e.g., marked bills) and credit inventory instead (requires item migration; out of scope here).
• Option 3: Zero it out (strongly discouraged unless you’ve announced a wipe).

Option 1 implementation:

-- Add dirty wallet in JSON (servers that support extra wallets)
UPDATE players p

JOIN identifier_crosswalk x ON x.citizenid = p.citizenid JOIN esx_balances b ON b.identifier = x.old_identifier SET p.money = JSON_MERGE_PATCH(p.money, JSON_OBJECT('dirty', CAST(b.esx_black AS UNSIGNED)));

Ensure your framework/resources actually respect the extra wallet. Otherwise prefer Option 2.

---

Step 4 — Re‑key foreign tables that referenced ESX identifier

Typical tables to fix:

• owned_vehicles.owner → map to citizenid (QB: player_vehicles.citizenid)
• Any custom tables containing identifier columns (houses, billing, gangs, businesses)

Vehicles (ESX → QB)

-- If you keep ESX \`owned_vehicles\`, re‑key owner → citizenid for forward compatibility
ALTER TABLE owned_vehicles ADD COLUMN citizenid VARCHAR(20) NULL;

UPDATE owned_vehicles v

JOIN identifier_crosswalk x ON x.old_identifier = v.owner SET v.citizenid = x.citizenid WHERE v.citizenid IS NULL;

CREATE INDEX ix_ov_cid ON owned_vehicles (citizenid);

**Vehicles into QB’s **“ (minimal fields; adjust

**Vehicles into QB’s **“ (minimal fields; adjust to your schema):

INSERT IGNORE INTO player_vehicles (citizenid, plate, vehicle, state, garage)
SELECT x.citizenid, v.plate, v.vehicle, 0 AS state, 'A' AS garage

FROM owned_vehicles v JOIN identifier_crosswalk x ON x.old_identifier = v.owner;

Validate JSON field names (vehicle vs mods/props) and column list against your actual QB/QBOX schema.

---

Step 5 — Constraints, indexes, and integrity checks

-- Ensure primary/unique keys
ALTER TABLE players

ADD UNIQUE KEY ux_players_citizenid (citizenid);

-- Optional: keep a quick lookup by license/steam
ALTER TABLE players

ADD KEY ix_players_license (license);

-- Spot orphaned crosswalks (no players row)
SELECT x.*

FROM identifier_crosswalk x LEFT JOIN players p ON p.citizenid = x.citizenid WHERE p.citizenid IS NULL;

-- Spot players with zeroed wallets (sanity)
SELECT citizenid, money FROM players

WHERE JSON_EXTRACT(money, '$.cash') IS NULL OR JSON_EXTRACT(money, '$.bank') IS NULL;

-- Detect duplicates (same human with multiple identifiers)
SELECT old_identifier, COUNT(*)

FROM identifier_crosswalk GROUP BY old_identifier HAVING COUNT(*) > 1;

---

Step 6 — Validation suite

1. Row counts: COUNT(users) ≈ COUNT(players) (within expected deltas).
2. Balance totals: Sum of ESX cash/bank ≈ Sum of QB wallets after migration.
3. Sample audit: Pick 10 players by name; verify citizenid, balances, vehicles.
4. Login test: Bring server up in staging; log in a few known players; verify UIs.

Totals check examples:

-- ESX totals

SELECT SUM(COALESCE(JSON_EXTRACT(accounts,'$.money'),0)) AS esx_cash_total, SUM(COALESCE(JSON_EXTRACT(accounts,'$.bank'),0)) AS esx_bank_total FROM users;

-- QB totals

SELECT SUM(COALESCE(JSON_EXTRACT(money,'$.cash'),0)) AS qb_cash_total, SUM(COALESCE(JSON_EXTRACT(money,'$.bank'),0)) AS qb_bank_total FROM players;

---

Step 7 — Runtime compatibility (adapters)

Even after migration, some legacy scripts may still reference ESX identifier. Keep the crosswalk and use a helper to resolve “ (or inverse) at runtime.

Lua helper (server):

Lua helper (server):

--- lookup_citizenid.lua
local function getCitizenIdByIdentifier(identifier)
    local result = MySQL.query.await('SELECT citizenid FROM identifier_crosswalk WHERE old_identifier = ? LIMIT 1', { identifier })
    if result and result[1] then return result[1].citizenid end
    return nil
end

return { getCitizenIdByIdentifier = getCitizenIdByIdentifier }

Use this in legacy event handlers until all scripts are QB/QBOX‑native. See the adapter patterns article for full interface shims.

• Adapter Patterns: https://vertexmods.com/blog/adapter-patterns
• Full conversion guide (Pillar): https://vertexmods.com/blog/converting-fivem-scripts/

---

Rollback strategy

1. Keep identifier_crosswalk and a pre‑migration backup.
2. If something goes wrong, drop the new players rows created in this window and restore the backup.
3. Re‑run migration after fixing data edge cases.

Simple label to mark your window:

-- Tag new rows
UPDATE players SET metadata = JSON_MERGE_PATCH(COALESCE(metadata,'{}'), JSON_OBJECT('migration_tag','esx_to_qb_2025_08_16'))

WHERE citizenid IN (SELECT citizenid FROM identifier_crosswalk);

---

Edge cases & tips

• Multiple characters per human: If your ESX used one identifier per account (no multi‑char), but you plan multi‑char on QB, consider generating additional citizens later via in‑game flows, not here.
• Name collisions: Two ESX users with same firstname/lastname are fine; citizenid is the key.
• Missing “ values: Prefer whatever stable identifier you have (steam, license2, fivem). Populate players.license with the best available.
• Old MySQL without JSON: Use plain text JSON strings and parse in app code; plan to upgrade.
• Black money policy: Communicate your decision. If converting to items, run a separate, transparent item migration.

---

Cutover checklist (production)

---

FAQ

Q: Can I keep using ESX “ anywhere?
A: Yes, but treat it as legacy. Use the crosswalk to resolve when needed, and update scripts to citizenid ASAP.

Q: Does QBOX require different SQL?
A: Not for identifiers/money; QBOX tracks QB’s schema closely. Validate column names before running.

Q: What about inventories, jobs, gangs

Q: What about inventories, jobs, gangs?
A: Outside this article’s scope. Handle them after identifiers/money stabilize. Use the Pillar guide for full coverage.

---

Next steps

• Implement runtime shims from Adapter Patterns: https://vertexmods.com/blog/adapter-patterns
• Finish your full migration using the Framework Guide (Pillar): https://vertexmods.com/blog/converting-fivem-scripts/
• Document your local deviations (custom wallets, extra columns) inside your repo.

---

Appendix — Idempotent wrappers

Wrap critical UPDATE/INSERTs with guards so you can re‑run safely.

-- Example guard: only update players with untouched money
UPDATE players p

JOIN identifier_crosswalk x ON x.citizenid = p.citizenid JOIN esx_balances b ON b.identifier = x.old_identifier SET p.money = JSON_OBJECT('cash', CAST(b.esx_cash AS UNSIGNED), 'bank', CAST(b.esx_bank AS UNSIGNED)) WHERE JSON_EXTRACT(p.money, '$.cash') = 0 AND JSON_EXTRACT(p.money, '$.bank') = 0;

Keep the crosswalk forever. It’s your Rosetta Stone for old logs and scripts.