Queries

Construction

jsorm uses a structured query shape instead of chain builders. Every operation is a plain object passed to jsorm.* — readable, serializable, and inspectable.

Tip

All operations return types inferred directly from your model definition. You never write return types manually.

Select

import { jsorm } from 'jsorm';
import { User } from './src/schema/index.js';

const result = await jsorm.get(User, {
  select: {
    id: true,
    name: true,
    email: true,
    role: { name: true },
    _count: true,
  },
  where: {
    AND: [
      { active: true },
      { role: { name: { eq: 'admin' } } },
    ],
  },
  orderBy: [{ createdAt: 'desc' }],
  pagination: {
    perPage: 10,
    currentPage: 1,
  },
});
// result.data: Array<{ id: number; name: string; email?: string; role: { name: string }; _count: number }>
// result.pagination: { perPage, currentPage, total, lastPage, firstPage }

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

Use AND/OR at the where level to group conditions:

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

Cursor pagination

Cursor/keyset pagination is available alongside offset pagination. The two are mutually exclusive — use cursor or pagination, not both:

const page = await jsorm.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 primary-key tie-breaker when needed
• cursor.take is clamped by JSORM_CURSOR_HARD_MAX_LIMIT (default 1000)

Insert

await jsorm.insert(User, {
  name: 'Alice',
  email: 'alice@example.com',
  createdAt: new Date(),
  role: { connect: 1 },
});

Return the inserted row with returning:

const inserted = await jsorm.insert(User, {
  name: 'Bob',
  createdAt: new Date(),
  role: { connect: 2 },
  returning: { id: true, name: true },
});
// inserted: { id: number; name: string }

Bulk insert

await jsorm.insertMany(User, [
  { name: 'Alice', createdAt: new Date(), role: { connect: 1 } },
  { name: 'Bob', createdAt: new Date(), role: { connect: 2 } },
]);

Update

Caution

where is required on update and delete. jsorm throws a validation error if where is missing on a write operation.

await jsorm.update(User, {
  data: { active: false },
  where: { role: { name: { eq: 'guest' } } },
});

Delete

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

Count

const count = await jsorm.count(User, {
  where: { active: true },
});
// count: number

First record

const user = await jsorm.first(User, {
  select: { id: true, name: true },
  where: { email: { eq: 'alice@example.com' } },
});
// user: { id: number; name: string } | null

Relation mutations

// belongsTo: shorthand or explicit connect
await jsorm.insert(User, {
  name: 'Alice',
  createdAt: new Date(),
  role: { connect: 1 },
});

// manyToMany: array of IDs shorthand
await jsorm.insert(User, {
  name: 'Bob',
  createdAt: new Date(),
  roles: [1, 2],
});

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

Supported relation mutation shapes:

• belongsTo: role: 1, role: { connect: 1 }, role: { connect: null }
• hasOne: profile: { connect: 1 }, profile: { create: { ... } }
• hasMany: posts: { connect: [1, 2] }, posts: { create: [{ ... }] }
• manyToMany: roles: [1, 2], roles: { connect: [...] }, roles: { create: [...] }

Precompile queries

Precompile at startup to eliminate cold-path planning on hot routes:

const compiledUsers = await jsorm.compileGet(User, {
  select: { id: true, name: true },
});

const compiledSql = await jsorm.compileSql(
  'SELECT id, name FROM users WHERE id = ?',
  [1],
);

Raw SQL

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

Caution

Always use parameterized placeholders ($1, ?). Never interpolate user input into SQL strings.

Best practices

1. Keep where explicit on all write operations.
2. Prefer nested relation filters over raw join strings.
3. Use orderBy with pagination for stable list endpoints.
4. Use cursor pagination for infinite-scroll or very large datasets.
5. Use count() or first() rather than fetching all rows to check existence.
6. Reserve executeSql() for queries the JSON DSL genuinely cannot express.