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 - /register, /login, and every player put back where they left - and freeroam.lua uses it for the same two commands; a mode of yours uses it for whatever it likes, in Lua or C#.

1. The database server

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.

2. server.toml

[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.

The environment the dev compose file uses, when you run the server from the repository: docker compose --profile db up -d mariadb postgres starts both engines with a kcdmp / kcdmp user and database, on the host’s 3306 and 5432.

3. What a game mode gets

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).

4. Safe

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.

5. Fast

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.

6. Finding the keys

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.

Privacy Cookie settings