BluePin•9mo ago

“Integrating Drizzle Prepared Statements with tRPC Context”

I’m currently using tRPC and Drizzle for my project. In tRPC, we generally place the database driver in the context, which allows us to call this variable from within the context and create queries with Drizzle, among other ORMs. This is possible because the database is within the context.

export const createTRPCContext = async (opts: { headers: Headers }) => {
  return {
    db,
    ...opts,
  };
};

export const createTRPCContext = async (opts: { headers: Headers }) => {
  return {
    db,
    ...opts,
  };
};

Drizzle has a feature called Prepared Statements that allows me to speed up queries by creating queries with placeholders. This significantly accelerates the query as the SQL binary is already ready and the ORM doesn’t have to pass the logic to the Query Builder, skipping some processing steps.

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%'

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%'

3 Replies

BluePinOP•9mo ago

I thought about using this to make my queries super fast using the least amount of CPU and RAM possible. However, I found a problem. Since the prepared queries are within a variable and they usually use the db variable, I can’t use it unless I put ALL the variables with the prepared queries, which can easily be 10,000 different queries for various contexts and needs.

import * as usersPrepared from ".db/PreparedStatements/users"
import * as accountsPrepared from ".db/PreparedStatements/acconts"
export const createTRPCContext = async (opts: { headers: Headers }) => {
  return {
    db,
    ...usersPrepared,
    ...accountsPrepared
    ...opts,
  };
};

import * as usersPrepared from ".db/PreparedStatements/users"
import * as accountsPrepared from ".db/PreparedStatements/acconts"
export const createTRPCContext = async (opts: { headers: Headers }) => {
  return {
    db,
    ...usersPrepared,
    ...accountsPrepared
    ...opts,
  };
};

How can I solve this? Is there a way to integrate Drizzle’s Prepared Statements with the tRPC context without having to put all the prepared queries in the context? Any guidance or suggestions would be greatly appreciated. Thank you.

BluePinOP•9mo ago

Sorces: https://orm.drizzle.team/docs/perf-queries#prepared-statement https://trpc.io/docs/server/context#creating-the-context

Drizzle ORM - Queries

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Context | tRPC

Your context holds data that all of your tRPC procedures will have access to, and is a great place to put things like database connections or authentication information.

BluePinOP•9mo ago

Another thing is, if you put all the prepared statements in the context, does it have a memory limit on how long it can stay within the context? Is it bad to put too many things in context?