Lua SQLite Database Tutorial

Mako Server SQLite Lua Bindings


SQLite is 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.

Download SQLite Example:

Related SQL Articles:

Lua-SQLite and LSP Considerations

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.

Discover More:

Whether you are a maker, a startup, or a large business, we've got you covered. Please send us an email if you have any questions or if you are unsure on what product to select. We are here to help you find the best solution, and we'd really like to help you with your hardware/software project challenges.


OPC-UA

OPC-UA Client & Server

An easy to use OPC UA stack that enables bridging of OPC-UA enabled industrial products with cloud services, IT, and HTML5 user interfaces.

Edge Controller

Edge Controller

Use our user programmable Edge-Controller as a tool to accelerate development of the next generation industrial edge products and to facilitate rapid IoT and IIoT development.

On-Premises IoT

On-Premises IoT Platform

Learn how to use the Barracuda App Server as your On-Premises IoT Foundation.

Embedded Web Server

Barracuda Embedded Web Server

The compact Web Server C library is included in the Barracuda App Server protocol suite but can also be used standalone.

WebSocket Server

Microcontroller Friendly

The tiny Minnow Server enables modern web server user interfaces to be used as the graphical front end for tiny microcontrollers. Make sure to check out the reference design and the Minnow Server design guide.

WebDAV Server

Network File System

Why use FTP when you can use your device as a secure network drive.

HTTP Client

Secure HTTP Client Library

PikeHTTP is a compact and secure HTTP client C library that greatly simplifies the design of HTTP/REST style apps in C or C++.

WebSocket Client

Microcontroller Friendly

The embedded WebSocket C library lets developers design tiny and secure IoT applications based on the WebSocket protocol.

SMTP Client

Secure Embedded SMTP Library

Send alarms and other notifications from any microcontroller powered product.

Crypto Library

RayCrypto C Library

The RayCrypto engine is an extremely small and fast embedded crypto library designed specifically for embedded resource-constrained devices.

Embedded PKI Service

Automatic SSL Certificate Management for Devices

Real Time Logic's SharkTrust™ service is an automatic Public Key Infrastructure (PKI) solution for products containing an Embedded Web Server.

Modbus

Modbus TCP client

The Modbus client enables bridging of Modbus enabled industrial products with modern IoT devices and HTML5 powered HMIs.

Posted in Tutorials