Troubleshooters.Com and Code Corner Present

Litt's Lua Laboratory:
Hitting Postgres From Lua
(With Snippets)

Introduction
Installing LuaSQL
Hello World
Reading and Writing Postgres from Lua

Introduction

With the purchase of MySQL by Oracle, many MySQL users are reevaluating their options. Personally, I was always a Postgres man, and the introduction of Larry Ellison just makes me more so.

There are many tools to interface between Lua and Postgres. Probably the best known, and one of the most tested, is the LuaSQL package. LuaSQL also has the advantage of interfacing to several different databases, including ODBC, MySQL, SQLite, Oracle, and ADO. Most of this web page uses LuaSQL.

One criticism of LuaSQL is that because it's so abstracted and generalized, it doesn't reveal some of Postgres' special abilities. Fair enough. But this can be mostly overcome by programming those special Postgresisms as stored procedures, and then just querying the stored procedures from LuaSQL.

Installing LuaSQL

The biggest challenge on this page, assuming you already have Lua and Postgres installed, is installing LuaSQL. With Ubuntu and probably several other Linux distros, installation is as simple as enabling LuaSQL. For instance, in Ubuntu you go into Synaptic, look up luasql, and from the selections presented check package "liblua5.1-sql-postgres-2", click the Apply button, and bang, you have LuaSQL installed for Postgres. While you're at it, you might as well install LuaSQL for SqLite, the LuaSQL documentation, and if you insist, MySQL.

If your distro doesn't have a package for LuaSQL/postgres, you'll need to use Lua's LuaRocks program. Install LuaRocks from your distro's package manager. If your distro doesn't have a LuaRocks package (it really should, but if it doesn't), you'll need to do some RTFW to find out how to install it. Those instructions are beyond the scope of this web page.

So now you have LuaRocks installed. Now do this:

slitt@mydesk:~$ luarocks
LuaRocks 1.0.1, a module deployment system for Lua

luarocks - LuaRocks main command-line interface
usage: luarocks [--from=<server> | --only-from=<server>] [--to=<tree>] [VAR=VALUE]... <command> [<argument>]

Variables from the "variables" table of the configuration file
can be overriden with VAR=VALUE assignments.

--from=<server>       Fetch rocks/rockspecs from this server
                      (takes priority over config file)
--only-from=<server>  Fetch rocks/rockspecs from this server only
                      (overrides any entries in the config file)
--to=<tree>           Which tree to operate on.

Supported commands:

build   Build/compile a rock.
help    Help on commands.
install Install a rock.
list    Lists currently installed rocks.
make    Compile package in current directory using a rockspec.
pack    Create a rock, packing sources or binaries.
remove  Uninstall a rock.
search  Query the LuaRocks servers.
unpack  Unpack the contents of a rock.
slitt@mydesk:~$

The preceding command told you all the LuaRocks commands you have at your disposal, and also the --from=<server> tag, which you'll need if you can't find what you need in the default servers.

Obviously the first step is to find LuaSQL/Postgres. Do this:

slitt@mydesk:~$ luarocks search luasql

Search results:
===============

Rockspecs and source rocks:
---------------------------

luasql-mysql
   2.2.0-1 (src) - http://luarocks.luaforge.net/rocks/
   2.2.0-1 (rockspec) - http://luarocks.luaforge.net/rocks/

luasql-sqlite3
   2.2.0-1 (src) - http://luarocks.luaforge.net/rocks/
   2.2.0-1 (rockspec) - http://luarocks.luaforge.net/rocks/

slitt@mydesk:~$

The preceding would be perfect if you wanted LuaSQL for SqLite or MySQL, but it gets you nothing for Postgres. Don't worry -- LuaRocks just isn't yet looking in the right place. Do a web search on LuaRocks LuaSQL Postgres and one of the top search results is http://www.luarocks.org/repositories/rocks-cvs/, which contains a list of "Rocks" including one for LuaSQL-Postgres. Use that URL as the --from=:

slitt@mydesk:~$ luarocks search luasql --from=http://www.luarocks.org/repositories/rocks-cvs/

Search results:
===============

Rockspecs and source rocks:
---------------------------

luasql-mysql
   cvs-1 (rockspec) - http://www.luarocks.org/repositories/rocks-cvs/
   2.2.0-1 (src) - http://luarocks.luaforge.net/rocks/
   2.2.0-1 (rockspec) - http://luarocks.luaforge.net/rocks/
   2.2.0rc1-1 (rockspec) - http://www.luarocks.org/repositories/rocks-cvs/

luasql-odbc
   cvs-1 (rockspec) - http://www.luarocks.org/repositories/rocks-cvs/

luasql-postgres
   cvs-2 (rockspec) - http://www.luarocks.org/repositories/rocks-cvs/

luasql-sqlite
   cvs-1 (rockspec) - http://www.luarocks.org/repositories/rocks-cvs/
   2.2.0rc1-1 (rockspec) - http://www.luarocks.org/repositories/rocks-cvs/

luasql-sqlite3
   cvs-1 (rockspec) - http://www.luarocks.org/repositories/rocks-cvs/
   2.2.0-1 (src) - http://luarocks.luaforge.net/rocks/
   2.2.0-1 (rockspec) - http://luarocks.luaforge.net/rocks/
   2.2.0rc1-1 (rockspec) - http://www.luarocks.org/repositories/rocks-cvs/

slitt@mydesk:~$

Hello! Now we have the right package. You can either use that same --from= with your install command, or you add that URL to the rock_servers table in your config.lua LuaRocks config file, probably in directory /etc/luarocks. The rest of this article assumes you added the URL to the config file.

Now just do this:

luarocks install luasql-postgres

and LuaSQL/Postgres installs.

Hello World

Unlike most Hello World programs, this one prints nothing. If it doesn't error out, you've passed a significant milestone. And take it from me, it will take you several tries to get this running. Here's the hello.lua program:

#!/usr/bin/lua
require "luasql.postgres"
envv = assert (luasql.postgres())
con = assert (envv:connect('mydb', 'myuid', 'mypass', "127.0.0.1", 5432))

ATTENTION!

You need ALL FIVE arguments to envv:connect() in order to connect to a Postgres database. Please ignore all the web examples, including the one that comes with the LuaSQL docs themselves, indicating that you need only one argument. That "one argument" documentation is just to send the tourists on a wild goose chase. You're local, so you know you need all five -- four strings and an integer!

Please ignore all documentation indicating that arg1 might be something like 'PostgreSQL'. It isn't unless you actually have a database named PostgreSQL. Arg1 is the name of a database created with the SQL CREATE DATABASE command or the Linux command createdb.

Remember, you're a local home-team Lua/Postgres guy, so ignore those docs designed to get tourists lost.

Run the program and see where it bombs out. If it bombs out on either line 2 or 3, the most likely cause is you haven't installed LuaSQL/Postgres, or haven't installed it right. Troubleshoot.

But it probably won't bomb out on lines 2 or 3, but will almost bomb out on line 4, the one with envv:connect(). That's because every argument to envv:connect() must exactly match what's going on in your Postgres setup. Here's how that bombout will most likely look:

slitt@mydesk:~$ ./hello.lua 
/usr/bin/lua: ./hello.lua:4: LuaSQL: Error connecting to database.
stack traceback:
        [C]: in function 'assert'
        ./hello.lua:4: in main chunk
        [C]: ?
slitt@mydesk:~$

All it tells you is it failed to connect to the database in line 4. Remember, in order to run without error, every argument to envv:connect() must exactly match what's going on in your Postgres setup. The meaning of each argument is as follows:

Arg1 (the first arg) must be the name of a database in your Postgres installation
Arg2 must be the Posgres username of the owner of the database from arg1
Arg3 must be the password of the username in arg2. Note that later we'll present a way to enable you to put nil as this argument for heightened security.
Arg4 must be the IP address at which you access Postgres.
Arg5 must be the port on which Postgres listens at the IP address in arg 4

All the preceding takes place in an environment of black boxes. It could take hours or days to troubleshoot by RTFW and trial and error. So instead this article gives you some tools with which to peer inside the black boxes...

Peering Tool 1: Netstat

Args 4 and 5 are pretty easy. Run this command:

slitt@mydesk:~$ sudo netstat -nap | grep -i post
[sudo] password for slitt: 
tcp        0      0 192.168.100.2:5433      0.0.0.0:*               LISTEN      14229/postgres  
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      14229/postgres  
udp6       0      0 ::1:36746               ::1:36746               ESTABLISHED 14229/postgres  
unix  2      [ ACC ]     STREAM     LISTENING     1470139  14229/postgres      /var/run/postgresql/.s.PGSQL.5433
slitt@mydesk:~$

The preceding indicates that on my machine, Postgres is listening on port 5433, not the default 5432. It's listening on that port on both 127.0.0.1 (localhost) and 192.168.100.2. You now know args 4 and 5.

Peering Tool 2: pgadmin3

The pgadmin3 program is, in my opinion, buggy and hard to use. What it does well though is to show you what's going on. Furthermore, if you can connect via pgadmin3 it's likely you can connect via LuaSQL.postgres using the same database name, user, password, IP address and port.

Use pgadmin3 to get a better look at what's going on, but never rely on it exclusively. My experience is that pgadmin3 is a very poor way to actually write to the database.

Peering Tool 3: psql

The psql tool is a bare bones, command driven, command line interface tool. It's difficult to use and even more difficult to see what's going on. For instance, it tells you nothing about which port or what IP address, and it can give info on users that will mislead the inexperienced. But for writing to the database, creating new databases and users and tables, for doing anything substantial with the database, psql is the reliable tool.

Setting Up Postgres For Hello World

Start by setting up the right Postgres database, user, and password for Hello World.

Find out your server's IP and port with netstat -nap | grep -i post. This information will make it much easier to diagnose and to use pgadmin3.
Log in to Linux as user postgres. All the following is much easier if you're logged into Linux as user postgres.
Run the createuser program. In response to the prompts, create user myuid, do NOT give that user rights to create new roles or databases and do not make it superuser.
Run the command createdb -O myuid mydb. This creates the mydb database, owned by user myuid.
psql -U postgres template1.

This gets you into psql as an administrator. Remember, this only works if you were Linux user postgres. To do this from other Linux usernames, you need to use a different command. The template1 database exists by default on all new Postgres installations and should not be changed unless you REALLY REALLY REALLY have a good reason to do so. However, it makes a great place to make other databases and change rights and the like.

alter user myuid password 'mypass';

This gives user myuid the password mypass. Obviously, as soon as you're done with these experiments you want to change the password to something much more secure. Better yet, for security's sake, you might want to give it a secure password right now and as you go through this web page just translate every instance of 'mypass' to whatever password you made.

Ctrl+D to get you out of the psql environment.

If you've followed the preceding instructions exactly, and if you make sure your hello.lua's envv:connect() command has fourth and fifth arguments matching the IP address and port you found with your netstat command, theoretically your hello.lua program will not error out, but instead will give absolutely no output. No output means it worked.

If it still gives errors, troubleshoot by exploring with psql, pgadmin3 and netstat until the first three arguments match a Postgres database with a specific owner with a specific password, and the fourth and fifth arguments correspond to the IP address and port on which Postgres listens.

Once your Hello World program works, the hardest part of this job is behind you.

Reading and Writing Postgres from Lua

This section examines a simple program to write and read data. The program is called readwrite.lua. Like hello.lua, it opens a connection. But then it goes on to drop any existing people table, define and create a people table with four columns, read the people table, sorted by last name, into a cursor, loop through the cursor printing each row, and finally closing all variables. Here's the program:

#!/usr/bin/lua
require "luasql.postgres"
envv = assert (luasql.postgres())
con = assert (envv:connect('mydb', 'myuid', 'mypass', "127.0.0.1", 5433))

-- DROP ANY EXISTING PEOPLE TABLE
res = con:execute("DROP TABLE people")

-- RECREATE PEOPLE TABLE
res = assert (con:execute[[
	CREATE TABLE people(
		id integer,
		fname text,
		lname text,
		job text
	)
]])

-- ADD SOME PEOPLE TO THE PEOPLE TABLE
res = assert(con:execute("INSERT INTO people " ..
	"VALUES (1, 'Roberto', 'Ierusalimschy', 'Programmer')"))
res = assert(con:execute("INSERT INTO people " ..
	"VALUES (2, 'Barack', 'Obama', 'President')"))
res = assert(con:execute("INSERT INTO people " ..
	"VALUES (3, 'Taylor', 'Swift', 'Singer')"))
res = assert(con:execute("INSERT INTO people " ..
	"VALUES (4, 'Usain', 'Bolt', 'Sprinter')"))

-- RETRIEVE THE PEOPLE TABLE SORTED BY LAST NAME INTO CURSOR
cur = assert (con:execute"SELECT * from people order by lname")

-- LOOP THROUGH THE CURSOR AND PRINT
print()
print(string.format("%15s  %-15s %-15s %-15s",
	"#", "FNAME", "LNAME", "JOB"))
print(string.format("%15s  %-15s %-15s %-15s",
	"-", "-----", "-----", "---"))
row = cur:fetch ({}, "a")
while row do
	print(string.format("%15d  %-15s %-15s %-15s",
		row.id, row.fname, row.lname, row.job))
	row = cur:fetch (row, "a")
end
print()

-- CLOSE EVERYTHING
cur:close()
con:close()
envv:close()

As expected, the preceding code produces the following output:

slitt@mydesk:~$ ./readwrite.lua 

              #  FNAME           LNAME           JOB            
              -  -----           -----           ---            
              4  Usain           Bolt            Sprinter       
              1  Roberto         Ierusalimschy   Programmer     
              2  Barack          Obama           President      
              3  Taylor          Swift           Singer         

slitt@mydesk:~$

[ Troubleshooters.com| Code Corner | Email Steve Litt ]