Performance

When it comes to Drizzle - we're a thin TypeScript layer on top of SQL with almost 0 overhead and to make it actual 0 - you can utilise our prepared statements API

When you run query to the database there're several things that happens:

all the configurations of the query builder got concatenated to the SQL string
that string and params are sent to the database driver
driver compiles SQL query to the binary SQL executable format and sends it to the database

With prepared statements you do SQL concatenation once on the Drizzle ORM side and then database driver is able to reuse precompiled binary SQL instead of parsing query all the time. It has extreme performance benefits on large SQL queries.

Different database drivers support prepared statements in different ways and sometimes Drizzle ORM you can go faster than better-sqlite3 driver (opens in a new tab)

Prepared statement

const db = drizzle(...);
 
const prepared = db.select().from(customers).prepare("statement_name");
 
const res1 = await prepared.execute();
const res2 = await prepared.execute();
const res3 = await prepared.execute();

Placeholder

Whenever you need to embed a dynamic runtime vaule - you can use a placeholder(...) api

import { sql, placeholder } from "drizzle-orm";
 
const p1 = db
  .select()
  .from(customers)
  .where(eq(customers.id, 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 ${placeholder('name')}`)
  .prepare("p2");
 
await p2.execute({ name: '%an%' }) // SELECT * FROM customers WHERE name ilike '%an%'

Serverless environments

You can get immense benefits with serverless functions like AWS lambdas or Vercel Server functions(they're AWS lambdas based), since they can live up to 15mins and reuse both database connections and prepared statements
On the other hand edge functions tend to clean up straight after they're invoked which leads to little to no perf benefits

To reuse your database connection and prepared statements - you just have to declare them outside of handler scope

const databaseConnection = ...;
const db = drizzle(databaseConnection);
const prepared = db.select().from(...).prepare();
 
// AWS handler
export const handler = async (event: APIGatewayProxyEvent) => {
  return prepared.execute();
}

Transactions Migrations