Lua SQLite Database Tutorial

Mako Server SQLite Lua Bindings

SQLiteis a popular database library that is designed specifically for being embedded into computer programs. In the Mako Server, Lua applications interact with the SQLite database via the SQLite Lua Bindings. The SQLite Lua Bindings are a component of the Lua Server Pages.

When working with database driven web applications designed in, for example, PHP and MySQL, you must first connect to a MySQL database server (another process) and establish a connection by providing the required database credentials. You do not set up such a connection when using the Mako Server since the SQLite database is integrated into the Mako Server executable. Instead, the server side web application opens a database file via SQLite. A database file is a regular file that is managed by SQLite and stored on your file system.

In this tutorial we will show you how to use the SQLite database from Lua and Lua Server Pages (LSP). The example we build in this tutorial shows you how to add basic comment management to a page such as a blog entry. If you are new to SQL, you may want to read a SQL tutorial before continuing with this tutorial. You can find many tutorials online such as the w3schools.com SQL tutorial. The Mako Server SQLite documentation can be found in the Mako Server Documentation under section Manual -> Lua -> Persistence -> LuaSQL.

The following example shows a typical database sequence in an LSP page:

local env = luasql.sqlite() -- Create a database environment object
local conn = env:connect("/path/file.db") -- Open a database file
-- Run one or multiple database operations on the opened database file
conn:close() -- Close the database file connection object
env:close() -- Close the database environment object

In the above example, we first create a database environment object and then use this object when opening the database file. We can run any number of database operations when we have the database file connection object. We will get into this in detail later. Finally, the database file connection object and the environment object must be closed. Although the Lua garbage collector will eventually close any objects not closed, we suggest that you close the objects at the end of the LSP page. You can otherwise run into situations where opening the SQLite database file at a later time returns the error code "busy".

Every time you want to perform a database action in an LSP page, you must first open the database by calling luasql.sqlite() and env:connect(), perform the database action, and then close the database objects. This operation becomes tedious and we have therefore created a basic SQL utility library that simplifies some of the more common database operations. The SQL utility library is loaded by calling require"sqlutil". The above example can be rewritten as follows:

local su=require"sqlutil" -- Load SQL utility library
 -- Create a database environment object and open "data/file.sqlite.db"
local env,conn = su.open"file"
-- Run one or multiple database operations on the opened database file
su.close(env,conn) -- Close the database connection and environment objects

Parentheses  are not required in Lua when you provide only one argument to a function, thus su.open("file") and su.open"file" are identical.

The two function calls luasql.sqlite() and env:connect() are now replaced by calling one function. Function su.open() returns two values, the database environment object and a database file connection object. Function su.open() also normalizes the location where you store your database files. See the sqlutil documentation for more information.

We must create the required database tables before we can start running queries and insert data into the database. You can create the tables in an LSP page, but it is much more convenient to create the database tables in a .preload script. The .preload script is an optional Lua file you can add to your application. This file is executed when the Mako Server loads your application.

The following code snippet is from our .preload script:

local su=require"sqlutil" -- Load SQL utility library
if not su.exist"file" then
   -- Create a database environment object and open data/file.sqlite.db
   local env,conn = su.open"file"
   conn:execute"CREATE TABLE messages (key INTEGER PRIMARY KEY, msg TEXT)"
   su.close(env,conn)
end

Function su.open(), which internally calls env:connect(), automatically creates a database file if the file does not exist. The problem with this construction is that we end up with an empty database file with no tables. What we want to do, if there is no database file, is to create the database file and then create the SQL tables we require for our LSP pages. The utility function su.exist() returns false if the database file does not exist. The above example uses this utility function to detect when the SQL tables must be created. We only create one table in the above example, the 'messages' table. This table contains two columns, the key and the message (msg).

The table 'messages' will be created by the .preload script when you start the Mako Server. The following LSP code snippet shows how to open the database file and print all rows in the 'messages' table.

local su=require"sqlutil"
local env,conn = su.open"file"
-- Create a SQL cursor object by running a DB query
local cur = conn:execute"SELECT key,msg FROM messages"
local key,msg = cur:fetch()
while key do
   -- Iterate cursor and print all messages in table 'messages'
   response:write('<h3>Message ',key,'</h3><div>',msg,'</div>')
   key,msg = cur:fetch()
end
cur:close() -- Close cursor
su.close(env,conn)

The "sqlutil" library includes a select() function that simplifies creating the cursor object. We can redesign the above example and use the sqlutil library instead of directly using the SQLite bindings.

local su=require"sqlutil"
local env,conn = su.open"file"
local function execute(cur) -- su.select() callback function
   local key,msg = cur:fetch()
   while key do
      response:write('<h3>Message ',key,'</h3><div>',msg,'</div>')
      key,msg = cur:fetch()
   end
end
su.select(conn,"key,msg FROM messages",execute)
su.close(env,conn)

The first parameter to su.select() is the connection object. The second parameter is the SQL query without "SELECT", and the third argument is the cursor callback function. Notice that we have deliberately not performed any error management in our examples. The cursor callback function execute() will not be called should the select query fail. The su.select() function takes care of opening and closing the cursor object.

The above example can be further simplified by providing a function that opens the database file and then passes this function in as the first argument to su.select() instead of providing a connection object.

local su=require"sqlutil"
local function execute(cur)
   local key,msg = cur:fetch()
   while key do
      response:write('<h3>Message ',key,'</h3><div>',msg,'</div>')
      key,msg = cur:fetch()
   end
end
su.select(function() return su.open"file" end, "key,msg FROM messages", execute)

In the above example, we pass in an anonymous function that opens the database file. Function su.select() will in this case also take care of closing the database.

So far, we have not inserted any messages into our "messages" table and the above example will not print anything before we insert at least one message. A message can be inserted as follows:

local data=request:data()
if data.msg then
   -- Quote msg and prevent SQL injection by escaping any ' characters
   local msg=luasql.quotestr(data.msg)
   local sql=string.format("INSERT INTO messages (msg) values(%s)",msg)
   local env,conn = su.open"file"
   conn:execute(sql) -- Insert msg into database
   su.close(env,conn)
end

Function request:data() returns URL encoded data sent to the server as a table. We could alternatively have used the construction local msg=request"msg" since we only require one parameter. Data can be sent to the server by using a HTML form or by entering the parameter at the end of the url e.g. http://my-server/?msg=hello

The above example wraps up our introduction to using SQLite from Lua and LSP. You can either use the direct SQLite interface or encapsulate some of the tedious operations using our sqlutil library. The sqlutil library is a Lua module found in mako.zip.

The complete code for this tutorial can be downloaded. Download sql-tutorial-1.zip and start the example using the Mako Server as follows:

mako -l::sql-tutorial-1.zip

You can also unpack the example into a directory and run the example using the Mako Server as follows:

mako -l::sql-tutorial-1

The above command assumes you unpacked the ZIP file into directory "sql-tutorial-1". Running the example in non-deployed (developer) mode enables you to experiment with the code.

Posted in Tutorials