Reading Data (TypeScript)

Overview

The Prisma client is generated from your datamodel. Its API exposes CRUD and other operations for the models defined in the datamodel.

For this page, we'll assume your Prisma project is based on the following datamodel:

type Post {
  id: ID! @unique
  createdAt: DateTime!
  updatedAt: DateTime!
  title: String!
  published: Boolean! @default(value: "false")
  author: User
  comments: [Comment!]!
}

type User {
  id: ID! @unique
  name: String
  email: String! @unique
  role: Role! @default(value: "USER")
  posts: [Post!]!
  comments: [Comment!]!
}

type Comment {
  id: ID! @unique
  createdAt: DateTime!
  text: String!
  post: Post!
  writtenBy: User!
}

enum Role {
  USER
  ADMIN
}

Whenever a database record is queried using the Prisma client, all scalar fields of that record are fetched. This is true no matter if a single record or a list of records is queried.

For example, the following query returns all scalar fields of a single User:

const user: User = await prisma.user({ email: 'ada@prisma.io' })
Copy
 

In this case, the returned user record will have four properties (that correspond to the scalar fields of the User model): id, name, email and role. The posts and comments fields are both relation fields and are therefore not included in the response.

Here is an example of fetching a list of User records:

const users: User[] = await prisma.users()
Copy
 

Similar to the previous request, each object inside the users array only has the scalar and no relation fields.

Fetching single records

For each model in your datamodel, there is a method generated in the Prisma client API that allows to fetch single records of that model.

The method is named after the model but starts with a lowercase character. For the sample datamodel from above, the three generated methods for fetching single records are:

• user(where: UserWhereUniqueUniqueInput): UserPromise for User
• post(where: PostWhereUniqueUniqueInput): PostPromise for Post
• comment(where: CommentWhereUniqueUniqueInput): CommentPromise for Comment

The where input argument for these methods is an object that has as properties all unique fields of the model. This means, for all three methods, the id field is accepted (as the corresponding models each have an id field annotated as @unique). The input object for the user method additionaly has an email field.

Examples

Fetch a single post by its id:

const post: Post = await prisma.post({ id: 'cjlgpyueg001o0a239d3i07ao' })
Copy
 

Fetch a single user by their email:

const user: User = await prisma.user({ email: 'ada@prisma.io' })
Copy
 

Fetching lists

For each model in your datamodel, there is a method generated in the Prisma client API that allows to fetch a list of the respective records.

The method is named after the model but starts with a lowercase character and uses the plural form. For the sample datamodel from above, the three generated methods for fetching lists are:

• users for User:

  users: (
    args?: {
      where?: UserWhereInput;
      orderBy?: UserOrderByInput;
      skip?: Int;
      after?: String;
      before?: String;
      first?: Int;
      last?: Int;
    }
  ) => FragmentableArray<User>;
  

• posts for Post:

  posts: (
    args?: {
      where?: PostWhereInput;
      orderBy?: PostOrderByInput;
      skip?: Int;
      after?: String;
      before?: String;
      first?: Int;
      last?: Int;
    }
  ) => FragmentableArray<Post>;
  

• comments for Comment:

  comments: (
    args?: {
      where?: CommentWhereInput;
      orderBy?: CommentOrderByInput;
      skip?: Int;
      after?: String;
      before?: String;
      first?: Int;
      last?: Int;
    }
  ) => FragmentableArray<Comment>;
  

The input arugment for these functions is an object that has properties for:

• filtering: where
• ordering: orderBy
• pagination: before, after, first, last, skip

Examples

Fetch all comments:

const comments: Comment[] = await prisma.comments()
Copy
 

Fetch a list of users:

const users: User[] = await prisma.users()
Copy
 

Relations

Prisma client has a fluent API to query relations in your database. Meaning you can simply chain your method calls to navigate the relation properties of the returned records.

// not possible
const result = await prisma.users().posts()

If you're building a GraphQL server and are not sure how to implement your resolvers to resolve the relations in your GraphQL schema, check out this tutorial.

Examples

Fetch all the posts of a single user:

const postsByUser: Post[] = await prisma
  .user({ email: 'ada@prisma.io' })
  .posts()
Copy
 

Fetch all the comments for a certain post:

const commentsForPost: Comment[] = await prisma
  .post({ id: 'cjsviilj60g8y0b43bme3x8ib' })
  .comments()
Copy
 

Selecting fields

Instead of querying all scalar fields of a record (which is the default behavior), you can specify which fields you'd like retrieve by using the $fragment API. This is useful to exclude large unneeded fields (like BLOB values or huge strings). It also lets you fetch arbitrary relations.

The next version of the Prisma client API will have an improved and type-safe API to select fields. Find more info here.

Examples

Fetch the id, name and email of the users as well the the id and title of their related post:

const fragment = `
fragment UserWithPosts on User {
  id
  name
  email
  posts {
    id
    title
  }
}
`

const userWithPosts = await prisma.users().$fragment(fragment)
Copy
 

Basic filters for lists

Basic filters let you specify certain criteria to constrain which records should be returned in a list. The filters are specified in the where object of the input argument which is accepted by any list query.

The type of the where object depends on the model for which it was generated.

It is also possible to combine multiple filters using the AND and OR fields.

Examples

Fetch users that have an A in their names:

const usersWithAInName: User[] = await prisma.users({
  where: { name_contains: 'A' },
})
Copy
 

Use auto-completion to explore the filter system

Fetch users called Ada or Grace:

const usersCalledAdaOrGrace: User[] = await prisma.users({
  where: {
    name_in: ['Ada', 'Grace'],
  },
})
Copy
 

Fetch comments created before December 24, 2019:

const commentsBeforeChristmas: Comment[] = await prisma.comments({
  where: { createdAt_lt: '2019-12-24' },
})
Copy
 

Dates and times in the Prisma client API follow the ISO 8601 standard which generally is of the form: YYYY-MM-DDThh:mm:ss. Learn more.

Fetch posts that have prisma or graphql in their title and were created in 2019:

const filteredPosts: Post[] = await prisma.posts({
  where: {
    AND: [
      {
        OR: [
          {
            description_contains: 'graphql',
          },
          {
            description_contains: 'prisma',
          },
        ],
      },
      {
        AND: [
          {
            createdAt_gt: '2017',
          },
          {
            createdAt_lt: '2019',
          },
        ],
      },
    ],
  },
})
Copy
 

The AND filter can actually be omitted since multiple filter criteria are by default combined using a logical and. This means the above filter can also be expressed as follows:

const filteredPosts: Post[] = await prisma.posts({
  where: {
    OR: [
      {
        title_contains: 'graphql',
      },
      {
        title_contains: 'prisma',
      },
    ],
    createdAt_gt: '2018',
    createdAt_lt: '2020',
  },
})
Copy
 

Relational filters for lists

Relational filters can be used to constrain the returned records on a relation list field. The types used for filtering are identical to basic filters, the only difference is that the filters are not applied on the root level of the method call but when querying a relation (via the fluent API) on a later level.

Examples

Fetch posts by a certain user that were created before christmas:

const postsByUserBeforeChristmas: Post[] = await prisma
  .user({ email: 'ada@prisma.io' })
  .posts({
    where: {
      createdAt_lt: '2019-12-24',
    },
  })
Copy
 

Ordering

When querying a list of records, you can order (sort) the list by any scalar field of that model. Each generated method to query a list of records therefore accepts the orderBy field on its input object.

The type of the orderBy field depends on the scalar fields of the model for which it was generated.

Note that you can always order by createdAt and updatedAt, even when the fields have not been added to your models.

Examples

Sort comments by their creation date (ascending):

const sortedComments: Comment[] = await prisma.comments({
  orderBy: 'createdAt_ASC',
})
Copy
 

Sort users alphabetically by their names (descending):

const sortedUsers: User[] = await prisma.users({ orderBy: 'name_DESC' })
Copy
 

Pagination

When querying a list of records, you can fetch certain parts (i.e. pages) of that list by supplying pagination arguments.

Seeking forward and backward with first and last

You can seek forwards or backwards through the list and supply an optional starting point:

• To seek forward, use first; specify the id of a starting record with after.
• To seek backward, use last; specify the id of a starting record with before.

You cannot combine first with before or last with after. If you do so, before/after will simply be ignored and only first or last is actually applied (at the very beginning or end of the list, depending on which you're using).

Note that you can query for more records than actually exist in the database without an error message.

Skipping elements with skip

You can also skip an arbitrary amount of records in whichever direction you are seeking by supplying the skip argument:

• When using first, skip skips records at the beginning of the list.
• When using last, skip skips records from the end of the list.

Examples

For the following examples, we're assuming a list of exactly 30 records:

Fetch the first 3 records (seeking forward):

const posts: Post[] = await prisma.posts({ first: 3 })
Copy
 

Fetch the records from position 6 to position 10 (seeking forward):

const posts: Post[] = await prisma.posts({
  first: 5,
  skip: 5,
})
Copy
 

Fetch the last 3 records (seeking backward):

const posts: Post[] = await prisma.posts({ last: 3 })
Copy
 

Fetch the records from position 21 to position 27 (seeking backward):

const posts: Post[] = await prisma.posts({
  last: 7,
  skip: 3,
})
Copy
 

Fetch the first 3 records after the record with cixnen24p33lo0143bexvr52n as id:

const posts: Post[] = await prisma.posts({
  first: 3,
  after: 'cixnen24p33lo0143bexvr52n',
})
Copy
 

Fetch the first 5 records after the record with cixnen24p33lo0143bexvr52n as id and skipping 3 records:

const posts: Post[] = await prisma.posts({
  first: 5,
  after: 'cixnen24p33lo0143bexvr52n',
  skip: 3,
})
Copy
 

Fetch the last 5 records before the record with cixnen24p33lo0143bexvr52n as id:

const posts: Post[] = await prisma.posts({
  last: 5,
  before: 'cixnen24p33lo0143bexvr52n',
})
Copy
 

Fetch the last 3 records before the record with cixnen24p33lo0143bexvr52n as id and skipping 5 records:

const post: Post[] = await prisma.post({
  last: 3,
  before: 'cixnen24p33lo0143bexvr52n',
  skip: 5,
})
Copy
 

Aggregations

You can submit aggregation queries via connection queries. The following aggregation functions are supported:

• count: Counts the number of records in a list
• avg (coming soon): Computes the average of a list of numbers.
• median (coming soon): Computes the median of a list of numbers.
• max (coming soon): Returns the greatest element of a list of numbers.
• min (coming soon): Returns the smallest element of a list of numbers.
• sum (coming soon): Computes the sum of a list of numbers.

See this GitHub issue to learn more about the upcoming aggregation functions.

Examples

Count the number of post records:

const postCount: number = await prisma
  .postsConnection()
  .aggregate()
  .count()
Copy
 

GraphQL requests

The Prisma client lets you send GraphQL queries and mutations directly to your Prisma service using the $graphql method:

$graphql: <T = any>(query: string, variables?: {[key: string]: any}) => Promise<T>;

As the GraphQL operation is passed untyped (as a regular string), the type of the returned promise is any.

Examples

Fetching a single user record:

const query = `
  query  {
    user(where: { id: "cjcdi63j80adw0146z7r59bn5" }) {
      id
      name
    }
  }`

const result: any = await prisma.$graphql(query)
Copy
 

Fetching a single user using variables:

const query = `
  query ($id: ID!){
    user(where: { id: $id }) {
      id
      name
    }
  }
`
const variables: UserWhereUniqueInput = { id: 'cjsviilie0g880b43twzd93li' }
const result: any = prisma.$graphql(query, variables)
Copy