Intro to Lua and Openresty, Part 8: Bulk Retrieval from Postgres
Posted on March 8, 2017
In Part 7 of this series, we explored restricting access by HTTP method in lua.
In this post, we will use lua to read from the database. It’s one of the last of the basic capabilities we need to know how to do for our demo.
If you are following along in the code, we are here.
GET to retrieve JSON msg from postgres
For a GET to /list, the app should retrieve the top 100 entries in the posts table in postgres.
The web app is pretty simple:
- if GET:
- connect to the database
- run a query to select the TOP entries
- return the results as JSON in the response
- else, access denied
Here it is:
worker_processes 1;
env DB_HOST;
env DB_USER;
env DB_PASS;
env DB_NAME;
error_log error.log;
events {
worker_connections 1024;
}
http {
server {
listen 8000;
charset utf-8;
charset_types application/json;
default_type application/json;
location /list {
content_by_lua '
local cjson = require "cjson"
local http_method = ngx.var.request_method
if http_method == "GET" then
local pgmoon = require "pgmoon"
local pg = pgmoon.new({
host = os.getenv("DB_HOST"),
port = "5432",
user = os.getenv("DB_USER"),
password = os.getenv("DB_PASS"),
database = os.getenv("DB_NAME")
})
assert(pg:connect())
local get, post, files = require "resty.reqargs"()
top = pg:query("SELECT data FROM posts ORDER BY id DESC LIMIT 100;")
pg:keepalive()
pg = nil
ngx.status = ngx.HTTP_OK
ngx.say(cjson.encode({ msg = top }))
return ngx.exit(ngx.HTTP_OK)
else
ngx.status = ngx.HTTP_NOT_ALLOWED
ngx.say(cjson.encode({method = http_method , status = "denied"}))
return ngx.exit(ngx.HTTP_NOT_ALLOWED)
end
';
}
}
}The producer.lua
If we are going to retrieve a bunch of messages from the database, we first need to populate it with some data. We have producer.lua to do that:
local cjson = require "cjson"
local pgmoon = require "pgmoon"
local pg = pgmoon.new({
host = os.getenv("DB_HOST"),
port = "5432",
user = os.getenv("DB_USER"),
password = os.getenv("DB_PASS"),
database = os.getenv("DB_NAME")
})
assert(pg:connect())
local encode_json = require("pgmoon.json").encode_json
write = function(data)
assert(pg:query("INSERT INTO posts (data) VALUES(" .. encode_json(data) .. ");"))
print(data)
end
--
-- MAIN
for l=1, 500
do
print("enqueue: " .. l)
date_fmt = "%m-%d-%Y--%H-%M-%S"
write(cjson.encode({timestamp = os.date(date_fmt), msg = "hi! this is " .. l}))
l = l + 1
endThe Makefile
build:
docker build --tag=db:9 --rm=true ./db
docker build --tag=app:9 --rm=true ./app
run:
docker run -d --name db --net host -p 127.0.0.1:5342:5432 db:9
docker run -d --name app --net host -p 127.0.0.1:8000:8000 app:9
dev:
docker run -d --name db --net host -p 127.0.0.1:5342:5432 db:9
docker run -d --name app --net host -p 127.0.0.1:8000:8000 -v `pwd`/app/producer.lua:/src/producer.lua -v `pwd`/app/nginx.conf:/usr/local/openresty/nginx/conf/nginx.conf app:9
shell:
docker exec -it app /bin/sh
clean:
docker stop db || true
docker stop app || true
docker rm db || true
docker rm app || true
reload:
docker exec -it app /usr/local/openresty/nginx/sbin/nginx -s reload
logs:
docker exec -it app tail -f /usr/local/openresty/nginx/error.log
cat-posts:
docker exec -it db psql -U postgres -d lua-app -c 'SELECT * FROM posts;'
load-pg:
docker exec app /usr/local/openresty/luajit/bin/luajit /src/producer.lua
test:
curl -i -H "Content-Type: application/json" -X POST -d '{"username":"xyz","password":"xyz"}' localhost:8000/list
curl -i -H "Content-Type: application/json" localhost:8000/listApp Dockerfile
This has both the lua web app as well as producer.lua stand-alone script, so either “role” can be run from the same image (for simplicity of demonstration):
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
RUN /usr/local/openresty/luajit/bin/luarocks install lua-cjson
RUN /usr/local/openresty/luajit/bin/luarocks install luasocket
ADD nginx.conf /usr/local/openresty/nginx/conf/nginx.conf
ADD producer.lua /src/
ENV DB_HOST 127.0.0.1
ENV DB_USER postgres
ENV DB_PASS password
ENV DB_NAME lua-appThe Database
FROM postgres:alpine
ENV POSTGRES_PASSWORD password
ENV POSTGRES_DB lua-app
ADD init.sql /docker-entrypoint-initdb.d/…and the db’s init.sql:
CREATE TABLE posts (ID SERIAL PRIMARY KEY, data JSONB);Build the Docker images
ᐅ make build
docker build --tag=db:9 --rm=true ./db
Sending build context to Docker daemon 3.072 kB
Step 1 : FROM postgres:alpine
---> f0476a087b97
Step 2 : ENV POSTGRES_PASSWORD password
---> Using cache
---> 30d766415bf6
Step 3 : ENV POSTGRES_DB lua-app
---> Using cache
---> 449a34987810
Step 4 : ADD init.sql /docker-entrypoint-initdb.d/
---> Using cache
---> 7ac365dda47a
Successfully built 7ac365dda47a
docker build --tag=app:9 --rm=true ./app
Sending build context to Docker daemon 18.43 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
---> Using cache
---> effd23d59e55
Step 4 : RUN /usr/local/openresty/luajit/bin/luarocks install lua-resty-reqargs
---> Using cache
---> 0cde38c767ed
Step 5 : RUN /usr/local/openresty/luajit/bin/luarocks install lua-cjson
---> Using cache
---> e36fd7404d14
Step 6 : RUN /usr/local/openresty/luajit/bin/luarocks install luasocket
---> Using cache
---> c637b4563598
Step 7 : ADD nginx.conf /usr/local/openresty/nginx/conf/nginx.conf
---> Using cache
---> 9cdebc902eb9
Step 8 : ADD producer.lua /src/
---> Using cache
---> bed09e7cc06e
Step 9 : ENV DB_HOST 127.0.0.1
---> Using cache
---> c8bd96c15c01
Step 10 : ENV DB_USER postgres
---> Using cache
---> bab0419316af
Step 11 : ENV DB_PASS password
---> Using cache
---> 6260be3616d9
Step 12 : ENV DB_NAME lua-app
---> Using cache
---> 806f38e79387
Successfully built 806f38e79387Run the stack
ᐅ make run
docker run -d --name db --net host -p 127.0.0.1:5342:5432 db:9
7ba8470ae9b3d42007831c957d7e075bcfa3d598ce0f4107e41a1d467023d119
docker run -d --name app --net host -p 127.0.0.1:8000:8000 app:9
3349b9bb44acbf6dbd456d5e66048072708e8a82f1a30e4a9e43bf464ade0416Load up the database with some posts
ᐅ make load-pg
...
enqueue: 497
{"timestamp":"03-05-2017--06-07-02","msg":"hi! this is 497"}
enqueue: 498
{"timestamp":"03-05-2017--06-07-02","msg":"hi! this is 498"}
enqueue: 499
{"timestamp":"03-05-2017--06-07-02","msg":"hi! this is 499"}
enqueue: 500
{"timestamp":"03-05-2017--06-07-02","msg":"hi! this is 500"}Test query!
ᐅ make test | less
Date: Sun, 05 Mar 2017 06:07:51 GMT
Content-Type: application/json; charset=utf-8
Transfer-Encoding: chunked
Connection: keep-alive
{"status":"denied","method":"POST"}
curl -i -H "Content-Type: application/json" localhost:8000/list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
HTTP/1.1 200 OK 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
Server: openresty/1.11.2.2
Date: Sun, 05 Mar 2017 06:07:51 GMT
Content-Type: application/json; charset=utf-8
Transfer-Encoding: chunked
Connection: keep-alive
{"msg":[{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 500\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 499\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 498\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 497\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 496\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 495\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 494\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 493\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 492\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 491\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 490\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 489\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 488\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 487\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 486\"}"},{"data":"{\"timestamp\":\"03-05-2017--06-07-02\",\"msg\":\"hi! this is 485\"}"}
...OK, I think we’re ready to put together a demo for the original task!
Time Tracking: since last check: 8 hours; total: 15 hours
