Lua Database Access

Lua - Database Access

While Lua can use plain files for data storage, for more complex, scalable, and efficient data operations, databases are essential. LuaSQL is a common library that provides a consistent interface from Lua to various database management systems (DBMS). Other database-specific drivers also exist, often installable via LuaRocks.

LuaSQL supports several backends, including:

• SQLite
• MySQL / MariaDB
• PostgreSQL
• ODBC (for connecting to SQL Server, Access, etc.)
• Oracle

This tutorial will focus on SQLite and MySQL as examples, demonstrating the general LuaSQL workflow. The principles can be adapted for other supported databases.

General LuaSQL Workflow

1. Load the Driver: Use require to load the specific LuaSQL driver (e.g., luasql.sqlite3 or luasql.mysql).
2. Create an Environment Object: Call the driver’s main function (e.g., sqlite3.sqlite3()) to get an environment object.
3. Connect to the Database: Use the environment object’s connect method to establish a connection. Connection parameters vary by database.
4. Execute SQL Statements: Use the connection object’s execute method to run SQL queries (CREATE, INSERT, UPDATE, DELETE, SELECT).
5. Process Results (for SELECT): If execute returns a cursor object (for SELECT queries), use its fetch method to retrieve rows.
6. Close Resources: Close cursors, connections, and environment objects when done to free resources.

Error Handling

Most LuaSQL functions return nil and an error message on failure, or specific objects (like connection, cursor) on success. conn:execute() might return 0 or a number of affected rows for non-SELECT queries, or a cursor for SELECTs. Always check return values for errors.

SQLite Example

SQLite is a lightweight, file-based database, excellent for embedded applications and simple projects.

1. Setup and Connection:

local sqlite3 = require "luasql.sqlite3"

-- Create an environment object
local env, err_env = sqlite3.sqlite3()
if not env then
  error("Failed to create SQLite3 environment: " .. (err_env or "unknown error"))
end

-- Connect to the database (creates mydb.sqlite if it doesn't exist)
local conn, err_conn = env:connect('mydb.sqlite')
if not conn then
  env:close() -- Close environment if connection failed
  error("Failed to connect to SQLite database: " .. (err_conn or "unknown error"))
end

print("Successfully connected to SQLite database 'mydb.sqlite'")

2. Create Table:

local sql_create_table = [[
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  );
]]

local ok, err_create = conn:execute(sql_create_table)
if not ok then
  print("Error creating table: " .. (err_create or "unknown error"))
else
  print("Table 'users' ensured or created.")
end

3. Insert Data:

-- WARNING: For production, always sanitize inputs or use parameterized queries if supported by the driver
-- to prevent SQL injection. LuaSQL itself is basic; consider ORMs for more safety.
local function insert_user(name, email)
  -- Basic quoting for this example, NOT secure for untrusted input
  local safe_name = string.gsub(name, "'", "''")
  local safe_email = string.gsub(email, "'", "''")
  local sql_insert = string.format("INSERT INTO users (name, email) VALUES ('%s', '%s')", safe_name, safe_email)

  local affected_rows, err_insert = conn:execute(sql_insert)
  if not affected_rows then -- For INSERT, execute returns number of affected rows or nil on error
    print(string.format("Error inserting user %s: %s", name, (err_insert or "unknown error")))
  else
    print(string.format("User %s inserted. Affected rows: %d", name, affected_rows))
  end
end

insert_user("Alice Wonderland", "alice@example.com")
insert_user("Bob The Builder", "bob@example.com")

4. Select Data:

local sql_select = "SELECT id, name, email FROM users WHERE name LIKE '%Alice%';"
local cursor, err_select = conn:execute(sql_select)

if not cursor then
  print("Error selecting users: " .. (err_select or "unknown error"))
else
  print("\nUsers found:")
  -- Fetch rows one by one. fetch({}, 'a') populates a new table with field names as keys.
  local row = cursor:fetch({}, "a")
  while row do
    print(string.format("  ID: %d, Name: %s, Email: %s", row.id, row.name, row.email))
    row = cursor:fetch(row, "a") -- Reuse the table for efficiency
  end
  cursor:close() -- Always close the cursor
end

5. Update and Delete Data:

local sql_update = "UPDATE users SET email = 'alice.wonder@example.com' WHERE name = 'Alice Wonderland';"
local affected_update, err_update = conn:execute(sql_update)
if affected_update then
  print(string.format("\nAlice's email updated. Affected rows: %d", affected_update))
else
  print("Error updating Alice's email: ", err_update or "unknown error")
end

local sql_delete = "DELETE FROM users WHERE name = 'Bob The Builder';"
local affected_delete, err_delete = conn:execute(sql_delete)
if affected_delete then
  print(string.format("Bob deleted. Affected rows: %d", affected_delete))
else
  print("Error deleting Bob: ", err_delete or "unknown error")
end

6. Close Connection and Environment:

conn:close()
env:close()
print("\nSQLite connection closed.")

-- Ensure you add appropriate error checks around each conn:execute() call in real applications.

MySQL / MariaDB Example

MySQL is a popular client-server relational database. MariaDB is a community-developed fork of MySQL and is largely compatible.

1. Setup and Connection:

local mysql = require "luasql.mysql"

local env, err_env = mysql.mysql()
if not env then
  error("Failed to create MySQL environment: " .. (err_env or "unknown error"))
end

-- Connection parameters: database_name, user, password, host (optional), port (optional)
-- Replace with your actual MySQL credentials and database details.
-- It's highly recommended to use environment variables or a config file for credentials.
local db_name = os.getenv("LUA_DB_NAME") or "test_lua_db"
local db_user = os.getenv("LUA_DB_USER") or "lua_user"
local db_pass = os.getenv("LUA_DB_PASS") or "password"
local db_host = os.getenv("LUA_DB_HOST") or "127.0.0.1"
local db_port = tonumber(os.getenv("LUA_DB_PORT")) -- or nil for default

local conn, err_conn = env:connect(db_name, db_user, db_pass, db_host, db_port)
if not conn then
  env:close()
  error(string.format("Failed to connect to MySQL (Host: %s, DB: %s, User: %s): %s",
    db_host, db_name, db_user, (err_conn or "unknown error")))
end

print("Successfully connected to MySQL database '" .. db_name .. "'")

-- The DDL, DML (CREATE, INSERT, SELECT etc.) operations are very similar to SQLite example above.
-- Key differences might be in SQL syntax details (e.g., AUTO_INCREMENT vs AUTOINCREMENT).

-- Example: Create table for MySQL
local sql_create_mysql_table = [[
  CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2)
  );
]]
local ok_mysql_create, err_mysql_create = conn:execute(sql_create_mysql_table)
if not ok_mysql_create then
  print("Error creating MySQL table: " .. (err_mysql_create or "unknown error"))
else
  print("Table 'products' in MySQL ensured or created.")
end

-- Remember to close connection and environment for MySQL as well
-- conn:close()
-- env:close()
-- print("\nMySQL connection closed.")

-- For brevity, further INSERT, SELECT operations follow the same pattern as SQLite
-- just with MySQL-specific SQL syntax if needed.

Transactions

Transactions ensure data consistency by grouping multiple SQL statements into a single unit of work. They follow ACID properties (Atomicity, Consistency, Isolation, Durability).

• conn:execute('START TRANSACTION;') or conn:execute('BEGIN;'): Begins a transaction.
• conn:execute('COMMIT;'): Saves all changes made during the transaction.
• conn:execute('ROLLBACK;'): Undoes all changes made during the transaction.

Many drivers also offer conn:commit() and conn:rollback() methods, and conn:setautocommit(false) to start implicit transaction mode.

-- Assuming 'conn' is an active LuaSQL connection (e.g., to SQLite or MySQL)

-- Example transaction (conceptual - ensure table 'accounts' exists)
-- CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL);
-- INSERT INTO accounts (id, name, balance) VALUES (1, 'Alice', 100.0);
-- INSERT INTO accounts (id, name, balance) VALUES (2, 'Bob', 50.0);

local ok_begin, err_begin = conn:execute('BEGIN TRANSACTION') -- Or START TRANSACTION
if not ok_begin then
  print("Error starting transaction: ", err_begin)
  -- Handle error, maybe close connection
else
  print("Transaction started.")
  local transfer_amount = 10.0
  local ok_debit, err_debit = conn:execute(string.format("UPDATE accounts SET balance = balance - %f WHERE id = 1", transfer_amount))
  local ok_credit, err_credit = conn:execute(string.format("UPDATE accounts SET balance = balance + %f WHERE id = 2", transfer_amount))

  if ok_debit and ok_credit then
    local ok_commit, err_commit = conn:execute('COMMIT')
    if ok_commit then
      print("Transaction committed successfully.")
    else
      print("Error committing transaction: ", err_commit)
      -- Attempt rollback on commit failure
      local ok_rb_on_commit_fail, err_rb_on_commit_fail = conn:execute('ROLLBACK')
      print("Rollback on commit failure status: ", ok_rb_on_commit_fail, err_rb_on_commit_fail or "")
    end
  else
    print("Error during transaction (debit/credit failed). Rolling back...")
    print("Debit error: ", err_debit or "N/A")
    print("Credit error: ", err_credit or "N/A")
    local ok_rollback, err_rollback = conn:execute('ROLLBACK')
    if ok_rollback then
      print("Transaction rolled back.")
    else
      print("Error rolling back transaction: ", err_rollback)
    end
  end
end

-- Don't forget to close your main connection and environment when fully done.
-- conn:close()
-- env:close()

Security Note on SQL Injection: The examples above use string formatting to build SQL queries. This is highly vulnerable to SQL injection if input values come from untrusted sources (like user input). LuaSQL itself does not provide built-in prepared statements or parameter binding in a standardized way across all its drivers. For production applications, you MUST sanitize all inputs rigorously or use a Lua ORM (Object-Relational Mapper) or query builder library that handles prepared statements or proper escaping. For simplicity, this tutorial shows direct execution, but be aware of the risks.

Further Learning: Explore LuaRocks for other database drivers (e.g., pgmoon for PostgreSQL, specific drivers for NoSQL databases like Redis or MongoDB). For complex applications, consider using a Lua ORM like Lapis ORM (if using Lapis framework) or lua-resty-orm (for OpenResty).