Skip to content

Connecting a database

The server keeps its own world by itself - the doors, the chests, the taken items, the bans - in a file it makes on its first start (data/server.db), and nothing needs to be set up for that. What it does not keep is anything about a player: a name is whoever typed it, and a player who leaves is forgotten. Accounts, factions, ranks, scores, the place a player left, the rules on doors and chests - all of that is the game mode’s, and a game mode keeps it in a database of its own. This guide connects one. The shipped basicrp.lua is the worked example - a password box to register and log in with, and every character put back where they left with what they carried, their skills and their horse (section 3) - and freeroam.lua uses it for a /register and a /login; a mode of yours uses it for whatever it likes, in Lua or C#.

MySQL, MariaDB or PostgreSQL, on the same machine as the game server or anywhere it can reach. Make a database and a user that may use that database and nothing else:

-- MySQL / MariaDB
CREATE DATABASE kcdmp CHARACTER SET utf8mb4;
CREATE USER 'kcdmp'@'%' IDENTIFIED BY 'a long password';
GRANT ALL PRIVILEGES ON kcdmp.* TO 'kcdmp'@'%';
-- PostgreSQL
CREATE USER kcdmp WITH PASSWORD 'a long password';
CREATE DATABASE kcdmp OWNER kcdmp;

A game mode creates its own tables from there (CREATE TABLE IF NOT EXISTS ... in its init), so the user needs to be allowed to.

[gamemode.database]
driver = "mysql" # or "postgres"
host = "127.0.0.1"
port = 0 # 0 = the driver's default (3306 / 5432)
user = "kcdmp"
password = "a long password"
database = "kcdmp"
ssl = "preferred" # "required" for a database on another machine

One line does instead of the parts: url = "mysql://kcdmp:a%20long%[email protected]:3306/kcdmp". The credentials live in this file and nowhere else - never in a game mode script, which you may share; keep server.toml readable by the server’s own user alone. The server opens the database before the game mode starts and refuses to start on one it cannot reach, so a typo shows in the first lines of the log, not at the first player. Every key is in the configuration page; the four limits (pool, timeout_seconds, max_rows, slow_query_ms) have defaults that suit a game server.

BasicRP needs nothing more than the two steps above: an empty database, its user, and the [gamemode.database] block, with the mode named in server.toml:

[gamemode]
script = "gamemodes/basicrp.lua"

There is no SQL file to run. At its first start BasicRP makes every table it uses, and the log says BasicRP: accounts on (mysql) (or postgres) once they are there. Without a database it says accounts are off instead: everyone plays, and nothing of theirs is kept when they leave. A later version that needs a new table or column adds it by itself at the next start - the release notes name each one - so an update is the new files and a restart. Its tables, for a look in your database tool:

Table One row per
players account: the name, the password’s hash, the place they left, the purse, the look, how fed and rested they are
player_items stack a character carries, what they wear marked worn = 1
player_horses horse a character owns: its breed, its coat and whether it is the one /horse brings
horse_items stack in a horse’s saddlebags, its saddle and other gear marked worn = 1
player_progress stat or skill of a character: its level, the experience toward the next and the perk points not spent yet
player_perks perk a character holds
horse_stats stat an admin gave a horse

Deleting a row of players deletes everything of that character with it. The tables are plain SQL, so a question like “who carries this sword” is one SELECT on player_items. Leave the tables’ layout to BasicRP, and change a character’s rows only while that player is offline: BasicRP reads a character at the login and writes on every change, so an edit made while they play is overwritten.

GetDatabase() in Lua (IServerApi.Database in C#) answers the handle, or nil when this file has no driver: a mode says so and runs without. Every query runs on a thread of its own and answers in a callback a tick or more later - the server never waits for the database:

local db = GetDatabase()
function OnGameModeInit()
if not db then Log("no database: the scores are not kept") return end
db:ExecuteSync("CREATE TABLE IF NOT EXISTS scores (name VARCHAR(24) PRIMARY KEY, kills INTEGER NOT NULL DEFAULT 0)")
end
function OnPlayerConnect(pid)
if not db then return end
db:Query("SELECT kills FROM scores WHERE name = @n", { n = GetPlayerName(pid) }, function(rows, err)
if err then Log("scores: " .. err) return end
SetPlayerData(pid, "kills", rows[1] and rows[1].kills or 0)
end)
end

Query, Execute, Scalar and Batch (several writes in one transaction) are the four calls; QuerySync and ExecuteSync answer at once but only inside OnGameModeInit - the tables a mode makes, the rules it loads before the first player. The GetDatabase page has every call, what the values look like on the way in and out, and how a mode’s /register and /login fit together (SetSecretCommand, HashPassword, VerifyPassword).

What a mode can count on when it writes the same rows again and again - a character saved after every change, say:

  • One call at a time, in the order the mode made them. Query, Execute, Scalar and Batch wait in one line and run one after the other: a call starts only when the one before it has committed or failed. Two saves of one character never land the wrong way round, whether the mode made them in the same tick, in later ticks or across a /reload. QuerySync and ExecuteSync (the init, and the goodbye at a server stop) run at once, outside the line, and so does a connection a C# mode opens itself with CreateConnection().
  • The callbacks come back in that order almost always, but not for certain. Each answer reaches the tick from another thread, and on a busy server two can arrive swapped. A mode that must know which answer is the newer one numbers its calls.
  • Batch is one transaction on one connection, on MySQL / MariaDB and PostgreSQL alike: every statement, then the commit, and one that fails rolls the whole batch back. Two rules of MySQL / MariaDB still hold inside it: CREATE, ALTER, DROP and TRUNCATE commit on their own the moment they run (keep them in the init), and a MyISAM table ignores transactions (make the mode’s tables InnoDB, MySQL’s default).
  • An error is not always a rollback. A call that fails before its commit leaves nothing behind. But a timeout or a lost connection at the very moment of the commit reports an error while the database may have kept the write - neither side can tell. The server never retries a call; that is the mode’s choice.
  • A retry goes to the back of the line. A save sent again from its failed callback runs after every call made since - a newer save of the same character included - and would put the older data back over it. Send a save again only while it is still the newest of its character:
local newest = {} -- character id -> the number of its latest save
local function save(id, data, tries)
newest[id] = (newest[id] or 0) + 1
local mine = newest[id]
db:Execute("UPDATE characters SET data = @d WHERE id = @id", { d = data, id = id }, function(_, _, err)
if err and newest[id] == mine and (tries or 0) < 3 then
save(id, data, (tries or 0) + 1) -- no newer save was made since: safe to send again
end
end)
end

A version column does the same in the database: raise it at every save and write with ... WHERE id = @id AND version < @v, so an older save can never overwrite a newer one. Either way, sending again a save that did commit after all changes nothing.

  • Parameters, always. @name in the SQL and { name = value } beside it. There is no escape function on purpose: a value that is concatenated into the SQL text is how a player’s name becomes a DROP TABLE. Every example in these pages binds its values; do the same and injection is not a thing that can happen to your mode.
  • A user of its own, with rights on the mode’s database alone (above). The server’s own file is never reachable through the mode’s database, whatever the SQL says.
  • Passwords hashed, never stored: HashPassword gives one text to store, VerifyPassword checks a login against it.
  • The password of the database in server.toml only; the server never logs it.
  • ssl = "required" for a database on another machine.

The tick runs thirty times a second and every callback of the mode runs inside it. A database round trip is milliseconds; thirty of them a second would be the whole tick. The rule: the database is never on the hot path.

  • Read a player’s row once, on the connect, into SetPlayerData and the mode’s own tables. Answer every callback from memory. OnPlayerUseDoor deciding whether a faction’s rank may open a door reads a table the mode filled at init and keeps in step - never a query.
  • Write on change and on the disconnect; several writes at once go as one Batch.
  • QuerySync only at init. Anywhere else it is refused with an error, so a slow database can never freeze the server.
  • The limits are there for the day something goes wrong: a query over timeout_seconds is cut off, one returning more than max_rows is refused (page it), one slower than slow_query_ms is named in the log. A failing query hands its error to the callback and the server goes on.

A rule on a door or a chest names the object by its key, the entity’s name in the level file (AnimDoor[Door/door_village_left10_...], confessorsChest). Two ways to get them: the level lists have every door and chest with its position, and in the game an admin’s /objectinfo puts a label over every object around them for a minute - the kind, a readable name, the distance - and writes the full keys to the chat and the client’s log. A mode’s own command does better than either: GetNearestDoor names the door the player stands at, so a faction leader sets a rule by walking up to the door and typing /perm 3, and never sees a key at all.