Prisma Client API reference

model User {

  id           Int              @id @default(autoincrement())

  name         String?

  email        String           @unique

  profileViews Int              @default(0)

  role         Role             @default(USER)

  coinflips    Boolean[]

  posts        Post[]

  city         String

  country      String

  profile      ExtendedProfile?

  pets         Json

}

model ExtendedProfile {

  id     Int     @id @default(autoincrement())

  userId Int?    @unique

  bio    String?

  User   User?   @relation(fields: [userId], references: [id])

}

model Post {

  id        Int     @id @default(autoincrement())

  title     String

  published Boolean @default(true)

  author    User    @relation(fields: [authorId], references: [id])

  authorId  Int

  comments  Json

  views     Int     @default(0)

  likes     Int     @default(0)

}

enum Role {

  USER

  ADMIN

}

import { PrismaClient } from "../prisma/generated/client";

const prisma = new PrismaClient({

  datasources: {

    db: {

      url: "file:./dev_qa.db",

    },

  },

});

datasource db {

  provider = "sqlite"

}

import { PrismaClient } from "../prisma/generated/client";

const prisma = new PrismaClient({

  datasourceUrl: "postgresql://johndoe:randompassword@localhost:5432/mydb",

});

export type QueryEvent = {

  timestamp: Date;

  query: string; // Query sent to the database

  params: string; // Query parameters

  duration: number; // Time elapsed (in milliseconds) between client issuing query and database responding - not only time taken to run query

  target: string;

};

export type LogEvent = {

  timestamp: Date;

  message: string;

  target: string;

};

import { PrismaClient } from "../prisma/generated/client";

const prisma = new PrismaClient({ log: ["query", "info"] });

async function main() {

  const countUsers = await prisma.user.count({});

}

main()

  .then(async () => {

    await prisma.$disconnect();

  })

  .catch(async (e) => {

    console.error(e);

    await prisma.$disconnect();

    process.exit(1);

  });

prisma:info  Starting a postgresql pool with 13 connections.

prisma:info  Started http server

prisma:query SELECT COUNT(*) FROM (SELECT "public"."User"."id" FROM "public"."User" WHERE 1=1 ORDER BY "public"."User"."coinflips" ASC OFFSET $1) AS "sub"

import { PrismaClient } from "../prisma/generated/client";

const prisma = new PrismaClient({

  log: [{ level: "query", emit: "event" }],

});

prisma.$on("query", (e) => {

  console.log(e);

});

async function main() {

  const countUsers = await prisma.user.count({});

}

main()

  .then(async () => {

    await prisma.$disconnect();

  })

  .catch(async (e) => {

    console.error(e);

    await prisma.$disconnect();

    process.exit(1);

  });

{

  timestamp: 2020-11-17T10:32:10.898Z,

  query: 'SELECT COUNT(*) FROM (SELECT "public"."User"."id" FROM "public"."User" WHERE 1=1 OFFSET $1) AS "sub"',

  params: '[0]',

  duration: 5,

  target: 'quaint::connector::metrics'

}

import { PrismaClient } from "../prisma/generated/client";

const prisma = new PrismaClient({

  log: [

    { level: "warn", emit: "event" },

    { level: "info", emit: "event" },

    { level: "error", emit: "event" },

  ],

});

prisma.$on("warn", (e) => {

  console.log(e);

});

prisma.$on("info", (e) => {

  console.log(e);

});

prisma.$on("error", (e) => {

  console.log(e);

});

async function main() {

  const countUsers = await prisma.user.count({});

}

main()

  .then(async () => {

    await prisma.$disconnect();

  })

  .catch(async (e) => {

    console.error(e);

    await prisma.$disconnect();

    process.exit(1);

  });

{

  timestamp: 2020-11-17T10:33:24.592Z,

  message: 'Starting a postgresql pool with 13 connections.',

  target: 'quaint::pooled'

}

{

  timestamp: 2020-11-17T10:33:24.637Z,

  message: 'Started http server',

  target: 'query_engine::server'

}

const prisma = new PrismaClient({

  // Defaults to colorless

});

const prisma = new PrismaClient({

  errorFormat: "pretty",

});

const prisma = new PrismaClient({

  errorFormat: "colorless",

});

const prisma = new PrismaClient({

  errorFormat: "minimal",

});

import { PrismaNeon } from "@prisma/adapter-neon";

import { PrismaClient } from "../prisma/generated/client";

import dotenv from "dotenv";

dotenv.config();

const connectionString = `${process.env.DATABASE_URL}`;

const adapter = new PrismaNeon({ connectionString });

const prisma = new PrismaClient({ adapter });

import { PrismaClient } from "../prisma/generated/client";

import { PrismaPg } from "@prisma/adapter-pg";

import { queryTags } from "@prisma/sqlcommenter-query-tags";

import { traceContext } from "@prisma/sqlcommenter-trace-context";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });

const prisma = new PrismaClient({

  adapter,

  comments: [queryTags(), traceContext()],

});

import { PrismaClient } from "../prisma/generated/client";

import { PrismaPg } from "@prisma/adapter-pg";

import type { SqlCommenterPlugin } from "@prisma/sqlcommenter";

const appPlugin: SqlCommenterPlugin = (context) => ({

  application: "my-app",

  environment: process.env.NODE_ENV ?? "development",

  model: context.query.modelName,

  action: context.query.action,

});

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });

const prisma = new PrismaClient({

  adapter,

  comments: [appPlugin],

});

SELECT "id", "name" FROM "User" /*action='findMany',application='my-app',environment='production',model='User'*/

const prisma = new PrismaClient({

  rejectOnNotFound: true,

});

const prisma = new PrismaClient({

  rejectOnNotFound: {

    findUnique: true,

  },

});

const prisma = new PrismaClient({

  rejectOnNotFound: {

    findFirst: {

      User: (err) => new Error("User error"),

      Post: (err) => new Error("Post error!"),

    },

    findUnique: {

      User: (err) => new Error("User error"),

      Post: (err) => new Error("Post error!"),

    },

  },

});

const prisma = new PrismaClient({

  transactionOptions: {

    isolationLevel: Prisma.TransactionIsolationLevel.Serializable,

    maxWait: 5000, // default: 2000

    timeout: 10000, // default: 5000

  },

});

const result = await prisma.user.findUnique({

  where: {

    id: 42,

  },

});

const result = await prisma.user.findUnique({

  where: {

    email: "alice@prisma.io",

  },

});

const result = await prisma.user.findUnique({

  where: {

    fullname: {

      // name property of @@unique attribute - default is firstname_lastname

      firstName: "Alice",

      lastName: "Smith",

    },

  },

});

const result = await prisma.user.findUnique({

  where: {

    firstName_lastName: {

      firstName: "Alice",

      lastName: "Smith",

    },

  },

});

await prisma.user.findUniqueOrThrow({

  where: { id: 1 },

});

const user = await prisma.user.findFirst({

  where: { name: "Alice" },

});

import { PrismaClient } from "../prisma/generated/client";

const prisma = new PrismaClient({});

async function main() {

  const a = await prisma.post.create({

    data: {

      title: "A test 1",

    },

  });

  const b = await prisma.post.create({

    data: {

      title: "A test 2",

    },

  });

  const c = await prisma.post.findFirst({

    where: {

      title: {

        startsWith: "A test",

      },

    },

    orderBy: {

      title: "asc",

    },

    take: -1, // Reverse the list

  });

}

main();

const user = await prisma.user.findMany({

  where: { name: "Alice" },

});

const user = await prisma.user.create({

  data: { email: "alice@prisma.io" },

});

import { Prisma, PrismaClient } from "../prisma/generated/client";

const prisma = new PrismaClient({ log: ["query"] });

async function main() {

  let users: Prisma.UserCreateInput[] = [

    {

      email: "ariana@prisma.io",

      name: "Ari",

      profileViews: 20,

      coinflips: [true, false, false],

      role: "ADMIN",

    },

    {

      email: "elsa@prisma.io",

      name: "Elsa",

      profileViews: 20,

      coinflips: [true, false, false],

      role: "ADMIN",

    },

  ];

  await Promise.all(

    users.map(async (user) => {

      await prisma.user.create({

        data: user,

      });

    }),

  );

}

main()

  .then(async () => {

    await prisma.$disconnect();

  })

  .catch(async (e) => {

    console.error(e);

    await prisma.$disconnect();

    process.exit(1);

  });

prisma:query BEGIN

prisma:query INSERT INTO "public"."User" ("name","email","profileViews","role","coinflips") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."User"."id"

prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email", "public"."User"."profileViews", "public"."User"."role", "public"."User"."coinflips" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3

prisma:query INSERT INTO "public"."User" ("name","email","profileViews","role","coinflips") VALUES ($1,$2,$3,$4,$5) RETURNING "public"."User"."id"

prisma:query COMMIT

prisma:query SELECT "public"."User"."id", "public"."User"."name", "public"."User"."email", "public"."User"."profileViews", "public"."User"."role", "public"."User"."coinflips" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3

prisma:query COMMIT

const user = await prisma.user.update({

  where: { id: 1 },

  data: { email: "alice@prisma.io" },

});

const user = await prisma.user.upsert({

  where: { id: 1 },

  update: { email: "alice@prisma.io" },

  create: { email: "alice@prisma.io" },

});

model User {

  id           Int    @id

  profileViews Int

  userName     String @unique

  email        String

  @@unique([id, profileViews])

}

prisma.user.upsert({

  where: {

    userName: "Alice",

  },

  create: {

    id: 1,

    profileViews: 1,

    userName: "Alice",

    email: "alice@prisma.io",

  },

  update: {

    email: "updated@example.com",

  },

});

INSERT INTO "public"."User" ("id","profileViews","userName","email") VALUES ($1,$2,$3,$4)

ON CONFLICT ("userName") DO UPDATE

SET "email" = $5 WHERE ("public"."User"."userName" = $6 AND 1=1) RETURNING "public"."User"."id", "public"."User"."profileViews", "public"."User"."userName", "public"."User"."email"

prisma.User.upsert({

  where: {

    userName: "Alice",

    profileViews: 1,

    id: 1,

  },

  create: {

    id: 1,

    profileViews: 1,

    userName: "Alice",

    email: "alice@prisma.io",

  },

  update: {

    email: "updated@example.com",

  },

});

prisma.User.upsert({

  where: {

    userName: "Alice",

  },

  create: {

    id: 1,

    profileViews: 1,

    userName: "AliceS",

    email: "alice@prisma.io",

  },

  update: {

    email: "updated@example.com",

  },

});

prisma.user.upsert({

  select: {

    email: true,

    id: true,

    posts: {

      select: {

        title: true,

      },

    },

  },

  where: {

    userName: "Alice",

  },

  create: {

    id: 1,

    profileViews: 1,

    userName: "Alice",

    email: "alice@prisma.io",

  },

  update: {

    email: "updated@example.com",

  },

});

const user = await prisma.user.delete({

  where: { id: 1 },

});

const deleteUser = await prisma.user.delete({

  where: {

    email: "elsa@prisma.io",

  },

  select: {

    email: true,

    name: true,

  },

});

{ "email": "elsa@prisma.io", "name": "Elsa" }

const users = await prisma.user.createMany({

  data: [

    { name: "Sonali", email: "sonali@prisma.io" },

    { name: "Alex", email: "alex@prisma.io" },

  ],

});

const users = await prisma.user.createManyAndReturn({

  data: [

    { name: "Sonali", email: "sonali@prisma.io" },

    { name: "Alex", email: "alex@prisma.io" },

  ],

});

[

  { "id": 0, "name": "Sonali", "email": "sonali@prisma.io", "profileViews": 0 },

  { "id": 1, "name": "Alex", "email": "alex@prisma.io", "profileViews": 0 }

]

export type BatchPayload = {

  count: number;

};

const updatedUserCount = await prisma.user.updateMany({

  where: { name: "Alice" },

  data: { name: "ALICE" },

});

const updatedUserCount = await prisma.user.updateMany({

  where: {

    email: {

      contains: "prisma.io",

    },

    posts: {

      some: {

        likes: {

          gt: 10,

        },

      },

    },

  },

  data: {

    role: "USER",

  },

});

const updatedUserCount = await prisma.user.updateMany({

  where: {

    email: {

      contains: "prisma.io",

    },

  },

  data: {

    role: "USER",

  },

  limit: 5,

});

const users = await prisma.user.updateManyAndReturn({

  where: {

    email: {

      contains: "prisma.io",

    },

  },

  data: {

    role: "ADMIN",

  },

});

[

  { "id": 0, "name": "Sonali", "email": "sonali@prisma.io", "role": "ADMIN", "profileViews": 0 },

  { "id": 1, "name": "Alex", "email": "alex@prisma.io", "role": "ADMIN", "profileViews": 0 }

]

export type BatchPayload = {

  count: number;

};

const deletedUserCount = await prisma.user.deleteMany({});

const deletedUserCount = await prisma.user.deleteMany({

  where: { name: "Alice" },

});

const deletedUserCount = await prisma.user.deleteMany({

  where: {

    email: {

      contains: "prisma.io",

    },

  },

  limit: 5,

});

const result = await prisma.user.count();

const result = await prisma.user.count({

  where: {

    post: {

      some: {

        published: true,

      },

    },

  },

});

const c = await prisma.user.count({

  select: {

    _all: true,

    city: true,

    name: true,

  },

});

const minMaxAge = await prisma.user.aggregate({

  _count: {

    _all: true,

  },

  _max: {

    profileViews: true,

  },

  _min: {

    profileViews: true,

  },

});

{

  _count: { _all: 29 },

  _max: { profileViews: 90 },

  _min: { profileViews: 0 }

}

const setValue = await prisma.user.aggregate({

  _sum: {

    profileViews: true,

  },

});

{

  "_sum": {

    "profileViews": 9493

  }

}

const groupUsers = await prisma.user.groupBy({

  by: ["country", "city"],

  _count: {

    _all: true,

    city: true,

  },

  _sum: {

    profileViews: true,

  },

  orderBy: {

    country: "desc",

  },

  having: {

    profileViews: {

      _avg: {

        gt: 200,

      },

    },

  },

});

[

  {

    country: "Denmark",

    city: "Copenhagen",

    _sum: { profileViews: 490 },

    _count: {

      _all: 70,

      city: 8,

    },

  },

  {

    country: "Sweden",

    city: "Stockholm",

    _sum: { profileViews: 500 },

    _count: {

      _all: 50,

      city: 3,

    },

  },

];

const result = await prisma.user.findUnique({

  where: { id: 1 },

  select: {

    name: true,

    profileViews: true,

  },

});

{

  name: "Alice",

  profileViews: 0

}

const result = await prisma.user.findMany({

  select: {

    email: true,

    role: true,

  },

});

[

  {

    email: "alice@prisma.io",

    role: "ADMIN",

  },

  {

    email: "bob@prisma.io",

    role: "USER",

  },

];

const usersWithCount = await prisma.user.findMany({

  select: {

    _count: {

      select: { posts: true },

    },

  },

});

{

  _count: {

    posts: 3;

  }

}

const result = await prisma.user.findMany({

  select: {

    id: true,

    name: true,

    posts: {

      select: {

        id: true,

        title: true,

      },

    },

  },

});

[

  {

    id: 1,

    name: "Alice",

    posts: [

      { id: 1, title: "Hello World" },

      { id: 2, title: "Bye bye" },

    ],

  },

  {

    id: 2,

    name: "Bob",

    posts: [],

  },

];

const result = await prisma.user.findMany({

  select: {

    id: true,

    name: true,

    posts: {

      include: {

        author: true,

      },

    },

  },

});

[

  {

    id: 1,

    name: "Alice",

    posts: [

      {

        id: 1,

        title: "Hello World",

        published: true,

        author: {

          id: 1,

          name: "Alice",

          email: "alice@prisma.io",

          role: "ADMIN",

          coinflips: [true, false],

          profileViews: 0,

        },

      },

      {

        id: 2,

        title: "Bye bye",

        published: false,

        author: {

          id: 1,

          name: "Alice",

          email: "alice@prisma.io",

          role: "USER",

          coinflips: [],

          profileViews: 0,

        },

      },

    ],

  },

];

const selectNameEmailNotPosts = Prisma.validator<Prisma.UserSelect>()({

  name: true,

  email: true,

  posts: false,

});

const users = await prisma.user.findMany({

  include: {

    posts: true, // Returns all fields for all posts

    profile: true, // Returns all Profile fields

  },

});

const user = await prisma.user.create({

  data: {

    email: "alice@prisma.io",

    posts: {

      create: [{ title: "This is my first post" }, { title: "Here comes a second post" }],

    },

  },

  include: { posts: true }, // Returns all fields for all posts

});

const includePosts = Prisma.validator<Prisma.UserInclude>()({

  posts: true,

});

const usersWithCount = await prisma.user.findMany({

  include: {

    _count: {

      select: { posts: true },

    },

  },

});

{ id: 1, name: "Bob", email: "bob@prisma.io", _count: { posts: 3 } },

{ id: 2,  name: "Enya", email: "enya@prisma.io", _count: { posts: 2 } }

const result = await prisma.user.findMany({

  omit: {

    password: true,

  },

});

[

  {

    id: 1,

    email: "jenny@prisma.io",

    name: "Jenny",

  },

  {

    id: 2,

    email: "rose@prisma.io",

    name: "Rose",

  },

];

const results = await prisma.user.findMany({

  omit: {

    password: true,

  },

  include: {

    posts: {

      omit: {

        title: true,

      },

    },

  },

});

[

  {

    id: 1,

    email: "jenny@prisma.io",

    name: "Jenny",

    posts: [

      {

        id: 1,

        author: {

          id: 1,

          email: "jenny@prisma.io",

          name: "Jenny",

        },

        authorId: 1,

      },

    ],

  },

  {

    id: 2,

    email: "rose@prisma.io",

    name: "Rose",

    posts: [

      {

        id: 2,

        author: {

          id: 2,

          email: "rose@prisma.io",

          name: "Rose",

        },

        authorId: 2,

      },

    ],

  },

];

const omitPassword = Prisma.validator<Prisma.UserOmit>()({

  password: true,

});

generator client {

  provider        = "prisma-client"

  output          = "./generated"

  previewFeatures = ["relationJoins"]

}

const users = await prisma.user.findMany({

  relationLoadStrategy: "join",

  include: {

    posts: true,

  },

});

const users = await prisma.user.findMany({

  relationLoadStrategy: "join",

  select: {

    posts: true,

  },

});

const results = await prisma.user.findMany({

  where: {

    email: {

      endsWith: "prisma.io",

    },

  },

});

const users = await prisma.user.findMany({

  orderBy: {

    email: "asc",

  },

});

const users = await prisma.user.findMany({

  orderBy: {

    email: "desc",

  },

});

const posts = await prisma.post.findMany({

  orderBy: {

    author: {

      name: "asc",

    },

  },

});

const posts = await prisma.post.findMany({

  orderBy: {

    author: {

      name: { sort: "asc", nulls: "first" },

    },

  },

});

const posts = await prisma.post.findMany({

  orderBy: {

    _relevance: {

      fields: ['title'],

      search: 'database',

      sort: 'asc'

    },

})

const getActiveusers = await prisma.user.findMany({

  orderBy: {

    posts: {

      count: "desc",

    },

  },

});

const users = await prisma.user.findMany({

  select: {

    email: true,

    role: true,

  },

  orderBy: [

    {

      email: "desc",

    },

    {

      role: "desc",

    },

  ],

});

[

  {

    "email": "yuki@prisma.io",

    "role": "USER"

  },

  {

    "email": "nora@prisma.io",

    "role": "USER"

  },

  {

    "email": "mary@prisma.io",

    "role": "MODERATOR"

  },

  {

    "email": "elsa@prisma.io",

    "role": "MODERATOR"

  },

  {

    "email": "eloise@prisma.io",

    "role": "USER"

  },

  {

    "email": "coco@prisma.io",

    "role": "ADMIN"

  },

  {

    "email": "anna@prisma.io",

    "role": "USER"

  },

  {

    "email": "alice@prisma.io",

    "role": "USER"

  }

]

const users = await prisma.user.findMany({

  select: {

    email: true,

    role: true,

  },

  orderBy: [

    {

      role: "desc",

    },

    {

      email: "desc",

    },

  ],

});

[

  {

    "email": "mary@prisma.io",

    "role": "MODERATOR"

  },

  {

    "email": "elsa@prisma.io",

    "role": "MODERATOR"

  },

  {

    "email": "yuki@prisma.io",

    "role": "USER"

  },

  {

    "email": "nora@prisma.io",

    "role": "USER"

  },

  {

    "email": "eloise@prisma.io",

    "role": "USER"

  },

  {

    "email": "anna@prisma.io",

    "role": "USER"

  },

  {

    "email": "alice@prisma.io",

    "role": "USER"

  },

  {

    "email": "coco@prisma.io",

    "role": "ADMIN"

  }

]

const users3 = await prisma.user.findMany({

  orderBy: {

    email: "asc",

  },

  select: {

    name: true,

    email: true,

  },

});

[

  {

    name: "Alice",

    email: "alice@prisma.io",

  },

  {

    name: "Ariadne",

    email: "ariadne@prisma.io",

  },

  {

    name: "Bob",

    email: "bob@prisma.io",

  },

];

const usersWithPosts = await prisma.user.findMany({

  orderBy: {

    email: "asc",

  },

  include: {

    posts: {

      select: {

        title: true,

      },

      orderBy: {

        title: "asc",

      },

    },

  },

});

[

  {

    "id": 2,

    "email": "alice@prisma.io",

    "name": "Alice",

    "posts": [

      {

        "title": "Watch the talks from Prisma Day 2019"

      }

    ]

  },

  {

    "id": 3,

    "email": "ariadne@prisma.io",

    "name": "Ariadne",

    "posts": [

      {

        "title": "How to connect to a SQLite database"

      },

      {

        "title": "My first day at Prisma"

      }

    ]

  },

  {

    "id": 1,

    "email": "bob@prisma.io",

    "name": "Bob",

    "posts": [

      {

        "title": "Follow Prisma on Twitter"

      },

      {

        "title": "Subscribe to GraphQL Weekly for community news "

      }

    ]

  }

]

const userWithPosts = await prisma.user.findUnique({

  where: {

    id: 1,

  },

  include: {

    posts: {

      orderBy: {

        title: "desc",

      },

      select: {

        title: true,

        published: true,

      },

    },

  },

});

{

  "email": "sarah@prisma.io",

  "id": 1,

  "name": "Sarah",

  "extendedProfile": null,

  "role": "USER",

  "posts": [

    {

      "title": "Prisma Day 2020",

      "published": false

    },

    {

      "title": "My first post",

      "published": false

    },

    {

      "title": "All about databases",

      "published": true

    }

  ]

}

const sort = await prisma.user.findMany({

  orderBy: {

    role: "desc",

  },

  select: {

    email: true,

    role: true,

  },

});

[

  {

    "email": "emma@prisma.io",

    "role": "USER"

  },

  {

    "email": "suma@prisma.io",

    "role": "ADMIN"

  },

  {

    "email": "kwame@prisma.io",

    "role": "ADMIN"

  },

  {

    "email": "pearl@prisma.io",

    "role": "ADMIN"

  }

]

const orderEmailsByDescending = Prisma.validator<Prisma.UserOrderByInput>()({

  email: "desc",

});

const distinctCities = await prisma.user.findMany({

  select: {

    city: true,

    country: true,

  },

  distinct: ["city"],

});

[

  { city: "Paris", country: "France" },

  { city: "Lyon", country: "France" },

];

const distinctCitiesAndCountries = await prisma.user.findMany({

  select: {

    city: true,

    country: true,

  },

  distinct: ["city", "country"],

});

[

  { city: "Paris", country: "France" },

  { city: "Paris", country: "Denmark" },

  { city: "Lyon", country: "France" },

];

const distinctCitiesAndCountries = await prisma.user.findMany({

  where: {

    email: {

      contains: "prisma.io",

    },

  },

  select: {

    city: true,

    country: true,

  },

  distinct: ["city", "country"],

});

[

  { city: "Paris", country: "Denmark" },

  { city: "Lyon", country: "France" },

];

generator client {

  provider        = "prisma-client"

  output          = "./generated"

  previewFeatures = ["nativeDistinct"]

}

See [Preview Features](/orm/v6/reference/preview-features/client-preview-features#preview-features-promoted-to-general-availability) for more details.

## Nested queries

### `create`

A nested `create` query adds a new related record or set of records to a parent record. See: [Working with relations](/orm/v6/prisma-client/queries/relation-queries)

#### Remarks

- `create` is available as a nested query when you `create()` (`prisma.user.create(...)`) a new parent record or `update()` (`prisma.user.update(...)`) an existing parent record.

- You can use a nested `create` _or_ a nested [`createMany`](#createmany-1) to create multiple related records. If you require the [`skipDuplicates` query option](#nested-createmany-options) you should use `createMany`.

#### Examples

##### Create a new `User` record with a new `Profile` record

```ts highlight=5;normal

const user = await prisma.user.create({

  data: {

    email: 'alice@prisma.io',

    profile: {

      create: { bio: 'Hello World' },

    },

  },

});

const user = await prisma.profile.create({

  data: {

    bio: "Hello World",

    user: {

      create: { email: "alice@prisma.io" }, 

    },

  },

});

const user = await prisma.user.create({

  data: {

    email: "alice@prisma.io",

    posts: {

      create: { title: "Hello World" },

    },

  },

});

const user = await prisma.user.create({

  data: {

    email: "alice@prisma.io",

    posts: {

      create: [

        {

          title: "This is my first post",

        },

        {

          title: "Here comes a second post",

        },

      ],

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    profile: {

      create: { bio: "Hello World" },

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    posts: {

      create: { title: "Hello World" }, 

    },

  },

});

const user = await prisma.user.update({

  where: {

    id: 9,

  },

  data: {

    name: "Elliott",

    posts: {

      createMany: {

        data: [{ title: "My first post" }, { title: "My second post" }],

      },

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    posts: {

      set: [{ id: 32 }, { id: 42 }],

    },

  },

});

const user = await prisma.profile.create({

  data: {

    bio: "Hello World",

    user: {

      connect: { email: "alice@prisma.io" },

    },

  },

});

const user = await prisma.profile.create({

  data: {

    bio: "Hello World",

    user: {

      connect: { id: 42 }, // sets userId of Profile record

    },

  },

});

const user = await prisma.profile.create({

  data: {

    bio: "Hello World",

    userId: 42,

  },

});

const user = await prisma.post.create({

  data: {

    title: "Hello World",

    author: {

      connect: { email: "alice@prisma.io" },

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    profile: {

      connect: { id: 24 },

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    posts: {

      connect: [{ id: 24 }, { id: 42 }],

    },

  },

});

const user = await prisma.profile.create({

  data: {

    bio: 'The coolest Alice on the planet',

    user: {

      connectOrCreate: {

        where:  { email: 'alice@prisma.io' },

        create: { email: 'alice@prisma.io'}

    },

  },

})

const user = await prisma.post.create({

  data: {

    title: "Hello World",

    author: {

      connectOrCreate: {

        where: { email: "alice@prisma.io" },

        create: { email: "alice@prisma.io" },

      },

    },

  },

});

const updateUser = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    profile: {

      connectOrCreate: {

        where: { id: 20 },

        create: {

          bio: "The coolest Alice in town",

        },

      },

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    posts: {

      connectOrCreate: [

        {

          where: { id: 32 },

          create: { title: "This is my first post" },

        },

        {

          where: { id: 19 },

          create: { title: "This is my second post" },

        },

      ],

    },

  },

});

const user = await prisma.user.update({

  where: { email: "bob@prisma.io" },

  data: {

    profile: {

      disconnect: true,

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    posts: {

      disconnect: [{ id: 44 }, { id: 46 }],

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    profile: {

      update: { bio: "Hello World" },

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    posts: {

      update: [

        {

          data: { published: true },

          where: { id: 32 },

        },

        {

          data: { published: true },

          where: { id: 23 },

        },

      ],

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    profile: {

      upsert: {

        create: { bio: "Hello World" },

        update: { bio: "Hello World" },

      },

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    posts: {

      upsert: [

        {

          create: { title: "This is my first post" },

          update: { title: "This is my first post" },

          where: { id: 32 },

        },

        {

          create: { title: "This is my second post" },

          update: { title: "This is my second post" },

          where: { id: 23 },

        },

      ],

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    profile: {

      delete: true,

    },

  },

});

const user = await prisma.user.update({

  where: { email: "alice@prisma.io" },

  data: {

    posts: {

      delete: [{ id: 34 }, { id: 36 }],

    },

  },

});

const result = await prisma.user.update({

  where: {

    id: 2,

  },

  data: {

    posts: {

      updateMany: {

        where: {

          published: false,

        },

        data: {

          likes: 0,

        },

      },

    },

  },

});

const result = await prisma.user.update({

  where: {

    id: 2,

  },

  data: {

    name: "Updated name",

    posts: {

      deleteMany: {},

    },

  },

});

const result = await prisma.user.findMany({

  where: {

    name: {

      equals: "Eleanor",

    },

  },

});

const result = await prisma.user.findMany({

  where: {

    name: "Eleanor",

  },

});

const productsWithLowQuantity = await prisma.product.findMany({

  where: {

    quantity: {

      lte: prisma.product.fields.warnQuantity,

    },

  },

});

const favoriteColorFriends = await prisma.user.findMany({

  where: {

    favoriteColors: {

      equals: ["blue", "green"],

    },

  },

});

const result = await prisma.user.findMany({

  where: {

    name: {

      not: "Eleanor",

    },

  },

});

await prisma.user.findMany({

  where: {

    OR: [{ name: { not: "Eleanor" } }, { name: null }],

  },

});

const getUser = await prisma.user.findMany({

  where: {

    id: { in: [22, 91, 14, 2, 5] },

  },

});

const getUser = await prisma.user.findMany({

  where: {

    name: { in: ["Saqui", "Clementine", "Bob"] },

  },

});

const getUser = await prisma.user.findMany({

  where: {

    NOT: {

      name: { in: ["Saqui", "Clementine", "Bob"] },

    },

  },

});

const getUser = await prisma.user.findMany({

  where: {

    // Find users where..

    posts: {

      some: {

        // ..at least one (some) posts..

        categories: {

          some: {

            // .. have at least one category ..

            name: {

              in: ["Food", "Introductions"], // .. with a name that matches one of the following.

            },

          },

        },

      },

    },

  },

});

const getUser = await prisma.user.findMany({

  where: {

    id: { notIn: [22, 91, 14, 2, 5] },

  },

});

const getPosts = await prisma.post.findMany({

  where: {

    likes: {

      lt: 9,

    },

  },

});

const getPosts = await prisma.post.findMany({

  where: {

    likes: {

      lte: 9,

    },

  },

});

const getPosts = await prisma.post.findMany({

  where: {

    likes: {

      gt: 9,

    },

  },

});

const getPosts = await prisma.post.findMany({

  where: {

    likes: {

      gte: 9,

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    date_created: {

      gte: new Date("2020-03-19T14:21:00+0200") /* Includes time offset for UTC */,

    },

  },

});

const result = await prisma.post.count({

  where: {

    content: {

      contains: "databases",

    },

  },

});

const result = await prisma.post.count({

  where: {

    NOT: {

      content: {

        contains: "databases",

      },

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    title: {

      search: "cat | dog",

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    title: {

      search: "cat & dog",

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    title: {

      search: "!cat",

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    title: {

      contains: "prisma",

      mode: "insensitive",

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    title: {

      startsWith: "Pr",

    },

  },

});

const result = await prisma.user.findMany({

  where: {

    email: {

      endsWith: "prisma.io",

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    AND: [

      {

        content: {

          contains: "Prisma",

        },

      },

      {

        published: {

          equals: false,

        },

      },

    ],

  },

});

const result = await prisma.post.findMany({

  where: {

    content: {

      contains: "Prisma",

    },

    published: {

      equals: false,

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    OR: [

      {

        title: {

          contains: "Prisma",

        },

      },

      {

        title: {

          contains: "databases",

        },

      },

    ],

    AND: {

      published: false,

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    OR: [

      {

        title: {

          contains: "Prisma",

        },

      },

      {

        title: {

          contains: "databases",

        },

      },

    ],

  },

});

const result = await prisma.post.findMany({

  where: {

    OR: [

      {

        title: {

          contains: "Prisma",

        },

      },

      {

        title: {

          contains: "databases",

        },

      },

    ],

    NOT: {

      title: {

        contains: "SQL",

      },

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    OR: [

      {

        title: {

          contains: "Prisma",

        },

      },

      {

        title: {

          contains: "databases",

        },

      },

    ],

    AND: {

      published: false,

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    NOT: {

      title: {

        contains: "SQL",

      },

    },

  },

});

const result = await prisma.post.findMany({

  where: {

    OR: [

      {

        title: {

          contains: "Prisma",

        },

      },

      {

        title: {

          contains: "databases",

        },

      },

    ],

    NOT: {

      title: {

        contains: "SQL",

      },

    },

    user: {

      NOT: {

        email: {

          contains: "sarah",

        },

      },

    },

  },

  include: {

    user: true,

  },

});

const result = await prisma.user.findMany({

  where: {

    post: {

      some: {

        content: {

          contains: "Prisma"

        }

      }

    }

  }

}

const result = await prisma.user.findMany({

  where: {

    post: {

      every: {

        published: true

      },

    }

  }

}

const result = await prisma.user.findMany({

  where: {

    post: {

        none: {} // User has no posts

    }

  }

}

const result = await prisma.user.findMany({

  where: {

    post: {

        none: {

          published: true

        }

    }

  }

}

const result = await prisma.post.findMany({

  where: {

    user: {

        is: {

          name: "Bob"

        },

    }

  }

}

const result = await prisma.post.findMany({

  where: {

    user: {

        isNot: {

          name: "Bob"

        },

    }

  }

}

const setTags = await prisma.post.update({

  where: {

    id: 9,

  },

  data: {

    tags: {

      set: ["computing", "books"],

    },

  },

});

const setTags = await prisma.post.update({

  where: {

    id: 9,

  },

  data: {

    tags: ["computing", "books"],

  },

});

const setTags = await prisma.post.update({

  where: {

    id: 9,

  },

  data: {

    tags: {

      set: "computing",

    },

  },

});

const addTag = await prisma.post.update({

  where: {

    id: 9,

  },

  data: {

    tags: {

      push: "computing",

    },

  },

});

const addTag = await prisma.post.update({

  where: {

    id: 9,

  },

  data: {

    tags: {

      push: ["computing", "genetics"],

    },

  },

});

const setTags = await prisma.post.update({

  where: {

    id: 9,

  },

  data: {

    tags: {

      unset: true,

    },

  },

});

const posts = await client.post.findMany({

  where: {

    tags: {

      has: "databases",

    },

  },

});

const posts = await client.post.findMany({

  where: {

    NOT: {

      tags: {

        has: "databases",

      },

    },

  },

});

const posts = await prisma.post.findMany({

  where: {

    tags: {

      hasEvery: ["databases", "typescript"],

    },

  },

});

const posts = await prisma.post.findMany({

  where: {

    tags: {

      hasSome: ["databases", "typescript"],

    },

  },

});

const posts = await prisma.post.findMany({

  where: {

    tags: {

      isEmpty: true,

    },

  },

});

const posts = await prisma.post.findMany({

  where: {

    tags: {

      isSet: true,

    },

  },

});

const posts = await prisma.post.findMany({

  where: {

    tags: {

      equals: ["databases", "typescript"],

    },

  },

});

const order = await prisma.order.create({

  data: {

    // Normal relation

    product: { connect: { id: "some-object-id" } },

    color: "Red",

    size: "Large",

    // Composite type

    shippingAddress: {

      set: {

        street: "1084 Candycane Lane",

        city: "Silverlake",

        zip: "84323",

      },

    },

  },

});

const order = await prisma.order.create({

  data: {

    // Embedded optional type, set to null

    billingAddress: {

      set: null,

    },

  },

});

const order = await prisma.order.update({

  where: {

    id: "some-object-id",

  },

  data: {

    billingAddress: {

      // Unset the billing address

      // Removes "billingAddress" field from order

      unset: true,

    },

  },

});

const order = await prisma.order.update({

  where: {

    id: "some-object-id",

  },

  data: {

    shippingAddress: {

      // Update just the zip field

      update: {

        zip: "41232",

      },

    },

  },

});

const order = await prisma.order.update({

  where: {

    id: "some-object-id",

  },

  data: {

    billingAddress: {

      // Create the address if it doesn't exist,

      // otherwise update it

      upsert: {

        set: {

          street: "1084 Candycane Lane",

          city: "Silverlake",

          zip: "84323",

        },

        update: {

          zip: "84323",

        },

      },

    },

  },

});

const product = prisma.product.update({

  where: {

    id: 10,

  },

  data: {

    photos: {

      // Push a photo to the end of the photos list

      push: [{ height: 100, width: 200, url: "1.jpg" }],

    },

  },

});

const orders = await prisma.order.findMany({

  where: {

    shippingAddress: {

      equals: {

        street: "555 Candy Cane Lane",

        city: "Wonderland",

        zip: "52337",

      },

    },

  },

});

const product = prisma.product.findMany({

  where: {

    equals: {

      photos: [{ url: "1.jpg" }, { url: "2.jpg" }],

    },

  },

});

const orders = await prisma.order.findMany({

  where: {

    shippingAddress: {

      is: {

        street: "555 Candy Cane Lane",

      },

    },

  },

});

const orders = await prisma.order.findMany({

  where: {

    shippingAddress: {

      isNot: {

        zip: "52337",

      },

    },

  },

});

const product = prisma.product.findMany({

  where: {

    photos: {

      isEmpty: true,

    },

  },

});

const product = await prisma.product.findFirst({

  where: {

    photos: {

      every: {

        height: 200,

      },

    },

  },

});

const product = await prisma.product.findFirst({

  where: {

    photos: {

      some: {

        url: "2.jpg",

      },

    },

  },

});

const product = await prisma.product.findFirst({

  where: {

    photos: {

      none: {

        url: "2.jpg",

      },

    },

  },

});

const updatePosts = await prisma.post.updateMany({

  data: {

    views: {

      increment: 1,

    },

    likes: {

      increment: 1,

    },

  },

});

const updatePosts = await prisma.post.updateMany({

  data: {

    views: {

      set: 0,

    },

  },

});

const updatePosts = await prisma.post.updateMany({

  data: {

    views: 0,

  },

});

{

  "favorites": {

    "catBreed": "Turkish van",

    "dogBreed": "Rottweiler",

    "sanctuaries": ["RSPCA", "Alley Cat Allies"],

    "treats": [

      { "name": "Dreamies", "manufacturer": "Mars Inc" },

      { "name": "Treatos", "manufacturer": "The Dog People" }

    ]

  },

  "fostered": {

    "cats": ["Bob", "Alice", "Svetlana the Magnificent", "Queenie"]

  },

  "owned": {

    "cats": ["Elliott"]

  }

}

const getUsers = await prisma.user.findMany({

  where: {

    pets: {

      path: "$.favorites.treats[*].name",

      array_contains: "Dreamies",

    },

  },

});