MySQL and Redis Drivers

MySQL and Redis are two popular databases. The following tutorial shows how to use these two databases by using a plugin we have designed. The plugin is a Lua module that provides a minimal translation layer for the OpenResty platform, thus making it possible to use the two OpenResty components lua-resty-mysql and lua-resty-redis.

OpenResty Interface

Download

Download OpenResty.zip. This ZIP file includes MySQL and Redis drivers and an OpenResty to Mako Server translation layer that makes it possible to use these two drivers on the Mako Server. All source code in OpenResty.zip is licensed under the BSD license, including MySQL and Redis drivers.

Using

You do not need to unzip OpenResty.zip. Start the Mako Server as follows:

mako -l::OpenResty.zip -l::my-app-dir

where my-app-dir is a directory with your application's resource files.

MySQL Example

Download the MySQL.lsp test program and copy the file to your "my-app-dir". You can then run the LSP MySQL test by entering the LSP pages's URL in your browser. However, before running the LSP page, you must install MySQL and create the following database:

$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. mysql> CREATE TABLE Persons -> ( -> PersonID int, -> LastName varchar(255), -> FirstName varchar(255), -> Address varchar(255), -> City varchar(255) -> ); mysql> insert into Persons -> ( -> PersonID, -> LastName, -> FirstName, -> Address, -> City -> ) -> VALUES(1,"Bond","James"," Old Bond Street","London");

After performing the above MySQL commands, open MySQL.lsp in an editor and change the password. Save the file and enter the following URL in your browser: http://localhost/MySQL.lsp

You should see a response message in your browser and a response message being printed in the Mako Server's console window.

Redis Example

Install and start the Redis database on your computer. Download the Redis.lsp test program and copy the file to your "my-app-dir". Enter the following URL in your browser: http://localhost/Redis.lsp

You should see a response message in your browser and a response message being printed in the Mako Server's console window.

Blocking v.s. non Blocking Sockets

The MySQL and Redis drivers are internally using sockets when communicating with the database. It is, for this reason, important that you understand the difference between how sockets work in the Mako Server and in OpenResty.

The Mako Server supports both blocking and non blocking sockets, in contrast to the OpenResty environment, which only supports non blocking sockets. Non blocking sockets in OpenResty are referred to as Cosockets and are similar to the Mako Server's (and the Barracuda App Server's) non blocking sockets.

Sockets in the Mako Server are, by default, blocking, thus the drivers will be using blocking socket calls. This works great when used by LSP pages since each LSP page has its own native worker thread.

The following figure from the documentation illustrates how the server's socket dispatcher delegates HTTP requests to a thread in the Barracuda Server's Thread Pool. See the documentation, section Thread Mapping and Coroutines for details.

Barracuda App Server Threads

If you study the MySQL.lsp and the Redis.lsp example programs, you will notice that all database interactions are encapsulated in one function. The following is an excerpt from MySQL.lsp:

local function mySqlTest()
   -- Database interaction code here, which in turn opens a socket
   -- connection.
end

mySqlTest() -- Run using blocking socket calls

-- Run as "Non Blocking Sockets" (OpenResty compat Cosocket)
print=_G.print -- Redirect LSP print to global (console) print
ba.socket.event(mySqlTest)

In the above example, function mySqlTest opens a database connection and interacts with the database. All socket calls will be blocking when the function is called on line 6. The function will not return until all database interaction has completed, thus the HTTP response will not be committed before the function returns.

A Non Blocking Socket Coroutine is created on line 10. The function ba.socket.event returns immediately and the LSP page exits; however, the mySqlTest function is now running as a Socket Coroutine Thread (similar to Cosockets in OpenResty). All socket interactions are now managed directly by the Socket Event Dispatcher as shown in the above figure.

Notice how we redirect the print function, which by default emits data to the HTTP response, on line 9. The mySqlTest function is using function print to emit the results from the database interaction. This would fail if it attempted to use the print function in the expired LSP page. The global print function is Lua's original print function which prints to the console.

When to use blocking sockets and when to use non blocking sockets

Blocking database sockets connections should be used when accessing the database from an LSP page and when the response data from the database should be part of the HTTP response message. A non blocking socket works asynchronously and the LSP page will not be able to render a response message with data from the database if the database socket connection is in non blocking mode.

Non blocking database socket connections should be used when the database connections are used in combination with other non blocking APIs such as when using a persistent socket/WebSocket for say an IoT protocol or when used in combination with our SMQ IoT protocol.

Limitations

The OpenResty compatibility layer is currently not implementing the connection pool; thus a socket added to the pool is simply closed.

Posted in Tutorials