PostgreSQL guide

Server-side scripts can keep data in a PostgreSQL database - accounts, player statistics, bans, item stashes, anything that must survive a server restart. There are two ways to work with it, and they mix freely:

• SQL queries - db:query and friends: any SQL, values passed as parameters;
• models (ORM) - db:model: a table described in Lua, with functions that write the SQL for you (Player:findOne({ name = "Diego" }), Player:increment(...)).

The database API exists only on the server - client scripts never see the database. It needs nothing but a running PostgreSQL (version 10 or newer); gmp_server talks to it directly.

Setting up PostgreSQLКопировать ссылку на раздел

Install PostgreSQL on the machine that runs gmp_server (or on a machine in the same private network) and create a user and a database for the server. In psql (as the postgres user):

CREATE USER gmp WITH PASSWORD 'pick-a-long-password';
CREATE DATABASE gmp OWNER gmp;

With Docker, PostgreSQL can run next to the server in the same docker-compose.yml:

services:
  gmp:
    # ... the gmp_server service from the server package ...
    environment:
      GMP_DB_PASSWORD: pick-a-long-password
  db:
    image: postgres:16-alpine
    restart: unless-stopped
    environment:
      POSTGRES_USER: gmp
      POSTGRES_PASSWORD: pick-a-long-password
      POSTGRES_DB: gmp
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

The script then connects to host = "db". Do not publish the database port (5432) to the internet.

ConnectingКопировать ссылку на раздел

Connect once, at the top of a server script, and keep the object:

local db = dbConnect({
  host = "127.0.0.1",
  port = 5432,
  user = "gmp",
  password = os.getenv("GMP_DB_PASSWORD"),
  database = "gmp",
})

Keep the password out of the script: set the environment variable GMP_DB_PASSWORD before starting gmp_server (environment: in Docker, export GMP_DB_PASSWORD=... on Linux, set GMP_DB_PASSWORD=... on Windows). The options can also be an address: dbConnect("postgres://gmp:password@127.0.0.1:5432/gmp"). All options: DbConnectOptions.

dbConnect itself never fails - the connections open on the first query, and reopen by themselves when the database restarts. A database that cannot be reached shows up as an error of the first query.

How waiting worksКопировать ссылку на раздел

A database answers in milliseconds, but the server must not stop for them. So a query does not block the server: the script code that asked for the result waits, and everything else - players moving, other handlers, timers - goes on. When the answer arrives, the code continues on the next line as if nothing happened:

registerCommand("gold", function(pid)
  local gold = db:scalar("SELECT gold FROM players WHERE name = $1", getPlayerName(pid))   -- waits here
  sendMessage(pid, "You have " .. tostring(gold or 0) .. " ore")
end)

This works everywhere the server runs script code: event handlers, commands, timers, and the top level of script files (so a script can create its tables before going on). Things to keep in mind:

• A player may leave while the code waits. After a query, check that the player is still there before using pid - the slot may even belong to someone else by then:

  Копировать

  local name = getPlayerName(pid)
  local row = db:queryOne("SELECT gold FROM players WHERE name = $1", name)
  if not isPlayerConnected(pid) or getPlayerName(pid) ~= name then return end   -- left meanwhile

• Cancelling an event only works before the first query. return false in onPlayerChat and eventValue in onPlayerHit count only before the handler waits for the first time - after that the event has already gone on. Decide first, store afterwards.

• Errors are Lua errors. Bad SQL, a broken constraint (a duplicate unique value) or an unreachable database raise an error: it stops the handler and goes to the server log with the line of the script. Catch it with pcall when you want to react:

  Копировать

  local ok, err = pcall(db.query, db, "INSERT INTO players (name) VALUES ($1)", name)
  if not ok then print("could not register " .. name .. ": " .. err) end

• Not waiting at all. A function as the last argument of a query is called with (result, err) when the answer comes, and the code goes on at once - handy for fire-and-forget writes like a chat log. async runs a whole function this way.

SQL queriesКопировать ссылку на раздел

Values never go into the SQL text - they are parameters $1, $2... given after the SQL. The database gets them separately, so a player named '; DROP TABLE players; -- is just a strange name, not an attack.

-- all rows
local rows = db:query("SELECT name, kills FROM players ORDER BY kills DESC LIMIT $1", 10)
for i, row in ipairs(rows) do print(i, row.name, row.kills) end

-- one row, or nil
local p = db:queryOne("SELECT * FROM players WHERE name = $1", name)

-- one value, or nil
local count = db:scalar("SELECT count(*) FROM players")

-- changes: result.affected = number of rows
local res = db:query("UPDATE players SET gold = gold + $1 WHERE name = $2", 50, name)

-- RETURNING gives the stored row back
local new = db:queryOne("INSERT INTO players (name) VALUES ($1) RETURNING id", name)

-- several statements, no parameters: tables, indexes, other schema changes
db:exec([[
  CREATE TABLE IF NOT EXISTS players (
    id    serial PRIMARY KEY,
    name  text UNIQUE NOT NULL,
    gold  integer NOT NULL DEFAULT 0,
    kills integer NOT NULL DEFAULT 0
  );
  CREATE INDEX IF NOT EXISTS players_kills_idx ON players (kills);
]])

Lua values and database values convert like this:

Models (ORM)Копировать ссылку на раздел

A model describes a table in Lua. Its functions write the SQL, pass every value as a parameter and check field names - a typo is an error instead of a silently empty result.

local Player = db:model("players", {
  name  = { type = "text", unique = true, notNull = true },
  gold  = { type = "integer", default = 0 },
  kills = { type = "integer", default = 0, index = true },
  stats = "json",                     -- a Lua table, stored as jsonb
}, { timestamps = true })             -- adds created_at and updated_at
Player:sync()                         -- creates the table (and later-added columns)

Without a field marked primaryKey = true the model gets an id field (serial). All field types and options: ModelField. sync() creates a missing table, adds columns for fields added to the model later and creates indexes - it never changes or removes existing columns (do that with db:exec).

-- create
local p = Player:create({ name = "Diego", gold = 500 })        -- p.id, p.kills (0), p.created_at...

-- find
local diego = Player:findOne({ name = "Diego" })               -- or nil
local same  = Player:find(diego.id)                            -- by primary key
local top   = Player:findAll({ kills = { gt = 0 } }, { orderBy = "kills desc", limit = 10 })
local n     = Player:count({ gold = { gte = 1000 } })

-- change a record and save it (only changed fields are written)
diego.gold = diego.gold - 100
diego.stats = { favouriteWeapon = "ItMw_1H_Sword_01" }
diego:save()

-- change many rows at once
Player:update({ kills = 0 }, { gold = 0 })

-- counters: in the database itself, never lost when two happen at once
Player:increment({ name = "Diego" }, { kills = 1, gold = 25 })

-- insert or update by a unique field
Player:upsert({ name = "Lester", gold = 30 }, "name")

-- find, or create when missing
local rec, created = Player:findOrCreate({ name = "Milten" }, { gold = 100 })

-- delete
Player:delete({ name = "Lester" })

Conditions (where) are tables of field = value; all of them must match. A field may get a table of operators instead of a value:

update, increment and delete refuse an empty condition, so a mistake never wipes the whole table. For anything the conditions cannot express - joins, subqueries, SQL functions - write SQL and still get records back with Player:query.

Related tables (joins)Копировать ссылку на раздел

Data of one player usually lives in several tables: the player, their guild, their items, their house. Describe how the tables are connected once - with relations - and load a player together with everything they own in one call:

local Guild = db:model("guilds", {
  name = { type = "text", unique = true, notNull = true },
  camp = "text",
}):sync()

local Player = db:model("players", {
  name     = { type = "text", unique = true, notNull = true },
  gold     = { type = "integer", default = 0 },
  guild_id = { type = "integer", references = Guild, onDelete = "set null" },
}):sync()

local Item = db:model("items", {
  owner_id = { type = "integer", references = Player, onDelete = "cascade", index = true },
  class    = { type = "text", notNull = true },
  amount   = { type = "integer", default = 1 },
}):sync()

local House = db:model("houses", {
  owner_id = { type = "integer", references = Player, onDelete = "cascade", unique = true },
  location = "text",
}):sync()

-- how the tables are connected
Player:belongsTo("guild", Guild, "guild_id")   -- players.guild_id -> guilds.id      (one guild)
Player:hasMany("items", Item, "owner_id")      -- items.owner_id   -> players.id     (many items)
Player:hasOne("house", House, "owner_id")      -- houses.owner_id  -> players.id     (one house)
Guild:hasMany("members", Player, "guild_id")   -- the other direction, for guild lists

-- a player with their guild, items and house
local p = Player:findOne({ name = "Diego" }, { include = { "guild", "items", "house" } })
if p then
  print(p.name, p.gold)
  print("guild:", p.guild and p.guild.name or "none")      -- a Guild record, or nil
  for _, item in ipairs(p.items) do                        -- a list of Item records (maybe empty)
    print("item:", item.class, item.amount)
  end
  print("house:", p.house and p.house.location or "none")   -- a House record, or nil
end

include loads each relation with one extra query for all the records at once - also for findAll returning a hundred players - never one query per player. Each relation can have its own condition, order and nested relations:

-- a guild with its richest members, each with their swords
local g = Guild:findOne({ name = "Old Camp Guards" }, {
  include = {
    members = {
      where = { gold = { gte = 100 } },
      orderBy = "gold desc",
      include = { items = { where = { class = { like = "ItMw_%" } } } },
    },
  },
})
for _, m in ipairs(g.members) do print(m.name, m.gold, #m.items .. " swords") end

-- all players of a camp's guilds, with their guild (two queries in total)
local soldiers = Player:findAll({ guild_id = { ["in"] = { 1, 2 } } }, { include = { "guild" } })

The loaded records are ordinary records - change and save them on their own (p.items[1].amount = 5, p.items[1]:save()); p:toTable() gives everything, relations included, as plain tables (ready for json.encode or triggerClientEvent).

When you need the related data in the same row - a column from another table, or a count - write the JOIN yourself with Player:query. The extra columns become extra fields of the records:

local ranking = Player:query([[
  SELECT p.*, g.name AS guild_name, count(i.id) AS item_count
  FROM players p
  LEFT JOIN guilds g ON g.id = p.guild_id
  LEFT JOIN items i ON i.owner_id = p.id
  GROUP BY p.id, g.name
  ORDER BY item_count DESC
  LIMIT $1]], 10)
for i, p in ipairs(ranking) do
  print(i, p.name, p.guild_name or "-", p.item_count)
end
Player:loadRelations(ranking, { "house" })   -- relations work for such records too

TransactionsКопировать ссылку на раздел

Changes that belong together - money leaving one player and reaching another - go into a transaction: either all of them are saved, or none.

local function pay(from, to, amount)
  db:transaction(function(tx)
    local gold = tx:scalar("SELECT gold FROM players WHERE name = $1 FOR UPDATE", from)
    if (gold or 0) < amount then error("not enough ore") end      -- error = nothing is saved
    Player:with(tx):increment({ name = from }, { gold = -amount })
    Player:with(tx):increment({ name = to }, { gold = amount })
  end)
end

Inside the function use tx (not db) for SQL, Model:with(tx) for models and rec:save(tx) for records. An error inside rolls everything back and is raised again after the rollback.

Keeping whole charactersКопировать ссылку на раздел

The server package comes with a ready pattern, scripts/database_example.lua (not enabled by default): it creates the tables players, player_items and player_skills, saves a player's attributes, inventory, worn armor and weapons and skills when they leave (and every few minutes), loads them when they join and gives everything back in onPlayerSpawn; a new player gets a start kit. Enable it in config.xml (<script src="scripts/database_example.lua" type="server" />) and read the instructions at its top.

The two rules it follows apply to any such script:

• Read the player, then wait. In onPlayerDisconnect read everything first (getPlayerInventory, getPlayerEquipment, getPlayerSkills, the attributes) - after the first database call the player is gone.
• Do not save what was never loaded. A player who left during loading, or whose data could not be loaded because the database was down, must not be saved - it would overwrite their real data.

A complete example: player statisticsКопировать ссылку на раздел

Kills, deaths and time played, kept across restarts, with /stats and /top:

local db = dbConnect({ host = "127.0.0.1", user = "gmp", password = os.getenv("GMP_DB_PASSWORD"), database = "gmp" })

local Stats = db:model("player_stats", {
  name    = { type = "text", unique = true, notNull = true },
  kills   = { type = "integer", default = 0, index = true },
  deaths  = { type = "integer", default = 0 },
  seconds = { type = "bigint", default = 0 },
}, { timestamps = true }):sync()

local joinedAt = {}   -- pid -> os.time() of joining

addEventHandler("onPlayerConnect", function(pid)
  joinedAt[pid] = os.time()
  local name = getPlayerName(pid)
  local s, created = Stats:findOrCreate({ name = name })
  if not isPlayerConnected(pid) or getPlayerName(pid) ~= name then return end
  sendMessage(pid, created and "Welcome to the Colony!" or ("Welcome back! Kills: " .. s.kills .. ", deaths: " .. s.deaths))
end)

addEventHandler("onPlayerDisconnect", function(pid)
  if joinedAt[pid] then
    Stats:increment({ name = getPlayerName(pid) }, { seconds = os.time() - joinedAt[pid] })
    joinedAt[pid] = nil
  end
end)

addEventHandler("onPlayerDead", function(pid, killerId)
  if isNpc(pid) then return end
  Stats:increment({ name = getPlayerName(pid) }, { deaths = 1 })
  if killerId and not isNpc(killerId) and killerId ~= pid then
    Stats:increment({ name = getPlayerName(killerId) }, { kills = 1 })
  end
end)

registerCommand("stats", function(pid)
  local s = Stats:findOne({ name = getPlayerName(pid) })
  if s and isPlayerConnected(pid) then
    sendMessage(pid, ("Kills %d, deaths %d, played %d min"):format(s.kills, s.deaths, s.seconds // 60))
  end
end)

registerCommand("top", function(pid)
  local best = Stats:findAll({ kills = { gt = 0 } }, { orderBy = "kills desc", limit = 5 })
  for i, s in ipairs(best) do
    if not isPlayerConnected(pid) then return end
    sendMessage(pid, i .. ". " .. s.name .. " - " .. s.kills)
  end
end)

Good practiceКопировать ссылку на раздел

• Counters and currencies with increment. Reading a value, changing it in Lua and saving it back loses updates when two happen at once; increment (or SET x = x + $1 in SQL) never does.
• Do not query on every hit. onPlayerHit comes many times a second in a fight - keep such values in a Lua table and write them on death, on disconnect or every few minutes from a timer.
• Indexes (index = true, unique = true) on the fields you search and sort by.
• Data and players. Find players by something stable you store yourself (here the name); ids (pid) are slot numbers, reused by the next player.
• Shutdown. When the server stops, every player still playing gets onPlayerDisconnect, and the server waits up to 10 seconds for the queued queries - player data saved on leaving is saved on a stop too.
• Pool. pool (default 4) is how many queries run at the same time. A transaction holds one connection until it ends - keep transactions short.

LimitsКопировать ссылку на раздел

• No TLS: the database must be reachable without SSL (same machine, private network, SSH tunnel).
• One statement per query (several are allowed in exec, without parameters). COPY ... FROM STDIN and LISTEN / NOTIFY are not supported.
• numeric values become Lua numbers (about 15 significant digits); timestamps and dates come back as text, for example "2026-10-31 18:30:00.123+01".
• A query may take at most timeout seconds (default 30), then it fails and its connection is replaced.