ExpressX

Appearance

On this page

Find ​

See prisma reference

Find a single element ​

findUnique query lets you retrieve a single database record:

  • By ID
  • By a unique attribute
js
// Query returns User or null
const user = await app.service('User').findUnique({
   where: {
      id: 22,
   },
})

Example result:

js
{
   id: 22,
   name: "Alice",
   email: "alice@prisma.io",
   profileViews: 0,
   role: "ADMIN",
   coinflips: [true, false],
}

Find multiple elements ​

js
// Query returns User or null
const user = await app.service('User').findMany({
   where: {
      role: 'ADMIN',
   },
})

Example result:

js
[{
   id: 22,
   name: "Alice",
   email: "alice@prisma.io",
   profileViews: 0,
   role: "ADMIN",
   coinflips: [true, false],
}, ...

Select specific fields ​

Use select to return a limited subset of fields instead of all fields. The following example returns the email and name fields only:

js
// Returns an object or null
const user = await app.service('User').findUnique({
   where: {
      id: 22,
   },
   select: {
      email: true,
      name: true,
   },
})

Example result:

js
{
   name: "Alice",
   email: "alice@prisma.io",
}

Include relations and select relation fields ​

To return specific relation fields, you can:

  • Use a nested select
  • Use a select within an include

To return all relation fields, use include only - for example, { include: { posts: true } }.

The following query uses a nested select to select each user's name and the title of each related post:

js
const users = await app.service('User').findMany({
   select: {
      name: true,
      posts: {
         select: {
            title: true,
         },
      },
   },
})

Example result:

js
{
   "name":"Sabelle",
   "posts":[
      {
         "title":"Getting started with Azure Functions"
      },
      {
         "title":"All about databases"
      }
   ]
}

The following query uses select within an include, and returns all user fields and each post's title field:

js
const users = await app.service('User').findMany({
   // Returns all user fields
   include: {
      posts: {
         select: {
            title: true,
         },
      },
   },
})

Result:

js
{
   "id": 9
   "name": "Sabelle",
   "email": "sabelle@prisma.io",
   "profileViews": 90,
   "role": "USER",
   "profile": null,
   "coinflips": [],
   "posts":[
      {
         "title":"Getting started with Azure Functions"
      },
      {
         "title":"All about databases"
      }
   ]
}

Filter conditions and operators ​

  • equal: value equals n
  • not: value does not equal n
  • in: value n exists in list
  • notIn: value n does not exist in list
  • lt: value n is less than x
  • lte: value n is less than or equal to x
  • gt: value n is greater than x
  • gte: value n is greater than or equal to x
  • contains: value n exists in list
  • search: value n exists in list
  • startsWith: value n exists in list
  • endsWith: value n exists in list

Combining operators ​

You can use operators (such as NOT and OR) to filter by a combination of conditions.

The following query returns all users with an email that ends in "prisma.io" or "gmail.com", but not "hotmail.com":

js
const result = await app.service('User').findMany({
   where: {
      OR: [
         {
            email: {
               endsWith: 'prisma.io',
            },
         },
         {
            email: {
               endsWith: 'gmail.com',
            },
         },
      ],
      NOT: {
         email: {
            endsWith: 'hotmail.com',
         },
      },
   },
   select: {
      email: true,
   },
})

Result:

js
[{ email: 'yewande@prisma.io' }, { email: `raheem@gmail.com` }]

Filter on relations ​

ExpressX/Prisma supports filtering on related records. For example, in the following schema, a user can have many blog posts:

prisma
model User {
   id    Int     @id @default(autoincrement())
   name  String?
   email String  @unique
   posts Post[] // User can have many posts
}

model Post {
   id        Int     @id @default(autoincrement())
   title     String
   published Boolean @default(true)
   author    User    @relation(fields: [authorId], references: [id])
   authorId  Int
}

The one-to-many relation between User and Post allows you to query users based on their posts - for example, the following query returns all users where at least one post (some) has more than 10 views:

js
const result = await app.service('User').findMany({
   where: {
      posts: {
         some: {
            views: {
               gt: 10,
            },
         },
      },
   },
})

You can also query posts based on the properties of the author. For example, the following query returns all posts where the author's email contains "prisma.io":

js
const res = await app.service('Post')findMany({
   where: {
      author: {
         email: {
            contains: 'prisma.io',
         },
      },
   },
})

Filter on scalar lists / arrays ​

Scalar lists (for example, String[]) have a special set of filter conditions - for example, the following query returns all posts where the tags array contains databases:

js
const posts = await app.service('Post').findMany({
   where: {
      tags: {
         has: 'databases',
      },
   },
})

Case-insensitive filtering ​

Case-insensitive filtering is available as a feature for the PostgreSQL and MongoDB providers. MySQL, MariaDB and Microsoft SQL Server are case-insensitive by default, and do not require a Prisma Client feature to make case-insensitive filtering possible.

To use case-insensitive filtering, add the mode property to a particular filter and specify insensitive:

js
const users = await app.service('User').findMany({
   where: {
      email: {
         endsWith: 'prisma.io',
         mode: 'insensitive', // Default value: default
      },
      name: {
         equals: 'Archibald', // Default mode
      },
   },
})

Pagination ​

See prisma reference

Offset pagination ​

Offset pagination uses skip and take to skip a certain number of results and select a limited range. The following query skips the first 3 Post records and returns records 4 - 7:

js
const results = await app.service('Post').findMany({
   skip: 3,
   take: 4,
})

The following query returns all records where the email field contains Prisma, and sorts the result by the title field. The query skips the first 200 records and returns records 201 - 220.

js
const results = await app.service('Post').findMany({
   skip: 200,
   take: 20,
   where: {
      email: {
         contains: 'Prisma',
      },
   },
   orderBy: {
      title: 'desc',
   },
})

Cursor-based pagination ​

Cursor-based pagination uses cursor and take to return a limited set of results before or after a given cursor. A cursor bookmarks your location in a result set and must be a unique, sequential column - such as an ID or a timestamp.

The following example returns the first 4 Post records that contain the word "Prisma" and saves the ID of the last record as myCursor:

Note: Since this is the first query, there is no cursor to pass in.

js
const firstQueryResults = await app.service('Post').findMany({
   take: 4,
   where: {
      title: {
         contains: 'Prisma' /* Optional filter */,
      },
   },
   orderBy: {
      id: 'asc',
   },
})

// Bookmark your location in the result set - in this
// case, the ID of the last post in the list of 4.

const lastPostInResults = firstQueryResults[3] // Remember: zero-based index! :)
const myCursor = lastPostInResults.id // Example: 29

The second query returns the first 4 Post records that contain the word "Prisma" after the supplied cursor (in other words - IDs that are larger than 29):

js
const secondQueryResults = await app.service('Post').findMany({
   take: 4,
   skip: 1, // Skip the cursor
   cursor: {
      id: myCursor,
   },
   where: {
      title: {
         contains: 'Prisma' /* Optional filter */,
      },
   },
   orderBy: {
      id: 'asc',
   },
})

const lastPostInResults = secondQueryResults[3] // Remember: zero-based index! :)
const myCursor = lastPostInResults.id // Example: 52