Database

Server scripts get MySQL.*, compatible with oxmysql. ESX and Qbox SQL calls work without changes. @oxmysql/lib/MySQL.lua and dependency 'oxmysql' in manifests keep working too. Every server ships with an oxmysql and a mysql-async resource for this. They also provide the exports, so exports.oxmysql:query(sql, params, cb), exports.oxmysql:scalar_async(…), exports.oxmysql:executeSync(…) and exports['mysql-async']:mysql_fetch_all(…) work.

Connect

server.cfg

set mysql_connection_string "mysql://user:password@127.0.0.1:3306/my_server?charset=utf8mb4"

Other formats:

• user=root;password=secret;host=127.0.0.1;port=3306;database=my_server
• sqlite:data/server.db: a single file, good for small servers and testing. It uses the SQLite dialect. The path is relative to the server folder, missing folders are created. Use each file for one server only.

Game servers

Game servers support SQLite for now, and the server template starts with sqlite:data/server.db. MariaDB and MySQL work in gampeon-server and come to game servers later.

Options for the mysql:// format are connectionLimit (default 4), charset, connectTimeout (ms), multipleStatements and ssl.

Query

server.lua

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

-- inside a thread or event handler: wait for the result
local money = MySQL.scalar.await('SELECT money FROM users WHERE identifier = ?', { identifier })
local id = MySQL.insert.await('INSERT INTO bills (owner, amount) VALUES (:owner, :amount)', { owner = identifier, amount = 250 })
local changed = MySQL.update.await('UPDATE users SET money = money + ? WHERE id IN (?)', { 100, { 1, 2, 3 } })

Function	Returns
MySQL.query	rows, or { affectedRows, insertId } for statements without rows
MySQL.single	the first row or nil
MySQL.scalar	the first value of the first row or nil
MySQL.insert	the new ID
MySQL.update	the number of matched rows
MySQL.prepare	like query, simplified; a list of parameter lists runs as a batch
MySQL.rawExecute	like query
MySQL.transaction	true, or false after a rollback

Every function also has .await. MySQL.Async.* and MySQL.Sync.* from mysql-async exist as well.

Parameters

• ? with a list: { 'police', 2 }
• @name or :name with a table: { ['@id'] = 5 } or { id = 5 }
• A list for IN (?) expands to one value per entry.

Values are always escaped or bound, never pasted into the SQL. Don’t build SQL from player input with ...

Types

As in oxmysql:

• TINYINT(1) and BIT(1) become true or false.
• DATE, DATETIME and TIMESTAMP become milliseconds since 1970.
• DECIMAL becomes a number, and NULL becomes nil.
• Tables and vectors you pass as values are stored as JSON.

Errors

A failed query writes SCRIPT ERROR: [MySQL] … with the query to the server log. With .await the call raises the error, which you can catch with pcall. Transactions roll back and return false.

Queries run on their own threads, so a slow query never stalls the game.