# Using oxmysql (/docs/guides/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.

## Overview [#overview]

- Resource: [oxmysql](https://github.com/overextended/oxmysql) by Overextended, official docs at [overextended.dev/oxmysql](https://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 oxmysql [#set-up-oxmysql]

<Steps>
<Step>
### Configure the connection string [#configure-the-connection-string]

```bash title="server.cfg"
set mysql_connection_string "mysql://user:password@localhost/es_extended?charset=utf8mb4"

ensure oxmysql
ensure esx_lib
ensure es_extended
```

oxmysql must start before `esx_lib` and `es_extended`. The txAdmin ESX recipe uses this order. See [server.cfg](/docs/getting-started/server-cfg) for a full example.
</Step>
<Step>
### Load the MySQL library in your resource [#load-the-mysql-library-in-your-resource]

```lua title="fxmanifest.lua"
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',
}
```

`@oxmysql/lib/MySQL.lua` creates the global `MySQL` table. Without it, `MySQL` is `nil` in your resource.
</Step>
<Step>
### Wait for the connection when you query at startup [#wait-for-the-connection-when-you-query-at-startup]

Queries at the top level of a file can run before the database is ready. Wrap startup queries in `MySQL.ready`:

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

es_extended itself uses `MySQL.ready` to load items and jobs.
</Step>
</Steps>

## Query functions [#query-functions]

| Function | Returns | Typical use |
| --- | --- | --- |
| `MySQL.query(sql, params, cb)` | Array of rows (or a result object for non-SELECT) | Read many rows |
| `MySQL.single(sql, params, cb)` | First row or `nil` | Read one row |
| `MySQL.scalar(sql, params, cb)` | First column of the first row | `COUNT(*)`, one value |
| `MySQL.insert(sql, params, cb)` | Insert id | `INSERT` into tables with `AUTO_INCREMENT` |
| `MySQL.update(sql, params, cb)` | Number of affected rows | `UPDATE`, `DELETE` |
| `MySQL.prepare(sql, params, cb)` | Depends on the query (see below) | Hot, repeated queries and batches |
| `MySQL.transaction(queries, cb)` | `true` on commit, `false` on rollback | Several writes that must succeed together |

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

### Callback vs. await [#callback-vs-await]

```lua title="server/main.lua"
-- 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 tables [#examples-with-esx-tables]

```lua title="server/main.lua"
-- 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 batches [#prepared-statements-and-batches]

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

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

### Transactions [#transactions]

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

```lua title="server/main.lua"
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.

<Callout type="warn" title="Society money">
The example above edits the database directly. If `esx_addonaccount` is running, it keeps society balances in memory and writes them back, so a direct `UPDATE` can be overwritten. For live society money, use the account object from `esx_addonaccount` instead.
</Callout>

## Migrate from mysql-async [#migrate-from-mysql-async]

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 | oxmysql |
| --- | --- |
| `MySQL.Async.fetchAll` | `MySQL.query` |
| `MySQL.Async.fetchScalar` | `MySQL.scalar` |
| `MySQL.Async.fetchSingle` | `MySQL.single` |
| `MySQL.Async.insert` | `MySQL.insert` |
| `MySQL.Async.execute` | `MySQL.update` |
| `MySQL.Async.transaction` | `MySQL.transaction` |
| `MySQL.Sync.fetchAll(...)` | `MySQL.query.await(...)` |
| `MySQL.Sync.execute(...)` | `MySQL.update.await(...)` |
| `MySQL.Sync.fetchScalar(...)` | `MySQL.scalar.await(...)` |

<Steps>
<Step>
### Replace the manifest include [#replace-the-manifest-include]

Change `'@mysql-async/lib/MySQL.lua'` to `'@oxmysql/lib/MySQL.lua'` in `server_scripts`, and replace `mysql-async` with `oxmysql` in `dependencies`.
</Step>
<Step>
### Rename the calls [#rename-the-calls]

Before:

```lua title="server/main.lua"
MySQL.Async.fetchAll('SELECT * FROM users WHERE identifier = @identifier', {
    ['@identifier'] = identifier
}, function(result)
    print(result[1].job)
end)
```

After:

```lua title="server/main.lua"
local row = MySQL.single.await('SELECT `job` FROM `users` WHERE `identifier` = ?', { identifier })
print(row and row.job)
```

Named parameters (`@identifier`) still work in oxmysql, but positional `?` placeholders are what es_extended uses and are easier to keep in sync.
</Step>
<Step>
### Remove mysql-async [#remove-mysql-async]

Delete `mysql-async` and `ghmattimysql` from `server.cfg`. Running two connectors wastes connections and hides which one a script uses.
</Step>
</Steps>

For a full conversion checklist (shared object, xPlayer, manifest), see [Convert old ESX scripts](/docs/guides/convert-old-esx-scripts).

## Common issues [#common-issues]

- **`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](/docs/guides/performance).

## Related [#related]

- [Database schema](/docs/guides/database-schema)
- [Callbacks](/docs/guides/callbacks)
- [Convert old ESX scripts](/docs/guides/convert-old-esx-scripts)
- [Performance](/docs/guides/performance)
- [server.cfg](/docs/getting-started/server-cfg)