Database

SQLite or MySQL, moving between them, every table, and saving from addons.

Everything RPG X saves goes to the server's own sv.db (SQLite) by default. Nothing to set up; it's wiped along with the server files (like a reinstall).

It can all move to an external MySQL / MariaDB (InnoDB) instead. That needs the mysqloo module on the server: garrysmod/lua/bin/gmsv_mysqloo_linux64.dll for this (64-bit Linux) server. It can't be shipped in the Workshop addon.

All database commands are server console only (or RCON), since they handle credentials:

Typical move to MySQL:

rpgx_db_mysql db.example.com rpgx secretpassword rpgx
rpgx_db_migrate

• The settings are kept in garrysmod/data/rpgx/database.json (server-side only, never sent to players). The password is stored there in plain text, so keep that folder private.
• sv.db is left untouched by a migration, as a backup.
• If MySQL is unreachable at startup, nothing loads or saves (so nothing gets overwritten) and it retries every 30 seconds.

Tables #

All created automatically, in both sv.db and MySQL. Foreign keys are ON DELETE CASCADE: deleting a player deletes their inventory and its items.

Ids are 32-character strings made by the server, so a save never has to wait for the database to hand one back, and an item keeps the same id for its whole life. Inventories saved in the first version (one JSON row per player) are converted automatically on startup.

Saving things from code (addons too) #

Everything that's saved goes through RPGX.Database (server side, available once the gamemode has loaded). Nobody writes SQL: you register tables and build queries, and it turns them into whatever the server uses, sv.db or MySQL, so rpgx_db_migrate covers your data too.

Tables. Register them once, parents before the tables that reference them:

local DB = RPGX.Database

DB:RegisterTable( "myaddon_bank", {
	columns = {
		{ "id", "id" },
		{ "steamid", "string" },
		{ "balance", "int" },
		{ "note", "text", null = true },
	},
	primary = { "id" },
	foreign = { steamid = "rpgx_player(steamid)" },   -- ON DELETE CASCADE
	index   = { "steamid" },
} )

Types: id (use DB:NewId() for values), string, text, int, number, bool. Columns are NOT NULL unless marked null = true; default = value gives a number or bool default.

Columns added to a spec later are added to the existing table on the next start (sv.db and MySQL). They need null = true or a default, since the rows already there need a value.

Queries are built by chaining, then :Run( callback ):

-- Select: callback( rows, err ). Rows come back typed: numbers, true/false, strings.
DB:Select( "myaddon_bank" ):Where( "steamid", steamid ):Run( function( rows, err ) end )

DB:Select( "rpgx_inventory_item" )
	:Columns( "class", "slot", "rpgx_player.name", { "rpgx_inventory.id", "inventory" } )
	:Join( "rpgx_inventory", "rpgx_inventory.id", "rpgx_inventory_item.inventory_id" )
	:LeftJoin( "rpgx_player", "rpgx_player.steamid", "rpgx_inventory.steamid" )
	:Where( "slot", "<=", 10 ):Where( "rpgx_player.name", "LIKE", "Maff%" )
	:OrderBy( "slot", "DESC" ):Limit( 20 ):Offset( 0 )
	:Run( function( rows, err ) end )

DB:Select( "myaddon_bank" ):Where( "balance", ">", 0 ):Count():Run( function( count, err ) end )
DB:Select( "myaddon_bank" ):Where( "id", id ):First( function( row, err ) end )   -- row or nil

-- Writes: callback( ok, err )
DB:Insert( "myaddon_bank" ):Values( { id = DB:NewId(), steamid = steamid, balance = 0 } ):Run( callback )
DB:Update( "myaddon_bank" ):Set( { balance = 50, note = DB.NULL } ):Where( "id", id ):Run( callback )
DB:Delete( "myaddon_bank" ):WhereIn( "id", { a, b } ):Run( callback )
DB:Upsert( "myaddon_bank" ):Values( row ):Keep( "created" ):Run( callback )   -- insert, or update if the key exists

-- Several writes, all or nothing: callback( ok, err )
DB:Transaction( function( tx )
	GAMEMODE:PlayerRecord( tx, steamid, name )   -- make sure rpgx_player has them
	tx:Insert( "myaddon_bank" ):Values( { id = DB:NewId(), steamid = steamid, balance = 100 } )
	tx:Update( "myaddon_bank" ):Set( { balance = 0 } ):Where( "id", oldId )
end, callback )

• Every table and column name is checked against the registered tables; a typo is a Lua error right away, and values are always sent as parameters, so nothing can inject SQL.
• Update and Delete need a Where, so a whole table can't be wiped by accident.
• DB:IsReady() is false while MySQL is unreachable or a migration runs; queries then fail with an error instead of running. Save later rather than lose data.
• Hook RPGXDatabaseFlush (save everything now, wait = block) and RPGXDatabaseSwitched (reload after rpgx_db_use) if you keep data in memory.