Composite types

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

We made composite types Generally Available in v3.12.0. They were previously available in Preview from v3.10.0.

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
1generator client {
2  provider = "prisma-client-js"
3}
4
5datasource db {
6  provider = "mongodb"
7  url      = env("DATABASE_URL")
8}
9
10model Product {
11  id     String  @id @default(auto()) @map("_id") @db.ObjectId
12  name   String  @unique
13  price  Float
14  colors Color[]
15  sizes  Size[]
16  photos Photo[]
17  orders Order[]
18}
19
20model Order {
21  id              String   @id @default(auto()) @map("_id") @db.ObjectId
22  product         Product  @relation(fields: [productId], references: [id])
23  color           Color
24  size            Size
25  shippingAddress Address
26  billingAddress  Address?
27  productId       String   @db.ObjectId
28}
29
30enum Color {
31  Red
32  Green
33  Blue
34}
35
36enum Size {
37  Small
38  Medium
39  Large
40  XLarge
41}
42
43type Photo {
44  height Int    @default(200)
45  width  Int    @default(100)
46  url    String
47}
48
49type Address {
50  street String
51  city   String
52  zip    String
53}

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 Prisma Client:

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

Default values for required fields on composite types

From version 4.0.0, if you carry out a database read on a composite type when all of the following conditions are true, then Prisma Client inserts the default value into the result.

Conditions:

• A field on the composite type is required, and
• this field has a default value, and
• this field is not present in the returned document or documents.

Note:

• This is the same behavior as with model fields.
• On read operations, Prisma Client inserts the default value into the result, but does not insert the default value into the database.

In our example schema, suppose that you add a required field to photo. This field, bitDepth, has a default value:

schema.prisma
1...
2type Photo {
3  ...
+  bitDepth    Int    @default(8)
5}
6...

Suppose that you then run npx prisma migrate deploy to deploy your database changes and regenerate your Prisma Client with npx prisma generate. Then, you run the following application code:

console.dir(await prisma.product.findMany({}), { depth: Infinity })

The bitDepth field has no content because you have only just added this field, so the query returns the default value of 8.

Earlier versions

Before version 4.0.0, Prisma threw a P2032 error as follows:

Error converting field "bitDepth" of expected non-nullable
type "int", found incompatible value of "null".

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: {
      equals: {
        street: '555 Candy Cane Lane',
        city: 'Wonderland',
        zip: '52337',
      },
    },
  },
})

const orders = await prisma.order.findMany({
  where: {
    shippingAddress: {
      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: {
    photos: {
      equals: [
        {
          url: '1.jpg',
          height: 200,
          width: 100,
        },
        {
          url: '2.jpg',
          height: 200,
          width: 100,
        },
      ],
    },
  },
})

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

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: {
    // Normal relation
    product: { connect: { id: 'some-object-id' } },
    color: 'Red',
    size: 'Large',
    // Composite type
    shippingAddress: {
      street: '1084 Candycane Lane',
      city: 'Silverlake',
      zip: '84323',
    },
    // 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: '62de6d328a65d8fffdae2c18',
  },
  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: '62de6d328a65d8fffdae2c18',
  },
  data: {
    photos: {
      updateMany: {
        where: {
          url: '1.jpg',
        },
        data: {
          url: '2.png',
        },
      },
    },
  },
})

const product = prisma.product.update({
  where: {
    id: '62de6d328a65d8fffdae2c18',
  },
  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: {
      equals: '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',
    },
  },
})

Duplicate values in unique fields of composite types

Be careful when you carry out any of the following operations on a record with a composite type that has a unique constraint. In this situation, MongoDB does not enforce unique values inside a record.

• When you create the record
• When you add data to the record
• When you update data in the record

If your schema has a composite type with a @@unique constraint, MongoDB prevents you from storing the same value for the constrained value in two or more of the records that contain this composite type. However, MongoDB does does not prevent you from storing multiple copies of the same field value in a single record.

Note that you can use Prisma relations to work around this issue.

For example, in the following schema, MailBox has a composite type, addresses, which has a @@unique constraint on the email field.

type Address {
  email String
}

model MailBox {
  name       String
  addresses  Address[]

  @@unique([addresses.email])
}

The following code creates a record with two identical values in address. MongoDB does not throw an error in this situation, and it stores alice@prisma.io in addresses twice.

await prisma.MailBox.createMany({
  data: [
    {
      name: 'Alice',
      addresses: {
        set: [
          {
            address: 'alice@prisma.io', // Not unique
          },
          {
            address: 'alice@prisma.io', // Not unique
          },
        ],
      },
    },
  ],
})

Note: MongoDB throws an error if you try to store the same value in two separate records. In our example above, if you try to store the email address alice@prisma.io for the user Alice and for the user Bob, MongoDB does not store the data and throws an error.

model Address {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  name      String
  mailbox   Mailbox? @relation(fields: [mailboxId], references: [id])
  mailboxId String?  @db.ObjectId

  @@unique([name])
}

model Mailbox {
  id        String    @id @default(auto()) @map("_id") @db.ObjectId
  name      String
  addresses Address[] @relation
}

await prisma.MailBox.create({
  data: {
    name: 'Alice',
    addresses: {
      create: [
        { name: 'alice@prisma.io' }, // Not unique
        { name: 'alice@prisma.io' }, // Not unique
      ],
    },
  },
})