OpenResty 中使用 pgmoon 连接 PostgreSQL

项目介绍

pgmoon是纯Lua语言开发的PostgreSQL客户端驱动。旨在利用OpenResty的cosocket api提供异步查询

开源地址：https://github.com/leafo/pgmoon

快速入门

安装依赖

luarocks install pgmoon

代码示例

local pgmoon = require("pgmoon")
local pg = pgmoon.new({
  host = "127.0.0.1",
  port = "5432",
  database = "z-blog",
  user = "fendoudebb"
})

local success, err = pg:connect()
if err then
    ngx.log(ngx.ERR, 'connect pg error#', err)
    ngx.exit(ngx.HTTP_INTERNAL_SERVER_ERROR)
end

local res = pg:query("select * from users where username = " .. pg:escape_literal("fendoudebb"))

pg:keepalive()

常用方法

success, err = pg:connect()

连接PostgreSQL，成功返回true，失败返回nil并且附带错误信息。

可在OpenResty配置文件中统一设置socket连接超时时间，如：lua_socket_connect_timeout 5s;。

pg:settimeout(time)

为连接、写、接收设置超时时间（毫秒值），没有返回值。

success, err = pg:disconnect()

关闭一个已经打开的连接。这个方法的代码后面除了再次调用connect方法，不应该调用其它方法。

success, err = pg:keepalive(...)

将连接放入OpenResty连接池中复用，此方法传入的参数将传递给OpenResty的[setkeepalive](https://github.com/openresty/lua-nginx-module#tcpsocksetkeepalive)方法。

result, num_queries = pg:query(sql)

发送sql到PostgreSQL执行。失败返回nil和错误信息，成功返回的结果依赖于查询条件。

escaped = pg:escape_literal(val)

防止SQL注入攻击，转义Lua相关的值。

escaped = pg:escape_identifier(val)

当名称与内置语言关键字冲突时，需要使用标识符转义。转义Lua用作PostgreSQL的标识符，包括表名、列名，不包括常规值。常规值转义应使用escape_literal。

escape_literal与escape_identifier其他比较

当需要转义的变量是字符串时

local text = '127.0.0.1'
local escaped1 = pg:escape_literal(text)
local escaped2 = pg:escape_identifier(text)
ngx.log(ngx.ERR, text, '---', escaped1, '---', escaped2)

输出

127.0.0.1---'127.0.0.1'---"127.0.0.1"

当需要转义的变量是数字时

local num= 333
local escaped3 = pg:escape_literal(num)
local escaped4 = pg:escape_identifier(num)
ngx.log(ngx.ERR, num, '---', escaped3, '---', escaped4)

输出

127.0.0.1---'127.0.0.1'---"127.0.0.1"

简要示例

多语句查询

local res, num_queries = pg:query([[
  select id, name from users;
  select id, title from posts
]])

返回

num_queries = 2

res = {
  {
    {
      id = 123,
      name = "abc"
    },
    {
      id = 234,
      name = "def"
    }
  },
  {
    {
      id = 546,
      title = "My first post"
    }
  }
}

删除delete

local res = pg:query("delete from users")

返回

{
  affected_rows = 2
}

处理数组

local pgmoon = require("pgmoon")
local pg = pgmoon.new(auth)
pg:connect()

local encode_array = require("pgmoon.arrays").encode_array
local my_array = {1,2,3,4,5}
pg:query("insert into some_table (some_arr_col) values(" .. encode_array(my_array) .. ")")

处理JSON

local pgmoon = require("pgmoon")
local pg = pgmoon.new(auth)
pg:connect()

local encode_json = require("pgmoon.json").encode_json
local my_tbl = {hello = "world"}
pg:query("insert into some_table (some_json_col) values(" .. encode_json(my_tbl) .. ")")

处理hstore

local pgmoon = require("pgmoon")
local pg = pgmoon.new(auth)
pg:connect()
pg:setup_hstore()

存放hstore-编码

local encode_hstore = require("pgmoon.hstore").encode_hstore
local tbl = {foo = "bar"}
pg:query("insert into some_table (hstore_col) values(" .. encode_hstore(tbl) .. ")")

读取hstore-解码

local decode_hstore = require("pgmoon.hstore").decode_hstore
local res = pg:query("select * from some_table")
local hstore_tbl = decode_hstore(res[1].hstore_col)

开源案例

https://github.com/fendoudebb/z-blog-openresty