BluePin Posts

BluePin

•Created by BluePin on 3/22/2024 in #❓-help

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

4 replies