oxmysql queries guide: MySQL.query.await, single, scalar, insert

You need to read or write the database from a server script, and examples online mix old MySQL.Async calls, callbacks and exports.oxmysql in different styles. This article shows the current MySQL functions, which one to use for which result, and how to keep queries safe and cheap.

#Load the MySQL object

To use the global MySQL, import oxmysql's Lua wrapper in the manifest of the resource, before your own server scripts:

-- fxmanifest.lua
fx_version 'cerulean'
game 'gta5'

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

dependency 'oxmysql'

Without that line, MySQL is nil and you get attempt to index a nil value (global 'MySQL'). The file only works on the server. If oxmysql itself cannot connect, see oxmysql connection errors.

#The functions and what they return

Each function has an .await form that waits for the result and returns it. It must run in a thread or an event handler, which is where most of your code already runs.

-- all rows
local rows = MySQL.query.await('SELECT identifier, name FROM users WHERE job = ?', { 'police' })
for _, row in ipairs(rows) do
    print(row.identifier, row.name)
end

-- one row
local user = MySQL.single.await('SELECT * FROM users WHERE identifier = ?', { identifier })
if not user then return end

-- one value
local count = MySQL.scalar.await('SELECT COUNT(*) FROM owned_vehicles WHERE owner = ?', { identifier })

-- insert
local id = MySQL.insert.await('INSERT INTO my_logs (identifier, action) VALUES (?, ?)', { identifier, 'login' })

-- update or delete
local changed = MySQL.update.await('UPDATE users SET job = ? WHERE identifier = ?', { 'unemployed', identifier })

Check the results. A single or scalar query that finds nothing gives nil, and using it without a check is the source of many attempt to index a nil value errors.

#Parameters with ?

Always pass values through ? placeholders and the second argument, a table in the same order:

-- safe
MySQL.query.await('SELECT * FROM users WHERE name = ?', { name })

-- dangerous: SQL injection
MySQL.query.await('SELECT * FROM users WHERE name = "' .. name .. '"')

With the unsafe version, a name such as x" OR 1=1 -- rewrites your query. With placeholders, oxmysql sends the values separately, so they can never become SQL. This applies to every value that comes from a player, an event argument or a command.

Warning: placeholders only work for values, not for table or column names. If a column name comes from outside, check it against a fixed list of allowed names instead of pasting it in.

#Callback style

The same functions accept a callback if you do not want to wait. This is useful outside a thread:

MySQL.query('SELECT * FROM users WHERE job = ?', { 'police' }, function(rows)
    print(#rows)
end)

.await reads better and is fine inside events. Use the callback style when you want the code that follows to keep running without waiting for the database.

#MySQL.prepare

MySQL.prepare runs a prepared statement, which the database can reuse when you run the same query again and again. It uses the same ? parameters:

local row = MySQL.prepare.await('SELECT * FROM users WHERE identifier = ?', { identifier })

What it returns depends on the result: a single row comes back as a table, and a query with one column and one row can come back as a value. For code where the result shape matters, single and scalar are more predictable, so reach for prepare when you have a hot query that runs often.

#Do not query in a loop

Each query is a round trip to the database. A loop that runs one query per item gets slow quickly, and it blocks the thread while it waits:

-- one query per player
for _, id in ipairs(ids) do
    local row = MySQL.single.await('SELECT * FROM users WHERE id = ?', { id })
end

Fetch everything in a single query, then loop over the result in Lua:

local rows = MySQL.query.await('SELECT * FROM users WHERE job = ?', { 'police' })

For many inserts, build one statement with several rows, or run them together in a transaction. Also avoid querying on every tick or every frame: read a value once and keep it in a Lua table. If a query is slow, add an index on the column you search by. Slow queries show up as server thread hitch warnings.

#Transactions

When several writes must all succeed or all fail, such as taking money from one row and giving it to another, use a transaction:

local ok = MySQL.transaction.await({
    { query = 'UPDATE bank SET balance = balance - ? WHERE identifier = ?', values = { 100, fromId } },
    { query = 'UPDATE bank SET balance = balance + ? WHERE identifier = ?', values = { 100, toId } },
})

if not ok then
    print('transfer failed, nothing was changed')
end

If any statement fails, the database undoes all of them. It returns true when everything went through.

#Tables must exist first

A query against a table that is not there gives Table 'x' doesn't exist. Create the table with an SQL file when you install the script, or at resource start with MySQL.query.await('CREATE TABLE IF NOT EXISTS …'). More in SQL table doesn't exist.

#Checklist