@xiangnanscu/model

@xiangnanscu/model Declarative, intuitive and powerful PostgreSQL ORM library.

Features

Declarative Model Definition: Define your database models using simple JavaScript objects
Automatic Migration Generation: Generate SQL migration scripts from model changes
Type Safety: Full TypeScript support with type inference
Query Builder: Intuitive query building with method chaining
Relationship Management: Support for foreign keys and complex relationships
Schema Validation: Built-in field validation and constraints

Installation

npm install @xiangnanscu/model

Quick Start

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

// Configure database connection
Model.db_config = {
  host: "localhost",
  port: "5432",
  user: "postgres",
  password: "postgres",
  database: "test",
  max: 20,
  idle_timeout: 20,
  connect_timeout: 3,
};

// Model definitions
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: "Name", 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];

// Create tables from model definitions
for (const model of model_list) {
  const createSQL = create_table_sql(model);
  await Model.query(createSQL);
}

Query Operations

Basic Queries

JavaScript	SQL
// Query all records const blogs = await Blog.exec();	SELECT * FROM blog T
// Conditional query 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'
// String condition query const result = await Blog.where("name", "First Blog").exec();	SELECT * FROM blog T WHERE T.name = 'First Blog'

Field Selection

JavaScript	SQL
// Select single field const result = await Blog.select("name").where({ id: 1 }).exec(); // Result: [{ name: "First Blog" }]	SELECT T.name FROM blog T WHERE T.id = 1
// Select multiple fields const result = await Blog.select("name", "tagline").where({ id: 1 }).exec(); // Or using array form const result = await Blog.select(["name", "tagline"]).where({ id: 1 }).exec();	SELECT T.name, T.tagline FROM blog T WHERE T.id = 1
// Field aliases 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
// Literal selection 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
// Literal aliases 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

Foreign Key Queries

JavaScript

SQL

// Query foreign key fields
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

// Nested foreign key query
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

// Reverse foreign key query
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'

Conditional Queries

JavaScript	SQL
// Basic conditions const result = await Book.where({ price: 100 }).exec();	SELECT * FROM book T WHERE T.price = 100
// Comparison operators const result = await Book.where({ price__gt: 100 }).exec(); // greater than	SELECT * FROM book T WHERE T.price > 100
const result = await Book.where({ price__lt: 100 }).exec(); // less than	SELECT * FROM book T WHERE T.price < 100
const result = await Book.where({ price__gte: 100 }).exec(); // greater than or equal	SELECT * FROM book T WHERE T.price >= 100
const result = await Book.where({ price__lte: 100 }).exec(); // less than or equal	SELECT * FROM book T WHERE T.price <= 100
// String operations const result = await Blog.where({ name__contains: "blog" }).exec(); // contains	SELECT * FROM blog T WHERE T.name LIKE '%blog%'
const result = await Blog.where({ name__startswith: "First" }).exec(); // starts with	SELECT * FROM blog T WHERE T.name LIKE 'First%'
const result = await Blog.where({ name__endswith: "Blog" }).exec(); // ends with	SELECT * FROM blog T WHERE T.name LIKE '%Blog'
// List operations const result = await Blog.where({ id__in: [1, 2, 3] }).exec(); // in list	SELECT * FROM blog T WHERE T.id IN (1, 2, 3)
const result = await Blog.where({ id__notin: [1, 2, 3] }).exec(); // not in list	SELECT * FROM blog T WHERE T.id NOT IN (1, 2, 3)
// Null checks const result = await Blog.where({ tagline__isnull: true }).exec(); // is null	SELECT * FROM blog T WHERE T.tagline IS NULL
const result = await Blog.where({ tagline__notnull: true }).exec(); // is not null	SELECT * FROM blog T WHERE T.tagline IS NOT NULL

Complex Conditional Queries

JavaScript

SQL

// Using Q objects for complex queries
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)

// Negation conditions
const result = await Book.where(Q({ price__gt: 100 }).not()).exec();

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

// Combined conditions
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))

Foreign Key Conditional Queries

JavaScript	SQL
// Foreign key equality query const result = await Entry.where({ blog_id: 1 }).exec(); const result = await Entry.where({ blog_id__id: 1 }).exec(); // equivalent	SELECT * FROM entry T WHERE T.blog_id = 1
// Foreign key field query 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'
// Nested foreign key query 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'
// Reverse foreign key query 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 Field Queries

JavaScript	SQL
// JSON key existence check const result = await Author.where({ resume__has_key: "start_date" }).exec();	SELECT * FROM author T WHERE (T.resume) ? 'start_date'
// JSON multiple keys existence check 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 any keys existence check const result = await Author.where({ resume__has_any_keys: ["a", "b"] }).exec();	SELECT * FROM author T WHERE (T.resume) ?\| ARRAY['a', 'b']
// JSON path access 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 contains check 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 contained by check 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"}'

Date Queries

JavaScript

SQL

// Year query
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'

// Month query
const result = await ViewLog.where({ ctime__month: 1 }).exec();

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

// Day query
const result = await ViewLog.where({ ctime__day: 15 }).exec();

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

Ordering

JavaScript	SQL
// Single field ordering const result = await Blog.order_by("name").exec();	SELECT * FROM blog T ORDER BY T.name
// Descending order const result = await Blog.order_by("-name").exec();	SELECT * FROM blog T ORDER BY T.name DESC
// Multiple field ordering const result = await Blog.order_by("name", "-id").exec();	SELECT * FROM blog T ORDER BY T.name, T.id DESC
// Foreign key field ordering 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

Aggregation Queries

JavaScript	SQL
// Group by query 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
// Aggregation functions 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(); // auto alias as price_sum	SELECT SUM(T.price) AS price_sum FROM book T
// Multiple aggregations 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 clause 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
// Complex HAVING conditions 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)

Field Expressions

JavaScript

SQL

// Field operations
const result = await Book.annotate({
  double_price: F("price").mul(2)
}).exec();

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

// Field-to-field operations
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

// String concatenation
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

Related Query Counting

JavaScript	SQL
// Left join counting 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)

Insert Operations

Basic Insert

JavaScript

SQL

// Insert single record
const result = await Blog.insert({
  name: "New Blog",
  tagline: "New blog tagline"
}).exec();

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

// Insert and return specified fields
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

// Return all fields
const result = await Blog.insert({
  name: "All Fields Blog"
}).returning("*").exec();

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

Bulk Insert

JavaScript

SQL

// Bulk insert
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')

// Bulk insert with return
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 *

Insert from Subquery

JavaScript	SQL
// Insert from SELECT subquery 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'
// Insert with specified column names 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'
// Insert from 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
// Insert from 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

Column-Specific Insert

JavaScript

SQL

// Insert only specified columns
const result = await BlogBin.insert(
  {
    name: "Column Test Blog",
    tagline: "Column test tagline",
    note: "should not be inserted"
  },
  ["name", "tagline"] // Only insert these two columns
).returning("name", "tagline", "note").exec();

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

Update Operations

Basic Update

JavaScript

SQL

// Basic update
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 *

// Update using field expressions
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

// Field arithmetic update
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

Field Increment

JavaScript

SQL

// Single field increment
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

// Multiple field increment
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 *

// String parameter increment
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

Update with Joins

JavaScript

SQL

// Update with foreign key conditions
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

Advanced Operations

MERGE Operations

JavaScript

SQL

// Basic merge (update if exists, insert if not)
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

// Insert only non-existing records
const result = await Blog.merge([
  { name: "First Blog" }, // exists, no update
  { name: "Blog added by merge" } // doesn't exist, insert
]).exec();

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

UPSERT Operations

JavaScript

SQL

// UPSERT (update if exists, insert if not, return both)
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 from subquery
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

// UPSERT from SELECT subquery
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 Operations

JavaScript

SQL

// Bulk update (only update existing records)
const result = await Blog.updates([
  { name: "Third Blog", tagline: "Updated by updates" },
  { name: "Fourth Blog", tagline: "wont update" } // doesn't exist, no 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

// Bulk update from SELECT subquery
const result = await BlogBin.updates(
  Blog.where({ name: "Second Blog" }).select("name", "tagline"),
  "name" // match field
).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 *

// Bulk update from UPDATE subquery
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 Operations

JavaScript

SQL

// Merge then query
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 then query (query after)
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)

Delete Operations

JavaScript	SQL
// Basic delete const result = await Blog.delete({ name: "Blog to delete" }).exec();	DELETE FROM blog T WHERE T.name = 'Blog to delete'
// Delete with return const result = await Blog.delete({ name: "Blog to delete" }) .returning("*") .exec();	DELETE FROM blog T WHERE T.name = 'Blog to delete' RETURNING *
// Conditional delete const result = await Blog.delete({ name__startswith: "temp" }).exec();	DELETE FROM blog T WHERE T.name LIKE 'temp%'
// Delete all records const result = await Blog.delete().exec();	DELETE FROM blog T

Convenience Methods

Create Records

JavaScript

SQL

// Create single record and return complete object
const blog = await Blog.create({
  name: "Created Blog",
  tagline: "Created tagline"
});

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

Get Single Record

JavaScript

SQL

// Get single record
const blog = await Blog.where({ id: 1 }).get();

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

// Get specific fields of single record
const blog = await Blog.where({ id: 1 }).select("name").get();

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

Flatten Results

JavaScript

SQL

// Get array of single column values
const names = await Blog.flat("name");

SELECT T.name
FROM blog T

// Flatten with conditions
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

Raw Queries

JavaScript

SQL

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

-- Execute raw SQL (parameterized query)
SELECT * FROM blog WHERE id = $1
-- Parameters: [1]

// Get SQL statement (without execution)
const sql = Blog.where({ id: 1 }).statement();
console.log(sql); // "SELECT * FROM blog T WHERE T.id = 1"

-- Returns SQL string without execution
SELECT * FROM blog T WHERE T.id = 1

Field Types

Basic Types

const Model = Model({
  table_name: "example",
  fields: {
    // String type
    name: { type: "string", maxlength: 100, minlength: 2 },
    title: { maxlength: 200 }, // defaults to string type

    // Text type
    content: { type: "text" },

    // Integer type
    age: { type: "integer", min: 0, max: 150 },

    // Float type
    price: { type: "float" },

    // Boolean type
    is_active: { type: "boolean", default: true },

    // Date type
    birth_date: { type: "date" },

    // DateTime type
    created_at: { type: "datetime" },

    // Email type
    email: { type: "email" },

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

    // Foreign key reference
    user_id: { reference: User },

    // Composite field
    profile: { model: Profile }
  }
});

Field Constraints

const Model = Model({
  table_name: "example",
  fields: {
    // Unique constraint
    username: { unique: true },

    // Not null constraint
    email: { null: false },

    // Default value
    status: { default: "active" },

    // Label (for form display, etc.)
    name: { label: "Name" },

    // Compact storage (for long text)
    content: { compact: false }
  }
});

Model Options

const Model = Model({
  // Table name
  table_name: "my_table",

  // Auto create primary key
  auto_primary_key: true, // default true

  // Composite unique constraint
  unique_together: ["field1", "field2"],

  // Model inheritance
  mixins: [BaseModel],

  // Field definitions
  fields: {
    // ...
  }
});

Error Handling

try {
  // Unique constraint violation
  await Blog.insert({ name: "First Blog" }).exec();
} catch (error) {
  console.error("Insert failed:", error.message);
}

try {
  // Field length exceeded
  await Blog.insert({
    name: "This name is way too long and exceeds the maximum length"
  }).exec();
} catch (error) {
  console.error("Field validation failed:", error.message);
}

try {
  // Age out of range
  await Author.insert({ name: "Tom", age: 101 }).exec();
} catch (error) {
  console.error("Age validation failed:", error.message);
}

Debugging

// Get SQL statement without execution
const sql = Blog.where({ id: 1 }).statement();
console.log(sql);

// Enable full SQL matching (for testing)
process.env.SQL_WHOLE_MATCH = true;

Best Practices

Model Definition: Define related models in the same file for easier foreign key relationship management
Field Validation: Make full use of field constraints for data validation
Query Optimization: Use select() to query only needed fields
Transaction Handling: Consider using database transactions for complex operations
Error Handling: Always wrap database operations in try-catch blocks
Index Optimization: Add database indexes for frequently queried fields

This ORM provides rich query interfaces and flexible data manipulation methods that can meet the needs of most PostgreSQL applications.

Database Migration Tool

The library includes a powerful database migration tool that can generate SQL migration scripts by comparing model definitions.

Usage

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

// Define your model
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,
    },
  },
};

// Create table SQL
const create_sql = create_table_sql(user_model);
console.log(create_sql);

// Generate migration SQL (from old model to new model)
const migration_sql = generate_migration_sql(old_model, new_model);
console.log(migration_sql);

Supported Field Types

string: VARCHAR with specified length
text: TEXT field for long content
integer: Integer numbers
float: Floating point numbers with optional precision
boolean: Boolean true/false values
date: Date only (YYYY-MM-DD)
datetime: Timestamp with optional timezone
time: Time only with optional timezone
uuid: UUID with automatic generation
json: JSONB for structured data
foreignkey: Foreign key relationships
year/month: Integer fields for year/month values
year_month: VARCHAR for year-month combinations

Migration Features

Table Creation: Generate CREATE TABLE statements
Field Addition/Removal: Add or remove columns
Type Changes: Convert between compatible field types
Constraint Management: Handle PRIMARY KEY, UNIQUE, NOT NULL constraints
Index Management: Create and drop indexes
Foreign Key Management: Add, remove, and modify foreign key relationships
Default Values: Handle default value changes
Field Renaming: Automatic detection of field renames
Unique Together: Manage composite unique constraints

Example Migration Output

-- Creating a new table
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
);

-- Adding a field
ALTER TABLE users ADD COLUMN phone varchar(20);

-- Modifying field type
ALTER TABLE users ALTER COLUMN name TYPE text;

-- Adding foreign key
ALTER TABLE products ADD CONSTRAINT products_category_id_fkey
FOREIGN KEY (category_id) REFERENCES "categories" ("id")
ON DELETE CASCADE ON UPDATE CASCADE;

Testing

Run the test suite:

npm test

Run migration tool tests specifically:

npm test __test__/migrate.test.mjs

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Name		Name	Last commit message	Last commit date
Latest commit History 126 Commits
.github/workflows		.github/workflows
.vscode		.vscode
__test__		__test__
composables		composables
globals		globals
lib		lib
lua2js		lua2js
public		public
src		src
views		views
.env		.env
.eslintrc.cjs		.eslintrc.cjs
.gitignore		.gitignore
.npmignore		.npmignore
.nvmrc		.nvmrc
README.md		README.md
README.zh-CN.md		README.zh-CN.md
env.d.ts		env.d.ts
index.html		index.html
package.json		package.json
pnpm-lock.yaml		pnpm-lock.yaml
t.sql		t.sql
test.js		test.js
tsconfig.app.json		tsconfig.app.json
tsconfig.json		tsconfig.json
tsconfig.node.json		tsconfig.node.json
tsconfig.vitest.json		tsconfig.vitest.json
vite.config.ts		vite.config.ts
vitest.config.ts		vitest.config.ts

xiangnanscu/model

Folders and files

Latest commit

History

Repository files navigation

@xiangnanscu/model

Features

Installation

Quick Start

Query Operations

Basic Queries

Field Selection

Foreign Key Queries

Conditional Queries

Complex Conditional Queries

Foreign Key Conditional Queries

JSON Field Queries

Date Queries

Ordering

Aggregation Queries

Field Expressions

Related Query Counting

Insert Operations

Basic Insert

Bulk Insert

Insert from Subquery

Column-Specific Insert

Update Operations

Basic Update

Field Increment

Update with Joins

Advanced Operations

MERGE Operations

UPSERT Operations

UPDATES Operations

MERGE_GETS Operations

Delete Operations

Convenience Methods

Create Records

Get Single Record

Flatten Results

Raw Queries

Field Types

Basic Types

Field Constraints

Model Options

Error Handling

Debugging

Best Practices

Database Migration Tool

Usage

Supported Field Types

Migration Features

Example Migration Output

Testing

License

Contributing

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Uh oh!

Languages

Packages