Composite types

Composite types are available in Preview in versions 3.10.0 and later.

Composite types are only available with MongoDB.

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:

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

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

const orders = await prisma.order.findMany({
where: {
shippingAddress: {
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',
},
},
},
})

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 photos that match specific URL paths:

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

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' }, { url: '2.jpg' }],
},
})

The following example uses 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

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.

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: 10,
},
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: 10,
},
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: 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',
},
},
})
Edit this page on GitHub