Learn how to filter Prisma Client results with where, combine operators, use relation filters, and sort with orderBy.

When you use select or include to return a subset of the related data, you can filter and sort the list of relations inside the select or include. For example, the following query returns list of titles of the unpublished posts associated with the user: const result = await prisma.user.findFirst({ select: { posts: { where: { published: false, }, orderBy: { title: "asc", }, select: { title: true, }, }, }, });

To use a database upsert, Prisma Client sends the SQL construction INSERT ... ON CONFLICT SET .. WHERE to the database.

August 20, 2023 - For the case when filters is undefined, this works fine. undefined in Prisma means “do nothing”, so this case we’ll get all 15 records with a value back. Then, we’ll fetch the other five where their value is null.

However, I am getting the error: Type '{ where: {}; }' is not assignable to type 'boolean | transactionArgs | undefined'. Object literal may only specify known properties, and 'where' does not exist in type 'transactionArgs' The documentation at https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting says this should be possible.

Delete a user and all their posts with two separate queries in a transaction (all queries must succeed): const deletePosts = prisma.post.deleteMany({ where: { authorId: 7, }, }); const deleteUser = prisma.user.delete({ where: { id: 7, }, }); const transaction = await prisma.$transaction([deletePosts, deleteUser]);

December 9, 2023 - The where argument is invalid for the select property in the Prisma query.

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?

July 31, 2023 - We are doing this by simply checking it inside the checkFilteringQuery function and then pass it to the blogService's getAll function (will be explained later). And next up, it's just a simple task, we'll create a prisma instance in a separate file (just to get close into real-world scenario ...

This file will be created in the next step. Expand if you're using PostgreSQL, MySQL, MsSQL or Azure SQL · With PostgreSQL and MySQL, you need to configure the connection URL to point to the database server. You can learn more about the required connection URL format here. ... datasource db { provider = "postgresql" } generator client { provider = "prisma-client" output = "../src/generated/prisma" moduleFormat = "cjs" }

Prisma Client allows you to aggregate on the number fields (such as Int and Float) of a model. The following query returns the average age of all users: const aggregations = await prisma.user.aggregate({ _avg: { age: true }, }); console.log('Average age:' + aggregations._avg.age); You can combine aggregation with filtering and ordering. For example, the following query returns the average age of users: ... const aggregations = await prisma.user.aggregate({ _avg: { age: true }, where: { email: { contains: 'prisma.io', }, }, orderBy: { age: 'asc' }, take: 10, }); console.log('Average age:' + aggregations._avg.age);

December 28, 2023 - Hi all. My structure is TaskList → Contains Groups → Which contain tasks. I am trying to return tasks for a given taskList if the task is within a timeframe, any ideas on how to craft this, it is just returning everything. export const upcomingTasks: QueryResolvers['upcomingTasks'] = ({ weddingId, }) => { const today = new Date() const nextWeek = new Date(today) nextWeek.setDate(today.getDate() + 7) return db.taskList.findUnique({ where: { weddingId, }, include: ...

February 9, 2021 - There is no ability to filter outside of what query options are currently available for where: https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting · Have the ability to use whereRaw. whereRaw: ('price > IF(state = "TX", ?, 100)', [200]) whereRaw: ('DATEDIFF(next_review_dt, added_dt) <= ?', [30]) whereRaw: ('sources in (?)', ["pimsleur", "iknow", "tango"])

The following example retrieves user, uses push() to add three new coin flips, and overwrites the coinflips field in an update: const user = await prisma.user.findUnique({ where: { email: "eloise@prisma.io", }, }); if (user) { console.log(user.coinflips); user.coinflips.push(true, true, false); const updatedUser = await prisma.user.update({ where: { email: "eloise@prisma.io", }, data: { coinflips: user.coinflips, }, }); console.log(updatedUser.coinflips); } Available for PostgreSQL, CockroachDB, and MongoDB.

Learn how to use select and include in Prisma Client to return only the fields and relations you need.

August 24, 2022 - This worked fine in 3.15.2 but returns a type error in 4.2.1: { "code": "P2010", "clientVersion": "4.2.1", "meta": { "code": "42883", "message": "db error: ERROR: operator does not exist: uuid = text\nHINT: No operator matches the given name and argument types. You might need to add explicit type casts." } } I've tried different castings but nothing seemed to work. ... const rows = await this.prismaClient.$queryRaw< { id: string }[] >(Prisma.sql` SELECT id FROM repro WHERE id = ANY (ARRAY[${Prisma.join(ids)}]::uuid[]); `)

October 11, 2020 - Hello I have products organized in different categories. One product can exist in several categories, and a category can obviously have many products. My (somewhat simplified) schema looks like this model SimpleProduct { sku String @id name String regular_price Float stock Int images String? ean String? manufacturer String? categories ...

You can also define where the client will be generated to with the output option. By default Prisma Client Python will be generated to the same location it was installed to, whether that's inside a virtual environment, the global python installation or anywhere else that python packages can be imported from.