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)

TableCopy Anchor Link

syncCopy Anchor Link

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.

withCopy Anchor Link

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.

CreatingCopy Anchor Link

createCopy Anchor Link

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.

upsertCopy Anchor Link

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.

findOrCreateCopy Anchor Link

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:

newCopy Anchor Link

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.

FindingCopy Anchor Link

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

findCopy Anchor Link

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.

findOneCopy Anchor Link

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.

findAllCopy Anchor Link

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.

countCopy Anchor Link

Player:count(where?)

Counts the rows matching where.

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

Returns: integer - The number of rows.

existsCopy Anchor Link

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.

queryCopy Anchor Link

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.

RelationsCopy Anchor Link

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

belongsToCopy Anchor Link

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.

hasManyCopy Anchor Link

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.

hasOneCopy Anchor Link

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.

loadRelationsCopy Anchor Link

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 deletingCopy Anchor Link

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.

updateCopy Anchor Link

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.

incrementCopy Anchor Link

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.

deleteCopy Anchor Link

Player:delete(where)

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

Ban:delete({ name = name })

Returns: integer - Number of deleted rows.

TypesCopy Anchor Link

ModelFieldCopy Anchor Link

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

ModelOptionsCopy Anchor Link

Options of Database:model.

ModelFindOptionsCopy Anchor Link

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