查询性能

对于 Drizzle 而言，我们是一个基于 SQL 的轻量级 TypeScript 层，几乎没有开销；为了实现真正的零开销，您可以使用我们的预处理语句 API。

当您在数据库上运行查询时，会发生以下几件事：

查询构建器的所有配置被连接成 SQL 字符串
该字符串和参数被发送到数据库驱动程序
驱动程序将 SQL 查询编译成二进制 SQL 可执行格式并发送到数据库

使用预处理语句，您只需在 Drizzle ORM 侧进行一次 SQL 拼接，然后数据库驱动程序就可以重用预编译的二进制 SQL，而不是每次都解析查询。这对于大型 SQL 查询具有极高的性能优势。

不同的数据库驱动程序以不同的方式支持预处理语句，有时 Drizzle ORM 的性能甚至可以**超越 better-sqlite3 驱动程序**。

预处理语句

PostgreSQL

MySQL

SQLite

SingleStore

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();

const db = drizzle(...);

const prepared = db.select().from(customers).prepare();

const res1 = await prepared.execute();
const res2 = await prepared.execute();
const res3 = await prepared.execute();

const db = drizzle(...);

const prepared = db.select().from(customers).prepare();

const res1 = await prepared.execute();
const res2 = await prepared.execute();
const res3 = await prepared.execute();

占位符

无论何时您需要嵌入动态运行时值，都可以使用 sql.placeholder(...) API。

PostgreSQL

MySQL

SQLite

SingleStore

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()

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();

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.get({ id: 10 }) // SELECT * FROM customers WHERE id = 10
p1.get({ 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.all({ 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()

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();

await p2.execute({ name: '%an%' }) // SELECT * FROM customers WHERE name ilike '%an%'