Model

A model is a table of the PostgreSQL database described in Lua, created with db:model. Its functions build the SQL themselves and return rows as records. In the examples Player is a model.

local Player = db:model("players", {
  name  = { type = "text", unique = true, notNull = true },
  kills = { type = "integer", default = 0 },
  gold  = { type = "integer", default = 0 },
  stats = "json",                                  -- a Lua table, stored as jsonb
}, { timestamps = true })                           -- + created_at, updated_at
Player:sync()                                      -- CREATE TABLE IF NOT EXISTS ...

addEventHandler("onPlayerConnect", function(pid)
  local p = Player:findOrCreate({ name = getPlayerName(pid) }, { gold = 100 })
  sendMessage(pid, "Gold: " .. p.gold .. ", kills: " .. p.kills)
end)

addEventHandler("onPlayerDead", function(pid, killerId)
  if killerId and not isNpc(killerId) then
    Player:increment({ name = getPlayerName(killerId) }, { kills = 1 })   -- atomic, no lost kills
  end
end)

registerCommand("top", function(pid)
  for i, p in ipairs(Player:findAll({ kills = { gt = 0 } }, { orderBy = "kills desc", limit = 5 })) do
    sendMessage(pid, i .. ". " .. p.name .. " - " .. p.kills)
  end
end)

TableLink zum Abschnitt kopieren

syncLink zum Abschnitt kopieren

Player:sync()

Creates the table when it does not exist yet, adds the columns of fields added to the model since (existing columns are never changed or removed) and creates the indexes of fields with index = true. Call it once at script start. Returns the model, so it can follow db:model(...) directly. A new notNull field needs a default when the table already has rows.

local Ban = db:model("bans", { name = { type = "text", unique = true }, reason = "text" }):sync()

Returns: Model - The same model.

withLink zum Abschnitt kopieren

Player:with(tx)

The same model, with its queries running inside a transaction (the tx of Database:transaction).

db:transaction(function(tx)
  Player:with(tx):increment({ name = from }, { gold = -amount })
  Player:with(tx):increment({ name = to }, { gold = amount })
end)

Returns: Model - The model bound to the transaction.

CreatingLink zum Abschnitt kopieren

createLink zum Abschnitt kopieren

Player:create(values)

Inserts a row and returns it as a record - with the values the database filled in (the id, defaults, created_at).

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

Returns: Record - The new row.

upsertLink zum Abschnitt kopieren

Player:upsert(values, conflict?)

Inserts a row, or - when a row with the same value(s) of the conflict field(s) already exists - updates that row with the given values. One statement, safe when two players do it at the same moment. The conflict field must be the primary key or unique.

Player:upsert({ name = getPlayerName(pid), stats = { lastLogin = os.time() } }, "name")

Returns: Record - The inserted or updated row.

findOrCreateLink zum Abschnitt kopieren

Player:findOrCreate(where, defaults?)

Returns the row matching where, or creates it from where plus defaults when there is none. The second result tells which happened. where may hold plain values only (no operators).

local p, isNew = Player:findOrCreate({ name = getPlayerName(pid) }, { gold = 100 })
if isNew then sendMessage(pid, "Welcome, newcomer! Here are 100 ore.") end

Returns:

newLink zum Abschnitt kopieren

Player:new(values?)

A record that is not in the database yet - record:save() inserts it.

local p = Player:new({ name = "Lester" })
p.gold = 30
p:save()

Returns: Record - The new, unsaved record.

FindingLink zum Abschnitt kopieren

The functions below take a condition where: a table of field = value pairs, all of which must match. Instead of a value a field can get a table of operators: eq, ne, gt, gte, lt, lte, like, ilike (case-insensitive), notLike, in and notIn (a list), between ({ from, to }), isNull (true / false). Alternatives go into _or = { {...}, {...} } (_and groups conditions the same way). A value can also be db.raw("..."). The options may include related tables (see Relations below). Examples: { name = "Diego" }, { kills = { gte = 10 }, gold = { lt = 100 } }, { name = { ilike = "%gor%" } }, { guild = { ["in"] = { "GRD", "STT" } } }, { _or = { { kills = { gt = 100 } }, { gold = { gt = 1000 } } } }.

findLink zum Abschnitt kopieren

Player:find(id, options?)

Returns the record with the given primary key value, or nil.

local p = Player:find(7)
local p2 = Player:find(7, { include = { "items" } })   -- with its items (see Relations)

Returns: Record | nil - The row, or nil.

findOneLink zum Abschnitt kopieren

Player:findOne(where?, options?)

Returns the first record matching where, or nil.

local p = Player:findOne({ name = getPlayerName(pid) })

Returns: Record | nil - The row, or nil.

findAllLink zum Abschnitt kopieren

Player:findAll(where?, options?)

Returns all records matching where (a list, possibly empty).

local rich = Player:findAll({ gold = { gte = 1000 } }, { orderBy = "gold desc", limit = 10 })
for _, p in ipairs(rich) do print(p.name, p.gold) end

Returns: Record[] - The rows.

countLink zum Abschnitt kopieren

Player:count(where?)

Counts the rows matching where.

local veterans = Player:count({ kills = { gte = 100 } })

Returns: integer - The number of rows.

existsLink zum Abschnitt kopieren

Player:exists(where)

true when at least one row matches where.

if Player:exists({ name = name }) then sendMessage(pid, "This name is taken") end

Returns: boolean - Whether such a row exists.

queryLink zum Abschnitt kopieren

Player:query(sql, ...)

Runs your own SQL that returns rows of this table, and returns them as records. For conditions the where tables cannot express (joins, subqueries, functions). Extra columns of the query - from a JOIN or a calculation - become extra fields of the records; save() never writes them.

local active = Player:query("SELECT * FROM players WHERE updated_at > now() - interval '7 days' ORDER BY kills DESC")

-- a JOIN: each player with the name of their guild and the number of their items
local list = 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 _, p in ipairs(list) do print(p.name, p.guild_name, p.item_count) end

Returns: Record[] - The rows.

RelationsLink zum Abschnitt kopieren

Rows of other tables loaded together with the records: define a relation once (belongsTo, hasMany, hasOne), then name it in include of find, findOne and findAll - Player:findOne({ name = "Diego" }, { include = { "guild", "items" } }) gives the player with p.guild (a record) and p.items (a list of records). Each relation costs one extra query for all the records together, never one query per record. An include entry may carry its own options - items = { where = ..., orderBy = ..., include = ... } - and includes can be nested (the items' own relations).

belongsToLink zum Abschnitt kopieren

Player:belongsTo(name, target, foreignKey?)

Declares that each record of this model points to one record of target: the field foreignKey of this model holds the primary key of the target. With include the target record lands in rec[name] (nil when the field is empty or the row is gone). Returns the model.

local Guild = db:model("guilds", { name = { type = "text", unique = true } }):sync()
local Player = db:model("players", {
  name     = { type = "text", unique = true },
  guild_id = { type = "integer", references = Guild },
}):sync()
Player:belongsTo("guild", Guild, "guild_id")

local p = Player:findOne({ name = "Diego" }, { include = { "guild" } })
print(p.guild and p.guild.name)

Returns: Model - This model.

hasManyLink zum Abschnitt kopieren

Player:hasMany(name, target, foreignKey)

Declares that each record of this model has many records of target: the field foreignKey of the target holds this model's primary key. With include the list (possibly empty) lands in rec[name]. Returns the model.

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

local p = Player:findOne({ name = "Diego" }, { include = { items = { orderBy = "class" } } })
for _, item in ipairs(p.items) do print(item.class, item.amount) end

Returns: Model - This model.

hasOneLink zum Abschnitt kopieren

Player:hasOne(name, target, foreignKey)

Like hasMany, but for one related record: rec[name] is a record or nil. Returns the model.

Player:hasOne("house", House, "owner_id")

Returns: Model - This model.

loadRelationsLink zum Abschnitt kopieren

Player:loadRelations(records, include)

Loads relations into records fetched some other way - for example by Model:query - or adds relations to records later. Takes one record or a list; returns what it got.

local top = Player:query("SELECT * FROM players ORDER BY kills DESC LIMIT 5")
Player:loadRelations(top, { "guild", "items" })

Returns: Record | Record[] - The same record(s).

Changing and deletingLink zum Abschnitt kopieren

These functions require a condition with at least one field - an empty or missing where is an error, so a mistake never changes the whole table. To change every row on purpose, use db:query.

updateLink zum Abschnitt kopieren

Player:update(where, values)

Sets the given values in all rows matching where and returns how many rows changed. With timestamps = true updated_at is set too.

Player:update({ name = name }, { gold = 0 })

Returns: integer - Number of changed rows.

incrementLink zum Abschnitt kopieren

Player:increment(where, amounts)

Adds amounts to number fields of all rows matching where, in the database itself (kills = kills + 1), so two changes at the same moment never overwrite each other - the right way for counters and currencies. Negative amounts subtract. Returns how many rows changed.

Player:increment({ name = getPlayerName(pid) }, { kills = 1, gold = 25 })

Returns: integer - Number of changed rows.

deleteLink zum Abschnitt kopieren

Player:delete(where)

Deletes all rows matching where and returns how many were deleted.

Ban:delete({ name = name })

Returns: integer - Number of deleted rows.

TypesLink zum Abschnitt kopieren

ModelFieldLink zum Abschnitt kopieren

A field of a model: name = "type", or name = { type = "type", options }.

Types: "serial" / "bigserial" (auto-numbered integer - for the primary key), "integer" ("int"), "bigint", "smallint", "float" ("double", "number"), "real", "numeric" ("decimal", exact - with precision and scale), "boolean" ("bool"), "text" ("string"; with length = varchar), "json" ("jsonb" - any Lua table), "timestamp" ("datetime"; read back as text, may be set with an os.time() number), "date", "uuid".

ModelOptionsLink zum Abschnitt kopieren

Options of Database:model.

ModelFindOptionsLink zum Abschnitt kopieren

Options of Model:find, Model:findOne and Model:findAll.