Support for manually managing prepared statements

[kavyantic]

Problem

Currently, Prisma ORM prepares queries dynamically each time a CRUD operation is performed on a table. This can result in unnecessary overhead, while in complex queries e.g. nesting relations and selecting fields, it can significantly reduce the performance. especially when the same query is executed multiple times with different values it does not make sense to build the query on each execution when only a few fixed parameters are changed.

Comparison with Existing Feature:

Drizzle ORM: drizzle-orm provides support for prepared statement which can be used to prebuild queries to use later with dynamic parameters. below snipped from the official docs.

import { sql } from "drizzle-orm";

const p1 = db
  .select()
  .from(customers)
  .where(eq(customers.id, sql.placeholder('id')))
  .prepare("p1")
await p1.execute({ id: 10 }) // SELECT * FROM customers WHERE id = 10
await p1.execute({ id: 12 }) // SELECT * FROM customers WHERE id = 12
const p2 = db
  .select()
  .from(customers)
  .where(sql`lower(${customers.name}) like ${sql.placeholder('name')}`)
  .prepare("p2");
await p2.execute({ name: '%an%' }) // SELECT * FROM customers WHERE name ilike '%an%'

Similar to prisma.$queryRaw, prepared statements offer the advantage of dynamic query execution. However, they provide 100% type safety just like prisma's ordinary table methods.

Suggested solution

Introduce support for prepared statements in Prisma ORM. Prepared statements act as pre-defined query templates that can be constructed once and reused with varying parameters. This approach can improve query performance and simplify code complexity.
For the above feature I expect a similar FluentApi to build a prepared statement or alternatively expected syntax can be :

const findUserPrepared = prisma.users.prepare((options: { username: string }, prepare) => {
  return prepare.findUnique({
    where: {
      username: options.username
    }
  });
});

// This function should have prepared query something like this "SELECT id, username, password, firstName, lastName from users where username='${options.username}'" and have as strongly typed return type as prisma.users.findUnique({where:{username:"kavya"}})
findUserPrepared({ username: "kavya" }).then((user) => {
  user.id
  user.username
  // Handle user data
});

[janpio]

Prisma actually uses prepared statements under the hood and also caches and reuses them.

I hence renamed the issue a tiny bit to make this a request for "manually managing" prepared statements.

[kavyantic]

Prisma actually uses prepared statements under the hood and also caches and reuses them.

I hence renamed the issue a tiny bit to make this a request for "manually managing" prepared statements.

If so, suppose if I have a conditional parameter like this

const isUsername = true
const username = "kavya"

PrismaClient.users.findUnique(
  isUsername ? { where: { username } } : { where: { email: username } }
);
// there can be two different prepared sql query which are 
// 1) select x,y,z from users where username="kavya"
// 2) select x,y,z from users where email="kavya"

Is this handled by prisma caching mechanism? like which query to select form cache.
It gets even more complicated when using multiple conditional statement in complex queries.

[janpio]

Yes, when executing these different "paths" different queries are generated - and the correct prepared statement from the cache is used for each case.

[kavyantic]

Alright, that's nice but still we need a manual prepared statement feature to optimize queries further and remove redundant overhead. Are contributors actively working on this?

[janpio]

We at Prisma are not.