lua-resty-model

Openresty super postgresql orm finally comes out! Inspired by django orm. Support arbitrary depth of automatic join, group by, reversed lookup, F, Q, annotate, etc.

Install

opm get xiangnanscu/lua-resty-model

Synopsis

local Model = require("resty.model")
local Q = Model.Q
local F = Model.F
local Sum = Model.Sum
local Avg = Model.Avg
local Max = Model.Max
local Min = Model.Min
local Count = Model.Count


---@class Blog
local Blog = Model {
  table_name = 'blog',
  fields = {
    { "name",   maxlength = 100 },
    { "tagline" },
  }
}

-- define a structured json field from a abstract model
local Resume = Model:create_model {
  fields = {
    { "start_date",  type = 'date' },
    { "end_date",    type = 'date' },
    { "company",     maxlength = 200 },
    { "position",    maxlength = 200 },
    { "description", maxlength = 200 },
  }
}
---@class Author
local Author = Model {
  table_name = 'author',
  fields = {
    { "name",   maxlength = 200 },
    { "email",  type = 'email' },
    { "age",    type = 'integer' },
    { "resume", model = Resume },
  }
}

---@class Entry
local Entry = Model {
  table_name = 'entry',
  fields = {
    { 'blog_id',             reference = Blog, related_query_name = 'entry' },
    { 'reposted_blog_id',    reference = Blog, related_query_name = 'reposted_entry' },
    { "headline",            maxlength = 255 },
    { "body_text" },
    { "pub_date",            type = 'date' },
    { "mod_date",            type = 'date' },
    { "number_of_comments",  type = 'integer' },
    { "number_of_pingbacks", type = 'integer' },
    { "rating",              type = 'integer' },
  }
}

---@class ViewLog
local ViewLog = Model {
  table_name = 'view_log',
  fields = {
    { 'entry_id', reference = Entry },
    { "ctime",    type = 'datetime' },
  }
}

---@class Publisher
local Publisher = Model {
  table_name = 'publisher',
  fields = {
    { "name", maxlength = 300 },
  }
}

---@class Book
local Book = Model {
  table_name = 'book',
  fields = {
    { "name",         maxlength = 300 },
    { "pages",        type = 'integer' },
    { "price",        type = 'float' },
    { "rating",       type = 'float' },
    { "author",       reference = Author },
    { 'publisher_id', reference = Publisher },
    { "pubdate",      type = 'date' },
  }
}

---@class Store
local Store = Model {
  table_name = 'store',
  fields = {
    { "name", maxlength = 300 },
  }
}

Book:where { price = 100 }
Book:where { price__gt = 100 }
Book:where(-Q { price__gt = 100 })
Book:where(Q { price__gt = 100 } / Q { price__lt = 200 })
Book:where(-(Q { price__gt = 100 } / Q { price__lt = 200 }))
Book:where(Q { id = 1 } * (Q { price__gt = 100 } / Q { price__lt = 200 }))
Entry:where { blog_id = 1 }
Entry:where { blog_id__id = 1 }
Entry:where { blog_id__gt = 1 }
Entry:where { blog_id__id__gt = 1 }
Entry:where { blog_id__name = 'my blog name' }
Entry:where { blog_id__name__contains = 'my blog' }
ViewLog:where { entry_id__blog_id = 1 }
ViewLog:where { entry_id__blog_id__id = 1 }
ViewLog:where { entry_id__blog_id__name = 'my blog name' }
ViewLog:where { entry_id__blog_id__name__startswith = 'my' }
ViewLog:where { entry_id__blog_id__name__startswith = 'my', entry_id__headline = 'aa' }
-- reversed foreignkey
Blog:where { entry = 1 }
Blog:where { entry__id = 1 }
Blog:where { entry__rating = 1 }
Blog:where { entry__view_log = 1 }
Blog:where { entry__view_log__ctime__year = 2025 }
Blog:where(Q { entry__view_log = 1 } / Q { entry__view_log = 2 })
-- group by
Book:group_by { 'name' }:annotate { price_total = Sum('price') }
-- annotate + aggregate
Book:annotate { price_total = Sum('price') }
Book:annotate { Sum('price') }
-- annotate + aggregate + group by
Book:group_by { 'name' }:annotate { price_total = Sum('price') }
Book:group_by { 'name' }:annotate { Sum('price') }
-- annotate + aggregate + group by + having
Book:group_by { 'name' }:annotate { Sum('price') }:having { price_sum__gt = 100 }
Book:group_by { 'name' }:annotate { price_total = Sum('price') }:having { price_total__gt = 100 }
-- annotate + aggregate + group by + having + order by
Book:group_by { 'name' }:annotate { price_total = Sum('price') }:having { price_total__gt = 100 }:order_by { '-price_total' }
-- F expression
Book:annotate { double_price = F('price') * 2 }
Book:annotate { price_per_page = F('price') / F('pages') }
-- annotate  + reverse foreignkey
Blog:annotate { entry_count = Count('entry') }

-- update
Blog:update { name = F('name') .. ' updated' }
Entry:where { headline = F('blog_id__name') }
Entry:update { rating = F('rating') + 1 }
Entry:update { headline = F('blog_id__name') }
-- json field search
Author:where { resume__has_key = 'start_date' }
Author:where { resume__0__has_keys = { 'a', 'b' } }
Author:where { resume__has_any_keys = { 'a', 'b' } }
Author:where { resume__start_date__time = '12:00:00' }
Author:where { resume__contains = { start_date = '2025-01-01' } }
Author:where { resume__contained_by = { start_date = '2025-01-01' } }
-- select
ViewLog:where('entry_id__blog_id', 1)
ViewLog:where { entry_id__blog_id__gt = 1 }
Book:order_by('author', '-pubdate'):distinct('author')
Entry:increase('number_of_comments')
Entry:decrease('number_of_comments', 2)

Api Test

local Model = require("resty.model")


local Usr = Model:create_model {
  table_name = 'usr',
  fields = {
    { name = 'id',         type = 'integer', primary_key = true, serial = true },
    { name = 'username',   maxlength = 5,    required = true,    unique = true },
    { name = 'permission', type = 'integer', default = 0,        max = 5 },
  }
}

local Dept = Model:create_model {
  table_name = 'dept',
  { name = 'name', maxlength = 10, unique = true }
}

local Profile = Model:create_model {
  table_name = 'profile',
  { name = 'usr_id',    reference = Usr,  reference_column = 'id' },
  { name = 'dept_name', reference = Dept, reference_column = 'name' },
  { name = 'age',       required = true,  type = 'integer',         default = 0 },
  { name = 'sex',       default = 'f',    choices = { 'f', 'm' } },
  { name = 'salary',    type = 'float',   default = 1000 },

}

-- create with fields: id, utime and ctime
local Message = Model {
  table_name = 'message',
  { name = 'creator', reference = Profile, },
  { name = "target",  reference = Profile, },
  { name = 'content', maxlength = 100,     compact = false },
}

local Evaluate = Model {
  table_name = 'evaluate',
  unique_together = { 'usr_id', 'year' },
  { name = 'usr_id', reference = Usr, },
  { name = "year",   type = 'year', },
  { name = 'rank',   maxlength = 1,   default = 'C' },
}

local Log = Model:create_model {
  table_name = 'log',
  fields = {
    { name = 'id',         type = 'integer', primary_key = true, serial = true },
    { name = 'delete_id',  type = 'integer', default = 0 },
    { name = 'model_name', type = 'string',  maxlength = 20 },
    { name = 'action',     maxlength = 10, }
  }
}

local Log2 = Model:create_model {
  table_name = 'log2',
  fields = {
    { name = 'buyer',  reference = Usr, },
    { name = 'seller', reference = Usr, },
  }
}

local Log3 = Model:create_model {
  table_name = 'log3',
  fields = {
    { name = 'start_log', reference = Log2, },
    { name = 'end_log',   reference = Log2, },
  }
}

local TableModel = Model:create_model {
  { name = 'ages',  type = 'array', field = { type = 'integer', max = 2 } },
  { name = 'users', type = 'table', model = Usr }
}

Xodel:insert(rows:table|table[]|Sql, columns?:string[])

insert one user

 usr:insert{permission=1, username ='u1'}:exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u1', 1)

{
  affected_rows: 1,
}

ok 27 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user

insert one user returning one column

 usr:insert{permission=1, username ='u2'}:returning('permission'):exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u2', 1)
RETURNING
  T.permission

[
  {
    permission: 1,
  },
]

ok 28 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user returning one column

insert one user with default permission

 usr:insert{username ='u3'}:returning('permission'):exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u3', 0)
RETURNING
  T.permission

[
  {
    permission: 0,
  },
]

ok 29 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user with default permission

insert one user returning two columns

 usr:insert{permission=1, username ='u4'}:returning('permission','username'):exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u4', 1)
RETURNING
  T.permission,
  T.username

[
  {
    permission: 1,
    username  : "u4",
  },
]

ok 30 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user returning two columns

insert one user returning one column in compact form

 usr:insert{permission=1, username ='u5'}:returning('username'):compact():exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u5', 1)
RETURNING
  T.username

[
  [
    "u5",
  ],
]

ok 31 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user returning one column in compact form

insert two users

 usr:insert{{permission=1, username ='u6'}, {permission=1, username ='u7'}}:exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u6', 1),
  ('u7', 1)

{
  affected_rows: 2,
}

ok 32 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users

insert two users returning one column

 usr:insert{{permission=1, username ='u8'}, {permission=1, username ='u9'}}:returning('username'):exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u8', 1),
  ('u9', 1)
RETURNING
  T.username

[
  {
    username: "u8",
  },
  {
    username: "u9",
  },
]

ok 33 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users returning one column

insert two users returning two columns

 usr:insert{{permission=2, username ='u10'}, {permission=3, username ='u11'}}:returning('username','permission'):exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u10', 2),
  ('u11', 3)
RETURNING
  T.username,
  T.permission

[
  {
    permission: 2,
    username  : "u10",
  },
  {
    permission: 3,
    username  : "u11",
  },
]

ok 34 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users returning two columns

insert two users returning one column in flatten form

 usr:insert{{permission=1, username ='u12'}, {permission=1, username ='u13'}}:returning('username'):flat()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u12', 1),
  ('u13', 1)
RETURNING
  T.username

[
  "u12",
  "u13",
]

ok 35 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users returning one column in flatten form

insert two users returning two columns in flatten form

 usr:insert{{permission=1, username ='u14'}, {permission=2, username ='u15'}}:returning('username','permission'):flat()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u14', 1),
  ('u15', 2)
RETURNING
  T.username,
  T.permission

[
  "u14",
  1,
  "u15",
  2,
]

ok 36 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users returning two columns in flatten form

insert one user with specific columns (permission being ignored)

 usr:insert({permission=4, username ='u16'}, {'username'}):returning('username','permission'):exec()

INSERT INTO
  usr AS T (username)
VALUES
  ('u16')
RETURNING
  T.username,
  T.permission

[
  {
    permission: 0,
    username  : "u16",
  },
]

ok 37 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user with specific columns (permission being ignored)

insert one user with specific columns

 usr:insert({permission=4, username ='u17'}, {'username', 'permission'}):returning('username','permission'):exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u17', 4)
RETURNING
  T.username,
  T.permission

[
  {
    permission: 4,
    username  : "u17",
  },
]

ok 38 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user with specific columns

insert two users with specific columns (permission being ignored)

 usr:insert({{permission=4, username ='u18'},{permission=5, username ='u19'}}, {'username'}):returning('username','permission'):exec()

INSERT INTO
  usr AS T (username)
VALUES
  ('u18'),
  ('u19')
RETURNING
  T.username,
  T.permission

[
  {
    permission: 0,
    username  : "u18",
  },
  {
    permission: 0,
    username  : "u19",
  },
]

ok 39 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users with specific columns (permission being ignored)

insert two users with specific columns

 usr:insert({{permission=4, username ='u20'},{permission=5, username ='u21'}}, {'username', 'permission'}):returning('username','permission'):exec()

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('u20', 4),
  ('u21', 5)
RETURNING
  T.username,
  T.permission

[
  {
    permission: 4,
    username  : "u20",
  },
  {
    permission: 5,
    username  : "u21",
  },
]

ok 40 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users with specific columns

insert users with default permission

 usr:insert{{username ='f1'},{username ='f2'}}:flat('permission')

INSERT INTO
  usr AS T (username, permission)
VALUES
  ('f1', 0),
  ('f2', 0)
RETURNING
  T.permission

[
  0,
  0,
]

ok 41 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert users with default permission

insert one user validate required failed

ok 42 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user validate required failed

insert one user validate maxlength failed

ok 43 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user validate maxlength failed

insert one user validate max failed

ok 44 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert one user validate max failed

insert two users validate max failed

ok 45 - Xodel:insert(rows:table|table[]|Sql, columns?:string[]) insert two users validate max failed

Xodel:insert

create

dept:insert{name ='d1'}:returning('*'):execr()

INSERT INTO
  dept AS T (name)
VALUES
  ('d1')
RETURNING
  *

[
  {
    id  : 1,
    name: "d1",
  },
]

ok 46 - Xodel:insert create

create multiple rows

dept:insert{{name ='d2'}, {name ='d3'}}:returning('name'):execr()

INSERT INTO
  dept AS T (name)
VALUES
  ('d2'),
  ('d3')
RETURNING
  T.name

[
  {
    name: "d2",
  },
  {
    name: "d3",
  },
]

ok 47 - Xodel:insert create multiple rows

Xodel:count(cond?, op?, dval?)

specify condition

usr:count{id__lt=3}

SELECT
  count(*)
FROM
  usr T
WHERE
  T.id < 3

2

ok 48 - Xodel:count(cond?, op?, dval?) specify condition

test with Xodel:all

dept:execr()

SELECT
  *
FROM
  dept T

[
  {
    id  : 1,
    name: "d1",
  },
  {
    id  : 2,
    name: "d2",
  },
  {
    id  : 3,
    name: "d3",
  },
]

dept:count()

SELECT
  count(*)
FROM
  dept T

3

ok 49 - Xodel:count(cond?, op?, dval?) test with Xodel:all

XodelInstance:save(names?:string[], key?:string)

save basic

profile{usr_id=1, dept_name='d1', age=20}:save()

INSERT INTO
  profile AS T (salary, age, usr_id, dept_name, sex)
VALUES
  (1000, 20, 1, 'd1', 'f')
RETURNING
  *

{
  age      : 20,
  dept_name: "d1",
  id       : 1,
  salary   : 1000,
  sex      : "f",
  usr_id   : 1,
}

ok 50 - XodelInstance:save(names?:string[], key?:string) save basic

save with specific names

profile{usr_id=2, dept_name='d2', salary=500, sex='m', age=50}:save{'usr_id','dept_name'}

INSERT INTO
  profile AS T (usr_id, dept_name)
VALUES
  (2, 'd2')
RETURNING
  *

{
  age      : 0,
  dept_name: "d2",
  id       : 2,
  salary   : 1000,
  sex      : "f",
  usr_id   : 2,
}

ok 51 - XodelInstance:save(names?:string[], key?:string) save with specific names

save with primary key specified to update

profile{id=1, age=33}:save()

UPDATE profile T
SET
  age = 33
WHERE
  T.id = 1
RETURNING
  id

{
  age: 33,
  id : 1,
}

ok 52 - XodelInstance:save(names?:string[], key?:string) save with primary key specified to update

save with primary key ignored and force create

profile{id=5, age=55, usr_id=3, dept_name='d3',}:save_create()

INSERT INTO
  profile AS T (salary, age, usr_id, dept_name, sex)
VALUES
  (1000, 55, 3, 'd3', 'f')
RETURNING
  *

{
  age      : 55,
  dept_name: "d3",
  id       : 3,
  salary   : 1000,
  sex      : "f",
  usr_id   : 3,
}

ok 53 - XodelInstance:save(names?:string[], key?:string) save with primary key ignored and force create

save with wrong name

profile{usr_id=1, dept_name='d1', age=20}:save{'xxxx'}

ok 54 - XodelInstance:save(names?:string[], key?:string) save with wrong name

Xodel:merge(rows:table[], key?:string|string[], columns?:string[])

merge multiple rows returning inserted rows with all columns

usr:merge({{permission=4, username ='u1'},{permission=2, username ='u22'}}, 'username'):returning('*'):exec()

WITH
  V (permission, username) AS (
    VALUES
      (4::integer, 'u1'::varchar),
      (2, 'u22')
  ),
  U AS (
    UPDATE usr W
    SET
      permission = V.permission
    FROM
      V
    WHERE
      V.username = W.username
    RETURNING
      V.permission,
      V.username
  )
INSERT INTO
  usr AS T (permission, username)
SELECT
  V.permission,
  V.username
FROM
  V
  LEFT JOIN U AS W ON (V.username = W.username)
WHERE
  W.username IS NULL
RETURNING
  *

[
  {
    id        : 24,
    permission: 2,
    username  : "u22",
  },
]

ok 55 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with all columns

merge multiple rows returning inserted rows with specific columns

usr:merge({{username ='u23'},{username ='u24'}}, 'username'):returning('username'):exec()

WITH
  V (username) AS (
    VALUES
      ('u23'::varchar),
      ('u24')
  ),
  U AS (
    SELECT
      V.username
    FROM
      V
      INNER JOIN usr AS W ON (V.username = W.username)
  )
INSERT INTO
  usr AS T (username)
SELECT
  V.username
FROM
  V
  LEFT JOIN U AS W ON (V.username = W.username)
WHERE
  W.username IS NULL
RETURNING
  T.username

[
  {
    username: "u23",
  },
  {
    username: "u24",
  },
]

ok 56 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with specific columns

merge multiple rows returning inserted rows with specific columns in compact form

usr:merge({{username ='u25'},{username ='u26'}}, 'username'):returning('username'):flat()

WITH
  V (username) AS (
    VALUES
      ('u25'::varchar),
      ('u26')
  ),
  U AS (
    SELECT
      V.username
    FROM
      V
      INNER JOIN usr AS W ON (V.username = W.username)
  )
INSERT INTO
  usr AS T (username)
SELECT
  V.username
FROM
  V
  LEFT JOIN U AS W ON (V.username = W.username)
WHERE
  W.username IS NULL
RETURNING
  T.username

[
  "u25",
  "u26",
]

ok 57 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with specific columns in compact form

merge multiple rows returning inserted rows with array key

evaluate:merge({{usr_id=1, year=2021, rank='A'},{usr_id=1, year=2022, rank='B'}}, {'usr_id', 'year'}):returning('rank'):flat()

WITH
  V (usr_id, rank, year) AS (
    VALUES
      (1::integer, 'A'::varchar, 2021::integer),
      (1, 'B', 2022)
  ),
  U AS (
    UPDATE evaluate W
    SET
      rank = V.rank
    FROM
      V
    WHERE
      V.usr_id = W.usr_id
      AND V.year = W.year
    RETURNING
      V.usr_id,
      V.rank,
      V.year
  )
INSERT INTO
  evaluate AS T (usr_id, rank, year)
SELECT
  V.usr_id,
  V.rank,
  V.year
FROM
  V
  LEFT JOIN U AS W ON (
    V.usr_id = W.usr_id
    AND V.year = W.year
  )
WHERE
  W.usr_id IS NULL
RETURNING
  T.rank

[
  "A",
  "B",
]

ok 58 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with array key

merge multiple rows returning inserted rows with array key and specific columns

evaluate:merge({{usr_id=2, year=2021, rank='A'},{usr_id=2, year=2022, rank='B'}}, {'usr_id', 'year'}, {'usr_id', 'year'}):returning('rank'):flat()

WITH
  V (usr_id, year) AS (
    VALUES
      (2::integer, 2021::integer),
      (2, 2022)
  ),
  U AS (
    SELECT
      V.usr_id,
      V.year
    FROM
      V
      INNER JOIN evaluate AS W ON (
        V.usr_id = W.usr_id
        AND V.year = W.year
      )
  )
INSERT INTO
  evaluate AS T (usr_id, year)
SELECT
  V.usr_id,
  V.year
FROM
  V
  LEFT JOIN U AS W ON (
    V.usr_id = W.usr_id
    AND V.year = W.year
  )
WHERE
  W.usr_id IS NULL
RETURNING
  T.rank

[
  "C",
  "C",
]

ok 59 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows returning inserted rows with array key and specific columns

merge multiple rows validate max failed

ok 60 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows validate max failed

merge multiple rows missing default unique value failed

ok 61 - Xodel:merge(rows:table[], key?:string|string[], columns?:string[]) merge multiple rows missing default unique value failed

Xodel:upsert(rows:table[], key?:string|string[], columns?:string[])

upsert multiple rows returning inserted rows with all columns

usr:upsert({{permission=4, username ='u1'},{permission=2, username ='u27'}}, 'username'):returning('username'):exec()

INSERT INTO
  usr AS T (permission, username)
VALUES
  (4, 'u1'),
  (2, 'u27')
ON CONFLICT (username) DO
UPDATE
SET
  permission = EXCLUDED.permission
RETURNING
  T.username

[
  {
    username: "u1",
  },
  {
    username: "u27",
  },
]

ok 62 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) upsert multiple rows returning inserted rows with all columns

upsert multiple rows returning inserted rows with specific columns in compact form

usr:upsert({{username ='u28'},{username ='u29'}}, 'username'):returning('username'):flat()

INSERT INTO
  usr AS T (username)
VALUES
  ('u28'),
  ('u29')
ON CONFLICT (username) DO NOTHING
RETURNING
  T.username

[
  "u28",
  "u29",
]

ok 63 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) upsert multiple rows returning inserted rows with specific columns in compact form

upsert multiple rows returning inserted rows with array key

evaluate:upsert({{usr_id=1, year=2021, rank='A'},{usr_id=1, year=2022, rank='B'}}, {'usr_id', 'year'}):returning('rank'):flat()

INSERT INTO
  evaluate AS T (usr_id, rank, year)
VALUES
  (1, 'A', 2021),
  (1, 'B', 2022)
ON CONFLICT (usr_id, year) DO
UPDATE
SET
  rank = EXCLUDED.rank
RETURNING
  T.rank

[
  "A",
  "B",
]

ok 64 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) upsert multiple rows returning inserted rows with array key

upsert multiple rows validate max failed

ok 65 - Xodel:upsert(rows:table[], key?:string|string[], columns?:string[]) upsert multiple rows validate max failed

Xodel.update

update one user

 usr:update{permission=2}:where{id=1}:exec()

UPDATE usr T
SET
  permission = 2
WHERE
  T.id = 1

{
  affected_rows: 1,
}

ok 66 - Xodel.update update one user

update one user returning one column

 usr:update{permission=3}:where{id=1}:returning('permission'):exec()

UPDATE usr T
SET
  permission = 3
WHERE
  T.id = 1
RETURNING
  T.permission

[
  {
    permission: 3,
  },
]

ok 67 - Xodel.update update one user returning one column

update users returning two columns in table form

 usr:update{permission=3}:where{id__lt=3}:returning{'permission','id'}:exec()

UPDATE usr T
SET
  permission = 3
WHERE
  T.id < 3
RETURNING
  T.permission,
  T.id

[
  {
    id        : 1,
    permission: 3,
  },
  {
    id        : 2,
    permission: 3,
  },
]

ok 68 - Xodel.update update users returning two columns in table form

update users returning one column in flatten form

 usr:update{permission=3}:where{id__lt=3}:returning{'username'}:flat()

UPDATE usr T
SET
  permission = 3
WHERE
  T.id < 3
RETURNING
  T.username

[
  "u1",
  "u2",
]

ok 69 - Xodel.update update users returning one column in flatten form

update by where with foreignkey

profile:update{age=11}:where{usr_id__username__contains='1'}:returning('age'):exec()

UPDATE profile T
SET
  age = 11
FROM
  usr T1
WHERE
  (T1.username LIKE '%1%')
  AND (T.usr_id = T1.id)
RETURNING
  T.age

[
  {
    age: 11,
  },
]

ok 70 - Xodel.update update by where with foreignkey

update returning foreignkey

profile:update { sex = 'm' }:where { id = 1 }:returning('id', 'usr_id__username'):exec()

UPDATE profile T
SET
  sex = 'm'
FROM
  usr T1
WHERE
  (T.id = 1)
  AND (T.usr_id = T1.id)
RETURNING
  T.id,
  T1.username AS usr_id__username

[
  {
    id              : 1,
    usr_id__username: "u1",
  },
]

ok 71 - Xodel.update update returning foreignkey

Xodel:updates(rows:table[], key?:string|string[], columns?:string[])

updates partial

usr:updates({{permission=2, username ='u1'},{permission=3, username ='??'}}, 'username'):returning("*"):exec()

WITH
  V (permission, username) AS (
    VALUES
      (2::integer, 'u1'::varchar),
      (3, '??')
  )
UPDATE usr T
SET
  permission = V.permission
FROM
  V
WHERE
  V.username = T.username
RETURNING
  *

[
  {
    id        : 1,
    permission: 2,
    username  : "u1",
  },
]

ok 72 - Xodel:updates(rows:table[], key?:string|string[], columns?:string[]) updates partial

updates all

usr:updates({{permission=1, username ='u1'},{permission=3, username ='u3'}}, 'username'):returning("*"):exec()

WITH
  V (permission, username) AS (
    VALUES
      (1::integer, 'u1'::varchar),
      (3, 'u3')
  )
UPDATE usr T
SET
  permission = V.permission
FROM
  V
WHERE
  V.username = T.username
RETURNING
  *

[
  {
    id        : 1,
    permission: 1,
    username  : "u1",
  },
  {
    id        : 3,
    permission: 3,
    username  : "u3",
  },
]

ok 73 - Xodel:updates(rows:table[], key?:string|string[], columns?:string[]) updates all

Xodel.where

where basic

 usr:select('username','id'):where{id=1}:exec()

SELECT
  T.username,
  T.id
FROM
  usr T
WHERE
  T.id = 1

[
  {
    id      : 1,
    username: "u1",
  },
]

ok 74 - Xodel.where where basic

where condition by 2 args

 usr:select('id'):where('id', 3):exec()

SELECT
  T.id
FROM
  usr T
WHERE
  T.id = 3

[
  {
    id: 3,
  },
]

ok 75 - Xodel.where where condition by 2 args

where condition by 3 args

 usr:select('id'):where('id', '<',  3):flat()

SELECT
  T.id
FROM
  usr T
WHERE
  T.id < 3

[
  1,
  2,
]

ok 76 - Xodel.where where condition by 3 args

where by arithmetic operator: __lt

usr:where{id__lt=2}:select('id')

SELECT
  T.id
FROM
  usr T
WHERE
  T.id < 2

ok 77 - Xodel.where where by arithmetic operator: __lt

where by arithmetic operator: __lte

usr:where{id__lte=2}:select('id')

SELECT
  T.id
FROM
  usr T
WHERE
  T.id <= 2

ok 78 - Xodel.where where by arithmetic operator: __lte

where by arithmetic operator: __gt

usr:where{id__gt=2}:select('id')

SELECT
  T.id
FROM
  usr T
WHERE
  T.id > 2

ok 79 - Xodel.where where by arithmetic operator: __gt

where by arithmetic operator: __gte

usr:where{id__gte=2}:select('id')

SELECT
  T.id
FROM
  usr T
WHERE
  T.id >= 2

ok 80 - Xodel.where where by arithmetic operator: __gte

where by arithmetic operator: __eq

usr:where{id__eq=2}:select('id')

SELECT
  T.id
FROM
  usr T
WHERE
  T.id = 2

ok 81 - Xodel.where where by arithmetic operator: __eq

where by arithmetic operator: __ne

usr:where{id__ne=2}:select('id')

SELECT
  T.id
FROM
  usr T
WHERE
  T.id <> 2

ok 82 - Xodel.where where by arithmetic operator: __ne

where in

usr:where{username__in={'u1','u2'}}

SELECT
  *
FROM
  usr T
WHERE
  T.username IN ('u1', 'u2')

ok 83 - Xodel.where where in

where contains

usr:where{username__contains='u'}

SELECT
  *
FROM
  usr T
WHERE
  T.username LIKE '%u%'

ok 84 - Xodel.where where contains

where startswith

usr:where{username__startswith='u'}

SELECT
  *
FROM
  usr T
WHERE
  T.username LIKE 'u%'

ok 85 - Xodel.where where startswith

where endswith

usr:where{username__endswith='u'}

SELECT
  *
FROM
  usr T
WHERE
  T.username LIKE '%u'

ok 86 - Xodel.where where endswith

where null true

usr:where{username__null=true}

SELECT
  *
FROM
  usr T
WHERE
  T.username IS NULL

ok 87 - Xodel.where where null true

where null false

usr:where{username__null=false}

SELECT
  *
FROM
  usr T
WHERE
  T.username IS NOT NULL

ok 88 - Xodel.where where null false

where notin

usr:where{username__notin={'u1','u2'}}

SELECT
  *
FROM
  usr T
WHERE
  T.username NOT IN ('u1', 'u2')

ok 89 - Xodel.where where notin

where foreignkey eq

profile:where{usr_id__username__eq='u1'}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.username = 'u1'

ok 90 - Xodel.where where foreignkey eq

where foreignkey in

profile:where{usr_id__username__in={'u1','u2'}}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.username IN ('u1', 'u2')

ok 91 - Xodel.where where foreignkey in

where foreignkey contains

profile:where{usr_id__username__contains='u'}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.username LIKE '%u%'

ok 92 - Xodel.where where foreignkey contains

where foreignkey startswith

profile:where{usr_id__username__startswith='u'}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.username LIKE 'u%'

ok 93 - Xodel.where where foreignkey startswith

where foreignkey endswith

profile:where{usr_id__username__endswith='u'}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.username LIKE '%u'

ok 94 - Xodel.where where foreignkey endswith

where foreignkey null true

profile:where{usr_id__username__null=true}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.username IS NULL

ok 95 - Xodel.where where foreignkey null true

where foreignkey null false

profile:where{usr_id__username__null=false}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.username IS NOT NULL

ok 96 - Xodel.where where foreignkey null false

where foreignkey number operator lt

profile:where{usr_id__permission__lt=2}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.permission < 2

ok 97 - Xodel.where where foreignkey number operator lt

where foreignkey number operator lte

profile:where{usr_id__permission__lte=2}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.permission <= 2

ok 98 - Xodel.where where foreignkey number operator lte

where foreignkey number operator gt

profile:where{usr_id__permission__gt=2}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.permission > 2

ok 99 - Xodel.where where foreignkey number operator gt

where foreignkey number operator gte

profile:where{usr_id__permission__gte=2}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.permission >= 2

ok 100 - Xodel.where where foreignkey number operator gte

where foreignkey number operator eq

profile:where{usr_id__permission__eq=2}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.permission = 2

ok 101 - Xodel.where where foreignkey number operator eq

where foreignkey number operator ne

profile:where{usr_id__permission__ne=2}

SELECT
  *
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T1.permission <> 2

ok 102 - Xodel.where where foreignkey number operator ne

Xodel.select

select fk column

profile:select('id', 'usr_id__username'):where { id = 1 }:exec()

SELECT
  T.id,
  T1.username AS usr_id__username
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T.id = 1

[
  {
    id              : 1,
    usr_id__username: "u1",
  },
]

ok 103 - Xodel.select select fk column

Xodel:get(cond?, op?, dval?)

basic

usr:get{id=3}

SELECT
  *
FROM
  usr T
WHERE
  T.id = 3
LIMIT
  2

{
  id        : 3,
  permission: 3,
  username  : "u3",
}

ok 104 - Xodel:get(cond?, op?, dval?) basic

model load foreign row

SELECT
  *
FROM
  profile T
WHERE
  T.id = 1
LIMIT
  2

ok 105 - Xodel:get(cond?, op?, dval?) model load foreign row

fetch extra foreignkey field from database on demand

SELECT
  *
FROM
  usr T
WHERE
  T.id = 1
LIMIT
  2

ok 106 - Xodel:get(cond?, op?, dval?) fetch extra foreignkey field from database on demand

model load foreign row with specified columns

profile:load_fk('usr_id', 'username', 'permission'):get{id=1}

SELECT
  T.usr_id,
  T1.username AS usr_id__username,
  T1.permission AS usr_id__permission
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T.id = 1
LIMIT
  2

{
  usr_id: {
    permission: 1,
    username  : "u1",
  },
}

ok 107 - Xodel:get(cond?, op?, dval?) model load foreign row with specified columns

model load foreign row with all columns by *

profile:load_fk('usr_id', '*'):get{id=1}

SELECT
  T.usr_id,
  T.usr_id AS usr_id__id,
  T1.username AS usr_id__username,
  T1.permission AS usr_id__permission
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T.id = 1
LIMIT
  2

{
  usr_id: {
    id        : 1,
    permission: 1,
    username  : "u1",
  },
}

SELECT
  *
FROM
  usr T
WHERE
  T.id = 1
LIMIT
  2

ok 108 - Xodel:get(cond?, op?, dval?) model load foreign row with all columns by *

model load foreign row with specified columns two api are the same

profile:select("sex"):load_fk('usr_id', 'username', 'permission'):get{id=1}

SELECT
  T.sex,
  T.usr_id,
  T1.username AS usr_id__username,
  T1.permission AS usr_id__permission
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T.id = 1
LIMIT
  2

{
  sex   : "m",
  usr_id: {
    permission: 1,
    username  : "u1",
  },
}

profile:select("sex"):load_fk('usr_id', {'username', 'permission'}):get{id=1}

SELECT
  T.sex,
  T.usr_id,
  T1.username AS usr_id__username,
  T1.permission AS usr_id__permission
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T.id = 1
LIMIT
  2

{
  sex   : "m",
  usr_id: {
    permission: 1,
    username  : "u1",
  },
}

ok 109 - Xodel:get(cond?, op?, dval?) model load foreign row with specified columns two api are the same

Xodel:get(cond?, op?, dval?)

usr:get{id__lt=3}

SELECT
  *
FROM
  usr T
WHERE
  T.id < 3
LIMIT
  2

ok 110 - Xodel:get(cond?, op?, dval?) Xodel:get(cond?, op?, dval?)

Xodel:get_or_create(params:table, defaults?:table, columns?:string[])

basic

usr:get_or_create{username='goc'}

WITH
  new_records (id, username) AS (
    INSERT INTO
      "usr" (username)
    SELECT
      'goc'
    WHERE
      NOT EXISTS (
        SELECT
          1
        FROM
          usr T
        WHERE
          T.username = 'goc'
      )
    RETURNING
      id,
      username
  )
SELECT
  id,
  username,
  TRUE AS __is_inserted__
FROM
  new_records new_records
UNION ALL
(
  SELECT
    id,
    username,
    FALSE AS __is_inserted__
  FROM
    usr T
  WHERE
    T.username = 'goc'
)

{
  id      : 33,
  username: "goc",
}

ok 111 - Xodel:get_or_create(params:table, defaults?:table, columns?:string[]) basic

model get_or_create with defaults

usr:get_or_create({username='goc2'}, {permission = 5})

WITH
  new_records (id, permission, username) AS (
    INSERT INTO
      "usr" (permission, username)
    SELECT
      5,
      'goc2'
    WHERE
      NOT EXISTS (
        SELECT
          1
        FROM
          usr T
        WHERE
          T.username = 'goc2'
      )
    RETURNING
      id,
      permission,
      username
  )
SELECT
  id,
  permission,
  username,
  TRUE AS __is_inserted__
FROM
  new_records new_records
UNION ALL
(
  SELECT
    id,
    permission,
    username,
    FALSE AS __is_inserted__
  FROM
    usr T
  WHERE
    T.username = 'goc2'
)

{
  id        : 34,
  permission: 5,
  username  : "goc2",
}

ok 112 - Xodel:get_or_create(params:table, defaults?:table, columns?:string[]) model get_or_create with defaults

test chat model

INSERT INTO
  message AS T (creator, target, content)
VALUES
  (1, 2, 'c121'),
  (1, 2, 'c122'),
  (2, 1, 'c123'),
  (1, 3, 'c131'),
  (1, 3, 'c132'),
  (3, 1, 'c133'),
  (1, 3, 'c134'),
  (2, 3, 'c231')
RETURNING
  *

ok 113 - Xodel api: test chat model

go crazy with where clause with recursive join

INSERT INTO
  message AS T (content, creator, target)
VALUES
  ('crazy', 1, 2)
RETURNING
  *

SELECT
  *
FROM
  profile T
WHERE
  T.id = 1
LIMIT
  2

SELECT
  *
FROM
  usr T
WHERE
  T.id = 1
LIMIT
  2

SELECT
  T.id,
  T1.age AS creator__age,
  T2.username AS creator__usr_id__username
FROM
  message T
  INNER JOIN profile T1 ON (T.creator = T1.id)
  INNER JOIN usr T2 ON (T1.usr_id = T2.id)
WHERE
  T.id = 9
  AND T2.username LIKE '%1%'
  AND T1.age = 11

SELECT
  T.id,
  T1.age AS creator__age,
  T2.username AS creator__usr_id__username
FROM
  message T
  INNER JOIN profile T1 ON (T.creator = T1.id)
  INNER JOIN usr T2 ON (T1.usr_id = T2.id)
WHERE
  T.id = 9

ok 114 - Xodel api: go crazy with where clause with recursive join

etc

wrong fk name

models.message:where {creator__usr_id__views=0}:exec()

ok 115 - etc wrong fk name

wrong fk name3

models.message:select('creator__usr_id__views'):exec()

ok 116 - etc wrong fk name3

test shortcuts join

profile:join('dept_name'):get { id = 1 }

SELECT
  *
FROM
  profile T
  INNER JOIN dept T1 ON (T.dept_name = T1.name)
WHERE
  T.id = 1
LIMIT
  2

{
  age      : 11,
  dept_name: {
    name: "d1",
  },
  id       : 1,
  name     : "d1",
  salary   : 1000,
  sex      : "m",
  usr_id   : {
    id: 1,
  },
}

ok 117 - etc test shortcuts join

sql select_as

usr:select_as('id', 'value'):select_as('username', 'label'):where { id = 2 }:exec()

SELECT
  T.id AS value,
  T.username AS label
FROM
  usr T
WHERE
  T.id = 2

[
  {
    label: "u2",
    value: 2,
  },
]

ok 118 - etc sql select_as

sql select_as foreignkey

profile:select_as('usr_id__permission', 'uperm'):where { id = 2 }:exec()

SELECT
  T1.permission AS uperm
FROM
  profile T
  INNER JOIN usr T1 ON (T.usr_id = T1.id)
WHERE
  T.id = 2

[
  {
    uperm: 3,
  },
]

ok 119 - etc sql select_as foreignkey

sql injection

where key

ok 120 - sql injection where key

where value

SELECT
  *
FROM
  usr T
WHERE
  T.id = '1 or 1=1'

ok 121 - sql injection where value

order

ok 122 - sql injection order

select

ok 123 - sql injection select

Xodel:delete(cond?, op?, dval?)

model class delete all

evaluate:delete{}:exec()

DELETE FROM evaluate T

{
  affected_rows: 4,
}

ok 124 - Xodel:delete(cond?, op?, dval?) model class delete all

model instance delete

DELETE FROM message T

DELETE FROM message T

SELECT
  *
FROM
  profile T
WHERE
  T.id = 1
LIMIT
  2

du:delete()

DELETE FROM profile T
WHERE
  T.id = 1
RETURNING
  T.id

[
  {
    id: 1,
  },
]

ok 125 - Xodel:delete(cond?, op?, dval?) model instance delete

model instance delete use non primary key

SELECT
  *
FROM
  usr T
WHERE
  T.id = 1
LIMIT
  2

du:delete('username')

DELETE FROM usr T
WHERE
  T.username = 'u1'
RETURNING
  T.username

[
  {
    username: "u1",
  },
]

ok 126 - Xodel:delete(cond?, op?, dval?) model instance delete use non primary key

create with foreign model returning all

SELECT
  *
FROM
  usr T
WHERE
  T.id = 3
LIMIT
  2

profile:insert{usr_id=u, age=12}:returning("*"):execr()

INSERT INTO
  profile AS T (usr_id, dept_name, age, sex, salary)
VALUES
  (3, DEFAULT, 12, 'f', 1000)
RETURNING
  *

[
  {
    age   : 12,
    id    : 4,
    salary: 1000,
    sex   : "f",
    usr_id: 3,
  },
]

ok 127 - Xodel:delete(cond?, op?, dval?) create with foreign model returning all

insert from delete returning

SELECT
  *
FROM
  usr T
WHERE
  T.id = 2
LIMIT
  2

log:insert(profile:delete { id = 2 }:returning('id'):returning_literal("usr", "delete"),
      { 'delete_id', 'model_name', "action" }):returning("*"):execr()

WITH
  D (delete_id, model_name, action) AS (
    DELETE FROM profile T
    WHERE
      T.id = 2
    RETURNING
      T.id,
      'usr',
      'delete'
  )
INSERT INTO
  log AS T (delete_id, model_name, action)
SELECT
  delete_id,
  model_name,
  action
FROM
  D
RETURNING
  *

[
  {
    action    : "delete",
    delete_id : 2,
    id        : 1,
    model_name: "usr",
  },
]

TODO

• ManyToManyField