Database¶
Database helpers for Lilia storage module setup, schema creation, SQL value conversion, table queries, migrations, transactions, and snapshot import/export.
Overview
lia.db.connect(callback, reconnect)View Source
Purpose
Connects the configured database module, marks the database as connected, assigns the module query and escape functions, and flushes queued queries.
Realm
Server
Parameters
function callback optional Optional function called after the database module finishes connecting.
boolean reconnect optional When true, allows the connection routine to run even if the database was previously marked as connected.
Example Usage
lia.db.connect(function()
lia.db.loadTables()
end)
lia.db.wipeTables(callback)View Source
Purpose
Drops every SQLite table whose name starts with `lia_`, logs the wiped tables, and then runs an optional callback.
Realm
Server
Parameters
function callback optional Optional function called after all matching tables have been dropped.
Example Usage
lia.db.wipeTables(function()
lia.db.loadTables()
end)
lia.db.loadTables()View Source
lia.db.waitForTablesToLoad()View Source
Purpose
Returns a deferred object that resolves immediately if tables are already loaded, or resolves when `OnDatabaseLoaded` runs.
Realm
Server
Returns
Deferred Resolves after database tables are available.
Example Usage
lia.db.waitForTablesToLoad():next(function()
print("Tables are ready")
end)
lia.db.convertDataType(value, noEscape)View Source
Purpose
Converts Lua values into SQL-safe literal values used when constructing database queries.
Realm
Server
Parameters
any value The value to convert. Strings and tables are escaped unless `noEscape` is true.
boolean noEscape optional When true, string and table values are returned without SQL escaping or quoting.
Returns
string|number The converted SQL literal, numeric boolean value, NULL literal, or original numeric value.
Example Usage
local value = lia.db.convertDataType({enabled = true})
lia.db.insertTable(value, Key, Key, callback, dbTable)View Source
Purpose
Inserts a row into a `lia_` database table and resolves with the query results and last inserted row ID.
Realm
Server
Parameters
table value Key-value pairs to insert into the target table.
unknown Key value pairs to insert into the target table.
unknown Key value pairs to insert into the target table.
function callback optional Optional function called with `results` and `lastID` after the insert query completes.
string dbTable optional Table suffix to insert into. Defaults to `characters`, producing `lia_characters`.
Returns
Deferred Resolves with a table containing `results` and `lastID`.
Example Usage
lia.db.insertTable({steamID = client:SteamID(), steamName = client:Name()}, nil, "players")
lia.db.updateTable(value, Key, Key, callback, dbTable, condition)View Source
Purpose
Updates rows in a `lia_` database table using the supplied values and optional condition.
Realm
Server
Parameters
table value Key-value pairs to assign in the update statement.
unknown Key value pairs to assign in the update statement.
unknown Key value pairs to assign in the update statement.
function callback optional Optional function called with `results` and `lastID` after the update query completes.
string dbTable optional Table suffix to update. Defaults to `characters`, producing `lia_characters`.
string|table condition optional Optional WHERE clause as a raw string or a table of field comparisons.
Returns
Deferred Resolves with a table containing `results` and `lastID`.
Example Usage
lia.db.updateTable({lastOnline = os.time()}, nil, "players", {steamID = client:SteamID()})
lia.db.select(fields, dbTable, condition, limit)View Source
Purpose
Selects rows from a `lia_` database table with optional conditions and result limiting.
Realm
Server
Parameters
string|table fields Field list to select, or `*` for all fields.
string dbTable optional Table suffix to select from. Defaults to `characters`, producing `lia_characters`.
string|table condition optional Optional WHERE clause as a raw string or a table of field comparisons.
number|string limit optional Optional SQL LIMIT value.
Returns
Deferred Resolves with a table containing `results` and `lastID`.
Example Usage
lia.db.select({"id", "name"}, "characters", {steamID = client:SteamID()}, 1)
lia.db.selectWithCondition(fields, dbTable, conditions, limit, orderBy)View Source
Purpose
Selects rows from a `lia_` database table using optional conditions, ordering, and result limiting.
Realm
Server
Parameters
string|table fields Field list to select, or `*` for all fields.
string dbTable optional Table suffix to select from. Defaults to `characters`, producing `lia_characters`.
string|table conditions optional Optional WHERE clause as a raw string or a table of field comparisons.
number|string limit optional Optional SQL LIMIT value.
string orderBy optional Optional SQL ORDER BY expression.
Returns
Deferred Resolves with a table containing `results` and `lastID`.
Example Usage
lia.db.selectWithCondition("*", "logs", {category = "admin"}, 25, "timestamp DESC")
lia.db.count(dbTable, condition)View Source
Purpose
Counts rows in a `lia_` database table that match an optional condition.
Realm
Server
Parameters
string dbTable Table suffix to count from, producing `lia_
string|table condition optional Optional WHERE clause as a raw string or a table of field comparisons.
Returns
Deferred Resolves with the numeric row count.
Example Usage
lia.db.count("characters", {steamID = client:SteamID()}):next(function(total)
print(total)
end)
lia.db.addDatabaseFields()View Source
lia.db.ensureIndexes()View Source
lia.db.exists(dbTable, condition)View Source
Purpose
Checks whether at least one row exists in a `lia_` database table for the supplied condition.
Realm
Server
Parameters
string dbTable Table suffix to check, producing `lia_
string|table condition optional Optional WHERE clause as a raw string or a table of field comparisons.
Returns
Deferred Resolves with true when at least one matching row exists, otherwise false.
Example Usage
lia.db.exists("players", {steamID = client:SteamID()}):next(function(found)
print(found)
end)
lia.db.selectOne(fields, dbTable, condition)View Source
Purpose
Selects the first row from a `lia_` database table that matches an optional condition.
Realm
Server
Parameters
string|table fields Field list to select, or `*` for all fields.
string dbTable Table suffix to select from, producing `lia_
string|table condition optional Optional WHERE clause as a raw string or a table of field comparisons.
Returns
Deferred Resolves with the first result row, or nil when no row matches.
Example Usage
lia.db.selectOne("*", "players", {steamID = client:SteamID()}):next(function(row)
PrintTable(row or {})
end)
lia.db.bulkInsert(dbTable, rows)View Source
Purpose
Inserts multiple rows into a `lia_` database table with one multi-value INSERT query.
Realm
Server
Parameters
string dbTable Table suffix to insert into, producing `lia_
table rows Array of row tables to insert. Column names are taken from the first row.
Returns
Deferred Resolves when the bulk insert finishes.
Example Usage
lia.db.bulkInsert("logs", {
{category = "server", message = "Started"},
{category = "server", message = "Loaded"}
})
lia.db.bulkUpsert(dbTable, rows)View Source
Purpose
Inserts or replaces multiple rows in a `lia_` database table with one multi-value INSERT OR REPLACE query.
Realm
Server
Parameters
string dbTable Table suffix to upsert into, producing `lia_
table rows Array of row tables to upsert. Column names are taken from the first row.
Returns
Deferred Resolves when the bulk upsert finishes.
Example Usage
lia.db.bulkUpsert("invdata", {
{invID = 1, key = "width", value = "6"},
{invID = 1, key = "height", value = "4"}
})
lia.db.insertOrIgnore(value, Key, Key, dbTable)View Source
Purpose
Inserts a row into a `lia_` database table only when it does not conflict with existing constraints.
Realm
Server
Parameters
table value Key-value pairs to insert into the target table.
unknown Key value pairs to insert into the target table.
unknown Key value pairs to insert into the target table.
string dbTable optional Table suffix to insert into. Defaults to `characters`, producing `lia_characters`.
Returns
Deferred Resolves with a table containing `results` and `lastID`.
Example Usage
lia.db.insertOrIgnore({usergroup = "admin", privileges = "{}"}, "admin")
lia.db.tableExists(tbl)View Source
Purpose
Checks whether a table exists in SQLite by its exact table name.
Realm
Server
Parameters
string tbl Exact table name to check, such as `lia_characters`.
Returns
Deferred Resolves with true when the table exists, otherwise false.
Example Usage
lia.db.tableExists("lia_characters"):next(function(exists)
print(exists)
end)
lia.db.fieldExists(tbl, field)View Source
Purpose
Checks whether a column exists in a table using SQLite table information.
Realm
Server
Parameters
Returns
Deferred Resolves with true when the column exists, otherwise false.
Example Usage
lia.db.fieldExists("lia_characters", "money"):next(function(exists)
print(exists)
end)
lia.db.getTables()View Source
Purpose
Retrieves all SQLite table names that start with `lia_`.
Realm
Server
Returns
Deferred Resolves with an array of matching table names.
Example Usage
lia.db.getTables():next(function(tables)
PrintTable(tables)
end)
lia.db.transaction(queries)View Source
Purpose
Runs a list of SQL queries inside a transaction, committing when all queries pass and rolling back if any query fails.
Realm
Server
Parameters
table queries Ordered array of SQL query strings to run.
Returns
Deferred Resolves after COMMIT succeeds, or rejects after ROLLBACK on failure.
Example Usage
lia.db.transaction({
"UPDATE lia_players SET userGroup = 'admin' WHERE steamID = 'STEAM_0:1:1'",
"INSERT INTO lia_logs (category, message) VALUES ('admin', 'Promoted user')"
})
lia.db.escapeIdentifier(id)View Source
lia.db.upsert(value, Key, Key, dbTable)View Source
Purpose
Inserts or replaces a row in a `lia_` database table.
Realm
Server
Parameters
table value Key-value pairs to insert or replace in the target table.
unknown Key value pairs to insert or replace in the target table.
unknown Key value pairs to insert or replace in the target table.
string dbTable optional Table suffix to upsert into. Defaults to `characters`, producing `lia_characters`.
Returns
Deferred Resolves with a table containing `results` and `lastID`.
Example Usage
lia.db.upsert({id = 1, name = "Example"}, "characters")
lia.db.delete(dbTable, condition)View Source
Purpose
Deletes rows from a `lia_` database table using an optional condition.
Realm
Server
Parameters
string dbTable optional Table suffix to delete from. Defaults to `character`, producing `lia_character`.
string|table condition optional Optional WHERE clause as a raw string or a table of field comparisons.
Returns
Deferred Resolves with a table containing `results` and `lastID`.
Example Usage
lia.db.delete("characters", {id = 5})
lia.db.createTable(dbName, primaryKey, schema)View Source
Purpose
Creates a `lia_` prefixed table from a schema definition when it does not already exist.
Realm
Server
Parameters
string dbName Table suffix to create, producing `lia_
string primaryKey optional Optional primary key column name.
table schema Array of column definitions with `name`, `type`, optional `not_null`, and optional `default` fields.
Returns
Deferred Resolves with true when the create-table query completes.
Example Usage
lia.db.createTable("example", "id", {
{name = "id", type = "integer", not_null = true},
{name = "value", type = "text", default = ""}
})
lia.db.createColumn(tableName, columnName, columnType, defaultValue)View Source
Purpose
Adds a column to a `lia_` prefixed table when the column does not already exist.
Realm
Server
Parameters
string tableName Table suffix to alter, producing `lia_
string columnName Column name to add.
string columnType SQL column type to add.
any defaultValue optional Optional default value for the new column.
Returns
Deferred Resolves with true when the column is added, or false when it already exists.
Example Usage
lia.db.createColumn("warnings", "notes", "text", "")
lia.db.removeTable(tableName)View Source
lia.db.removeColumn(tableName, columnName)View Source
Purpose
Removes a column from a `lia_` prefixed table by rebuilding the table without that column.
Realm
Server
Parameters
Returns
Deferred Resolves with true when the column is removed, false when the table or column does not exist, or rejects when removal is not possible.
Example Usage
lia.db.removeColumn("warnings", "notes")
lia.db.getCharacterTable(callback)View Source
Purpose
Retrieves the column names from the `lia_characters` table and passes them to a callback.
Realm
Server
Parameters
function callback Function called with an array of character table column names.
Example Usage
lia.db.getCharacterTable(function(columns)
PrintTable(columns)
end)
lia.db.createSnapshot(tableName)View Source
Purpose
Saves all rows from a `lia_` prefixed table to a JSON snapshot file under `data/lilia/snapshots`.
Realm
Server
Parameters
string tableName Table suffix to snapshot, producing `lia_
Returns
Deferred Resolves with snapshot file name, path, and record count.
Example Usage
lia.db.createSnapshot("characters"):next(function(snapshot)
print(snapshot.path)
end)
lia.db.loadSnapshot(fileName)View Source
Purpose
Loads a JSON snapshot from `data/lilia/snapshots`, clears the target table, and restores the snapshot rows in batches.
Realm
Server
Parameters
string fileName Snapshot file name to load from `lilia/snapshots`.
Returns
Deferred Resolves with restored table name, record count, and snapshot timestamp.
Example Usage
lia.db.loadSnapshot("snapshot_characters_1700000000.json")
Hooks
Library-specific hooks documented for this library.