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.connect(function()
    print("Database connected")
end)

---

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.wipeTables(function()
    print("Tables wiped")
end)

---

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

---

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.waitForTablesToLoad():next(function()
    print("Tables loaded")
end)

---

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 when true.

Realm

Shared

Returns

• string | number: SQL-ready representation.

Example Usage

local str = lia.db.convertDataType({ name = "Lilia" })

---

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 the lia_ prefix.

Realm

Server

Returns

• nil: This function does not return a value.

Example Usage

lia.db.insertTable({ name = "Test" }, function(id)
    print("Inserted", id)
end, "characters")

---

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 without lia_.

• condition (string): SQL WHERE 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 without lia_.

• 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.select("*", "characters", "id = 1"):next(function(rows)
    PrintTable(rows)
end)

---

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 without lia_.

Realm

Server

Returns

• deferred: Resolves when done.

Example Usage

lia.db.upsert({ id = 1, name = "John" }, "characters")

---

lia.db.delete

Purpose

Deletes rows matching a condition; deletes all rows if no condition.

Parameters

• dbTable (string): Table name without lia_.

• condition (string): SQL condition.

Realm

Server

Returns

• deferred: Resolves with deletion result.

Example Usage

lia.db.delete("characters", "id = 1"):next(function()
    print("Row deleted")
end)

---

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.GetCharacterTable(function(cols)
    PrintTable(cols)
end)

---

lia.db.count

Purpose

Counts rows in a table, optionally filtered.

Parameters

• dbTable (string): Table name without lia_.

• condition (string): Optional SQL condition.

Realm

Server

Returns

• deferred: Resolves to the row count.

Example Usage

lia.db.count("characters", "faction = 1"):next(function(n)
    print("Character count:", n)
end)

---

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

-- After creating custom character variables
lia.db.addDatabaseFields()

---

lia.db.exists

Purpose

Checks if any row satisfies a condition.

Parameters

• dbTable (string): Table name without lia_.

• condition (string): SQL condition.

Realm

Server

Returns

• deferred: Resolves to true or false.

Example Usage

lia.db.exists("characters", "id = 5"):next(function(found)
    print("Character exists:", found)
end)

---

lia.db.selectOne

Purpose

Fetches the first row that matches a condition.

Parameters

• fields (table | string): Columns.

• dbTable (string): Table name without lia_.

• 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 without lia_.

• 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 without lia_.

• 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 without lia_.

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

local col = lia.db.escapeIdentifier("desc")
print(col)

---

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.preparedCall("updateName", nil, "Alice", 1)

---

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.query("SELECT 1"):next(function(res)
    PrintTable(res.results)
end)

---

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

local safe = lia.db.escape(userInput)

---

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

print("Queue size:", lia.db.queue())

---

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

---

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

local db = lia.db.getObject()

---

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 or false.

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)

---