Query examples

All examples use the standard Prisma API. prisma-sql converts supported shapes to SQL; everything else falls back to Prisma.

Filters

{ age: { gt: 18, lte: 65 } }
{ status: { in: ['ACTIVE', 'PENDING'] } }
{ status: { notIn: ['DELETED'] } }

{ email: { contains: '@example.com' } }
{ email: { startsWith: 'user' } }
{ email: { endsWith: '.com' } }
{ email: { contains: 'EXAMPLE', mode: 'insensitive' } }

{ AND: [{ status: 'ACTIVE' }, { verified: true }] }
{ OR: [{ role: 'ADMIN' }, { role: 'MODERATOR' }] }
{ NOT: { status: 'DELETED' } }

{ deletedAt: null }
{ deletedAt: { not: null } }

Relations

{
  include: {
    posts: true,
    profile: true,
  },
}

Nested includes support where, orderBy and take:

{
  include: {
    posts: {
      where: { published: true },
      orderBy: { createdAt: 'desc' },
      take: 5,
      include: {
        comments: true,
      },
    },
  },
}

Relation list filters

{ where: { posts: { some: { published: true } } } }
{ where: { posts: { every: { published: true } } } }
{ where: { posts: { none: { published: false } } } }

Pagination and ordering

{
  take: 10,
  skip: 20,
  orderBy: { createdAt: 'desc' },
}

{
  cursor: { id: 100 },
  skip: 1,
  take: 10,
  orderBy: { id: 'asc' },
}

{
  orderBy: [
    { status: 'asc' },
    { priority: 'desc' },
    { createdAt: 'desc' },
  ],
}

Composite cursor pagination

Use an orderBy that starts with the cursor fields in the same order. This matches keyset pagination expectations and avoids unstable page boundaries.

{
  cursor: { tenantId: 10, id: 500 },
  skip: 1,
  take: 20,
  orderBy: [
    { tenantId: 'asc' },
    { id: 'asc' },
  ],
}

Optional relation null ordering

A required scalar reached through an optional to-one relation can use { sort, nulls }, because the relation's LEFT JOIN makes the SQL expression nullable. Queries using this form execute through prisma-sql; if acceleration fails they return an explicit "prisma-sql cannot fall back" error instead of forwarding input Prisma would reject.

await prisma.jobAd.findMany({
  orderBy: {
    boost: {
      boostedUntil: { sort: 'desc', nulls: 'last' },
    },
  },
})

Aggregates

await prisma.user.count({
  where: { status: 'ACTIVE' },
})

await prisma.task.aggregate({
  where: { status: 'DONE' },
  _count: { _all: true },
  _sum: { estimatedHours: true },
  _avg: { estimatedHours: true },
  _min: { startedAt: true },
  _max: { completedAt: true },
})

Aggregate values are mapped back to Prisma-style types — Decimal, BigInt,DateTime and _count keep their expected value types.

groupBy

await prisma.task.groupBy({
  by: ['status', 'priority'],
  _count: { _all: true },
  _avg: { estimatedHours: true },
  having: {
    status: {
      _count: { gte: 5 },
    },
  },
})