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
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:
You can also unpack the example into a directory and run the example using the Mako Server as follows:
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.
When using SQLite with Lua Server Pages it is unfortunately very easy to end up with the BUSY error code. Learn how to prevent this issue by reading the article Lua-SQLite and LSP Considerations.