luafan

fan.mariadb

LuaFan’s MariaDB module provides a complete, asynchronous database access layer for MariaDB/MySQL databases. All operations are non-blocking and integrate seamlessly with LuaFan’s event-driven architecture.

Constants

mariadb.LONG_DATA

Used as placeholder for stmt:bind_param() when sending large data via stmt:send_long_data().

Connection Management

conn = mariadb.connect(database, username, password, host?, port?)

Establishes an asynchronous connection to MariaDB server.

Parameters:

• database (string): Database name to connect to
• username (string): Database username
• password (string): Database password
• host (string, optional): Server hostname, defaults to “localhost”
• port (integer, optional): Server port, defaults to 3306

Returns:

• Connection object on success
• nil and error message on failure

Example:

local mariadb = require("fan.mariadb")
local conn = mariadb.connect("mydb", "user", "password", "localhost", 3306)
if not conn then
    error("Failed to connect to database")
end

Connection Methods

conn:close()

Closes the database connection and frees all associated resources.

conn:ping()

Tests the connection to verify it’s still alive. Automatically reconnects if necessary.

Returns: true on success, false on failure

conn:escape(string)

Escapes a string for safe use in SQL queries to prevent injection attacks.

Parameters:

• string (string): String to escape

Returns: Escaped string safe for SQL queries

conn:execute(sql)

Executes a SQL statement and returns a cursor for result access.

Parameters:

• sql (string): SQL statement to execute

Returns: Cursor object for SELECT queries, true for other successful queries, nil on error

Features:

• Multi-result set support: Handles stored procedures returning multiple result sets
• Automatic type conversion: Numbers, strings, dates converted to appropriate Lua types
• NULL handling: Database NULL values become Lua nil

Example:

-- Simple query
local cursor = conn:execute("SELECT * FROM users WHERE active = 1")

-- INSERT/UPDATE queries
local success = conn:execute("INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')")

conn:setcharset(charset)

Sets the connection character set.

Parameters:

• charset (string): Character set name (e.g., “utf8mb4”, “latin1”)

Example:

conn:setcharset("utf8mb4")  -- Recommended for full Unicode support

conn:prepare(sql)

Creates a prepared statement for efficient repeated execution.

Parameters:

• sql (string): SQL statement with parameter placeholders (?)

Returns: PreparedStatement object

Example:

local stmt = conn:prepare("INSERT INTO users (name, email) VALUES (?, ?)")

conn:getlastautoid()

Returns the last auto-increment ID generated by this connection.

Returns: Integer ID or nil if no auto-increment was generated

Transaction Control

conn:autocommit(enabled)

Enables or disables auto-commit mode.

Parameters:

• enabled (boolean): true to enable auto-commit, false to disable

Example:

conn:autocommit(false)  -- Start manual transaction control

conn:commit()

Commits the current transaction. Only works with transactional storage engines (InnoDB).

conn:rollback()

Rolls back the current transaction. Only works with transactional storage engines (InnoDB).

Transaction Example:

conn:autocommit(false)
local success, err = pcall(function()
    conn:execute("INSERT INTO users (name) VALUES ('Alice')")
    conn:execute("INSERT INTO orders (user_id, amount) VALUES (1, 100)")
    conn:commit()
end)

if not success then
    conn:rollback()
    error("Transaction failed: " .. err)
end
conn:autocommit(true)  -- Re-enable auto-commit

PreparedStatement

Prepared statements provide efficient execution of repeated queries with different parameters. They offer better performance and automatic SQL injection protection.

stmt:close()

Closes the prepared statement and frees associated resources.

stmt:bind_param(...)

Binds parameters to the prepared statement placeholders.

Parameters:

• ... (mixed): Values to bind to statement parameters in order

Supported parameter types:

• Numbers (integer, float)
• Strings and binary data
• Boolean values (true/false)
• nil (becomes SQL NULL)
• mariadb.LONG_DATA (placeholder for large data)

Example:

local stmt = conn:prepare("INSERT INTO users (name, email, age, active) VALUES (?, ?, ?, ?)")
stmt:bind_param("Alice", "[email protected]", 30, true)
local result = stmt:execute()

stmt:send_long_data(index, data)

Sends large data for a parameter marked with mariadb.LONG_DATA.

Parameters:

• index (integer): Parameter index (0-based)
• data (string): Data chunk to send

Example:

local stmt = conn:prepare("INSERT INTO files (name, content) VALUES (?, ?)")
stmt:bind_param("document.pdf", mariadb.LONG_DATA)
stmt:send_long_data(1, large_file_content)  -- Send large data to parameter 1
local result = stmt:execute()

stmt:execute()

Executes the prepared statement with bound parameters.

Returns:

• For SELECT: Cursor object for result access
• For INSERT/UPDATE/DELETE: true on success
• nil on error

stmt:fetch()

For prepared statements that return results, fetches the next row.

Returns:

• true, field1, field2, ... on success
• false when no more rows
• nil on error

Example:

local stmt = conn:prepare("SELECT name, email FROM users WHERE age > ?")
stmt:bind_param(25)
local result = stmt:execute()

if result then
    while true do
        local success, name, email = stmt:fetch()
        if not success then break end
        print(string.format("User: %s <%s>", name, email))
    end
end
stmt:close()

Cursor

Cursors provide access to query results with metadata information and efficient row iteration.

cursor:close()

Closes the cursor and frees associated resources. Always call this when done with results.

cursor:getcolnames()

Returns column names from the result set.

Returns: Array of column name strings

Example:

local cursor = conn:execute("SELECT name, email, age FROM users")
local columns = cursor:getcolnames()
-- columns = {"name", "email", "age"}

cursor:getcoltypes()

Returns column type information from the result set.

Returns: Array of MySQL column type constants

Example:

local cursor = conn:execute("SELECT name, email, age FROM users")
local types = cursor:getcoltypes()
-- types might be {MYSQL_TYPE_VAR_STRING, MYSQL_TYPE_VAR_STRING, MYSQL_TYPE_LONG}

cursor:fetch()

Retrieves the next row from the result set.

Returns:

• Table with column-name keys and their values
• nil when no more rows available

Example:

local cursor = conn:execute("SELECT name, email, age FROM users")
while true do
    local row = cursor:fetch()
    if not row then break end
    print(string.format("%s <%s> is %d years old", row.name, row.email, row.age))
end
cursor:close()

cursor:numrows()

Returns the number of rows in the result set.

Returns: Integer count of rows

Note: Only works reliably with mysql_store_result(). For large result sets, consider using cursor:fetch() in a loop instead.

Data Type Conversion

LuaFan automatically converts MySQL data types to appropriate Lua types:

MySQL Type	Lua Type	Notes
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT	number (integer)	Automatically converted to Lua integers
FLOAT, DOUBLE, DECIMAL	number (float)	Converted to Lua numbers
VARCHAR, TEXT, BLOB	string	Binary data preserved
DATE, TIME, DATETIME, TIMESTAMP	table	Structured time representation
NULL	nil	Database NULL becomes Lua nil

Date/Time Format

Date and time values are returned as Lua tables:

{
    year = 2023,
    month = 12,
    day = 25,
    hour = 14,
    minute = 30,
    second = 45,
    second_part = 123456  -- microseconds
}

Error Handling

All MariaDB operations can fail. Always check return values:

local conn = mariadb.connect("db", "user", "pass", "host")
if not conn then
    error("Connection failed")
end

local cursor = conn:execute("SELECT * FROM users")
if not cursor then
    error("Query failed")
end

-- Process results
while true do
    local row = cursor:fetch()
    if not row then break end
    -- Use row data
end

cursor:close()
conn:close()

Best Practices

1. Always close resources: Call close() on connections, cursors, and prepared statements
2. Use prepared statements: For repeated queries or user input to prevent SQL injection
3. Handle errors: Check return values and handle failures gracefully
4. Use transactions: For multi-statement operations that need atomicity
5. Set charset: Use “utf8mb4” for full Unicode support
6. Connection pooling: Use mariadb.pool for high-concurrency applications

This site is open source. Improve this page.