Lapis - Lua YeSql .lua

local function loader(file)
  local f = io.open(file, "rb")
  assert(f, "file not found")
  local c = f:read("*all")
  f:close()
  return c
end

local function sqlType(params, sql)
  return {params = params, sql = sql}
end

local function getSqlParams(sql)
    local colon_reg = ":[a-zA-Z0-9_]+"
    local final_sql = sql
    local params = string.gmatch(sql, colon_reg)
    local final_params = {}
    for param in params do
        final_params[#final_params+1] = string.gsub(param, ":", "")
        final_sql = string.gsub(final_sql, param, "?")
    end
    return sqlType(final_params, final_sql)
end

local function splitter(text)
  local pieces = {}

  local i = 1
  repeat
    local f1s, f1e = string.find(text, "-- name:", i)
    local f2s, f2e = string.find(text, "-- name:", f1e)
    if f2s then
      f2s = f2s - 1
    end
    local piece = string.sub(text, i, f2s)
    local name, query = string.match(piece, "-- name:%s*([^%s]+)%s*\n%s*(.-)%s*$")

    pieces[name] = getSqlParams(query)

    i = f2s or #text
  until i >= #text

  return pieces
end

local function loadSql(filepath)
  return splitter(loader(filepath))
end

--[[
--- Description: Creates YeSql style queries
--  Usage:
--  SQL File:
--  -- name: getUser
--  SELECT * FROM user WHERE userid = :userid;
--
--  local queries = queriesFactory(sqlFilePathWithYeSqlStyle, db)
--  dbResult = queries( 'getUser', { userid = 1 } )
 ]]
local function queriesFactory(sqlFilePath, db)
  local sqls = loadSql(sqlFilePath)
  return function(queryName, reqParams)
    local sqlType = sqls[queryName]
    local params = {}
    for i, paramName in pairs(sqlType.params) do
        params[#params+1] = reqParams[paramName]
    end
    return db.query(sqlType.sql, unpack(params))
  end
end

return {
  loadSql = loadSql,
  queriesFactory = queriesFactory
}

SQL file contents example

-- name: getUser
SELECT * FROM user WHERE userid = :userid;

Lua code example:

local lapis = require("lapis")
local yesql require("yesql")
local db = require("lapis.db")
local app = lapis.Application()

-- db expects api of db.query as per Lapis
local queries = yesql.queriesFactory(sqlFilePathWithYeSqlStyle, db)

-- local dbResult = queries( 'getUser', { userid = 1 } )

app:match("/user/:userid", function(self)
    return {
        json = {
            success = true,
            user = queries( 'getUser', self.params )
        }
    }
end)