Composite types

Composite types, known as embedded documents in MongoDB, allow you to embed records within other records.

This page explains how to:

• find records that contain composite types using findFirst and findMany
• create new records with composite types using create and createMany
• update composite types within existing records using update and updateMany
• delete records with composite types using delete and deleteMany

Example schema

We’ll use this schema for the examples that follow:

schema.prisma
1model Product {
2  id     String  @id @default(auto()) @map("_id") @db.ObjectId
3  name   String  @unique
4  price  Decimal
5  colors Color[]
6  sizes  Size[]
7  photos Photo[]
8  orders Order[]
9}
10
11model Order {
12  id              String   @id @default(auto()) @map("_id") @db.ObjectId
13  product         Product  @relation(fields: [productId], references: [id])
14  color           Color
15  size            Size
16  shippingAddress Address
17  billingAddress  Address?
18  productId       String   @db.ObjectId
19}
20
21enum Color {
22  Red
23  Green
24  Blue
25}
26
27enum Size {
28  Small
29  Medium
30  Large
31  XLarge
32}
33
34type Photo {
35  height Int    @default(200)
36  width  Int    @default(100)
37  url    String
38}
39
40type Address {
41  street String
42  city   String
43  zip    String
44}

In this schema, the Product model has a Photo[] composite type, and the Order model has two composite Address types. The shippingAddress is required, but the billingAddress is optional.

Considerations when using composite types

There are currently some limitations when using composite types in the Prisma Client:

• findUnique can't filter on composite types
• aggregrate, groupBy, count don’t support composite operations

Finding records that contain composite types with find and findMany

Records can be filtered by a composite type within the where operation.

The following section describes the operations available for filtering by a single type or multiple types, and gives examples of each.

const orders = await prisma.order.findMany({
  where: {
    shippingAddress: {
      is: {
        street: '555 Candy Cane Lane',
      },
    },
  },
})

const orders = await prisma.order.findMany({
  where: {
    shippingAddress: {
      street: '555 Candy Cane Lane',
    },
  },
})

const orders = await prisma.order.findMany({
  where: {
    shippingAddress: {
      equals: {
        street: '555 Candy Cane Lane',
        city: 'Wonderland',
        zip: '52337',
      },
    },
  },
})

const orders = await prisma.order.findMany({
  where: {
    shippingAddress: {
      isNot: {
        zip: '52337',
      },
    },
  },
})

const orders = await prisma.order.findMany({
  where: {
    billingAddress: {
      isSet: true,
    },
  },
})

const product = prisma.product.findMany({
  where: {
    equals: {
      photos: [{ url: '1.jpg' }, { url: '2.jpg' }],
    },
  },
})

const product = prisma.product.findMany({
  where: {
    photos: [{ url: '1.jpg' }, { url: '2.jpg' }],
  },
})

const product = prisma.product.findMany({
  where: {
    photos: {
      isEmpty: true,
    },
  },
})

const product = prisma.product.findFirst({
  where: {
    photos: {
      some: {
        { url: "2.jpg" },
      }
    }
  },
})

const product = prisma.product.findFirst({
  where: {
    photos: {
      none: {
        { url: "2.jpg" },
      }
    }
  },
})

Creating records with composite types using create and createMany

Composite types can be created within a create or createMany method using the set operation. For example, you can use set within create to create an Address composite type inside an Order:

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',
      },
    },
  },
})

You can also use a shorthand notation where you leave out the set and specify just the fields that you want to create:

const order = await prisma.order.create({
  data: {
    // Normal relation
    product: { connect: { id: 'some-object-id' } },
    color: 'Red',
    size: 'Large',
    // Composite type
    shippingAddress: {
      street: '1084 Candycane Lane',
      city: 'Silverlake',
      zip: '84323',
    },
  },
})

For an optional type, like the billingAddress, you can also set the value to null:

const order = await prisma.order.create({
  data: {
    // Embedded optional type, set to null
    billingAddress: {
      set: null,
    },
  },
})

To model the case where an product contains a list of multiple photos, you can set multiple composite types at once:

const product = await prisma.product.create({
  data: {
    name: "Forest Runners",
    price: 59.99,
    colors: ["Red", "Green"],
    sizes: ["Small", "Medium", "Large"]
    // New composite type
    photos: {
      set: [
        { height: 100, width: 200, url: "1.jpg" },
        { height: 100, width: 200, url: "2.jpg" }
      ]
    }
  }
})

You can also use a shorthand notation where you leave out the set and specify just the fields that you want to create:

const product = await prisma.product.create({
  data: {
    name: 'Forest Runners',
    price: 59.99,
    // Scalar lists that we already support
    colors: ['Red', 'Green'],
    sizes: ['Small', 'Medium', 'Large'],
    // New composite type
    photos: [
      { height: 100, width: 200, url: '1.jpg' },
      { height: 100, width: 200, url: '2.jpg' },
    ],
  },
})

These operations also work within the createMany method. For example, you can create multiple products which each contain a list of photos:

const product = await prisma.product.createMany({
  data: [
    {
      name: 'Forest Runners',
      price: 59.99,
      colors: ['Red', 'Green'],
      sizes: ['Small', 'Medium', 'Large'],
      photos: [
        { height: 100, width: 200, url: '1.jpg' },
        { height: 100, width: 200, url: '2.jpg' },
      ],
    },
    {
      name: 'Alpine Blazers',
      price: 85.99,
      colors: ['Blue', 'Red'],
      sizes: ['Large', 'XLarge'],
      photos: [
        { height: 100, width: 200, url: '1.jpg' },
        { height: 150, width: 200, url: '4.jpg' },
        { height: 200, width: 200, url: '5.jpg' },
      ],
    },
  ],
})

Changing composite types within update and updateMany

Composite types can be set, updated or removed within an update or updateMany method. The following section describes the operations available for updating a single type or multiple types at once, and gives examples of each.

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 order = await prisma.order.update({
  where: {
    id: 'some-object-id',
  },
  data: {
    billingAddress: {
      // Unset the billing address
      // Removes "billingAddress" field from order
      unset: true,
    },
  },
})

const orders = await prisma.order.updateMany({
  where: {
    shippingAddress: {
      is: {
        street: '555 Candy Cane Lane',
      },
    },
  },
  data: {
    shippingAddress: {
      update: {
        street: '111 Candy Cane Drive',
      },
    },
  },
})

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 product = prisma.product.update({
  where: {
    id: 10,
  },
  data: {
    photos: {
      updateMany: {
        where: {
          url: '1.jpg',
        },
        data: {
          url: '2.png',
        },
      },
    },
  },
})

const product = prisma.product.update({
  where: {
    id: 10,
  },
  data: {
    photos: {
      deleteMany: {
        where: {
          height: 100,
        },
      },
    },
  },
})

Upserting composite types with upsert

To create or update a composite type, use the upsert method. You can use the same composite operations as the create and update methods above.

For example, use upsert to either create a new product or add a photo to an existing product:

const product = await prisma.product.upsert({
  where: {
    name: 'Forest Runners',
  },
  create: {
    name: 'Forest Runners',
    price: 59.99,
    colors: ['Red', 'Green'],
    sizes: ['Small', 'Medium', 'Large'],
    photos: [
      { height: 100, width: 200, url: '1.jpg' },
      { height: 100, width: 200, url: '2.jpg' },
    ],
  },
  update: {
    photos: {
      push: { height: 300, width: 400, url: '3.jpg' },
    },
  },
})

Deleting records that contain composite types with delete and deleteMany

To remove records which embed a composite type, use the delete or deleteMany methods. This will also remove the embedded composite type.

For example, use deleteMany to delete all products with a size of "Small". This will also delete any embedded photos.

const deleteProduct = await prisma.product.deleteMany({
  where: {
    sizes: ['Small'],
  },
})

You can also use filters to delete records that match a composite type. The example below uses the some filter to delete products that contain a certain photo:

const product = await prisma.product.deleteMany({
  where: {
    photos: {
      some: {
        url: '2.jpg',
      },
    },
  },
})

Ordering composite types

You can use the orderBy operation to sort results in ascending or descending order.

For example, the following command finds all orders and orders them by the city name in the shipping address, in ascending order:

const orders = await prisma.order.findMany({
  orderBy: {
    shippingAddress: {
      city: 'asc',
    },
  },
})