Using oxmysql

oxmysql is the MySQL/MariaDB connector that ESX Legacy uses for all database access. In your own ESX resources you load @oxmysql/lib/MySQL.lua and call MySQL.query, MySQL.single, MySQL.scalar, MySQL.insert, MySQL.update, MySQL.prepare or MySQL.transaction, each in a callback form and an .await form.

OverviewCopy Anchor Link

• Resource: oxmysql by Overextended, official docs at overextended.dev/oxmysql
• Side: server only
• Required by: es_extended (listed in its dependencies) and most ESX addons
• Connection: the mysql_connection_string convar in server.cfg

Set up oxmysqlCopy Anchor Link

set mysql_connection_string "mysql://user:password@localhost/es_extended?charset=utf8mb4"

ensure oxmysql
ensure esx_lib
ensure es_extended

fx_version 'cerulean'
game 'gta5'
lua54 'yes'

shared_script '@es_extended/imports.lua'

server_scripts {
    '@oxmysql/lib/MySQL.lua',
    'server/main.lua',
}

dependencies {
    'oxmysql',
    'es_extended',
}

MySQL.ready(function()
    local count = MySQL.scalar.await('SELECT COUNT(*) FROM `users`')
    print(('[myresource] %s characters in the database'):format(count))
end)

Query functionsCopy Anchor Link

Every function has an .await variant, for example MySQL.query.await(sql, params), that returns the value instead of calling cb.

Callback vs. awaitCopy Anchor Link

-- Callback style: does not block
MySQL.query('SELECT `name`, `label` FROM `jobs` WHERE `whitelisted` = ?', { 1 }, function(rows)
    for i = 1, #rows do
        print(rows[i].name, rows[i].label)
    end
end)

-- Await style: blocks this thread until the result is there
CreateThread(function()
    local rows = MySQL.query.await('SELECT `name`, `label` FROM `jobs` WHERE `whitelisted` = ?', { 1 })
    print(#rows, 'whitelisted jobs')
end)

Use .await when the next line depends on the result. It reads top to bottom and avoids nested callbacks. Event handlers, commands registered with ESX.RegisterCommand and ESX callback handlers already run in a thread, so you can call .await directly there.

Examples with ESX tablesCopy Anchor Link

-- One row
local user = MySQL.single.await(
    'SELECT `firstname`, `lastname`, `job` FROM `users` WHERE `identifier` = ?',
    { xPlayer.getIdentifier() }
)

-- One value
local vehicleCount = MySQL.scalar.await(
    'SELECT COUNT(*) FROM `owned_vehicles` WHERE `owner` = ?',
    { xPlayer.getIdentifier() }
)

-- Insert, returns the AUTO_INCREMENT id
local billId = MySQL.insert.await(
    'INSERT INTO `billing` (`identifier`, `sender`, `target_type`, `target`, `label`, `amount`) VALUES (?, ?, ?, ?, ?, ?)',
    { targetIdentifier, xPlayer.getIdentifier(), 'society', 'society_police', 'Speeding', 250 }
)

-- Update, returns affected rows
local changed = MySQL.update.await(
    'UPDATE `owned_vehicles` SET `stored` = 1 WHERE `plate` = ? AND `owner` = ?',
    { plate, xPlayer.getIdentifier() }
)

if changed == 0 then
    print('Vehicle not found or not owned by this player')
end

The users.identifier column stores the identifier without the license: prefix (and with a charN: prefix when multicharacter is used). Always use xPlayer.getIdentifier() instead of building it yourself.

Prepared statements and batchesCopy Anchor Link

MySQL.prepare uses the binary protocol. It is a good fit for queries that run very often. A single-row, single-column result is unwrapped to the bare value, a single row is returned as a row, otherwise you get an array.

You can also pass an array of parameter arrays to run the same statement many times. es_extended does this in ESX.AddItems:

MySQL.prepare.await(
    'INSERT IGNORE INTO `items` (`name`, `label`, `weight`, `rare`, `can_remove`) VALUES (?, ?, ?, ?, ?)',
    {
        { 'coffee', 'Coffee', 1, 0, 1 },
        { 'donut', 'Donut', 1, 0, 1 },
    }
)

TransactionsCopy Anchor Link

A transaction runs several statements and rolls everything back if one fails. Use it for anything that moves money or items between rows.

local ok = MySQL.transaction.await({
    { query = 'UPDATE `addon_account_data` SET `money` = `money` - ? WHERE `account_name` = ? AND `owner` IS NULL', values = { 500, 'society_police' } },
    { query = 'UPDATE `addon_account_data` SET `money` = `money` + ? WHERE `account_name` = ? AND `owner` IS NULL', values = { 500, 'society_ambulance' } },
})

if not ok then
    print('Transfer rolled back')
end

ESX.CreateJob uses the same pattern (exports.oxmysql:transaction_async) to insert a job and its grades together.

Migrate from mysql-asyncCopy Anchor Link

Old ESX (1.1, 1.2, v1-final) used mysql-async with MySQL.Async.* and MySQL.Sync.*. oxmysql keeps these names as aliases, so old code usually runs, but the modern names are shorter and clearer.

MySQL.Async.fetchAll('SELECT * FROM users WHERE identifier = @identifier', {
    ['@identifier'] = identifier
}, function(result)
    print(result[1].job)
end)

local row = MySQL.single.await('SELECT `job` FROM `users` WHERE `identifier` = ?', { identifier })
print(row and row.job)

For a full conversion checklist (shared object, xPlayer, manifest), see Convert old ESX scripts.

Common issuesCopy Anchor Link

• attempt to index a nil value (global 'MySQL'): the resource does not load @oxmysql/lib/MySQL.lua, or loads it in client_scripts.
• .await errors at file load: top-level code is not inside a thread. Wrap it in MySQL.ready or CreateThread.
• SELECT * on users: the table has long JSON columns (accounts, inventory, loadout, metadata, skin). Select only the columns you need.
• String-built SQL: '... WHERE plate = "' .. plate .. '"' is an SQL injection. Use ? and the parameter table.
• Slow queries on large tables: add indexes for the columns in your WHERE clauses and check with EXPLAIN. See Performance.

RelatedCopy Anchor Link

• Database schema
• Callbacks
• Convert old ESX scripts
• Performance
• server.cfg

Frequently asked questions