Database Library
This page documents functions for connecting to the database.
Overview
The database library sets up the SQL connection used by the framework. It defines helpers for creating tables, performing queries, and waiting for asynchronous operations to finish.
lia.db.connect
Purpose
Establishes a connection to the configured database module. If the database is not already connected, or if reconnect
is true
, it creates or re-establishes a connection.
Parameters
-
callback
(function): Function called when the connection is established. -
reconnect
(boolean): Reconnect using an existing database object.
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.wipeTables
Purpose
Drops all Lilia tables from the database. Irreversible – all stored data is removed.
Parameters
callback
(function): Function called when the wipe completes.
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.loadTables
Purpose
Creates required tables if they do not already exist. Ensures the schema is set up.
Parameters
- None
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.waitForTablesToLoad
Purpose
Returns a deferred that resolves once tables are fully created. Useful for awaiting setup in async code.
Parameters
- None
Realm
Server
Returns
- deferred: Resolves when tables are ready.
Example Usage
lia.db.convertDataType
Purpose
Converts a Lua value into a string appropriate for SQL insertion, handling escaping unless noEscape
is true
.
Parameters
-
value
(any): Value to convert. -
noEscape
(boolean): Skip escaping whentrue
.
Realm
Shared
Returns
- string | number: SQL-ready representation.
Example Usage
lia.db.insertTable
Purpose
Inserts a row into a table using key-value pairs.
Parameters
-
value
(table): Column/value pairs. -
callback
(function): Function called after the insert. -
dbTable
(string): Table name without thelia_
prefix.
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.updateTable
Purpose
Updates one or more rows according to a condition.
Parameters
-
value
(table): Columns to update and new values. -
callback
(function): Function called after update. -
dbTable
(string): Table name withoutlia_
. -
condition
(string): SQLWHERE
clause.
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.updateTable({ name = "Updated" }, function()
print("Row updated")
end, "characters", "id = 1")
lia.db.select
Purpose
Selects rows, optionally filtered and limited, returning a deferred.
Parameters
-
fields
(table | string): Columns to select. -
dbTable
(string): Table name withoutlia_
. -
condition
(string): SQL condition (WHERE
). -
limit
(number): Maximum number of rows.
Realm
Server
Returns
- deferred: Resolves with results and last insert ID.
Example Usage
lia.db.upsert
Purpose
Inserts or updates a row depending on unique-key conflict. Returns a deferred.
Parameters
-
value
(table): Columns and values. -
dbTable
(string): Table name withoutlia_
.
Realm
Server
Returns
- deferred: Resolves when done.
Example Usage
lia.db.delete
Purpose
Deletes rows matching a condition; deletes all rows if no condition.
Parameters
-
dbTable
(string): Table name withoutlia_
. -
condition
(string): SQL condition.
Realm
Server
Returns
- deferred: Resolves with deletion result.
Example Usage
lia.db.GetCharacterTable
Purpose
Fetches the column names of lia_characters
for debugging/maintenance.
Parameters
callback
(function): Receives the list of columns.
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.count
Purpose
Counts rows in a table, optionally filtered.
Parameters
-
dbTable
(string): Table name withoutlia_
. -
condition
(string): Optional SQL condition.
Realm
Server
Returns
- deferred: Resolves to the row count.
Example Usage
lia.db.addDatabaseFields
Purpose
Ensures every registered character variable has a matching column in the
lia_characters
table, adding any that are missing. It can be invoked
multiple times; existing columns will be ignored.
Parameters
- None
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.exists
Purpose
Checks if any row satisfies a condition.
Parameters
-
dbTable
(string): Table name withoutlia_
. -
condition
(string): SQL condition.
Realm
Server
Returns
- deferred: Resolves to
true
orfalse
.
Example Usage
lia.db.selectOne
Purpose
Fetches the first row that matches a condition.
Parameters
-
fields
(table | string): Columns. -
dbTable
(string): Table name withoutlia_
. -
condition
(string): SQL condition.
Realm
Server
Returns
- deferred: Resolves with the row or
nil
.
Example Usage
lia.db.selectOne("*", "characters", "id = 1"):next(function(row)
if row then
print("Found character:", row._name)
end
end)
lia.db.bulkInsert
Purpose
Inserts multiple rows in a single query.
Parameters
-
dbTable
(string): Table name withoutlia_
. -
rows
(table): Array of row tables.
Realm
Server
Returns
- deferred: Resolves when done.
Example Usage
lia.db.bulkInsert("items", {
{ _invID = 1, _uniqueID = "pistol", _x = 0, _y = 0, _quantity = 1 },
{ _invID = 1, _uniqueID = "ammo", _x = 1, _y = 0, _quantity = 30 },
})
lia.db.bulkUpsert
Purpose
Inserts multiple rows and updates them if they already exist.
Parameters
-
dbTable
(string): Table name withoutlia_
. -
rows
(table): Array of row tables.
Realm
Server
Returns
- deferred: Resolves when done.
Example Usage
lia.db.bulkUpsert("items", {
{ _invID = 1, _uniqueID = "pistol", _x = 0, _y = 0, _quantity = 1 },
{ _invID = 1, _uniqueID = "ammo", _x = 1, _y = 0, _quantity = 30 },
})
lia.db.insertOrIgnore
Purpose
Attempts to insert a row; silently ignores unique-key violation.
Parameters
-
value
(table): Row data. -
dbTable
(string): Table name withoutlia_
.
Realm
Server
Returns
- deferred: Resolves with results and last insert ID.
Example Usage
lia.db.insertOrIgnore({ id = 1, name = "Bob" }, "characters"):next(function(r)
print("Insert ID:", r.lastID)
end)
lia.db.transaction
Purpose
Runs multiple queries inside a transaction, rolling back if any fail.
Parameters
queries
(table): Array of SQL strings.
Realm
Server
Returns
- deferred: Resolves when transaction completes.
Example Usage
lia.db.transaction({
"INSERT INTO lia_logs (_message) VALUES ('start')",
"INSERT INTO lia_logs (_message) VALUES ('end')",
}):next(function()
print("Transaction complete")
end)
lia.db.escapeIdentifier
Purpose
Escapes an identifier (column/table name) for SQL.
Parameters
id
(string): Identifier.
Realm
Shared
Returns
- string: Escaped identifier.
Example Usage
lia.db.prepare
Purpose
Registers a prepared statement (MySQLOO only).
Parameters
-
key
(string): Statement identifier. -
query
(string): SQL with placeholders. -
types
(table): Array of MySQLOO type constants.
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.prepare(
"updateName",
"UPDATE lia_characters SET _name = ? WHERE _id = ?",
{ MYSQLOO_STRING, MYSQLOO_INTEGER }
)
lia.db.preparedCall
Purpose
Executes a prepared statement registered with lia.db.prepare
. (MySQLOO only)
Parameters
-
key
(string): Statement identifier. -
callback
(function): Receives results and last insert ID. -
… (variant): Placeholder arguments.
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.query
Purpose
Executes a raw SQL string. If no callback is provided, returns a deferred.
Parameters
-
query
(string): SQL query. -
callback
(function): Optional results callback.
Realm
Server
Returns
- deferred | nil: Deferred when no callback.
Example Usage
lia.db.escape
Purpose
Escapes a string for safe inclusion in SQL queries.
Parameters
value
(string): Raw string.
Realm
Shared
Returns
- string: Escaped string.
Example Usage
lia.db.queue
Purpose
Returns the number of queued SQL queries. (MySQLOO only)
Parameters
- None
Realm
Server
Returns
- number: Count of queued queries.
Example Usage
lia.db.abort
Purpose
Cancels all running queries on every connection. (MySQLOO only)
Parameters
- None
Realm
Server
Returns
- nil: This function does not return a value.
Example Usage
lia.db.getObject
Purpose
Returns the least busy database object and its pool index. (MySQLOO only)
Parameters
- None
Realm
Server
Returns
- database, number: Connection object and pool index.
Example Usage
lia.db.tableExists
Purpose
Checks whether a table with the provided name exists in the connected database.
Parameters
tbl
(string): Name of the table to check.
Realm
Server
Returns
- deferred: Resolves to
true
orfalse
.
Example Usage
lia.db.tableExists("characters"):next(function(exists)
print("Characters table present:", exists)
end)
lia.db.fieldExists
Purpose
Determines whether a specific column is present in a table.
Parameters
-
tbl
(string): Table name. -
field
(string): Column name to check.
Realm
Server
Returns
- deferred: Resolves to
true
when the field exists.
Example Usage
lia.db.fieldExists("characters", "name"):next(function(hasField)
print("Name column exists:", hasField)
end)