Troubleshooters.Com and Code Corner Present

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

Copyright (C) 2011 by Steve Litt

Debug like a Ninja



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

2.2.0-1 (src) -
2.2.0-1 (rockspec) -

2.2.0-1 (src) -
2.2.0-1 (rockspec) -

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, which contains a list of "Rocks" including one for  LuaSQL-Postgres. Use that URL as the --from=:
slitt@mydesk:~$ luarocks search luasql --from=

Search results:

Rockspecs and source rocks:

cvs-1 (rockspec) -
2.2.0-1 (src) -
2.2.0-1 (rockspec) -
2.2.0rc1-1 (rockspec) -

cvs-1 (rockspec) -

cvs-2 (rockspec) -

cvs-1 (rockspec) -
2.2.0rc1-1 (rockspec) -

cvs-1 (rockspec) -
2.2.0-1 (src) -
2.2.0-1 (rockspec) -
2.2.0rc1-1 (rockspec) -

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:
require "luasql.postgres"
envv = assert (luasql.postgres())
con = assert (envv:connect('mydb', 'myuid', 'mypass', "", 5432))


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]: ?
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:
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* LISTEN 14229/postgres
tcp 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
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 (localhost) and 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.
  1. 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.
  2. Log in to Linux as user postgres. All the following is much easier if you're logged into Linux as user postgres.
  3. 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.
  4. Run the command createdb -O myuid mydb. This creates the mydb database, owned by user myuid.
  5. psql -U postgres template1.
    1. 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.
  6. alter user myuid password 'mypass';
    1. 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.
  7. 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:
require "luasql.postgres"
envv = assert (luasql.postgres())
con = assert (envv:connect('mydb', 'myuid', 'mypass', "", 5433))

res = con:execute("DROP TABLE people")

res = assert (con:execute[[
id integer,
fname text,
lname text,
job text

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')"))

cur = assert (con:execute"SELECT * from people order by lname")

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.fname, row.lname, row.job))
row = cur:fetch (row, "a")

As expected, the preceding code produces the following output:
slitt@mydesk:~$ ./readwrite.lua 

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


 [| Code Corner | Email Steve Litt ]

Copyright (C) 2011 by Steve Litt --Legal