Composite types

warning

Composite types are only available with MongoDB.

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

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}

model Product {
  id     String  @id @default(auto()) @map("_id") @db.ObjectId
  name   String  @unique
  price  Float
  colors Color[]
  sizes  Size[]
  photos Photo[]
  orders Order[]
}

model Order {
  id              String   @id @default(auto()) @map("_id") @db.ObjectId
  product         Product  @relation(fields: [productId], references: [id])
  color           Color
  size            Size
  shippingAddress Address
  billingAddress  Address?
  productId       String   @db.ObjectId
}

enum Color {
  Red
  Green
  Blue
}

enum Size {
  Small
  Medium
  Large
  XLarge
}

type Photo {
  height Int    @default(200)
  width  Int    @default(100)
  url    String
}

type Address {
  street String
  city   String
  zip    String
}

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

...
type Photo {
  ...
  bitDepth Int @default(8)
}

...

Suppose that you then run npx prisma db push to update your database 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 ORM 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.

Filtering for one composite type

Use the is, equals, isNot and isSet operations to change a single composite type:

• is: Filter results by matching composite types. Requires one or more fields to be present (e.g. Filter orders by the street name on the shipping address)
• equals: Filter results by matching composite types. Requires all fields to be present. (e.g. Filter orders by the full shipping address)
• isNot: Filter results by non-matching composite types
• isSet : Filter optional fields to include only results that have been set (either set to a value, or explicitly set to null). Setting this filter to true will exclude undefined results that are not set at all.

For example, use is to filter for orders with a street name of '555 Candy Cane Lane':

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

Use equals to filter for orders which match on all fields in the shipping address:

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

You can also use a shorthand notation for this query, where you leave out the equals:

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

Use isNot to filter for orders that do not have a zip code of '52337':

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

Use isSet to filter for orders where the optional billingAddress has been set (either to a value or to null):

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

Filtering for many composite types

Use the equals, isEmpty, every, some and none operations to filter for multiple composite types:

• equals: Checks exact equality of the list
• isEmpty: Checks if the list is empty
• every: Every item in the list must match the condition
• some: One or more of the items in the list must match the condition
• none: None of the items in the list can match the condition
• isSet : Filter optional fields to include only results that have been set (either set to a value, or explicitly set to null). Setting this filter to true will exclude undefined results that are not set at all.

For example, you can use equals to find products with a specific list of photos (all url, height and width fields must match):

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

You can also use a shorthand notation for this query, where you leave out the equals and specify just the fields that you want to filter for:

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

Use isEmpty to filter for products with no photos:

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

Use some to filter for products where one or more photos has a url of "2.jpg":

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

Use none to filter for products where no photos have a url of "2.jpg":

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

Creating records with composite types using create and createMany

info

When you create a record with a composite type that has a unique restraint, note that MongoDB does not enforce unique values inside a record. Learn more.

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

info

When you update a record with a composite type that has a unique restraint, note that MongoDB does not enforce unique values inside a record. Learn more.

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.

Changing a single composite type

Use the set, unset update and upsert operations to change a single composite type:

• Use set to set a composite type, overriding any existing value
• Use unset to unset a composite type. Unlike set: null, unset removes the field entirely
• Use update to update a composite type
• Use upsert to update an existing composite type if it exists, and otherwise set the composite type

For example, use update to update a required shippingAddress with an Address composite type inside an Order:

const order = await prisma.order.update({
  where: {
    id: 'some-object-id',
  },
  data: {
    shippingAddress: {
      // Update just the zip field
      update: {
        zip: '41232',
      },
    },
  },
})

For an optional embedded type, like the billingAddress, use upsert to create a new record if it does not exist, and update the record if it does:

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

You can also use the unset operation to remove an optional embedded type. The following example uses unset to remove the billingAddress from an Order:

const order = await prisma.order.update({
  where: {
    id: 'some-object-id',
  },
  data: {
    billingAddress: {
      // Unset the billing address
      // Removes "billingAddress" field from order
      unset: true,
    },
  },
})

You can use filters within updateMany to update all records that match a composite type. The following example uses the is filter to match the street name from a shipping address on a list of orders:

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

Changing multiple composite types

Use the set, push, updateMany and deleteMany operations to change a list of composite types:

• set: Set an embedded list of composite types, overriding any existing list
• push: Push values to the end of an embedded list of composite types
• updateMany: Update many composite types at once
• deleteMany: Delete many composite types at once

For example, use push to add a new photo to the photos list:

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

Use updateMany to update photos with a url of 1.jpg or 2.png:

const product = prisma.product.update({
  where: {
    id: '62de6d328a65d8fffdae2c18',
  },
  data: {
    photos: {
      updateMany: {
        where: {
          url: '1.jpg',
        },
        data: {
          url: '2.png',
        },
      },
    },
  },
})

The following example uses deleteMany to delete all photos with a height of 100:

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

Upserting composite types with upsert

info

When you create or update the values in a composite type that has a unique restraint, note that MongoDB does not enforce unique values inside a record. Learn more.

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 ORM 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.

Use Prisma ORM relations to enforce unique values in a record

In the example above, MongoDB did not enforce the unique constraint on a nested address name. However, you can model your data differently to enforce unique values in a record. To do so, use Prisma ORM relations to turn the composite type into a collection. Set a relationship to this collection and place a unique constraint on the field that you want to be unique.

In the following example, MongoDB enforces unique values in a record. There is a relation between Mailbox and the Address model. Also, the name field in the Address model has a unique constraint.

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

If you run the above code, MongoDB enforces the unique constraint. It does not allow your application to add two addresses with the name alice@prisma.io.