SQL & Identifiers Migration: steam/license to citizenid
August 16, 2025
Last Updated: February 26, 2026
10 min read
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/en/blog/adapter-patterns
- Converting FiveM Scripts – ESX, QBCore, QBOX (Framework Guide) — https://vertexmods.com/en/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)
- Freeze writes during migration (stop the game server + any external bots touching DB).
- Full backup and a dump of table structures. Store both with timestamps.
- Work in a transaction per table if possible; keep steps idempotent.
- 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):
text
1
2
-- 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 addcrypto,dirty, etc.
Step 0 — Snapshot & staging
text
1
2
3
4
5
# 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).
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
31
32
33
34
35
36
37
-- 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
playersrows, create the crosswalk by selecting theirlicense/steamand existingcitizenidinstead 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.
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 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 (
CONCATin MySQL) and JSON functions accordingly. For MySQL 5.7, replaceJSON_OBJECTwith manual string building if needed.
MySQL‑safe variant:
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 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
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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:
text
1
2
3
4
5
-- 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 tocitizenid(QB:player_vehicles.citizenid)- Any custom tables containing
identifiercolumns (houses, billing, gangs, businesses)
Vehicles (ESX → QB)
text
1
2
3
4
5
6
7
8
9
-- 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):
text
1
2
3
4
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 (
vehiclevsmods/props) and column list against your actual QB/QBOX schema.
Step 5 — Constraints, indexes, and integrity checks
text
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 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
- Row counts:
COUNT(users)≈COUNT(players)(within expected deltas). - Balance totals: Sum of ESX cash/bank ≈ Sum of QB wallets after migration.
- Sample audit: Pick 10 players by name; verify
citizenid, balances, vehicles. - Login test: Bring server up in staging; log in a few known players; verify UIs.
Totals check examples:
text
1
2
3
4
5
6
7
8
9
10
11
-- 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):
text
1
2
3
4
5
6
7
8
--- 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/en/blog/adapter-patterns
- Full conversion guide (Pillar): https://vertexmods.com/en/blog/converting-fivem-scripts/
Rollback strategy
- Keep
identifier_crosswalkand a pre‑migration backup. - If something goes wrong, drop the new
playersrows created in this window and restore the backup. - Re‑run migration after fixing data edge cases.
Simple label to mark your window:
text
1
2
3
-- 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
identifierper 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). Populateplayers.licensewith 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/en/blog/adapter-patterns
- Finish your full migration using the Framework Guide (Pillar): https://vertexmods.com/en/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.
text
1
2
3
4
5
6
-- 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.
Stay in the Loop
Get the latest FiveM tutorials, mod releases, and exclusive updates delivered to your inbox.
No spam. Unsubscribe anytime.
Share Article