@xiangnanscu/model

@xiangnanscu/model 一个声明式的、直观且强大的PostgreSQL ORM库。

English | 简体中文

特性

• 声明式模型定义: 使用简单的JavaScript对象定义数据库模型
• 自动迁移生成: 从模型变更生成SQL迁移脚本
• 类型安全: 完整的TypeScript支持和类型推断
• 查询构建器: 直观的链式查询构建
• 关系管理: 支持外键和复杂关系
• 模式验证: 内置字段验证和约束

安装

npm install @xiangnanscu/model

快速开始

import { Model, Q, F, Sum, Avg, Max, Min, Count, create_table_sql } from "@xiangnanscu/model";

// 配置数据库连接
Model.db_config = {
  host: "localhost",
  port: "5432",
  user: "postgres",
  password: "postgres",
  database: "test",
  max: 20,
  idle_timeout: 20,
  connect_timeout: 3,
};

// 模型定义
const User = Model({
  table_name: "user",
  fields: {
    username: { type: "string", maxlength: 20, minlength: 2, unique: true },
    password: { type: "text" },
  },
});

const Blog = Model({
  table_name: "blog",
  fields: {
    name: { type: "string", maxlength: 20, minlength: 2, unique: true },
    tagline: { type: "text", default: "default tagline" },
  },
});

const BlogBin = Model({
  table_name: "blog_bin",
  mixins: [Blog],
  fields: {
    name: { type: "string", unique: false },
    note: { type: "text", default: "" },
  },
});

const Resume = Model({
  auto_primary_key: false,
  table_name: "resume",
  unique_together: ["start_date", "end_date", "company", "position"],
  fields: {
    start_date: { type: "date" },
    end_date: { type: "date" },
    company: { type: "string", maxlength: 20 },
    position: { type: "string", maxlength: 20 },
    description: { type: "string", maxlength: 200 },
  },
});

const Author = Model({
  table_name: "author",
  fields: {
    name: { label: "姓名", type: "string", maxlength: 200, unique: true },
    email: { type: "email" },
    age: { type: "integer", max: 100, min: 10 },
    resume: { type: "table", model: Resume },
  },
});

const Entry = Model({
  table_name: "entry",
  fields: {
    blog_id: { type: "foreignkey", reference: Blog, related_query_name: "entry" },
    reposted_blog_id: { type: "foreignkey", reference: Blog, related_query_name: "reposted_entry" },
    headline: { type: "string", maxlength: 255, compact: false },
    body_text: { type: "text" },
    pub_date: { type: "date" },
    mod_date: { type: "date" },
    number_of_comments: { type: "integer", default: 0 },
    number_of_pingbacks: { type: "integer", default: 0 },
    rating: { type: "integer" },
  },
});

const ViewLog = Model({
  table_name: "view_log",
  fields: {
    entry_id: { type: "foreignkey", reference: Entry },
    ctime: { type: "datetime" },
  },
});

const Publisher = Model({
  table_name: "publisher",
  fields: {
    name: { type: "string", maxlength: 300 },
  },
});

const Book = Model({
  table_name: "book",
  fields: {
    name: { type: "string", maxlength: 300, compact: false },
    pages: { type: "integer" },
    price: { type: "float" },
    rating: { type: "float" },
    author: { type: "foreignkey", reference: Author },
    publisher_id: { type: "foreignkey", reference: Publisher },
    pubdate: { type: "date" },
  },
});

const Store = Model({
  table_name: "store",
  fields: {
    name: { type: "string", maxlength: 300 },
  },
});

const model_list = [User, Blog, BlogBin, Author, Entry, ViewLog, Publisher, Book, Store];

// 从模型定义创建表
for (const model of model_list) {
  const createSQL = create_table_sql(model);
  await Model.query(createSQL);
}

查询操作

// 获取辅助函数
const Q = Model.Q;
const F = Model.F;
const Sum = Model.Sum;
const Avg = Model.Avg;
const Max = Model.Max;
const Min = Model.Min;
const Count = Model.Count;

基础查询

// 查询所有记录
const blogs = await Blog.exec();

SELECT * FROM blog T

// 条件查询
const blog = await Blog.where({ id: 1 }).get();

SELECT * FROM blog T
WHERE T.id = 1

const blogs = await Blog.where({ name: "First Blog" }).exec();

SELECT * FROM blog T
WHERE T.name = 'First Blog'

// 字符串条件查询
const result = await Blog.where("name", "First Blog").exec();

SELECT * FROM blog T
WHERE T.name = 'First Blog'

字段选择

// 选择单个字段
const result = await Blog.select("name").where({ id: 1 }).exec();
// 结果: [{ name: "First Blog" }]

SELECT T.name
FROM blog T
WHERE T.id = 1

// 选择多个字段
const result = await Blog.select("name", "tagline").where({ id: 1 }).exec();
// 或者使用数组形式
const result = await Blog.select(["name", "tagline"]).where({ id: 1 }).exec();

SELECT T.name, T.tagline
FROM blog T
WHERE T.id = 1

// 字段别名
const result = await Blog.select_as({
  name: "blog_name",
  tagline: "blog_tagline"
}).where({ id: 1 }).exec();

SELECT T.name AS blog_name, T.tagline AS blog_tagline
FROM blog T
WHERE T.id = 1

// 字面量选择
const result = await Blog.select_literal("Hello World")
  .select("name")
  .where({ id: 1 })
  .exec();

SELECT 'Hello World' AS "?column?", T.name
FROM blog T
WHERE T.id = 1

// 字面量别名
const result = await Blog.select_literal_as({
  "Hello World": "greeting"
}).select("id").where({ id: 1 }).exec();

SELECT 'Hello World' AS greeting, T.id
FROM blog T
WHERE T.id = 1

外键查询

// 查询外键字段
const result = await Book.select("name", "author__name").where({ id: 1 }).exec();

SELECT T.name, T1.name AS author__name
FROM book T
INNER JOIN author T1 ON (T.author = T1.id)
WHERE T.id = 1

// 嵌套外键查询
const result = await ViewLog.select("entry_id__blog_id__name").where({ id: 1 }).exec();

SELECT T2.name AS entry_id__blog_id__name
FROM view_log T
INNER JOIN entry T1 ON (T.entry_id = T1.id)
INNER JOIN blog T2 ON (T1.blog_id = T2.id)
WHERE T.id = 1

// 反向外键查询
const result = await Blog.select("id", "name", "entry__rating")
  .where({ name: "Second Blog" })
  .exec();

SELECT T.id, T.name, T1.rating AS entry__rating
FROM blog T
INNER JOIN entry T1 ON (T.id = T1.blog_id)
WHERE T.name = 'Second Blog'

条件查询

// 基础条件
const result = await Book.where({ price: 100 }).exec();

SELECT * FROM book T
WHERE T.price = 100

// 比较操作符
const result = await Book.where({ price__gt: 100 }).exec(); // 大于

SELECT * FROM book T
WHERE T.price > 100

const result = await Book.where({ price__lt: 100 }).exec(); // 小于

SELECT * FROM book T
WHERE T.price < 100

const result = await Book.where({ price__gte: 100 }).exec(); // 大于等于

SELECT * FROM book T
WHERE T.price >= 100

const result = await Book.where({ price__lte: 100 }).exec(); // 小于等于

SELECT * FROM book T
WHERE T.price <= 100

// 字符串操作
const result = await Blog.where({ name__contains: "blog" }).exec(); // 包含

SELECT * FROM blog T
WHERE T.name LIKE '%blog%'

const result = await Blog.where({ name__startswith: "First" }).exec(); // 开始于

SELECT * FROM blog T
WHERE T.name LIKE 'First%'

const result = await Blog.where({ name__endswith: "Blog" }).exec(); // 结束于

SELECT * FROM blog T
WHERE T.name LIKE '%Blog'

// 列表操作
const result = await Blog.where({ id__in: [1, 2, 3] }).exec(); // 在列表中

SELECT * FROM blog T
WHERE T.id IN (1, 2, 3)

const result = await Blog.where({ id__notin: [1, 2, 3] }).exec(); // 不在列表中

SELECT * FROM blog T
WHERE T.id NOT IN (1, 2, 3)

// 空值检查
const result = await Blog.where({ tagline__isnull: true }).exec(); // 为空

SELECT * FROM blog T
WHERE T.tagline IS NULL

const result = await Blog.where({ tagline__notnull: true }).exec(); // 不为空

SELECT * FROM blog T
WHERE T.tagline IS NOT NULL

复杂条件查询

// 使用 Q 对象进行复杂查询
const result = await Book.where(
  Q({ price__gt: 100 }).or(Q({ price__lt: 200 }))
).exec();

SELECT * FROM book T
WHERE (T.price > 100) OR (T.price < 200)

// 否定条件
const result = await Book.where(Q({ price__gt: 100 }).not()).exec();

SELECT * FROM book T
WHERE NOT (T.price > 100)

// 组合条件
const result = await Book.where(
  Q({ id: 1 }).and(Q({ price__gt: 100 }).or(Q({ price__lt: 200 })))
).exec();

SELECT * FROM book T
WHERE (T.id = 1) AND ((T.price > 100) OR (T.price < 200))

外键条件查询

// 外键等值查询
const result = await Entry.where({ blog_id: 1 }).exec();
const result = await Entry.where({ blog_id__id: 1 }).exec(); // 等效

SELECT * FROM entry T
WHERE T.blog_id = 1

// 外键字段查询
const result = await Entry.where({ blog_id__name: "my blog name" }).exec();

SELECT * FROM entry T
INNER JOIN blog T1 ON (T.blog_id = T1.id)
WHERE T1.name = 'my blog name'

// 嵌套外键查询
const result = await ViewLog.where({ entry_id__blog_id__name: "my blog name" }).exec();

SELECT * FROM view_log T
INNER JOIN entry T1 ON (T.entry_id = T1.id)
INNER JOIN blog T2 ON (T1.blog_id = T2.id)
WHERE T2.name = 'my blog name'

// 反向外键查询
const result = await Blog.where({ entry__rating: 1 }).exec();

SELECT * FROM blog T
INNER JOIN entry T1 ON (T.id = T1.blog_id)
WHERE T1.rating = 1

JSON 字段查询

// JSON 键存在检查
const result = await Author.where({ resume__has_key: "start_date" }).exec();

SELECT * FROM author T
WHERE (T.resume) ? 'start_date'

// JSON 多键存在检查
const result = await Author.where({ resume__0__has_keys: ["a", "b"] }).exec();

SELECT * FROM author T
WHERE (T.resume #> ARRAY['0']) ?& ARRAY['a', 'b']

// JSON 任意键存在检查
const result = await Author.where({ resume__has_any_keys: ["a", "b"] }).exec();

SELECT * FROM author T
WHERE (T.resume) ?| ARRAY['a', 'b']

// JSON 路径访问
const result = await Author.where({ resume__start_date__time: "12:00:00" }).exec();

SELECT * FROM author T
WHERE (T.resume #> ARRAY['start_date', 'time']) = '"12:00:00"'

// JSON 包含检查
const result = await Author.where({
  resume__contains: { start_date: "2025-01-01" }
}).exec();

SELECT * FROM author T
WHERE (T.resume) @> '{"start_date":"2025-01-01"}'

// JSON 被包含检查
const result = await Author.where({
  resume__contained_by: { start_date: "2025-01-01" }
}).exec();

SELECT * FROM author T
WHERE (T.resume) <@ '{"start_date":"2025-01-01"}'

日期查询

// 年份查询
const result = await ViewLog.where({ ctime__year: 2025 }).exec();

SELECT * FROM view_log T
WHERE T.ctime BETWEEN '2025-01-01' AND '2025-12-31'

// 月份查询
const result = await ViewLog.where({ ctime__month: 1 }).exec();

SELECT * FROM view_log T
WHERE EXTRACT(MONTH FROM T.ctime) = 1

// 日期查询
const result = await ViewLog.where({ ctime__day: 15 }).exec();

SELECT * FROM view_log T
WHERE EXTRACT(DAY FROM T.ctime) = 15

排序

// 单字段排序
const result = await Blog.order_by("name").exec();

SELECT * FROM blog T
ORDER BY T.name

// 降序排序
const result = await Blog.order_by("-name").exec();

SELECT * FROM blog T
ORDER BY T.name DESC

// 多字段排序
const result = await Blog.order_by("name", "-id").exec();

SELECT * FROM blog T
ORDER BY T.name, T.id DESC

// 外键字段排序
const result = await Entry.order_by("blog_id__name").exec();

SELECT * FROM entry T
INNER JOIN blog T1 ON (T.blog_id = T1.id)
ORDER BY T1.name

聚合查询

// 分组查询
const result = await Book.group_by("name")
  .annotate({ price_total: Sum("price") })
  .exec();

SELECT T.name, SUM(T.price) AS price_total
FROM book T
GROUP BY T.name

// 聚合函数
const result = await Book.annotate({ price_total: Sum("price") }).exec();

SELECT SUM(T.price) AS price_total
FROM book T

const result = await Book.annotate([Sum("price")]).exec(); // 自动别名为 price_sum

SELECT SUM(T.price) AS price_sum
FROM book T

// 多种聚合
const result = await Book.annotate({
  price_sum: Sum("price"),
  price_avg: Avg("price"),
  price_max: Max("price"),
  price_min: Min("price"),
  book_count: Count("id")
}).exec();

SELECT
  SUM(T.price) AS price_sum,
  AVG(T.price) AS price_avg,
  MAX(T.price) AS price_max,
  MIN(T.price) AS price_min,
  COUNT(T.id) AS book_count
FROM book T

// HAVING 子句
const result = await Book.group_by("name")
  .annotate([Sum("price")])
  .having({ price_sum__gt: 100 })
  .exec();

SELECT T.name, SUM(T.price) AS price_sum
FROM book T
GROUP BY T.name
HAVING SUM(T.price) > 100

// 复杂 HAVING 条件
const result = await Book.group_by("name")
  .annotate([Sum("price")])
  .having(Q({ price_sum__lt: 100 }).or(Q({ price_sum__gt: 200 })))
  .exec();

SELECT T.name, SUM(T.price) AS price_sum
FROM book T
GROUP BY T.name
HAVING (SUM(T.price) < 100) OR (SUM(T.price) > 200)

字段表达式

// 字段运算
const result = await Book.annotate({
  double_price: F("price").mul(2)
}).exec();

SELECT (T.price * 2) AS double_price
FROM book T

// 字段间运算
const result = await Book.annotate({
  price_per_page: F("price").div(F("pages"))
}).exec();

SELECT (T.price / T.pages) AS price_per_page
FROM book T

// 字符串连接
const result = await Entry.update({
  headline: F("headline") + " suffix by function"
}).where({ id: 1 }).exec();

UPDATE entry T
SET headline = (T.headline || ' suffix by function')
WHERE T.id = 1

关联查询计数

// 左连接计数
const result = await Blog.annotate({
  entry_count: Count("entry")
}).exec();

SELECT COUNT(T1.id) AS entry_count
FROM blog T
LEFT JOIN entry T1 ON (T.id = T1.blog_id)

插入操作

基础插入

// 插入单条记录
const result = await Blog.insert({
  name: "New Blog",
  tagline: "New blog tagline"
}).exec();

INSERT INTO blog (name, tagline)
VALUES ('New Blog', 'New blog tagline')

// 插入并返回指定字段
const result = await Blog.insert({
  name: "Return Test Blog",
  tagline: "Return test tagline"
}).returning("id", "name").exec();

INSERT INTO blog (name, tagline)
VALUES ('Return Test Blog', 'Return test tagline')
RETURNING id, name

// 返回所有字段
const result = await Blog.insert({
  name: "All Fields Blog"
}).returning("*").exec();

INSERT INTO blog (name)
VALUES ('All Fields Blog')
RETURNING *

批量插入

// 批量插入
const result = await Blog.insert([
  { name: "bulk insert 1", tagline: "bulk insert 1" },
  { name: "bulk insert 2", tagline: "bulk insert 2" }
]).exec();

INSERT INTO blog (name, tagline)
VALUES
  ('bulk insert 1', 'bulk insert 1'),
  ('bulk insert 2', 'bulk insert 2')

// 批量插入并返回
const result = await Blog.insert([
  { name: "bulk return 1", tagline: "bulk return 1" },
  { name: "bulk return 2", tagline: "bulk return 2" }
]).returning("*").exec();

INSERT INTO blog (name, tagline)
VALUES
  ('bulk return 1', 'bulk return 1'),
  ('bulk return 2', 'bulk return 2')
RETURNING *

从子查询插入

// 从 SELECT 子查询插入
const result = await BlogBin.insert(
  Blog.where({ name: "Second Blog" }).select("name", "tagline")
).exec();

INSERT INTO blog_bin (name, tagline)
SELECT T.name, T.tagline
FROM blog T
WHERE T.name = 'Second Blog'

// 指定列名插入
const result = await BlogBin.insert(
  Blog.where({ name: "First Blog" })
    .select("name", "tagline")
    .select_literal("select from another blog"),
  ["name", "tagline", "note"]
).exec();

INSERT INTO blog_bin (name, tagline, note)
SELECT T.name, T.tagline, 'select from another blog'
FROM blog T
WHERE T.name = 'First Blog'

// 从 UPDATE RETURNING 插入
const result = await BlogBin.insert(
  Blog.update({ name: "update returning 2" })
    .where({ name: "update returning" })
    .returning("name", "tagline")
    .returning_literal("'update from another blog'"),
  ["name", "tagline", "note"]
).returning("name", "tagline", "note").exec();

INSERT INTO blog_bin (name, tagline, note)
WITH updated AS (
  UPDATE blog T
  SET name = 'update returning 2'
  WHERE T.name = 'update returning'
  RETURNING T.name, T.tagline, 'update from another blog' AS note
)
SELECT * FROM updated
RETURNING name, tagline, note

// 从 DELETE RETURNING 插入
const result = await BlogBin.insert(
  Blog.delete({ name: "delete returning" })
    .returning("name", "tagline")
    .returning_literal("'deleted from another blog'"),
  ["name", "tagline", "note"]
).returning("name", "tagline", "note").exec();

INSERT INTO blog_bin (name, tagline, note)
WITH deleted AS (
  DELETE FROM blog T
  WHERE T.name = 'delete returning'
  RETURNING T.name, T.tagline, 'deleted from another blog' AS note
)
SELECT * FROM deleted
RETURNING name, tagline, note

指定列插入

// 只插入指定列
const result = await BlogBin.insert(
  {
    name: "Column Test Blog",
    tagline: "Column test tagline",
    note: "should not be inserted"
  },
  ["name", "tagline"] // 只插入这两列
).returning("name", "tagline", "note").exec();

INSERT INTO blog_bin (name, tagline)
VALUES ('Column Test Blog', 'Column test tagline')
RETURNING name, tagline, note

更新操作

基础更新

// 基础更新
const result = await Blog.where({ name: "First Blog" })
  .update({ tagline: "changed tagline" })
  .returning("*")
  .exec();

UPDATE blog T
SET tagline = 'changed tagline'
WHERE T.name = 'First Blog'
RETURNING *

// 使用字段表达式更新
const result = await Entry.update({ headline: F("blog_id__name") })
  .where({ id: 1 })
  .returning("headline")
  .exec();

UPDATE entry T
SET headline = T1.name
FROM blog T1
WHERE T.blog_id = T1.id AND T.id = 1
RETURNING T.headline

// 字段运算更新
const result = await Entry.update({
  headline: F("headline") + " suffix by function"
}).where({ id: 1 }).returning("headline").exec();

UPDATE entry T
SET headline = (T.headline || ' suffix by function')
WHERE T.id = 1
RETURNING T.headline

字段递增

// 单字段递增
const result = await Entry.increase({ rating: 1 })
  .where({ id: 1 })
  .returning("rating")
  .exec();

UPDATE entry T
SET rating = (T.rating + 1)
WHERE T.id = 1
RETURNING T.rating

// 多字段递增
const result = await Entry.increase({
  number_of_comments: 1,
  number_of_pingbacks: 2
}).where({ id: 1 }).returning("*").exec();

UPDATE entry T
SET
  number_of_comments = (T.number_of_comments + 1),
  number_of_pingbacks = (T.number_of_pingbacks + 2)
WHERE T.id = 1
RETURNING *

// 字符串参数递增
const result = await Entry.increase("rating", 2)
  .where({ id: 1 })
  .returning("rating")
  .exec();

UPDATE entry T
SET rating = (T.rating + 2)
WHERE T.id = 1
RETURNING T.rating

带连接的更新

// 带外键条件的更新
const result = await Entry.update({
  headline: F("headline") + " from first blog"
}).where({
  blog_id__name: "First Blog"
}).returning("id", "headline").exec();

UPDATE entry T
SET headline = (T.headline || ' from first blog')
FROM blog T1
WHERE T.blog_id = T1.id AND T1.name = 'First Blog'
RETURNING T.id, T.headline

高级操作

MERGE 操作

// 基础 merge（存在则更新，不存在则插入）
const result = await Blog.merge([
  { name: "First Blog", tagline: "updated by merge" },
  { name: "Blog added by merge", tagline: "inserted by merge" }
]).exec();

INSERT INTO blog (name, tagline)
VALUES
  ('First Blog', 'updated by merge'),
  ('Blog added by merge', 'inserted by merge')
ON CONFLICT (name)
DO UPDATE SET tagline = EXCLUDED.tagline

// 只插入不存在的记录
const result = await Blog.merge([
  { name: "First Blog" }, // 存在，不更新
  { name: "Blog added by merge" } // 不存在，插入
]).exec();

INSERT INTO blog (name)
VALUES ('First Blog'), ('Blog added by merge')
ON CONFLICT (name) DO NOTHING

UPSERT 操作

// UPSERT（存在则更新，不存在则插入，都返回）
const result = await Blog.upsert([
  { name: "First Blog", tagline: "updated by upsert" },
  { name: "Blog added by upsert", tagline: "inserted by upsert" }
]).exec();

INSERT INTO blog (name, tagline)
VALUES
  ('First Blog', 'updated by upsert'),
  ('Blog added by upsert', 'inserted by upsert')
ON CONFLICT (name)
DO UPDATE SET tagline = EXCLUDED.tagline
RETURNING *

// 从子查询 UPSERT
const result = await Blog.upsert(
  BlogBin.update({ tagline: "updated by upsert returning" })
    .returning("name", "tagline")
).returning("id", "name", "tagline").exec();

WITH source AS (
  UPDATE blog_bin T
  SET tagline = 'updated by upsert returning'
  RETURNING T.name, T.tagline
)
INSERT INTO blog (name, tagline)
SELECT name, tagline FROM source
ON CONFLICT (name)
DO UPDATE SET tagline = EXCLUDED.tagline
RETURNING id, name, tagline

// 从 SELECT 子查询 UPSERT
const result = await Blog.upsert(
  BlogBin.where({
    name__notin: Blog.select("name").distinct()
  }).select("name", "tagline").distinct("name")
).returning("id", "name", "tagline").exec();

INSERT INTO blog (name, tagline)
SELECT DISTINCT T.name, T.tagline
FROM blog_bin T
WHERE T.name NOT IN (
  SELECT DISTINCT T.name FROM blog T
)
ON CONFLICT (name)
DO UPDATE SET tagline = EXCLUDED.tagline
RETURNING id, name, tagline

UPDATES 操作

// 批量更新（只更新存在的记录）
const result = await Blog.updates([
  { name: "Third Blog", tagline: "Updated by updates" },
  { name: "Fourth Blog", tagline: "wont update" } // 不存在，不更新
]).exec();

WITH V(name, tagline) AS (
  VALUES
    ('Third Blog', 'Updated by updates'),
    ('Fourth Blog', 'wont update')
)
UPDATE blog T
SET tagline = V.tagline
FROM V
WHERE V.name = T.name

// 从 SELECT 子查询批量更新
const result = await BlogBin.updates(
  Blog.where({ name: "Second Blog" }).select("name", "tagline"),
  "name" // 匹配字段
).returning("*").exec();

WITH V(name, tagline) AS (
  SELECT T.name, T.tagline
  FROM blog T
  WHERE T.name = 'Second Blog'
)
UPDATE blog_bin T
SET tagline = V.tagline
FROM V
WHERE V.name = T.name
RETURNING *

// 从 UPDATE 子查询批量更新
const result = await BlogBin.updates(
  Blog.where({ name: "Third Blog" })
    .update({ tagline: "XXX" })
    .returning("name", "tagline"),
  "name"
).exec();

WITH V(name, tagline) AS (
  UPDATE blog T
  SET tagline = 'XXX'
  WHERE T.name = 'Third Blog'
  RETURNING T.name, T.tagline
)
UPDATE blog_bin T
SET tagline = V.tagline
FROM V
WHERE V.name = T.name

MERGE_GETS 操作

// 先 merge 再查询
const result = await Blog.select("name")
  .merge_gets([
    { id: 1, name: "Merged First Blog" },
    { id: 2, name: "Merged Second Blog" }
  ], "id")
  .exec();

WITH V(id, name) AS (
  VALUES (1, 'Merged First Blog'), (2, 'Merged Second Blog')
)
INSERT INTO blog (id, name)
SELECT * FROM V
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;

SELECT T.name
FROM blog T
WHERE T.id IN (1, 2)

// 先 merge 再查询（查询在后）
const result = await Blog.merge_gets([
  { id: 1, name: "Merged First Blog" },
  { id: 2, name: "Merged Second Blog" }
], "id")
.select("name")
.exec();

WITH V(id, name) AS (
  VALUES (1, 'Merged First Blog'), (2, 'Merged Second Blog')
)
INSERT INTO blog (id, name)
SELECT * FROM V
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;

SELECT T.name
FROM blog T
WHERE T.id IN (1, 2)

删除操作

// 基础删除
const result = await Blog.delete({ name: "Blog to delete" }).exec();

DELETE FROM blog T
WHERE T.name = 'Blog to delete'

// 带返回的删除
const result = await Blog.delete({ name: "Blog to delete" })
  .returning("*")
  .exec();

DELETE FROM blog T
WHERE T.name = 'Blog to delete'
RETURNING *

// 条件删除
const result = await Blog.delete({ name__startswith: "temp" }).exec();

DELETE FROM blog T
WHERE T.name LIKE 'temp%'

// 删除所有记录
const result = await Blog.delete().exec();

DELETE FROM blog T

便捷方法

创建记录

// 创建单条记录并返回完整对象
const blog = await Blog.create({
  name: "Created Blog",
  tagline: "Created tagline"
});

INSERT INTO blog (name, tagline)
VALUES ('Created Blog', 'Created tagline')
RETURNING *

// 等效于
const result = await Blog.insert({
  name: "Created Blog",
  tagline: "Created tagline"
}).returning("*").exec();
const blog = result[0];

获取单条记录

// 获取单条记录
const blog = await Blog.where({ id: 1 }).get();

SELECT * FROM blog T
WHERE T.id = 1
LIMIT 1

// 获取单条记录的特定字段
const blog = await Blog.where({ id: 1 }).select("name").get();

SELECT T.name
FROM blog T
WHERE T.id = 1
LIMIT 1

扁平化结果

// 获取单列值的数组
const names = await Blog.flat("name");

SELECT T.name
FROM blog T

// 带条件的扁平化
const names = await Blog.where({ tagline__contains: "blog" })
  .order_by("name")
  .flat("name");

SELECT T.name
FROM blog T
WHERE T.tagline LIKE '%blog%'
ORDER BY T.name

原生查询

// 执行原生 SQL
const result = await Blog.query("SELECT * FROM blog WHERE id = $1", [1]);

-- 执行原生 SQL（参数化查询）
SELECT * FROM blog WHERE id = $1
-- 参数: [1]

// 获取 SQL 语句（不执行）
const sql = Blog.where({ id: 1 }).statement();
console.log(sql); // "SELECT * FROM blog T WHERE T.id = 1"

字段类型

基础类型

const Model = Model({
  table_name: "example",
  fields: {
    // 字符串类型
    name: { type: "string", maxlength: 100, minlength: 2 },
    title: { maxlength: 200 }, // 默认为 string 类型

    // 文本类型
    content: { type: "text" },

    // 整数类型
    age: { type: "integer", min: 0, max: 150 },

    // 浮点数类型
    price: { type: "float" },

    // 布尔类型
    is_active: { type: "boolean", default: true },

    // 日期类型
    birth_date: { type: "date" },

    // 日期时间类型
    created_at: { type: "datetime" },

    // 邮箱类型
    email: { type: "email" },

    // JSON 类型
    metadata: { type: "json" },

    // 外键引用
    user_id: { reference: User },

    // 复合字段
    profile: { model: Profile }
  }
});

字段约束

const Model = Model({
  table_name: "example",
  fields: {
    // 唯一约束
    username: { unique: true },

    // 非空约束
    email: { null: false },

    // 默认值
    status: { default: "active" },

    // 标签（用于表单显示等）
    name: { label: "姓名" },

    // 压缩存储（用于长文本）
    content: { compact: false }
  }
});

模型选项

const Model = Model({
  // 表名
  table_name: "my_table",

  // 是否自动创建主键
  auto_primary_key: true, // 默认 true

  // 联合唯一约束
  unique_together: ["field1", "field2"],

  // 模型继承
  mixins: [BaseModel],

  // 字段定义
  fields: {
    // ...
  }
});

错误处理

try {
  // 违反唯一约束
  await Blog.insert({ name: "First Blog" }).exec();
} catch (error) {
  console.error("插入失败:", error.message);
}

try {
  // 字段长度超限
  await Blog.insert({
    name: "This name is way too long and exceeds the maximum length"
  }).exec();
} catch (error) {
  console.error("字段验证失败:", error.message);
}

try {
  // 年龄超出范围
  await Author.insert({ name: "Tom", age: 101 }).exec();
} catch (error) {
  console.error("年龄验证失败:", error.message);
}

调试

// 获取 SQL 语句而不执行
const sql = Blog.where({ id: 1 }).statement();
console.log(sql);

// 启用完整 SQL 匹配（用于测试）
process.env.SQL_WHOLE_MATCH = true;

最佳实践

1. 模型定义: 将相关模型定义在同一个文件中，便于管理外键关系
2. 字段验证: 充分利用字段约束进行数据验证
3. 查询优化: 使用 select() 只查询需要的字段
4. 事务处理: 对于复杂操作，考虑使用数据库事务
5. 错误处理: 始终包装数据库操作在 try-catch 中
6. 索引优化: 为经常查询的字段添加数据库索引

这个 ORM 提供了丰富的查询接口和灵活的数据操作方法，能够满足大多数 PostgreSQL 应用的需求。

数据库迁移工具

该库包含一个强大的数据库迁移工具，可以通过比较模型定义来生成SQL迁移脚本。

使用方法

import { generate_migration_sql, create_table_sql } from './lib/migrate.mjs';

// 定义你的模型
const user_model = {
  table_name: "users",
  field_names: ["id", "name", "email", "created_at"],
  fields: {
    id: {
      name: "id",
      type: "integer",
      primary_key: true,
      serial: true,
      null: false,
    },
    name: {
      name: "name",
      type: "string",
      maxlength: 100,
      null: false,
    },
    email: {
      name: "email",
      type: "email",
      maxlength: 255,
      unique: true,
      null: false,
    },
    created_at: {
      name: "created_at",
      type: "datetime",
      auto_now_add: true,
      null: false,
    },
  },
};

// 创建表SQL
const create_sql = create_table_sql(user_model);
console.log(create_sql);

// 生成迁移SQL（从旧模型到新模型）
const migration_sql = generate_migration_sql(old_model, new_model);
console.log(migration_sql);

支持的字段类型

• string: 指定长度的VARCHAR
• text: 长内容的TEXT字段
• integer: 整数
• float: 可选精度的浮点数
• boolean: 布尔值true/false
• date: 仅日期（YYYY-MM-DD）
• datetime: 可选时区的时间戳
• time: 可选时区的仅时间
• uuid: 自动生成的UUID
• json: 结构化数据的JSONB
• foreignkey: 外键关系
• year/month: 年/月的整数字段
• year_month: 年-月组合的VARCHAR

迁移功能

• 表创建: 生成CREATE TABLE语句
• 字段添加/删除: 添加或删除列
• 类型更改: 在兼容的字段类型之间转换
• 约束管理: 处理PRIMARY KEY、UNIQUE、NOT NULL约束
• 索引管理: 创建和删除索引
• 外键管理: 添加、删除和修改外键关系
• 默认值: 处理默认值更改
• 字段重命名: 自动检测字段重命名
• 联合唯一: 管理复合唯一约束

迁移输出示例

-- 创建新表
CREATE TABLE users(
  id SERIAL PRIMARY KEY NOT NULL,
  name varchar(100) NOT NULL,
  email varchar(255) NOT NULL UNIQUE,
  created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 添加字段
ALTER TABLE users ADD COLUMN phone varchar(20);

-- 修改字段类型
ALTER TABLE users ALTER COLUMN name TYPE text;

-- 添加外键
ALTER TABLE products ADD CONSTRAINT products_category_id_fkey
FOREIGN KEY (category_id) REFERENCES "categories" ("id")
ON DELETE CASCADE ON UPDATE CASCADE;

测试

运行测试套件：

npm test

专门运行迁移工具测试：

npm test __test__/migrate.test.mjs

许可证

MIT

贡献

欢迎贡献！请随时提交Pull Request。