Any good wrapper for query of lua-resty-mysql?

xiangnanscu · 2016-08-03T17:02:05+00:00

I haven't done any formal comparison, but the impact in our system is minimal, hardly noticeable. Note that our system handles 50...

Any good wrapper for query of lua-resty-mysql?

xiangnanscu

I want a simple api like `query(sql_statement,rows)` that does the things like connect, set_keepalive and close of the connection in the background. Currently I write a module like this:

local client = require"resty.mysql"

local CONNECT_TABLE = {host = "127.0.0.1", port = 3306, 
        database = "test", user = 'root', password = '', }
local CONNECT_TIMEOUT = 1000
local IDLE_TIMEOUT = 10000
local POOL_SIZE = 800

return function(statement, rows)
    local db, err = client:new()
    if not db then
        return nil, err
    end
    db:set_timeout(CONNECT_TIMEOUT) 
    res, err, errno, sqlstate = db:connect(CONNECT_TABLE)
    if not res then
        return res, err, errno, sqlstate
    end
    res, err, errno, sqlstate =  db:query(statement, rows)
    if res ~= nil then
        local ok, err = db:set_keepalive(IDLE_TIMEOUT, POOL_SIZE)
        if not ok then
            return nil, err
        end
    end
    return res, err, errno, sqlstate
end

and use it like this:

local query = require"resty.query"
local res, err = query"SELECT * FROM users;"
local res2, err2 = query"SELECT * FROM blogs;"

it works, but I know it's not good because if I'm making many legal query(i.e. the `res` is not nil) in the same context (e.g. the same view in a MVC framework), `query` will invoke many unnecessary `db:connect` and `db:set_keepalive` calls. So, is it possible to design an api that meets the following needs:

1. wrapped as a module which returns a function or a callable table.

2. In the same context, if a query is legal, the connection will be reused in the next time other than put back to the pool. if not legal, when the function is called again, it will create a new connection just like being called at the first time.

3.automatically do the `set_keepalive` or `close` things. this is the hardest part I think.

I tried this:

local client = require"resty.mysql"

local CONNECT_TABLE = {host = "127.0.0.1", port = 3306, 
        database = "test", user = 'root', password = '', }
local CONNECT_TIMEOUT = 1000
local IDLE_TIMEOUT = 10000
local POOL_SIZE = 800

local function make_query_function()
    local db, err, res, errno, sqlstate;
    local function query(statement, rows, keepalive)
        -- first check any db to be used, create one if none
        if db == nil then 
            db, err = client:new()
            if not db then
                return nil, err
            end
            db:set_timeout(CONNECT_TIMEOUT) 
            res, err, errno, sqlstate = db:connect(CONNECT_TABLE)
            if not res then
                db = nil
                return nil, err, errno, sqlstate
            end
        end
        -- send the statement
        res, err, errno, sqlstate =  db:query(statement, rows)
        if res then
            -- legal result
            if keepalive then
                local ok, err = db:set_keepalive(IDLE_TIMEOUT, POOL_SIZE)
                if not ok then
                    return nil, err
                else
                    -- put the db back to the pool successfully
                    db = nil
                    return res, err, errno, sqlstate
                end
            else
                -- reserve the db for the next time
                return res, err, errno, sqlstate
            end
        else
            -- bad result, drop the db
            db = nil
            return nil, err, errno, sqlstate
        end
    end
    return query
end

return make_query_function

and use it like this:

local query = require"resty.query"()
local res, err = query"SELECT * FROM users;"
local res2, err2 = query"SELECT * FROM blogs;"
local res2, err2 = query("SELECT * FROM blogs;", 10, true) --last query statement, keepalive

but as you can see, I still need to pass `true` to the function to set_keepalive manually .

agentzh

Hello!

On Wed, Aug 3, 2016 at 2:02 AM, 项楠 wrote:
> and use it like this:
> local query = require"resty.query"()
> local res, err = query"SELECT * FROM users;"
> local res2, err2 = query"SELECT * FROM blogs;"
> local res2, err2 = query("SELECT * FROM blogs;", 10, true) --last query
> statement, keepalive
>
> but as you can see, I still need to pass `true` to the function to
> set_keepalive manually .
>

Well, you can always call set_keepalive in your query() function. It
can be more efficient because if you have some other I/O operations
between two successive query() calls in a single Lua handler, other
request handlers have a chance to reuse the MySQL connection in the
pool without creating a new one.

BTW, it's more efficient to concatenate multiple select statements in
a single MySQL query since it can save the round-trips between your
nginx and mysqld.

Regards,
-agentzh

xiangnanscu

Thanks, chun ge.

在 2016年8月6日星期六 UTC+8上午5:53:01，agentzh写道：

Hello!

On Wed, Aug 3, 2016 at 2:02 AM, 项楠 wrote:
> and use it like this:
> local query = require"resty.query"()
> local res, err = query"SELECT * FROM users;"
> local res2, err2 = query"SELECT * FROM blogs;"
> local res2, err2 = query("SELECT * FROM blogs;", 10, true) --last query
> statement, keepalive
>
> but as you can see, I still need to pass `true` to the function to
> set_keepalive manually .
>

Well, you can always call set_keepalive in your query() function. It
can be more efficient because if you have some other I/O operations
between two successive query() calls in a single Lua handler, other
request handlers have a chance to reuse the MySQL connection in the
pool without creating a new one.

BTW, it's more efficient to concatenate multiple select statements in
a single MySQL query since it can save the round-trips between your
nginx and mysqld.

Regards,
-agentzh