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.raspberrypi.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


Posted in Tutorials