Introduction

This BAS documentation is derived from the original Kepler LuaSQL documentation, but this page documents the SQLite3 driver included with the Barracuda App Server (BAS), Mako Server, and Xedge runtimes.

The original LuaSQL project defines a simple object-oriented API for database drivers. BAS uses this API for SQLite and adds SQLite-specific extensions, resource-management improvements, and bug fixes intended for long-running server programs.

Applications should still close cursors, BLOB objects, connections, and environments explicitly. The BAS driver attempts to clean up dependent objects where possible, but deterministic cleanup is the recommended pattern for persistent services, LSP applications, and embedded systems.

SQLite is the integrated database module for embedded and local-file use. Mako Server can also connect to external databases such as MySQL, Redis, MongoDB, and PostgreSQL. See the online Mako Server Database Drivers page for driver choices, tutorials, and API references.

LuaSQL defines one single global variable, a table called luasql. This table is used to store the initialization methods of the loaded drivers.

The initialization methods are used to create an SQL environment object which is used to create a connection object. A connection object can execute SQL statements and eventually create a cursor object which is used to retrieve data.

Note: Table luasql also includes function quotestr. This function is the same as env.quotestr.

Recommended reading:

• The Mako Server's introductory SQLite tutorial, which uses the simplified sqlutil module.
• Lua-SQLite and LSP Considerations is important when using SQLite from LSP pages, REST handlers, timers, or other request callbacks. It explains how SQLite BUSY errors can occur when several write-capable connections are open and how to structure LSP applications so write access is coordinated without sharing one connection across request threads.
• The BAS SQLite Dedicated Writer Skill provides additional SQLite design patterns for both humans and AI assistants. Use it when designing production BAS, Mako Server, Xedge, or LSP applications that write to SQLite, especially long-running services or applications with concurrent write paths.

Error handling

LuaSQL is just an abstraction layer that communicates between Lua and a database system. Therefore errors can occur on both levels, that is, inside the database client or inside LuaSQL driver.

Errors such as malformed SQL statements, unknown table names etc. are called database errors and normally return nil, error, detail: error is the SQLite error-name string, such as "BUSY", and detail is its explanatory message string. Individual entries below describe exceptions to this return shape. Errors such as wrong parameters, absent connection, invalid objects etc., called API errors, are usually program errors and so will raise a Lua error.

This behavior will be followed by all functions/methods described in this document unless otherwise stated.

SQL Environment Objects

An environment object is created by calling the driver's initialization function that is stored in the luasql table, indexed with the same name as the driver. For SQLite, use:

env = luasql.sqlite()

luasql.sqlite()

Creates an environment for opening SQLite connections. This does not open a database.

Parameters

None.

Return values

• environment userdata environment - New, open environment object.

Throws

Lua allocation errors can propagate. This function has no explicit argument checks or database-error return.

local io = A Barracuda I/O created by C code and returned by ba.openio
           or a Barracuda I/O created ba.mkio

-- Converts a name to the absolute path + name
local function datasource(name)
   return io:realpath(name) -- I/O from above
end

-- Open database helper function
local function opendb(name, mode)
   local conn
   local env,err = luasql.sqlite()
   if env then
      conn, err = env:connect(datasource(name), mode == "r" and "READONLY" or "")
      if conn then
         return env,conn
      end
      env:close()
   end
   trace("Opening db failed",name,err)
   return nil,err
end


-- Close database helper function
local function closedb(env,conn)
   if env then
      if conn then
         conn:close()
      end
      env:close()
   end
end


local env,conn = opendb"mydb.sqlite"
if env then
   .
   .
end
closedb(env,conn)

Methods

env:close()

Marks the environment closed so it cannot create new connections. Existing connections, cursors and BLOBs remain open; close those objects separately.

Parameters

None.

Return values

• boolean ok - True on first close; false if already closed.

Throws

Throws for an invalid environment object. Repeated close does not throw.

env:connect(sourcename[,options])

Opens a SQLite database connection.

Parameters

• string sourcename - Database filename. The special name ":memory:" creates a private in-memory database.
• string (optional) options - "READONLY" opens an existing database read-only; "NOCREATE" opens an existing database for read/write. Omitted, nil or an empty string allows read/write and creation.

Return values

• connection userdata or nil connection - New connection on success, with auto-commit enabled and an initial busy timeout of 500 milliseconds; nil on reported open failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed environment, invalid argument types, or an unknown options string. Lua allocation errors can propagate. Reported database-open failures return nil, error, detail.

SQLite extensions

The following environment methods are SQLite-specific.

env.version()

Returns the version of the SQLite library in this build.

Parameters

None.

Return values

• string version - SQLite version string.

Throws

Lua allocation errors can propagate.

env.memory()

Reports SQLite allocator usage across the process.

Parameters

None.

Return values

• integer current - Bytes currently allocated by SQLite.
• integer peak - Highest recorded allocation in bytes. Reading this value does not reset it.

Throws

Does not explicitly throw or validate the environment object.

env.quotestr(value)

Returns an SQL string literal with surrounding single quotes and each embedded single quote doubled. The same function is available as luasql.quotestr. Call it with dot syntax.

Parameters

• string value - Text to quote. Embedded zero bytes terminate the input; use prepared TEXT or BLOB parameters to preserve them.

Return values

• string or nil quoted - Quoted SQL literal, or nil if SQLite cannot allocate it.

Throws

Throws if value cannot be read as a string. Lua allocation errors can propagate.

-- Quote apostrophes in text used as an SQL value.
local quoted = env.quotestr("It's a happy day!")
-- quoted is: 'It''s a happy day!'

Connection Objects

A connection object contains specific attributes and parameters of a single data source connection. A connection object is created by calling the environment:connect method.

SQLite specific

The methods setautocommit, commit, and rollback on the connection object take an optional "transaction type" argument. The transaction type is set to DEFERRED if not specified.

SQLite has three different transaction types that start transactions in different locking states. Transactions can be started as DEFERRED, IMMEDIATE, or EXCLUSIVE. A transaction's type is specified in the BEGIN command:

A deferred transaction does not acquire any locks until it has to. Thus with a deferred transaction, the BEGIN statement itself does nothing - it starts in the unlocked state. This is the default. If you simply issue a BEGIN, then your transaction is DEFERRED, and therefore sitting in the unlocked state.

Why do you need to know this? Because if you don't know what you are doing, you can end up in a deadlock. If you disable auto commit and are using a database that other connections are also writing to, both you and they should use BEGIN IMMEDIATE or BEGIN EXCLUSIVE to initiate transactions. See the SQLite BEGIN TRANSACTION command for more information.

Example:

local env = luasql.sqlite()
local conn = env:connect(myDbName)
conn:setbusytimeout(1000) -- Wait max 1 sec on BUSY.
local ok,err=conn:setautocommit(false, "IMMEDIATE")
if ok then
   .
   .
   conn:commit() -- Commit and switch to "DEFERRED" mode
elseif err == "BUSY" then
  print"DB busy"
end

Methods

conn:close()

Closes the connection and its open cursors. Close associated BLOB objects yourself before calling this method.

Parameters

None, apart from the connection object.

Return values

• boolean or nil ok - True when closed; false if already closed; nil if a cursor fails to close or SQLite refuses connection closure.
• string (on failure) error - SQLite error name, such as "BUSY" when a BLOB is still open.
• string (on failure) detail - SQLite's description of the failure.

After a reported failure, the connection remains open and usable. Cursor cleanup stops at the first failure. Cursors already closed by this call remain closed; a failed cursor is also closed. If its write was rolled back, repeating close does not recover that write. Handle the error and repeat the database operation if appropriate, then retry connection close. For a BUSY error caused by an open BLOB, close that BLOB before retrying.

Throws

Throws for an invalid connection object or if a cursor remains open after cursor cleanup. Error-result allocation can also throw. A reported SQLite close failure returns nil, error, detail.

Garbage collection uses deferred native cleanup: if the connection is collected before its BLOBs or statements, SQLite releases the native connection after those objects close or are collected. A forgotten explicit close therefore does not prevent eventual cleanup. Prefer explicit closure when resources are no longer needed.

conn:commit([transactiontype])

Commits the current transaction, then starts a new transaction. The connection therefore remains in a transaction after success.

Parameters

• string (optional) transactiontype - Type of the next transaction: "DEFERRED" (default), "IMMEDIATE" or "EXCLUSIVE". Used when auto-commit has been disabled with setautocommit(false); otherwise the next transaction uses DEFERRED.

Return values

• boolean or nil ok - True on success; nil on reported failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

The two SQL operations run in sequence. If starting the next transaction fails, the preceding commit may already have succeeded. Calling this method without an active transaction returns a SQLite error.

Throws

Throws for an invalid or closed connection, or a transactiontype that cannot be read as a string when that argument is used. Invalid SQL transaction types return nil, error, detail. Lua allocation errors can propagate.

conn:execute(statement)

Executes the first SQL statement in the supplied string. A statement with result columns returns a cursor, including INSERT, UPDATE and DELETE statements with RETURNING. The first row obtained during execution is retained for fetch(); fetching does not execute the statement again.

Parameters

• string statement - SQL statement. Additional statements after the first are not executed by this method.

Return values

• cursor userdata, integer or nil result - Cursor when there are result columns, even for an empty result set; otherwise SQLite's changed-row count. Nil on reported failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed connection, or a statement that cannot be read as a string. Empty SQL, including whitespace or comments without a statement, throws "SQL statement expected". Lua allocation errors can propagate. Reported SQL preparation or execution failures return nil, error, detail. Errors encountered in later rows are returned by the cursor's fetch() method.

conn:rollback([transactiontype])

Rolls back the current transaction, then starts a new transaction. The connection therefore remains in a transaction after success.

Parameters

• string (optional) transactiontype - Type of the next transaction: "DEFERRED" (default), "IMMEDIATE" or "EXCLUSIVE". Used when auto-commit has been disabled with setautocommit(false); otherwise the next transaction uses DEFERRED.

Return values

• boolean or nil ok - True on success; nil on reported failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

The two SQL operations run in sequence. If starting the next transaction fails, the preceding rollback may already have succeeded. Calling this method without an active transaction returns a SQLite error.

Throws

Throws for an invalid or closed connection, or a transactiontype that cannot be read as a string when that argument is used. Invalid SQL transaction types return nil, error, detail. Lua allocation errors can propagate.

conn:setautocommit([enabled[,transactiontype]])

Enables auto-commit, or starts a transaction with auto-commit disabled.

Enabling auto-commit discards pending changes: conn:setautocommit(true) executes ROLLBACK. It does not commit the current transaction. To keep your changes, successfully call conn:commit() first, then call conn:setautocommit(true) to end the new transaction started by commit().

Parameters

• boolean (optional) enabled - True enables auto-commit and rolls back the current transaction. False, omitted or nil starts a transaction with auto-commit disabled.
• string (optional) transactiontype - "DEFERRED" (default), "IMMEDIATE" or "EXCLUSIVE". Applies only when disabling auto-commit. Pass false as the first argument to select a type, for example setautocommit(false, "IMMEDIATE").

Return values

• boolean or nil ok - True on success; nil on reported failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Enabling auto-commit without an active transaction returns a SQLite error because there is nothing to roll back. Disabling it while a transaction is already active also returns a SQLite error. A reported failure does not confirm that the requested transaction change took place.

Throws

Throws for an invalid or closed connection, or a transactiontype that cannot be read as a string when disabling auto-commit. Invalid SQL transaction types return nil, error, detail. Lua allocation errors can propagate.

SQLite extensions

The following connection methods are SQLite-specific.

conn:lastid()

Returns the connection's last inserted row ID.

Parameters

None.

Return values

• integer rowid - Exact SQLite last-insert-rowid value. Zero if no successful row-ID insert has occurred on this connection.

Throws

Throws for an invalid or closed connection.

conn:setbusytimeout(millisecs)

Sets how long SQLite may wait for a database lock before returning BUSY. New connections start with a 500-millisecond timeout. See SQLite busy timeout.

Parameters

• number millisecs - Finite timeout in milliseconds, from zero through INT_MAX (2147483647 on supported 32-bit-int builds). Fractional milliseconds are discarded. Zero disables waiting. The value must be representable within this range by the Lua build's number type.

Return values

None.

Throws

Throws for an invalid or closed connection, a value that cannot be read as a number, or a negative, non-finite or out-of-range timeout.

conn:tables()

Lists tables in the main database using sqlite_master, sorted by name.

Parameters

None.

Return values

• table or nil names - Complete array of table-name strings, or nil if the underlying query fails. An empty database returns an empty table.
• string (on failure) error - SQLite error name when preparing, executing or finishing the query fails.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed connection. Lua allocation errors can propagate. Reported query failures return nil, error, detail; a partial list is not returned.

conn:prepare(statement)

Prepares the first SQL statement without executing it. The normal sequence is prepare, bind when needed, then execute.

Parameters

• string statement - SQL to prepare. See cur:bind() for parameter placeholders.

Return values

• cursor userdata or nil cursor - Prepared statement, or nil on reported preparation failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed connection, or a statement that cannot be read as a string. Empty SQL, including whitespace or comments without a statement, throws "SQL statement expected". Lua allocation errors can propagate. Reported SQL preparation failures return nil, error, detail.

Lifetime: Execute the cursor before fetching. For a statement with result rows, fetching through the end releases its prepared statement. To run that query again, close the old cursor and call prepare() again. A prepared statement without result columns can be executed repeatedly until closed. This distinction also applies to statements with RETURNING.

conn:openblob(table,column,rowid[,update])

Opens a handle for incremental access to an existing BLOB or TEXT value.

Parameters

• string table - Table name in the main database.
• string column - Column containing the BLOB.
• integer rowid - SQLite row ID identifying the row. Must fit in a Lua integer in this build.
• boolean (optional) update - True permits writes; false or omitted opens a read-only handle.

Return values

• BLOB userdata or nil blob - New handle on success; nil on reported open failure, including a missing row or unsupported column value.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed connection, table or column that cannot be read as a string, or rowid that cannot be read as an integer. Lua allocation errors can propagate. SQLite open failures return nil, error, detail.

conn:zeroblob(table,column,rowid,size)

Replaces the selected column value with a BLOB containing size zero bytes. This discards its previous contents; reopen any existing handle after changing the value.

Parameters

• string table - Table name in the main database.
• string column - Column containing the BLOB.
• integer rowid - SQLite row ID identifying the row. Must fit in a Lua integer in this build.
• integer size - New BLOB size in bytes, from zero through INT_MAX and within the SQLite BLOB-size limit for this build.

Return values

• integer or nil size - Requested BLOB size on success; nil on reported SQL failure. This is not a changed-row count: a missing row can leave the database unchanged while still returning size.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed connection, table or column that cannot be read as a string, or rowid or size that cannot be read as an integer. A negative size or one above INT_MAX after integer conversion also throws. Lua allocation errors can propagate. Reported SQLite failures return nil, error, detail.

Cursor Objects

A cursor object contains methods to retrieve data resulting from an executed statement. A cursor object is created by using the connection:execute function or connection:prepare function.

Methods

cur:close()

Closes the cursor, releases its statement and drops its reference to the connection. Closing an unfinished write statement, such as INSERT with RETURNING, can commit its write in auto-commit mode. A failed commit can roll back that write.

Parameters

None.

Return values

• boolean or nil ok - True on successful close; false if already closed; nil if SQLite reports a statement-finalization error.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

The cursor is closed even when an error is returned. A subsequent close returns false and cannot recover a rolled-back write. The connection remains available so the application can handle the failure.

Throws

Throws for an invalid cursor object. Lua allocation errors can propagate when returning an error. Closing an already closed cursor does not throw.

cur:fetch([table[,modestring]])

Fetches the next row. Without a table, returns one value per column. With a table, updates that table and returns it. SQL NULL becomes nil; numeric and text values become strings, and BLOB values become binary strings. Use the table form to distinguish a row whose first column is NULL from the end of the results.

Parameters

• table (optional) table - Destination row table. Selected fields are overwritten; SQL NULL removes the corresponding entries. Other entries are unchanged.
• string (optional) modestring - Used with a table; default "n". Include "n" for one-based column indices, "a" for column-name keys, or both. Column names with duplicates overwrite earlier fields.

Return values

• table (table form) row - The supplied table, on a successful fetch.
• string or nil (value form) column1, ... - One value per column, in SELECT order. Nil represents SQL NULL.
• nil end-of-results - The first fetch past the last row returns nil and releases the statement. Later fetches return no values. For prepared queries, call prepare() again before another execution.
• string (on failure) error - SQLite error name, returned after nil.
• string (on failure) detail - SQLite error description, returned after error. The statement is released on a reported fetch failure.

Throws

Throws for an invalid or closed cursor, an invalid mode-string type when a row is copied into a table, or a cursor closed while its query was running. Lua allocation errors can propagate. Reported query failures return nil, error, detail.

cur:getcolnames()

Returns the cursor's column-name array.

Parameters

None, apart from the object.

Return values

• table or nil names - Column-name strings in SELECT order. Nil until result metadata has been established for a prepared cursor. This is the stored table, not a copy; do not modify it because named fetches use it.

Throws

Throws for an invalid or closed cursor.

cur:getcoltypes()

Returns the declared SQL column types, rather than Lua value types.

Parameters

None, apart from the object.

Return values

• table or nil types - Declared-type strings by column position, or nil until metadata exists. Expressions without a declared type have no entry, so the array can contain holes. This is the stored table, not a copy.

Throws

Throws for an invalid or closed cursor.

SQLite extensions

The following cursor methods are SQLite-specific.

cur:bind([parameters])

Binds values to a prepared statement. With no arguments, returns its parameter count. Binding resets statement execution; execute the cursor again before fetching.

Use ? for consecutive parameter indices or ?NNN for an explicit one-based index. The largest index is the parameter count, including unused indices below it. Supply an entry for every index. SQLite limits the largest parameter index according to its build configuration.

Parameters

• table (optional) parameters - Array of {type,value} pairs, one per parameter index. Omit the argument to query the count; nil is not an omitted argument.
• string parameters[i][1] (type) - One of "BLOB", "FLOAT", "INTEGER", "NULL" or "TEXT". FLOAT requires floating-point support in SQLite.
• any type (depends on type) parameters[i][2] (value) - Value interpreted according to type, as described below.

Return values

• cursor userdata, integer or nil result - With parameters, the same cursor on success or nil on reported binding failure. Without parameters, the parameter count.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed cursor, malformed parameter tables, an unknown type, or a value that cannot be converted to the required string or number. INTEGER must fit in a Lua integer after conversion. Numeric BLOB size must be between zero and INT_MAX after conversion. Lua allocation errors can propagate. Reported SQLite binding failures return nil, error, detail.

Value types

• number or numeric string INTEGER value - Converted to a Lua integer, then stored as a SQLite integer. This BAS build rounds fractional values down.
• number or numeric string FLOAT value - Converted to a Lua number and stored as a SQLite floating-point value.
• string or number TEXT value - Stored as text. Numbers are converted to strings; tables and booleans are not converted with tostring(). Embedded zero bytes are preserved.
• string or number BLOB value - A string supplies its exact bytes, including embedded zeros; numeric-looking strings remain bytes. A number requests that many zero bytes after conversion to a Lua integer, rounding down in this BAS build.
• any type or omitted NULL value - Ignored; the parameter is set to SQL NULL.

A failed bind can leave earlier parameters changed. Rebind all parameters successfully before executing. SQLite may also report size or other errors later, when executing the statement.

-- Bind text separately from SQL syntax, then execute the prepared INSERT.
local cur = assert(conn:prepare("INSERT INTO people(name,email) VALUES(?,?)"))
assert(cur:bind{{"TEXT", "Alice"}, {"TEXT", "alice@example.com"}})
assert(cur:execute())
cur:close()

cur:execute()

Executes the prepared statement from its start, using its current parameter values.

Parameters

None.

Return values

• cursor userdata, integer or nil result - The same cursor when the statement has result columns, even when no rows match; otherwise the changed-row count. Nil on reported failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed cursor. Lua allocation errors can propagate. SQLite execution failures return nil, error, detail.

Fetch rows after execution. Fetching through the end releases the prepared statement, as described under conn:prepare(). A statement with no result columns remains reusable. An execution failure releases its statement; prepare again before retrying.

cur:unbind()

Sets all bound parameters to SQL NULL. This does not restart an active execution; call execute() to start again with the cleared values.

Parameters

None.

Return values

• boolean or nil ok - True on success; nil on reported failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed cursor. Lua allocation errors can propagate when returning a SQLite error.

BLOB Objects

A blob object contains methods to read and write BLOBS. A BLOB object is created by using the connection:openblob function.

Methods

blob:close()

Closes the BLOB handle. Closing the last writable BLOB can commit its writes when the connection is in auto-commit mode. If that commit fails, SQLite rolls back the transaction.

Parameters

None.

Return values

• boolean or nil ok - True on successful close; false if already closed; nil on a reported SQLite close failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid BLOB object. Lua allocation errors can propagate when returning an error. Repeated close does not throw.

The handle is closed even when an error is returned. A failed close cannot be retried on that handle; retry the database operation with a new handle if appropriate.

#blob

Reports the current BLOB size.

Parameters

None.

Return values

• integer size - Size in bytes of the opened value; zero for a closed BLOB handle.

Throws

Throws for an invalid BLOB object.

blob:read(size,offset)

Reads bytes from the BLOB. For example, blob:read(#blob,0) reads its complete contents.

Parameters

• integer size - Number of bytes to read, from zero through INT_MAX. The requested range must fit within the BLOB.
• integer offset - Zero-based byte offset, from zero through INT_MAX; zero reads from the start.

Return values

• string or nil data - Exactly size bytes, including any embedded zero bytes, on success. Nil on reported read failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed BLOB, or arguments that cannot be read as integers. A size or offset outside zero through INT_MAX after integer conversion also throws. Lua allocation errors can propagate. SQLite read errors return nil, error, detail.

blob:write(data[,offset])

Writes bytes without changing the BLOB size. The handle must have been opened for updates. Use SQL or conn:zeroblob() to resize the stored BLOB.

Parameters

• string data - Bytes to write, including any embedded zero bytes. The write must fit within the BLOB.
• integer (optional) offset - Zero-based byte offset, from zero through INT_MAX; default is zero.

Return values

• integer or nil written - Number of bytes written on success; nil on reported write failure.
• string (on failure) error - SQLite error name.
• string (on failure) detail - SQLite error description.

Throws

Throws for an invalid or closed BLOB, data that cannot be read as a string, or an offset that cannot be read as an integer. An offset outside zero through INT_MAX after integer conversion, or data longer than INT_MAX bytes, also throws. Lua allocation errors can propagate when returning an error. SQLite write errors return nil, error, detail.

Examples

Here is an example of the basic use of the library. After that, another example shows how to create an iterator over the result of a SELECT query.

Basic use

-- create environment object
env = assert (luasql.sqlite())
-- connect to data source
con = assert (env:connect("luasql-test"))
-- reset our table
res = con:execute"DROP TABLE people"
res = assert (con:execute[[
  CREATE TABLE people(
    name  varchar(50),
    email varchar(50)
  )
]])
-- add a few elements
list = {
  { name="Jose das Couves", email="jose@couves.com", },
  { name="Manoel Joaquim", email="manoel.joaquim@cafundo.com", },
  { name="Maria das Dores", email="maria@dores.com", },
}
for i, p in pairs (list) do
  res = assert (con:execute(string.format([[
    INSERT INTO people
    VALUES ('%s', '%s')]], p.name, p.email)
  ))
end
-- retrieve a cursor
cur = assert (con:execute"SELECT name, email from people")
-- print all rows, the rows will be indexed by field names
row = cur:fetch ({}, "a")
while row do
  print(string.format("Name: %s, E-mail: %s", row.name, row.email))
  -- reusing the table of results
  row = cur:fetch (row, "a")
end
-- close everything
cur:close()
con:close()
env:close()

And the output of this script should be:

Name: Jose das Couves, E-mail: jose@couves.com
Name: Manoel Joaquim, E-mail: manoel.joaquim@cafundo.com
Name: Maria das Dores, E-mail: maria@dores.com

Iterator use

It may be useful to offer an iterator for the resulting rows:

function rows (connection, sql_statement)
  local cursor = assert (connection:execute (sql_statement))
  return function ()
    return cursor:fetch()
  end
end

Here is how the iterator is used:

env = assert (luasql.sqlite())
con = assert (env:connect"my_db")
for id, name, address in rows (con, "select * from contacts") do
  print (string.format ("%s: %s", name, address))
end

The above implementation relies on the garbage collector to close the cursor. It could be improved to give better error messages (including the SQL statement) or to explicitly close the cursor (by checking whether there are no more rows).

xlua Usage

SQLite can be used with the extended Lua interpreter (xlua).