Database with oxmysql · Lesson 1/3 · 9 min

oxmysql: reading and writing 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. QBCore uses oxmysql under the hood, so you use it the same way from your resources.

Load it in your resource

lua

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

fxmanifest.lua

The 4 functions you'll use most

lua

-- Read several rows
local filas = MySQL.query.await('SELECT * FROM player_vehicles WHERE citizenid = ?', { cid })

-- Read ONE row (or nil)
local casa = MySQL.single.await('SELECT * FROM houses WHERE id = ?', { 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 players SET money = ? WHERE citizenid = ?', { json.encode(money), cid })

CRUD with oxmysql

The .await makes the query "synchronous" within your function: the response arrives before continuing. In QBCore the main players table is 'players' and the key is citizenid (not the source).

Practice what you learned

0/3

Test

Quieres leer VARIAS filas de una tabla. ¿Qué función de oxmysql usas?

Rellena los huecos

Lee TODAS las filas de la tabla player_vehicles que pertenecen a un citizenid, de forma parametrizada.

1local citizenid = Player.PlayerData.citizenid

2local filas = MySQL.('SELECT * FROM player_vehicles WHERE citizenid = ?', { citizenid })

Pista

Varias filas → query. El .await espera la respuesta antes de seguir.

Corrige el error

Aquí se quiere leer UNA sola fila (la casa con ese id), pero query.await devuelve una lista. Cámbialo a la función correcta para una fila.

Este código tiene un fallo:

1local casa = MySQL.query.await('SELECT * FROM houses WHERE id = ?', { id })

Reescríbelo corregido:

Pista

Para una única fila (o nil) existe MySQL.single.await, así no tienes que hacer filas[1].

Challenge: code it yourself

Read how many vehicles a player has in the player_vehicles table, filtering by their citizenid.

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.query.await('SELECT * FROM player_vehicles WHERE citizenid = ?', { cid }) and count #rows.

Escribe aquí tu solución:

Next: Parameterized queries: close off SQL injection

How was this lesson?