Simple ORM for openresty
This library is NOT production ready.
local orm = require'orm'.open{
driver = 'mysql', -- or 'postgresql'
port = 3306,
host = '127.0.0.1',
user = 'root',
password = '123456',
database = 'test',
charset = 'utf8mb4',
expires = 100, -- cache expires time
debug = true -- log sql with ngx.log
}
Create sql expression which expression
and the rest params will be formated:
orm.expr('MAX([id])') -- MAX(`id`)
orm.expr('MAX(?d)', 10) -- MAX(10)
?t
table {1,2,'a'} => 1,2,'a'?b
bool(0, 1), only false or nil will be converted to 0 for mysql, TRUE | FALSE in postgresql?e
expression: MAX(id) | MIN(id) ...?d
digit number, convert by tonumber?n
NULL, false and nil wil be converted to 'NULL', orther 'NOT NULL'?s
string, escaped by ngx.quote_sql_str?
any, convert by guessing the value type
NOTICE: these modifiers can be used in where/having/join/select/expr methods
run fn
in transaction:
orm.transaction(function(conn)
User.new{ name = 'mow' }:save()
-- conn:commit()
conn:rollback()
end)
Notice: transaction can't be nested now. This should be fixed in near future.
Return a query builder instance, can now build select, update, insert, delete sql.
local sql = orm.create_query():from('table_name'):where('[id] = ?d', 99):one()
-- SELSECT * FROM table_name WHERE `id` = 99 LIMIT 1
query:from('table') -- SELECT * FROM table
query:from('[table]') -- SELECT * FROM `table`
query:from(another_query:from('user', 'u')) -- SELECT * FROM (SELECT * FROM user) AS u
query:select('t1, t2, [t3], proc(?d)', 10) -- SELECT t1, t2, `t3`, proc(10) ...
query:where('id = ?d or [key] like ?s', '10', '"lua-%-orm"') -- WHERE id = 10 or `key` like '\"lua-%-orm\"'
query:where('id in (?t)', 1) -- WHERE id in (1)
query:where('id in (?t)', {1, 2, 'a'}) --WHERE id in (1,2,'a')
-- ?t can be ? if don't know type of param
just like where
JOIN tbl
ON cond
, ...
params will be used in cond
Accept multiple group by
| order_by
expressions
limit for select sql
offset for select sql
Set alias for select
type sql.
Used in the UPDATE tbl SET ...
sql.
Used in the INSERT INTO tbl (...) VALUES (...)
Set the query type, tbl
param is optional, which can also be setted by from
method.
SELECT * FROM tbl WHERE id=1 FOR UPDATE
Return the sql string
Send query to database , returning (status, results)
define_model
accept table name as paramater and cache table fields in lrucache.
NOTICE: the table must have an auto increment column as its primary key
METHODS:
-
Model.new([attributes]) create new instance
-
Model.query() same as orm.create_query():from(Model.table_name())
-
Model.find() same as query(), but return Model instance
-
Model.find_one(cond, ...) find one record by condition
-
Model.find_all(cond, ...) find all records by condition
-
Model.update_where(attributes, cond, ...) update records filter by condition
-
Model.delete_where(cond, ...) delete records filter by condition
-
model:save() save the record, if pk is not nil then
update()
will be called, otherwiseinsert()
will be called -
model:load(attributes) load attributes to instance
-
model:set_dirty(attribute) make attribute dirty ( will be updated to database )
-
model:is_new() return if this instance is new or load from database
This method define a model:
local User = orm.define_model('tbl_user')
-- events
function User:on_before_save()
if self:is_new() then
self.create_at = os.time()
end
return true
end
function User:on_after_find()
if self.name = ngx.null then
self.name = 'UNNAMED'
end
end
-- build query from User
User.query() -- eq to orm.create_query():from('tbl_user')
-- fetch
-- SELECT * FROM tbl_user WHERE id > 1 LIMIT 10
local ok, users = User.find():where('id > 1'):limit(10)() -- notice the ()
-- SELECT * FROM tbl_user WHERE id > 10
local ok, users = User.find_all('id > ?d', 10)
if ok then
for _, u in ipairs(users) do
print(user.name)
end
end
-- SELECT * FROM tbl_user WHERE id = 10 LIMIT 1
local ok, user = User.find_one('id = 10')
if ok then
user.name = 'new name'
local ok, res = user:save() -- update user
end
-- UPDATE tbl_user SET name='name updated' WHERE id > 10
local attrs = { name = 'name updated' }
User.update_where(attrs, 'id > ?', 10)
-- DELETE FROM tbl_user WHERE id = 10
User.delete_where('id = ?', 10) --delete all by condition
user:delete() -- delete user instance
-- create new
local attrs = { name = 'new one' }
local user = User.new(attrs)
user:save()
local user = User.new()
user:load(attrs) -- same as User.new(attrs)
- [model] event (after_find, before_save & etc)
- [model] attributes validation