Skip to content

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: 

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

model User {
  id      String   @id @default(cuid())
  name    String
  points  Float    @default(0)
  meta    Json     @default("{}")
  number  Decimal  @default(0)
  emails  String[]
  posts   Post[]
  profile Profile?
}

model Profile {
  id      Int     @id @default(autoincrement())
  user    User    @relation(fields: [user_id], references: [id])
  user_id String  @unique
  bio     String
  image   Bytes?
  city    String?
  country String?
  views   Int     @default(0)
}

model Post {
  id          String     @id @default(cuid())
  created_at  DateTime   @default(now())
  updated_at  DateTime   @updatedAt
  title       String
  published   Boolean
  views       Int        @default(0)
  description String?
  author      User?      @relation(fields: [author_id], references: [id])
  author_id   String?
  categories  Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  posts Post[]
  name  String
}

Creating

Single Record

user = await db.user.create(
    data={
        'name': 'Robert',
    },
)

Many Records

users = await db.user.create_many(
    data=[
        {'name': 'Tegan'},
        {'name': 'Alfie'},
        {'name': 'Robert'},
    ]
)

Warning

the skip_duplicates argument is not supported when using MongoDB, SQLServer, or SQLite

users = await db.user.create_many(
    data=[
        {'id': 'abc', 'name': 'Tegan'},
        {'id': 'def', 'name': 'Alfie'},
        {'id': 'ghi', 'name': 'Robert'},
    ],
    skip_duplicates=True,
)

Relational Records

user = await db.user.create(
    data={
        'name': 'Robert',
        'profile': {
            'create': {
                'bio': 'My very cool bio!',
            }
        }
    }
)
user = await db.user.create(
    data={
        'name': 'Robert',
        'posts': {
            'create': [
                {
                    'title': 'My first post!',
                    'published': True,
                },
                {
                    'title': 'My draft post!',
                    'published': False,
                },
            ]
        }
    }
)

Finding

Unique Records

There are two different methods you can use here to find a unique record. Which one you use depends on the context surrounding the query:

  • Use find_unique if it is expected for the record to not exist in the database
  • Use find_unique_or_raise if it is unexpected for the record to not exist in the database

user = await db.user.find_unique(
    where={
        'id': '1',
    }
)
user = await db.user.find_unique_or_raise(
    where={
        'id': '2',
    },
    include={
        'posts': True,
    },
)

A Single Record

There are two different methods you can use here to find a single record. Which one you use depends on the context surrounding the query:

  • Use find_first if it is expected for the record to not exist in the database
  • Use find_first_or_raise if it is unexpected for the record to not exist in the database

post = await db.post.find_first(
    where={
        'title': {'contains': 'Post'},
    },
)
post = await db.post.find_first_or_raise(
    where={
        'title': {'contains': 'Post'},
    },
)
post = await db.post.find_first(
    skip=2,
    where={
        'title': {
            'contains': 'Post'
        },
    },
    cursor={
        'id': 'abcd',
    },
    include={
        'author': True,
    },
    order={
        'id': 'asc',
    }
)

Multiple Records

posts = await db.post.find_many(
    where={
        'published': True,
    },
)
posts = await db.post.find_many(
    take=5,
    skip=1,
    where={
        'published': True,
    },
    cursor={
        'id': 'desc',
    },
    include={
        'categories': True,
    },
    order={
        'id': 'desc',
    }
)

Distinct Records

The following query will find all Profile records that have a distinct city field.

profiles = await db.profiles.find_many(
    distinct=['city'],
)
# [
#  { city: 'Paris' },
#  { city: 'Lyon' },
# ]

You can also filter by distinct combinations, for example the following query will return all records that have a distinct city and country combination.

profiles = await db.profiles.find_many(
    distinct=['city', 'country'],
)
# [
#  { city: 'Paris', country: 'France' },
#  { city: 'Paris', country: 'Denmark' },
#  { city: 'Lyon', country: 'France' },
# ]

You can currently only use distinct with find_many() and find_first() queries.

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 db.post.find_first(
    where={
        'author': {
            'is': {
                'name': 'Robert',
            },
        },
    },
)
user = await db.user.find_first(
    where={
        'name': 'Robert',
    },
)

One to One

post = await db.post.find_first(
    where={
        'author': {
            'is': {
                'name': 'Robert',
            },
            'is_not': {
                'name': 'Tegan',
            },
        },
    },
)

One to Many

Excluding
post = await db.post.find_first(
    where={
        'categories': {
            'none': {
                'name': 'Exclude Category',
            },
        },
    },
)
At Least One
post = await db.post.find_first(
    where={
        'categories': {
            'some': {
                'name': {
                    'contains': 'Special',
                },
            },
        },
    },
)
Every
post = await db.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

Warning

Case insensitive filtering is only available on PostgreSQL and MongoDB Full-text search is only available on PostgreSQL and MySQL

post = await db.post.find_first(
    where={
        'description': 'Must be exact match',
        # or
        'description': {
            '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'],
            'mode': 'insensitive',
            'search': 'full-text query',
            'not': {
                # recursive type
                'contains': 'string must not be present',
                'mode': 'default',
                ...
            },
        },
    },
)

Please visit Prisma's documentation for more information on full-text search.

Integer Fields

post = await db.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 db.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 db.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 db.post.find_first(
    where={
        'published': True,
        # or
        'published': {
            'equals': True,
            'not': False,
        },
    },
)

Json Fields

Note

Json fields must match exactly.

Warning

Json fields are not supported on SQLite

from prisma import Json

user = await db.user.find_first(
    where={
        'meta': Json({'country': 'Scotland'})
        # or
        'meta': {
            'equals': Json.keys(country='Scotland'),
            'not': Json(['foo']),
        }
    }
)

Bytes Fields

Note

Bytes fields are encoded to and from Base64

from prisma import Base64

profile = await db.profile.find_first(
    where={
        'image': Base64.encode(b'my binary data'),
        # or
        'image': {
            'equals': Base64.encode(b'my binary data'),
            'in': [Base64.encode(b'my binary data')],
            'not_in': [Base64.encode(b'my other binary data')],
            'not': Base64(b'WW91IGZvdW5kIGFuIGVhc3RlciBlZ2chIExldCBAUm9iZXJ0Q3JhaWdpZSBrbm93IDop'),
        },
    },
)

Decimal Fields

from decimal import Decimal

user = await db.user.find_first(
    where={
        'number': Decimal(1),
        # or
        'number': {
            'equals': Decimal('1.23823923283'),
            'in': [Decimal('1.3'), Decimal('5.6')],
            'not_in': [Decimal(10), Decimal(20)],
            'gte': Decimal(5),
            'gt': Decimal(11),
            'lt': Decimal(4),
            'lte': Decimal(3),
            'not': Decimal('123456.28'),
            # or
            'not': {
                # recursive type
                ...
            }
        },
    },
)

Lists fields

Warning

Scalar list fields are only supported on PostgreSQL, CockroachDB and MongoDB

Every scalar type can also be defined as a list, for example:

user = await db.user.find_first(
    where={
        'emails': {
            # only one of the following fields is allowed at the same time
            'has': 'robert@craigie.dev',
            'has_every': ['email1', 'email2'],
            'has_some': ['email3', 'email4'],
            'is_empty': True,
        },
    },
)

Combining arguments

All of the above mentioned filters can be combined with other filters using AND, NOT and OR.

AND

The following query will return the first post where the title contains the words prisma and test.

post = await db.post.find_first(
    where={
        'AND': [
            {
                'title': {
                    'contains': 'prisma',
                },
            },
            {
                'title': {
                    'contains': 'test',
                },
            },
        ],
    },
)

OR

The following query will return the first post where the title contains the word prisma or is published.

post = await db.post.find_first(
    where={
        'OR': [
            {
                'title': {
                    'contains': 'prisma',
                },
            },
            {
                'published': True,
            },
        ],
    },
)

NOT

The following query will return the first post where the title is not My test post

post = await db.post.find_first(
    where={
        'NOT' [
            {
                'title': 'My test post',
            },
        ],
    },
)

Deleting

Unique Record

post = await db.post.delete(
    where={
        'id': 'cksc9m7un0028f08zwycxtjr1',
    },
)
post = await db.post.delete(
    where={
        'id': 'cksc9m1vu0021f08zq0066pnz',
    },
    include={
        'categories': True,
    }
)

Multiple Records

total = await db.post.delete_many(
    where={
        'published': False,
    }
)

Updating

Unique Record

post = await db.post.update(
    where={
        'id': 'cksc9lp7w0014f08zdkz0mdnn',
    },
    data={
        'views': {
            'increment': 1,
        }
    },
    include={
        'categories': True,
    }
)

Multiple Records

total = await db.post.update_many(
    where={
        'published': False
    },
    data={
        'views': 0,
    },
)

Creating On Not Found

post = await db.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 db.post.update(
    where={
        'id': 'abc',
    },
    data={
        'views': 1,
        # or
        'views': {
            'set': 5,
            'increment': 1,
            'decrement': 2,
            'multiply': 5,
            'divide': 10,
        },
    },
)

Float Fields

user = await db.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,
        },
    },
)

Updating List Fields

Warning

Scalar list fields are only supported on PostgreSQL, CockroachDB and MongoDB

Warning

The push operation is not supported on CockroachDB

user = await db.user.update(
    where={
        'id': 'cksc9lp7w0014f08zdkz0mdnn',
    },
    data={
        'email': {
            'set': ['robert@craigie.dev', 'robert@example.com'],
            # or
            'push': ['robert@example.com'],
        },
    }
)

Aggregrating

Counting Records

total = await db.post.count(
    where={
        'published': True,
    },
)
total = await db.post.count(
    take=10,
    skip=1,
    where={
        'published': True,
    },
    cursor={
        'id': 'cksca3xm80035f08zjonuubik',
    },
)

Grouping Records

Warning

You can only order by one field at a time however this is not possible to represent with python types

results = await db.profile.group_by(['country'])
# [
#   {'country': 'Denmark'},
#   {'country': 'Scotland'},
# ]

results = await db.profile.group_by(['country'], count=True)
# [
#   {'country': 'Denmark', '_count': {'_all': 20}},
#   {'country': 'Scotland', '_count': {'_all': 1}},
# ]

results = await db.profile.group_by(
    by=['country', 'city'],
    count={
        '_all': True,
        'city': True,
    },
    sum={
        'views': True,
    },
    order={
        'country': 'desc',
    },
    having={
        'views': {
            '_avg': {
                'gt': 200,
            },
        },
    },
)
# [
#   {
#       'country': 'Scotland',
#       'city': 'Edinburgh',
#       '_sum': {'views': 250},
#       '_count': {'_all': 1, 'city': 1}
#   },
#   {
#       'country': 'Denmark',
#       'city': None,
#       '_sum': {'views': 6000},
#       '_count': {'_all': 12, 'city': 0}
#   },
#   {
#       'country': 'Denmark',
#       'city': 'Copenhagen',
#       '_sum': {'views': 8000},
#       '_count': {'_all': 8, 'city': 8}
#   },
# ]

Batching Write Queries

async with db.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 db.execute_raw(
    '''
    SELECT *
    FROM User
    WHERE User.id = ?
    ''',
    'cksca3xm80035f08zjonuubik'
)

Selecting Multiple Records

posts = await db.query_raw(
    '''
    SELECT *
    FROM Post
    WHERE Post.published IS TRUE
    '''
)

Type Safety

from prisma.models import Post

posts = await db.query_raw(
    '''
    SELECT *
    FROM Post
    WHERE Post.published IS TRUE
    ''',
    model=Post,
)

Selecting a Single Record

post = await db.query_first(
    '''
    SELECT *
    FROM Post
    WHERE Post.published IS TRUE
    LIMIT 1
    '''
)

Type Safety

from prisma.models import Post

post = await db.query_first(
    '''
    SELECT *
    FROM Post
    WHERE Post.views > 50
    LIMIT 1
    ''',
    model=Post
)