Database with oxmysql · Lesson 1/3 · 9 min

oxmysql: read and write to the database

oxmysql is the bridge between your script and MySQL. With four functions you cover almost everything.

Almost everything that persists on your server (money, vehicles, houses, inventory) lives in MySQL. The oxmysql resource gives you simple functions to talk to the database from Lua.

Load it into your resource

lua

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

fxmanifest.lua

The 4 functions you'll use most

lua

-- Read several rows
local filas = MySQL.query.await('SELECT * FROM jobs')

-- Read ONE row (or nil)
local user = MySQL.single.await('SELECT * FROM users WHERE identifier = ?', { id })

-- Insert (returns the new id)
local nuevoId = MySQL.insert.await('INSERT INTO logs (texto) VALUES (?)', { 'hola' })

-- Update/delete (returns affected rows)
local cambiadas = MySQL.update.await('UPDATE users SET banned = 1 WHERE identifier = ?', { id })

CRUD with oxmysql

The .await makes the query «synchronous» within your function: the response arrives before continuing. Convenient and readable. (There's a callback version, but await is the norm today.)

Practice what you learned

0/3

Test

¿Qué función de oxmysql usas para leer UNA sola fila (o nil si no hay)?

Pista

«single» = una sola.

Rellena los huecos

Completa la lectura de UNA fila del jugador por su identifier, parametrizada.

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

Pista

single para una fila; el valor va en la tabla de parámetros { ... }.

Ordena el código

Ordena la función de servidor que lee el dinero del banco de un jugador y lo devuelve.

Coloca las líneas en el orden correcto con las flechas.

end

local function getBank(id)

return user.bank

if not user then return 0 end

local user = MySQL.single.await('SELECT bank FROM users WHERE identifier = ?', { id })

Pista

Primero consultas, luego compruebas que existe y al final devuelves el valor.

Challenge: code it yourself

Read a player's bank money from the users table by their identifier and return it.

Write it yourself in your editor (VS Code) and test it on your server. You learn here by doing it, not by copying.

See hint

MySQL.single.await('SELECT bank FROM users WHERE identifier = ?', { id }).

Escribe aquí tu solución:

Next: Parameterized queries: close the door to SQL injection

How was this lesson?