Query Operations
In lieu of more extensive documentation, this page documents query operations on the prisma client such as creating, finding, updating and deleting records.
Schema
The examples use the following prisma schema models:
model User {
id String @id @default(cuid())
name String
points Float @default(0)
posts Post[]
profile Profile?
}
model Profile {
id Int @id @default(autoincrement())
user User @relation(fields: [user_id], references: [id])
user_id String
bio String
}
model Post {
id String @id @default(cuid())
created_at DateTime @default(now())
updated_at DateTime @updatedAt
title String
published Boolean
views Int @default(0)
desc String?
author User? @relation(fields: [author_id], references: [id])
author_id String?
categories Category[] @relation(references: [id])
}
model Category {
id Int @id @default(autoincrement())
posts Post[] @relation(references: [id])
name String
}Creating
Single Record
user = await client.user.create(
data={
'name': 'Robert',
},
)Many Records
Warning
create_many is not available for SQLite
users = await client.user.create_many(
data=[
{'name': 'Tegan'},
{'name': 'Alfie'},
{'name': 'Robert'},
]
)users = await client.user.create_many(
data=[
{'id': 'abc', 'name': 'Tegan'},
{'id': 'def', 'name': 'Alfie'},
{'id': 'ghi', 'name': 'Robert'},
],
skip_duplicates=True,
)Relational Records
user = await client.user.create(
data={
'name': 'Robert',
'profile': {
'create': {
'bio': 'My very cool bio!',
}
}
}
)user = await client.user.create(
data={
'name': 'Robert',
'posts': {
'create': [
{
'title': 'My first post!',
'published': True,
},
{
'title': 'My draft post!',
'published': False,
},
]
}
}
)Finding
Unique Records
user = await client.user.find_unique(
where={
'id': '1',
}
)user = await client.user.find_unique(
where={
'id': '2',
},
include={
'posts': True,
},
)A Single Record
post = await client.post.find_first(
where={
'title': {'contains': 'Post'},
},
)post = await client.post.find_first(
skip=2,
where={
'title': {
'contains': 'Post'
},
},
cursor={
'id': 'abcd',
},
include={
'author': True,
},
order={
'id': 'asc',
}
)Multiple Records
posts = await client.post.find_many(
where={
'published': True,
},
)posts = await client.post.find_many(
take=5,
skip=1,
where={
'published': True,
},
cursor={
'id': 'desc',
},
include={
'categories': True,
},
order={
'id': 'desc',
}
)Filtering by Relational Fields
Within the filter you can query for everything you would normally query for, like it was a find_first() call on the relational field, for example:
post = await client.post.find_first(
where={
'author': {
'is': {
'name': 'Robert',
},
},
},
)
user = await client.user.find_first(
where={
'name': 'Robert',
},
)One to One
post = await client.post.find_first(
where={
'author': {
'is': {
'name': 'Robert',
},
'is_not': {
'name': 'Tegan',
},
},
},
)One to Many
Excluding
post = await client.post.find_first(
where={
'categories': {
'none': {
'name': 'Exclude Category',
},
},
},
)At Least One
post = await client.post.find_first(
where={
'categories': {
'some': {
'name': {
'contains': 'Special',
},
},
},
},
)Every
post = await client.post.find_first(
where={
'categories': {
'every': {
'name': {
'contains': 'Category',
},
},
},
},
)Filtering by Field Values
Note
The examples for filtering fields are simply to showcase possible arguments, all the arguments passed together will result in either an invalid query or no records being found.
String Fields
post = await client.post.find_first(
where={
'desc': 'Must be exact match',
# or
'desc': {
'equals': 'example_string',
'not_in': ['ignore_string_1', 'ignore_string_2'],
'lt': 'z',
'lte': 'y',
'gt': 'a',
'gte': 'b',
'contains': 'string must be present',
'startswith': 'must start with string',
'endswith': 'must end with string',
'IN': ['find_string_1', 'find_string_2'],
'NOT': {
# recursive type
'contains': 'string must not be present',
...
},
},
},
)Integer Fields
post = await client.post.find_first(
where={
'views': 10,
# or
'views': {
'equals': 1,
'IN': [1, 2, 3],
'not_in': [4, 5, 6],
'lt': 10,
'lte': 9,
'gt': 0,
'gte': 1,
'NOT': {
# recursive type
'gt': 10,
...
},
},
},
)Float Fields
user = await client.user.find_first(
where={
'points': 10.0,
# or
'points': {
'equals': 10.0,
'IN': [1.2, 1.3, 1.4],
'not_in': [4.7, 53.4, 6.8],
'lt': 100.5,
'lte': 9.9,
'gt': 0.0,
'gte': 1.2,
'NOT': {
# recursive type
'gt': 10.0,
...
},
},
},
)DateTime Fields
from datetime import datetime
post = await client.post.find_first(
where={
'updated_at': datetime.now(),
# or
'updated_at': {
'equals': datetime.now(),
'not_in': [datetime.now(), datetime.utcnow()],
'lt': datetime.now(),
'lte': datetime.now(),
'gt': datetime.now(),
'gte': datetime.now(),
'IN': [datetime.now(), datetime.utcnow()],
'NOT': {
# recursive type
'equals': datetime.now(),
...
},
},
},
)Boolean Fields
post = await client.post.find_first(
where={
'published': True,
# or
'published': {
'equals': True,
'NOT': False,
},
},
)Deleting
Unique Record
post = await client.post.delete(
where={
'id': 'cksc9m7un0028f08zwycxtjr1',
},
)post = await client.post.delete(
where={
'id': 'cksc9m1vu0021f08zq0066pnz',
},
include={
'categories': True,
}
)Multiple Records
total = await client.post.delete_many(
where={
'published': False,
}
)Updating
Unique Record
post = await client.post.update(
where={
'id': 'cksc9lp7w0014f08zdkz0mdnn',
},
data={
'views': {
'increment': 1,
}
},
include={
'categories': True,
}
)Multiple Records
total = await client.post.update_many(
where={
'published': False
},
data={
'views': 0,
},
)Creating On Not Found
post = await client.post.upsert(
where={
'id': 'cksc9ld4z0007f08z7obo806s',
},
data={
'create': {
'title': 'This post was created!',
'published': False,
}
'update': {
'title': 'This post was updated',
'published': True,
},
},
include={
'categories': True,
}
)Updating Atomic Fields
If a field is an int or float type then it can be atomically updated, i.e. mathematical operations can be applied without knowledge of the previous value.
Integer Fields
post = await client.post.update(
where={
'id': 'abc',
},
data={
'views': 1,
# or
'views': {
'set': 5,
'increment': 1,
'decrement': 2,
'multiply': 5,
'divide': 10,
},
},
)Float Fields
user = await client.user.update(
where={
'id': 'abc',
},
data={
'points': 1.0,
# or
'points': {
'set': 1.0,
'increment': 1.5,
'decrement': 0.5,
'multiply': 2.5,
'divide': 3.0,
},
},
)Aggregrating
Counting Records
total = await client.post.count(
where={
'published': True,
},
)total = await client.post.count(
take=10,
skip=1,
where={
'published': True,
},
cursor={
'id': 'cksca3xm80035f08zjonuubik',
},
order={
'created_at': 'asc',
},
)Batching Write Queries
async with client.batch_() as batcher:
batcher.user.create({'name': 'Robert'})
batcher.user.create({'name': 'Tegan'})Raw Queries
Note
SQL queries are sent directly to the database so you must use the syntax for your specific database provider
Warning
Raw query results are raw dictionaries unless the model argument is specified
Write Queries
total = await client.execute_raw(
'''
SELECT *
FROM User
WHERE User.id = ?
''',
'cksca3xm80035f08zjonuubik'
)Selecting Multiple Records
posts = await client.query_raw(
'''
SELECT *
FROM Post
WHERE Post.published IS TRUE
'''
)Type Safety
from prisma.models import Post
posts = await client.query_raw(
'''
SELECT *
FROM Post
WHERE Post.published IS TRUE
''',
model=Post,
)Selecting a Single Record
post = await client.query_first(
'''
SELECT *
FROM Post
WHERE Post.published IS TRUE
LIMIT 1
'''
)Type Safety
from prisma.models import Post
post = await client.query_first(
'''
SELECT *
FROM Post
WHERE Post.views > 50
LIMIT 1
''',
model=Post
)