Queries

Construction

jsorm uses a structured JSON query shape instead of chain builders.

Tip

Examples below assume const db = await createJsorm().init(); from your app bootstrap.

Select

const rows = await db.get(User, {
  select: {
    id: true,
    name: true,
    role: { name: true },
    _count: true,
  },
  where: {
    AND: [
      { active: true },
      { role: { name: { eq: 'admin' } } },
    ],
  },
  orderBy: [{ createdAt: 'desc' }],
});

When pagination is omitted, get() applies a default LIMIT 150 to prevent accidental full-table fetches. Override with JSORM_LIMIT_DEFAULT.

Scalar where operators

Operator	Behavior
{ eq: value }	Equal
{ ne: value }	Not equal
{ gt: value }	Greater than
{ gte: value }	Greater than or equal
{ lt: value }	Less than
{ lte: value }	Less than or equal
{ in: [...] }	Value in list
{ contains: string }	LIKE %value%
{ startsWith: string }	LIKE value%
{ endsWith: string }	LIKE %value

Logical operators

const rows = await db.get(User, {
  select: { id: true, name: true },
  where: {
    OR: [
      { email: { contains: '@corp.com' } },
      { role: { name: { eq: 'admin' } } },
    ],
  },
});

Cursor pagination

const page = await db.get(User, {
  select: { id: true, name: true },
  orderBy: [{ id: 'asc' }],
  cursor: {
    after: '...opaque-base64url-token...',
    take: 25,
  },
});
// page.pageInfo: { hasNextPage, hasPreviousPage, startCursor, endCursor }

Cursor rules:

• cursor mode requires orderBy
• jsorm auto-appends a primary-key tie-breaker when needed
• cursor.take is clamped by JSORM_CURSOR_HARD_MAX_LIMIT (default 1000)

Insert / update / delete

await db.insert(User, {
  name: 'Alice',
  createdAt: new Date(),
  role: { connect: 1 },
});

await db.update(User, {
  data: { active: false },
  where: { id: { eq: 10 } },
});

await db.delete(User, {
  where: { id: { eq: 10 } },
});

Caution

where is required on update and delete.

Relation mutations

await db.insert(User, {
  name: 'Bob',
  createdAt: new Date(),
  roles: [1, 2],
});

await db.update(User, {
  data: {
    roles: { connect: [3], disconnect: [1] },
  },
  where: { id: { eq: 5 } },
});

Raw SQL

Use the documented escape hatch only:

const rows = await db.raw.execute(
  'SELECT id, name FROM users WHERE active = $1 AND created_at > $2',
  [true, new Date('2024-01-01')],
);

const compiled = await db.raw.compile(
  'SELECT id, name FROM users WHERE active = $1',
  [true],
);

Caution

Always use placeholders ($1, ?) and params arrays. Never interpolate untrusted input into SQL strings.

Best practices

1. Keep where explicit on all write operations.
2. Prefer nested relation filters over handwritten join SQL.
3. Use orderBy with pagination for stable list endpoints.
4. Use cursor pagination for large or infinite-scroll datasets.
5. Reserve db.raw.* for queries the JSON DSL genuinely cannot express cleanly.