Intro to Lua and Openresty, Part 3: Writing Data to Postgres

Posted on March 4, 2017

In Part 2 of this series we used Lua and Openresty to process JSON in a POST request. The example was minimal (we simply echoed the POST data to the response with no change), but we expanded on our initial foundation with some key points for the demo:

handle a POST request with Openresty
capture and process the JSON data included with that request
install a lua module, build a docker image, and expand the Makefile

Here in Part 3 we will build on that with a few additions:

setup the connection to postgres (from lua in nginx)
write the JSON POST data to a table in postgres, before the echo response
update the echo to write our own JSON message, including the POST JSON in our message (as a sub-key).
explore the lua / postgres relationship

Where’s the Code?

If you are following along in the code, we’re at 04-write-to-postgres.

First Goal: setup and run Postgres

If our primary task is to connect and write some data to Postgres, we need to run and setup Postgres in a way that nginx can connect to it.

We’re going to need a second container for this, to run postgres. Let’s map out a few requirements for this aspect of the system:

use an existing base image with postgres installed
the image should be smart enough to “init” its database when needed
we need to setup authentication, and nginx should be able to connect

There is the postgres:alpine variant of the canonical image, we’ll use that as our base. The postgres image will auto-run *.sh or *.sql that are in the /init path in the image - we’ll rely on this to auto-create the tables (and in future exercises, to load initial data fixtures) for us. The postgres images will use POSTGRES_PASSWORD, POSTGRES_DB, and similar environment variables.

Given that, our Dockerfile will be fairly simple:

FROM postgres:alpine
ENV  POSTGRES_PASSWORD password
ENV  POSTGRES_DB       lua-app
ADD  init.sql /docker-entrypoint-initdb.d/
RUN  echo 'hosts: files mdns4_minimal [NOTFOUND=return] dns mdns4' > /etc/nsswitch.conf

The webapp in this example only needs a single, empty table, so init.sql is also very simple:

CREATE TABLE posts (ID SERIAL PRIMARY KEY, data JSONB);

We can confirm this works by building and running the db image, then peeking into the database to confirm we have a posts table:

ᐅ docker exec -it db psql -U postgres -d lua-app
psql (9.6.2)
Type "help" for help.

lua-app=# \dt
         List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | posts | table | postgres
(1 row)

lua-app=# ^D\q

Yay! There is the posts table.

Second Goal: Get lua connected to the database

As in any other language, connecting lua to Postgres is best with some module or library to handle the details of the connection (and submitting queries/etc). After researching the topic a little, I decided I would test out pgmoon first.

There are a few ways to carry out the details, but it’ll all look similar and likely use the new(), pg:connect(), pg:query(), and json.encode_json() functions. Here is the nginx.conf I ended up with:

worker_processes  1;
error_log error.log;
events {
    worker_connections 1024;
}
http {
    server {
        listen        8000;
        charset       utf-8;
        charset_types application/json;
        default_type  application/json;
        location / {
            content_by_lua '
                local cjson = require "cjson"
                local pgmoon = require("pgmoon")
                local pg = pgmoon.new({
                  host     = "127.0.0.1",
                  port     = "5432",
                  user     = "postgres",
                  password = "password",
                  database = "lua-app"
                })
                assert(pg:connect())
                local encode_json = require("pgmoon.json").encode_json
                local get, post, files = require "resty.reqargs"()
                assert(pg:query("INSERT INTO posts (data) VALUES(" .. encode_json(post) .. ");"))
                pg:keepalive()
                pg = nil
                ngx.status  = ngx.HTTP_OK
                ngx.say(cjson.encode({status = "saved", msg=post}))
                return ngx.exit(ngx.HTTP_OK)
            ';
        }
    }
}

We will also need to install the pgmoon module. As we did with the lua-resty-reqargs module in the previous example, this goes into our Dockerfile:

FROM openresty/openresty:alpine-fat

EXPOSE 8000
RUN /usr/local/openresty/luajit/bin/luarocks install pgmoon
RUN /usr/local/openresty/luajit/bin/luarocks install lua-resty-reqargs
ADD nginx.conf /usr/local/openresty/nginx/conf/nginx.conf
RUN echo 'hosts: files mdns4_minimal [NOTFOUND=return] dns mdns4' > /etc/nsswitch.conf

Build and Run!

Here are the updates made to the Makefile:

build:
        docker build --tag=db  --rm=true ./db
        docker build --tag=app --rm=true ./app

run:
        docker run -d --name db  --net host -p 127.0.0.1:5342:5432 db
        docker run -d --name app --net host -p 127.0.0.1:8000:8000 app

clean:
        docker stop db  || true
        docker stop app || true
        docker rm   db  || true
        docker rm   app || true

cat-posts:
        docker exec -it db psql -U postgres -d lua-app -c 'SELECT * FROM posts;'

...

Create the db and app Docker images with make build:

ᐅ make build
docker build --tag=db  --rm=true ./db
Sending build context to Docker daemon 15.87 kB
Step 1 : FROM postgres:alpine
 ---> f0476a087b97
Step 2 : ENV POSTGRES_PASSWORD password
 ---> Running in 08b1c7802a49
 ---> 5324840fb862
Removing intermediate container 08b1c7802a49
Step 3 : ENV POSTGRES_DB lua-app
 ---> Running in 52f3111e0f3f
 ---> 126f7a77f9eb
Removing intermediate container 52f3111e0f3f
Step 4 : ADD init.sql /docker-entrypoint-initdb.d/
 ---> d1294db462c4
Removing intermediate container ae40bad2713a
Step 5 : RUN echo 'hosts: files mdns4_minimal [NOTFOUND=return] dns mdns4' > /etc/nsswitch.conf
 ---> Running in 1b4276876287
 ---> e18cb105e24c
Removing intermediate container 1b4276876287
Successfully built e18cb105e24c
docker build --tag=app --rm=true ./app
Sending build context to Docker daemon 18.94 kB
Step 1 : FROM openresty/openresty:alpine-fat
 ---> 366babf2b04d
Step 2 : EXPOSE 8000
 ---> Using cache
 ---> 35a8c6e42825
Step 3 : RUN /usr/local/openresty/luajit/bin/luarocks install pgmoon
 ---> Running in 0e09ca9916e8
Installing https://luarocks.org/pgmoon-1.8.0-1.src.rock...

Missing dependencies for pgmoon:
lpeg

Using https://luarocks.org/pgmoon-1.8.0-1.src.rock... switching to 'build' mode
Using https://luarocks.org/lpeg-1.0.1-1.src.rock... switching to 'build' mode
gcc -O2 -fPIC -I/usr/local/openresty/luajit/include/luajit-2.1 -c lpcap.c -o lpcap.o
gcc -O2 -fPIC -I/usr/local/openresty/luajit/include/luajit-2.1 -c lpcode.c -o lpcode.o
gcc -O2 -fPIC -I/usr/local/openresty/luajit/include/luajit-2.1 -c lpprint.c -o lpprint.o
gcc -O2 -fPIC -I/usr/local/openresty/luajit/include/luajit-2.1 -c lptree.c -o lptree.o
gcc -O2 -fPIC -I/usr/local/openresty/luajit/include/luajit-2.1 -c lpvm.c -o lpvm.o
gcc -shared -o lpeg.so -L/usr/local/openresty/luajit/lib lpcap.o lpcode.o lpprint.o lptree.o lpvm.o
No existing manifest. Attempting to rebuild...
Updating manifest for /usr/local/openresty/luajit/lib/luarocks/rocks
lpeg 1.0.1-1 is now built and installed in /usr/local/openresty/luajit (license: MIT/X11)

Updating manifest for /usr/local/openresty/luajit/lib/luarocks/rocks
pgmoon 1.8.0-1 is now built and installed in /usr/local/openresty/luajit (license: MIT)

 ---> 1373c0014b0f
Removing intermediate container 0e09ca9916e8
Step 4 : RUN /usr/local/openresty/luajit/bin/luarocks install lua-resty-reqargs
 ---> Running in 22f051a9d6d4
Installing https://luarocks.org/lua-resty-reqargs-1.4-1.src.rock...
Using https://luarocks.org/lua-resty-reqargs-1.4-1.src.rock... switching to 'build' mode
Updating manifest for /usr/local/openresty/luajit/lib/luarocks/rocks
lua-resty-reqargs 1.4-1 is now built and installed in /usr/local/openresty/luajit (license: BSD)

 ---> 999ab5e6968c
Removing intermediate container 22f051a9d6d4
Step 5 : ADD nginx.conf /usr/local/openresty/nginx/conf/nginx.conf
 ---> 03c148603990
Removing intermediate container c7628b45a991
Step 6 : RUN echo 'hosts: files mdns4_minimal [NOTFOUND=return] dns mdns4' > /etc/nsswitch.conf
 ---> Running in f777c48e88ee
 ---> 7a6c0f1dbc97
Removing intermediate container f777c48e88ee
Successfully built 7a6c0f1dbc97

Run the images as containers:

ᐅ make run
docker run -d --name db  --net host -p 127.0.0.1:5342:5432 db
433c5bb9f8287850b833a9ea95c79d738c544e677492de43e14ff42455958230
docker run -d --name app --net host -p 127.0.0.1:8000:8000 app
4f70737f053bec44a5538cb72bba4a2e59ebb4a7beb9231b748ed27d68bb5064

ᐅ docker ps
CONTAINER ID  IMAGE  COMMAND                 CREATED         STATUS         PORTS  NAMES
4f70737f053b  app    "/usr/local/openresty"  17 seconds ago  Up 17 seconds         app
433c5bb9f828  db     "docker-entrypoint.sh"  17 seconds ago  Up 17 seconds         db

Let’s send in some arbitrary JSON as a POST:

ᐅ curl -i -H "Content-Type: application/json" -X POST -d '{"username":"xyz","password":"xyz"}' localhost:8000/

HTTP/1.1 200 OK
Server: openresty/1.11.2.2
Date: Fri, 03 Mar 2017 14:07:50 GMT
Content-Type: text/html; charset=utf-8
Transfer-Encoding: chunked
Connection: keep-alive

{"status":"saved","msg":{"password":"xyz","username":"xyz"}}

Is that in the database?

ᐅ make cat-posts
docker exec -it db psql -U postgres -d lua-app -c 'SELECT * FROM posts;'
 id |                  data
----+----------------------------------------
  1 | {"password": "xyz", "username": "xyz"}
(1 row)

YAY!

In this example, we can post arbitrary JSON:

ᐅ curl -H "Content-Type: application/json" -X POST -d '{"some":"arbitrary","json": {"password":"xyz"}}' localhost:8000/

{"status":"saved","msg":{"some":"arbitrary","json":{"password":"xyz"}}}

ᐅ make cat-posts
docker exec -it db psql -U postgres -d lua-app -c 'SELECT * FROM posts;'
 id |                        data
----+----------------------------------------------------
  1 | {"password": "xyz", "username": "xyz"}
  2 | {"json": {"password": "xyz"}, "some": "arbitrary"}
(2 rows)

If we give it invalid JSON, the situation is handled gracefully:

ᐅ curl -H "Content-Type: application/json" -X POST -d '{"invalid": "json"' localhost:8000/

{"status":"saved","msg":{}}

However, with the example as it is now, the empty JSON object is written to the database:

ᐅ make cat-posts
docker exec -it db psql -U postgres -d lua-app -c 'SELECT * FROM posts;'
 id |                        data
----+----------------------------------------------------
  1 | {"password": "xyz", "username": "xyz"}
  2 | {"json": {"password": "xyz"}, "some": "arbitrary"}
  3 | {}
(3 rows)

Obviously, one would want to filter out those empty/invalid requests and respond with a more appropriate message and status code. I’m going to skip over making those updates for now.

Bonus: Debug connection issues with `pgmoon`

While working on the exercise above, I ran into trouble connecting the lua app to the database:

worker_processes  1;
error_log error.log;
events {
    worker_connections 1024;
}
http {
    server {
        listen        8000;
        charset       utf-8;
        charset_types application/json;
        default_type  application/json;
        location / {
            default_type text/html;
            content_by_lua '
                local cjson = require "cjson"
                local pgmoon = require("pgmoon")
                local pg = pgmoon.new({
                  host     = "db",
                  port     = "5432",
                  user     = "postgres",
                  password = "password",
                  database = "lua-app",
                })
                pg:connect()
                local encode_json = require("pgmoon.json").encode_json
                local get, post, files = require "resty.reqargs"()
                assert(pg:query("INSERT INTO posts (msg) VALUES(" .. encode_json(post) .. ")"))
                ngx.status  = ngx.HTTP_OK
                ngx.say(cjson.encode({status = "saved", msg=post}))
                return ngx.exit(ngx.HTTP_OK)
            ';
        }
    }
}

would fail with…

2017/03/03 11:10:16 [error] 49#49: *10 attempt to send data on a closed socket: u:0000000041B484A0, c:0000000000000000, ft:8 eof:0, client: 127.0.0.1, server: , request: "POST / HTTP/1.1", host: "localhost:8000"
2017/03/03 11:10:16 [error] 49#49: *10 attempt to receive data on a closed socket: u:0000000041B484A0, c:0000000000000000, ft:8 eof:0, client: 127.0.0.1, server: , request: "POST / HTTP/1.1", host: "localhost:8000"
2017/03/03 11:10:16 [error] 49#49: *10 lua entry thread aborted: runtime error: content_by_lua(nginx.conf:33):14: receive_message: failed to get type: closed
stack traceback:
coroutine 0:
        [C]: in function 'assert'
        content_by_lua(nginx.conf:33):14: in function <content_by_lua(nginx.conf:33):1>, client: 127.0.0.1, server: , request: "POST / HTTP/1.1", host: "localhost:8000"
^Cmake: [logs] Error 130

Woah, that’s a little confusing! While it does make sense when you know about the details, it took me some hacking around with the code and env in the docker containers to figure out what was going on. Short explanation: the connect is failing, returns nil, and the query is attempted on a socket that is not connected to postgres.

It’d be nice if the error were more clear, and we can help make that happen by wrapping the pg:connect() with assert(), to end up with: assert(pg:connect()). Now the error will be like:

2017/03/03 00:19:29 [error] 88#88: *21 lua entry thread aborted: runtime error: content_by_lua(nginx.conf:33):11: no resolver defined to resolve "db"
stack traceback:
coroutine 0:
        [C]: in function 'assert'
        content_by_lua(nginx.conf:33):11: in function <content_by_lua(nginx.conf:33):1>, client: 172.17.0.1, server: , request: "POST / HTTP/1.1", host: "localhost:8000"

That’s a lot more specific, and gets us closer to the actual issue: the lua script is “unable to find (resolve the hostname) db”.

To look at what’s going on, let’s drop into the container and check out the connectivity between the app and db containers:

ᐅ docker exec -it app /bin/sh
/ # cat /etc/hosts
172.17.0.3      3751a3f850b9
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
172.17.0.2      db 0c0649632873
/ # ping db
PING db (172.17.0.2): 56 data bytes
64 bytes from 172.17.0.2: seq=0 ttl=64 time=0.139 ms
64 bytes from 172.17.0.2: seq=1 ttl=64 time=0.098 ms
^C
--- db ping statistics ---
2 packets transmitted, 2 packets received, 0% packet loss
round-trip min/avg/max = 0.098/0.118/0.139 ms

OK, so the container linking works as expected, but lua errors with: no resolver defined to resolve "db". If I use the IP of the linked container, the connection works as expected. This is likely to be an issue with the (super minimal) alpine base image I am using (need some dns utils or some such).

With some research, I found that /etc/nsswitch.conf is missing in alpine, and glibc apps may skip consulting /etc/hosts (where db is defined when using --link in docker). Theoretically, adding hosts: files mdns4_minimal [NOTFOUND=return] dns mdns4 to /etc/nsswitch.conf will tell glibc apps to use /etc/hosts first. We can then add this to the docker image build (Dockerfile), so it is ready to go at runtime:

RUN echo 'hosts: files mdns4_minimal [NOTFOUND=return] dns mdns4' > /etc/nsswitch.conf

Here are some helpful resources on this topic:

http://unix.stackexchange.com/questions/52954/nsswitch-conf-versus-host-conf
https://github.com/docker-library/golang/pull/76/files
https://gitlab.com/gitlab-org/gitlab-ci-multi-runner/issues/2142

Updating /etc/nsswitch.conf hasn’t worked as expected. While I am sure I can dig deeper and resolve that issue, this is an issue I’m going to skip for now. My reasoning is that this is specific to alpine, which is an implementation detail in this exercise, I want to get back to the exercise, and I have a work-around I can use for now.

The work around is to use --net=host, bind on 127.0.0.1, and use that when connecting to the db. In a more “production quality” deployment, I would opt for using consul or similar for service discovery, and would update the alpine images to include consul for DNS lookups.

Time Tracking: since last check: 2.5 hours; total: 6 hours