查询性能

当涉及 Drizzle 时 — 我们是一个薄的 TypeScript 层，位于 SQL 之上，几乎没有额外开销，并且为了使其实际为 0，您可以利用我们的预处理语句 API。

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

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

使用预处理语句，您可以在 Drizzle ORM 端一次进行 SQL 连接，然后数据库驱动程序能够重用预编译的二进制 SQL，而不是一直解析查询。这对大型 SQL 查询有极大的性能优势。

不同的数据库驱动程序以不同的方式支持预处理语句，有时候 Drizzle ORM 甚至可以比 better-sqlite3 驱动程序更快。

预处理语句

PostgreSQL

MySQL

SQLite

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 = prepared.all();
const res2 = prepared.all();
const res3 = prepared.all();

占位符

每当您需要嵌入动态运行时值时 - 您可以使用 sql.placeholder(...) api

PostgreSQL

MySQL

SQLite

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

Become a Gold Sponsor