Mod_lua implements a simple database feature for querying and running commands on the most popular database engines (mySQL, PostgreSQL, FreeTDS, ODBC, SQLite, Oracle) as well as mod_dbd.

The example below shows how to acquire a database handle and return information from a table:

function handle(r)
 -- Acquire a database handle
 local database, err = r:dbacquire("mysql", "server=localhost,user=someuser,pass=somepass,dbname=mydb")
 if not err then
 -- Select some information from it
 local results, err = database:select(r, "SELECT `name`, `age` FROM `people` WHERE 1")
 if not err then
 local rows = results(0) -- fetch all rows synchronously
 for k, row in pairs(rows) do
 r:puts( string.format("Name: %s, Age: %s<br/>", row[1], row[2]) )
 end
 else
 r:puts("Database query error: " .. err)
 end
 database:close()
 else
 r:puts("Could not connect to the database: " .. err)
 end
end

To utilize mod_dbd , specify mod_dbd as the database type, or leave the field blank:

local database = r:dbacquire("mod_dbd")

Database object and contained functions

The database object returned by dbacquire has the following methods:

Normal select and query from a database:

-- Run a statement and return the number of rows affected:
local affected, errmsg = database:query(r, "DELETE FROM `tbl` WHERE 1")
-- Run a statement and return a result set that can be used synchronously or async:
local result, errmsg = database:select(r, "SELECT * FROM `people` WHERE 1")

Using prepared statements (recommended):

-- Create and run a prepared statement:
local statement, errmsg = database:prepare(r, "DELETE FROM `tbl` WHERE `age` > %u")
if not errmsg then
 local result, errmsg = statement:query(20) -- run the statement with age > 20
end
-- Fetch a prepared statement from a DBDPrepareSQL directive:
local statement, errmsg = database:prepared(r, "someTag")
if not errmsg then
 local result, errmsg = statement:select("John Doe", 123) -- inject the values "John Doe" and 123 into the statement
end

Escaping values, closing databases etc:

-- Escape a value for use in a statement:
local escaped = database:escape(r, [["'|blabla]])
-- Close a database connection and free up handles:
database:close()
-- Check whether a database connection is up and running:
local connected = database:active()

Working with result sets

The result set returned by db:select or by the prepared statement functions created through db:prepare can be used to fetch rows synchronously or asynchronously, depending on the row number specified:
result(0) fetches all rows in a synchronous manner, returning a table of rows.
result(-1) fetches the next available row in the set, asynchronously.
result(N) fetches row number N , asynchronously:

-- fetch a result set using a regular query:
local result, err = db:select(r, "SELECT * FROM `tbl` WHERE 1")
local rows = result(0) -- Fetch ALL rows synchronously
local row = result(-1) -- Fetch the next available row, asynchronously
local row = result(1234) -- Fetch row number 1234, asynchronously
local row = result(-1, true) -- Fetch the next available row, using row names as key indexes.

One can construct a function that returns an iterative function to iterate over all rows in a synchronous or asynchronous way, depending on the async argument:

function rows(resultset, async)
 local a = 0
 local function getnext()
 a = a + 1
 local row = resultset(-1)
 return row and a or nil, row
 end
 if not async then
 return pairs(resultset(0))
 else
 return getnext, self
 end
end
local statement, err = db:prepare(r, "SELECT * FROM `tbl` WHERE `age` > %u")
if not err then
 -- fetch rows asynchronously:
 local result, err = statement:select(20)
 if not err then
 for index, row in rows(result, true) do
 ....
 end
 end
 -- fetch rows synchronously:
 local result, err = statement:select(20)
 if not err then
 for index, row in rows(result, false) do
 ....
 end
 end
end

Closing a database connection

Database handles should be closed using database:close() when they are no longer needed. If you do not close them manually, they will eventually be garbage collected and closed by mod_lua, but you may end up having too many unused connections to the database if you leave the closing up to mod_lua. Essentially, the following two measures are the same:

-- Method 1: Manually close a handle
local database = r:dbacquire("mod_dbd")
database:close() -- All done
-- Method 2: Letting the garbage collector close it
local database = r:dbacquire("mod_dbd")
database = nil -- throw away the reference
collectgarbage() -- close the handle via GC

Precautions when working with databases

Although the standard query and run functions are freely available, it is recommended that you use prepared statements whenever possible, to both optimize performance (if your db handle lives on for a long time) and to minimize the risk of SQL injection attacks. run and query should only be used when there are no variables inserted into a statement (a static statement). When using dynamic statements, use db:prepare or db:prepared .