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.
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:
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>
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.
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:
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:
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.
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:
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
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 ?>
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 a ready to run example from GitHub.
Our extensive collection of embedded web server and IoT tutorials is tailored to guide you every step of the way. Whether you're just starting out or looking to hone your skills, our resources are at your fingertips.
However, we understand that time is of the essence. Our consulting services are here to assist if you are stretched thin or seeking expert guidance. Let our seasoned professionals manage the complexities of networking, security, and device management for you.
Expedite your IoT and edge computing development with the "Barracuda App Server Network Library", a compact client/server multi-protocol stack and toolkit with an efficient integrated scripting engine. Includes Industrial Protocols, MQTT client, SMQ broker, WebSocket client & server, REST, AJAX, XML, and more. The Barracuda App Server is a programmable, secure, and intelligent IoT toolkit that fits a wide range of hardware options.
SharkSSL is the smallest, fastest, and best performing embedded TLS stack with optimized ciphers made by Real Time Logic. SharkSSL includes many secure IoT protocols.
SMQ lets developers quickly and inexpensively deliver world-class management functionality for their products. SMQ is an enterprise ready IoT protocol that enables easier control and management of products on a massive scale.
SharkMQTT is a super small secure MQTT client with integrated TLS stack. SharkMQTT easily fits in tiny microcontrollers.
An easy to use OPC UA stack that enables bridging of OPC-UA enabled industrial products with cloud services, IT, and HTML5 user interfaces.
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.
Learn how to use the Barracuda App Server as your On-Premises IoT Foundation.
The compact Web Server C library is included in the Barracuda App Server protocol suite but can also be used standalone.
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.
Why use FTP when you can use your device as a secure network drive.
PikeHTTP is a compact and secure HTTP client C library that greatly simplifies the design of HTTP/REST style apps in C or C++.
The embedded WebSocket C library lets developers design tiny and secure IoT applications based on the WebSocket protocol.
Send alarms and other notifications from any microcontroller powered product.
The RayCrypto engine is an extremely small and fast embedded crypto library designed specifically for embedded resource-constrained devices.
Real Time Logic's SharkTrust™ service is an automatic Public Key Infrastructure (PKI) solution for products containing an Embedded Web Server.
The Modbus client enables bridging of Modbus enabled industrial products with modern IoT devices and HTML5 powered HMIs.