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:
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.ObjectId3 name String @unique4 price Decimal5 colors Color[]6 sizes Size[]7 photos Photo[]8 orders Order[]9}1011model Order {12 id String @id @default(auto()) @map("_id") @db.ObjectId13 product Product @relation(fields: [productId], references: [id])14 color Color15 size Size16 shippingAddress Address17 billingAddress Address?18 productId String @db.ObjectId19}2021enum Color {22 Red23 Green24 Blue25}2627enum Size {28 Small29 Medium30 Large31 XLarge32}3334type Photo {35 height Int @default(200)36 width Int @default(100)37 url String38}3940type Address {41 street String42 city String43 zip String44}
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 typesaggregrate
,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.
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 typesisSet
: Filter optional fields to include only results that have been set (either set to a value, or explicitly set tonull
). Setting this filter totrue
will excludeundefined
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 listisEmpty
: Checks if the list is emptyevery
: Every item in the list must match the conditionsome
: One or more of the items in the list must match the conditionnone
: None of the items in the list can match the conditionisSet
: Filter optional fields to include only results that have been set (either set to a value, or explicitly set tonull
). Setting this filter totrue
will excludeundefined
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 relationproduct: { connect: { id: 'some-object-id' } },color: 'Red',size: 'Large',// Composite typeshippingAddress: {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 relationproduct: { connect: { id: 'some-object-id' } },color: 'Red',size: 'Large',// Composite typeshippingAddress: {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 nullbillingAddress: {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 typephotos: {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 supportcolors: ['Red', 'Green'],sizes: ['Small', 'Medium', 'Large'],// New composite typephotos: [{ 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 product
s 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. Unlikeset: null
,unset
removes the field entirely - Use
update
to update a composite type - Use
upsert
toupdate
an existing composite type if it exists, and otherwiseset
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 fieldupdate: {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 itupsert: {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 orderunset: 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 listpush
: Push values to the end of an embedded list of composite typesupdateMany
: Update many composite types at oncedeleteMany
: 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 listpush: [{ 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',},},})