SQL & Identifiers Migration: steam/license → citize&#…

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

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

text

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

text

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

text

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

-- 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 version): build JSON strings using CONCAT.

B) ESX stores balances in `user_accounts` rows

text

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

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

text

-- 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 to your schema):

text

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

text

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

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

text

--- 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_crosswalk and a pre‑migration backup.
If something goes wrong, drop the new players rows created in this window and restore the backup.
Re‑run migration after fixing data edge cases.

Simple label to mark your window:

text

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

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

SQL & Identifiers Migration: steam/license to citizenid

What changes between ESX and QBCore/QBOX

Golden rules (don’t skip)

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

Step 0 — Snapshot & staging

Step 1 — Build the crosswalk table

Step 2 — Normalize/prepare target `players` rows

Step 3 — Migrate Accounts → Money

A) ESX stores balances inside `users.accounts` JSON

B) ESX stores balances in `user_accounts` rows

Handling black_money (choose one)

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

Step 5 — Constraints, indexes, and integrity checks

Step 6 — Validation suite

Step 7 — Runtime compatibility (adapters)

Rollback strategy

Edge cases & tips

Cutover checklist (production)

FAQ

Next steps

Appendix — Idempotent wrappers

Stay in the Loop

What changes between ESX and QBCore/QBOX

Golden rules (don’t skip)

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

Step 0 — Snapshot & staging

Step 1 — Build the crosswalk table

Step 2 — Normalize/prepare target players rows

Step 3 — Migrate Accounts → Money

A) ESX stores balances inside users.accounts JSON

B) ESX stores balances in user_accounts rows

Handling black_money (choose one)

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

Step 5 — Constraints, indexes, and integrity checks

Step 6 — Validation suite

Step 7 — Runtime compatibility (adapters)

Rollback strategy

Edge cases & tips

Cutover checklist (production)

FAQ

Next steps

Appendix — Idempotent wrappers

Stay in the Loop

Step 2 — Normalize/prepare target `players` rows

A) ESX stores balances inside `users.accounts` JSON

B) ESX stores balances in `user_accounts` rows

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