Database with oxmysql: CRUD without the footguns

Make the resource folder

Inside your server's resources folder, create this folder:

resources/qu_oxmysql_crud

Create the files

Create this exact file layout:

resources/qu_oxmysql_crud/
fxmanifest.lua
server.lua

Write fxmanifest.lua

Open fxmanifest.lua and paste this:

fx_version 'cerulean'
game 'gta5'

server_script '@oxmysql/lib/MySQL.lua'
server_script 'server.lua'

dependencies {
'oxmysql'
}

Then run this SQL against your database before starting the resource:

CREATE TABLE IF NOT EXISTS qu_oxmysql_crud_rows (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
amount INT NOT NULL DEFAULT 0
);

Write the lesson code

Open server.lua and paste this:

RegisterCommand('dbtest', function(src)
local id = MySQL.insert.await('INSERT INTO qu_oxmysql_crud_rows (name, amount) VALUES (?, ?)', { 'lesson', 100 })
local row = MySQL.single.await('SELECT * FROM qu_oxmysql_crud_rows WHERE id = ?', { id })
print('[qu_oxmysql_crud] selected ' .. row.name .. ' ' .. row.amount)
MySQL.update.await('UPDATE qu_oxmysql_crud_rows SET amount = ? WHERE id = ?', { 150, id })
print('[qu_oxmysql_crud] updated ' .. id)
end, true)

Start and test it

Open server.cfg and add this line:

ensure qu_oxmysql_crud

Save, then run this in the server console (txAdmin Live Console):

restart qu_oxmysql_crud

Now run the test command in the same console:

dbtest

The number after updated is the new row id, so it climbs by one each time you run dbtest. The first run prints 1, the next prints 2, and so on. The selected lesson 100 line stays the same because every run inserts the same name and amount before reading it back.

See your write land in HeidiSQL

The console proved the code ran. Now prove the data is really on disk. Open HeidiSQL, connect to the same database your mysql_connection_string points at, click qu_oxmysql_crud_rows in the left tree, and open the Data tab. You should see the row your INSERT created, already carrying the 150 your UPDATE set:

This is the whole point of a database: the console message disappears the moment you close the server, but this row stays. Run dbtest a second time and refresh the Data tab (F5) to watch a second row appear with the next id:

New to this GUI? The full connect, create database, and run SQL walkthrough is in HeidiSQL hands on. If 127.0.0.1:3306 refuses the connection, your MariaDB is not running, not HeidiSQL being broken.