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#.
1. The database server
Section titled “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 / MariaDBCREATE DATABASE kcdmp CHARACTER SET utf8mb4;CREATE USER 'kcdmp'@'%' IDENTIFIED BY 'a long password';GRANT ALL PRIVILEGES ON kcdmp.* TO 'kcdmp'@'%';-- PostgreSQLCREATE 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
Section titled “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 machineOne 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.
3. Running BasicRP
Section titled “3. Running BasicRP”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.
4. What a game mode gets
Section titled “4. 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)endQuery, 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).
5. Order, failures and retries
Section titled “5. Order, failures and retries”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,ScalarandBatchwait 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.QuerySyncandExecuteSync(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 withCreateConnection(). - 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.
Batchis 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,DROPandTRUNCATEcommit 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)endA 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.
6. Safe
Section titled “6. Safe”- Parameters, always.
@namein 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 aDROP 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:
HashPasswordgives one text to store,VerifyPasswordchecks a login against it. - The password of the database in
server.tomlonly; the server never logs it. ssl = "required"for a database on another machine.
7. Fast
Section titled “7. 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
SetPlayerDataand the mode’s own tables. Answer every callback from memory.OnPlayerUseDoordeciding 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. QuerySynconly 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_secondsis cut off, one returning more thanmax_rowsis refused (page it), one slower thanslow_query_msis named in the log. A failing query hands its error to the callback and the server goes on.
8. Finding the keys
Section titled “8. 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.
