Using Lua to Access a PostgreSQL Database

PostgreSQL + Lua = Love

The Mako Server lets developers easily work with the integrated SQLite engine using Lua, but sometimes a more advanced database such as PostgreSQL is required. PostgreSQL supports clustering and high availability (HA) which are particular useful if you plan on using the Mako Server to power your IoT solution. In the article Setting Up a Low Cost IoT Server , we uncover how easy it is to run the Mako Server as an online service without going into detail on IoT data management. Since the Mako Server supports IoT clustering , but not HA storage, PostgreSQL complements the Mako Server's IoT clustering feature with its awesome database that supports HA storage.

PostgreSQL as a Service

Setting up a PostgreSQL HA cluster is beyond the scope of this tutorial. To simplify things, we will instead use ElephantSQL, which provides PostgreSQL as an online service. ElephantSQL is super easy to use, and they provide a free plan which is perfect for anyone wanting to learn more about PostgreSQL.

Sign up for the free Tiny Turtle ElephantSQL plan if you want to follow along this tutorial. After signing up, navigate to your PostgreSQL's details page. You should see a page similar to the following:

ElephantSQL Dashboard

Figure 1: ElephantSQL Dashboard

PostgreSQL Lua Bindings

Mako Server does not come with a ready to use integrated interface to PostgreSQL; however, adding the needed interface is not complicated. A search for "PostgreSQL Lua" provides several options, and the one we have tested and made Mako Server friendly is the luapgsql Lua bindings. Installing the bindings is explained in the next section. The following Lua Server Pages (LSP) script can be run when the bindings are installed.

<pre>
<?lsp
local pg = require "pgsql" -- Load PostgreSQL Lua Bindings

local cinfo = -- ElephantSQL connection info and credentials
   "host=babar.elephantsql.com port=5432 user=mxqhozskx password=yP6k..."

local conn=pg.connectdb(cinfo)
if conn:status() == pg.CONNECTION_OK then
   conn:exec'create table test (a integer, b numeric(8, 2), c float)'
   local n = 3.1415986
   conn:execParams(
      'insert into test (a,b,c) values ($1::integer,$1::numeric,$1)',
      n, n, n)
   print"Done"
else
   print("status",conn:status())
   print("error",conn:errorMessage())
end

conn:finish() -- Terminate DB TCP connection
?>
</pre>

Figure 2: Connect to ElephantSQL, create table 'test', and insert data into table 'test'.

In Figure 2, we create the string 'cinfo' and format the string with the connection information and credentials provided in Figure 1. This information is passed into the 'connectdb' function. If you look in the 'connectdb' function documentation , you will see that it does not explain the input parameter format. You must look in the PostgreSQL's libpq Database Connection String Parameters for details. The pgsql library provides Lua bindings for PostgreSQL's libpq C library, and you may want to consult the documentation for both libraries when working with the Lua API.

Compiling and Installing the PostgreSQL Lua Bindings

The following instructions are designed for Linux, but the commands also work on Windows 10 with the Linux sub system. It's super easy to install and use the "Windows Subsystem for Linux". Just open the Windows 10 Store and search for Ubuntu. See this tutorial if you feel you need more information.

Open a shell and run the following commands:

sudo apt-get update sudo apt-get install libpq-dev gcc git unzip sed wget lua5.2

Figure 3: Installing the required PostgreSQL's libpq C build tools

The second command (Figure 3) installs PostgreSQL's libpq C library and a number of utilities needed when building 'pgsql'.

The PostgreSQL Lua Bindings 'pgsql' must be downloaded from github and compiled on your computer. However, you should use a compilation script we have prepared. The compilation script makes the 'pgsql' library Mako Server friendly by adding wrappers around each Lua binding (function). Many of the functions provided by 'pgsql' block on database response messages and take time to execute. The wrappers make sure the multithreaded Mako Server can continue executing other threads when 'pgsql' functions block the current thread. You do not need to understand the details behind the function wrappers, but if you want to understand how they work, see the releasing the global mutex tutorial. The compilation script automatically adds the wrappers to 'pgsql' by using the Mutex wrapper script generator .

Download and run the PostgreSQL Lua Bindings 'pgsql' build script as follows:

cd /tmp wget /download/PostgreSQL.tar.gz tar xvzf PostgreSQL.tar.gz cd PostgreSQL ./build.sh

Figure 4: How to download the PostgreSQL's libpq C build script

The above script build.sh compiles 'pgsql' and produces the file pgsql.so. This file must be copied to a directory where the Mako Server, or more specifically, the Mako Server's integrated Lua engine can find the shared library. A suggestion is to run the script in Figure 2 and look at the error message produced when the library is not found. Any of the paths printed in the "not found" error message can be used as the destination directory for pgsql.so.

The following example shows how to copy the shared library built in Figure 4 to /usr/bin and how to install the Mako Server.

sudo cp luapgsql/pgsql.so /usr/bin/ #Replace XXX below with the platform specific name wget makoserver.net/download/mako.XXX.tar.gz tar xvzf mako.XXX.tar.gz sudo cp mako mako.zip /usr/bin/

Figure 5: How to optionally install the shared library pgsql.so and Mako Server in /usr/bin/

Using the PostgreSQL Lua Bindings

You may use the Lua code in Figure 2 as soon as the 'pgsql' module is installed as explained above. See the getting started guide for how to copy the code in Figure 2 to a Mako Server app directory.

If you run the code in Figure 2, you will notice that it takes some time for the LSP page to complete. Most of the time is spent on connecting to the ElephantSQL (See Figure 2, line 8 - connecting). You may open multiple browser windows and run the same code. Each page opens a new connection. The Mako Server includes an LSP/HTTP thread pool and runs each LSP page in its own native thread. You may have multiple concurrent ElephantSQL requests thanks to the wrappers added to the 'pgsql' module by the compilation script. See the previous section for details.

Using a PostgreSQL service as shown in Figure 2 works, but may not be the optimal solution for several reasons:

  • The connection establishment takes time.
  • Each LSP page requires its own TCP connection and may exhaust the ElephantSQL tiny turtle plan's max 5 connections.
  • A PostgreSQL query blocks until the PostgreSQL server responds, which may take time. Using a PostgreSQL connection via an asynchronous cosocket including the SMQ broker would freeze the server until the PostgreSQL server sends the response message.

Using One PostgreSQL TCP Connection For All Database Interactions

Using one DB connection object for all PostgreSQL interactions eliminates the issues listed above. However, only one dedicated thread can use the PostgreSQL connection object safely. What we need to do is to make sure all LSP pages and cosockets delegate the PostgreSQL interactions to one thread and that this thread is the only thread that operates on the database. This can easily be solved by using the Lua thread library. The following code snippet creates a dedicated thread for PostgreSQL interaction, by calling ba.thread.create(), and provides a global function dbexec() that can be used by any LSP page or any other code that wants to interact with the database.

local dbthread=ba.thread.create()
function dbexec(run)
   dbthread:run(run)
end

Figure 6: Creating a dedicated PostgreSQL interaction thread and DB worker function.

An LSP page can then delegate execution of a callback function to the thread as follows:

<?lsp
 
local function run()
   local n = 3.1415986
   conn:execParams(
      'insert into test (a,b,c) values ($1::integer,$1::numeric,$1)',
      n, n, n)
end
 
dbexec(run) -- Insert callback into thread queue
 
?>

Figure 7: Creating a PostgreSQL function that executes in the context of the thread in Figure 6.

In the above code snippet, an LSP page creates a callback function 'run' and calls the global dbexec function, passing in the callback. The function 'run' will then execute in the context of the 'dbthread' and not in the context of the LSP page. The thread library provides a queuing mechanism, and concurrent LSP page requests are queued in the internal thread queue. Each callback function is executed by the thread in order, thus making sure that only one thread operates on the DB connection object.

Download Example Code

Download a ready to run example from GitHub.

Related SQL Articles


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