阅读副本

当您的项目涉及一组读取副本实例，并且您需要一种便捷的方法来管理来自读取副本的 SELECT 查询，以及在主实例上执行创建、删除和更新操作时，您可以在 Drizzle 中利用 withReplicas() 函数。

PostgreSQL

MySQL

SQLite

SingleStore

import { sql } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/node-postgres';
import { boolean, jsonb, pgTable, serial, text, timestamp, withReplicas } from 'drizzle-orm/pg-core';

const usersTable = pgTable('users', {
	id: serial('id' as string).primaryKey(),
	name: text('name').notNull(),
	verified: boolean('verified').notNull().default(false),
	jsonb: jsonb('jsonb').$type<string[]>(),
	createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

const primaryDb = drizzle("postgres://user:password@host:port/primary_db");
const read1 = drizzle("postgres://user:password@host:port/read_replica_1");
const read2 = drizzle("postgres://user:password@host:port/read_replica_2");

const db = withReplicas(primaryDb, [read1, read2]);

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import { boolean, mysqlTable, serial, text, withReplicas } from 'drizzle-orm/mysql-core';

const usersTable = mysqlTable('users', {
	id: serial('id' as string).primaryKey(),
	name: text('name').notNull(),
	verified: boolean('verified').notNull().default(false),
});

const primaryClient = await mysql.createConnection({
  host: "host",
  user: "user",
  database: "primary_db",
})
const primaryDb = drizzle({ client: primaryClient });

const read1Client = await mysql.createConnection({
  host: "host",
  user: "user",
  database: "read_1",
})
const read1 = drizzle({ client: read1Client });

const read2Client = await mysql.createConnection({
  host: "host",
  user: "user",
  database: "read_2",
})
const read2 = drizzle({ client: read2Client });

const db = withReplicas(primaryDb, [read1, read2]);

import { sql } from 'drizzle-orm';
import { sqliteTable, int, text, withReplicas } from 'drizzle-orm/sqlite-core';
import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';

const usersTable = sqliteTable('users', {
	id: int('id' as string).primaryKey(),
	name: text('name').notNull(),
});

const primaryDb = drizzle({ client: createClient({ url: 'DATABASE_URL', authToken: 'DATABASE_AUTH_TOKEN' }) });
const read1 = drizzle({ client: createClient({ url: 'DATABASE_URL', authToken: 'DATABASE_AUTH_TOKEN' }) });
const read2 = drizzle({ client: createClient({ url: 'DATABASE_URL', authToken: 'DATABASE_AUTH_TOKEN' }) });

const db = withReplicas(primaryDb, [read1, read2]);

import { drizzle } from "drizzle-orm/singlestore";
import mysql from "mysql2/promise";
import { boolean, singlestoreTable, serial, text, withReplicas } from 'drizzle-orm/singlestore-core';

const usersTable = singlestoreTable('users', {
	id: serial('id' as string).primaryKey(),
	name: text('name').notNull(),
	verified: boolean('verified').notNull().default(false),
});

const primaryClient = await mysql.createConnection({
  host: "host",
  user: "user",
  database: "primary_db",
})
const primaryDb = drizzle({ client: primaryClient });

const read1Client = await mysql.createConnection({
  host: "host",
  user: "user",
  database: "read_1",
})
const read1 = drizzle({ client: read1Client });

const read2Client = await mysql.createConnection({
  host: "host",
  user: "user",
  database: "read_2",
})
const read2 = drizzle({ client: read2Client });

const db = withReplicas(primaryDb, [read1, read2]);

您现在可以像之前一样使用 db 实例。 Drizzle 将自动处理读取副本和主实例之间的选择。

// 从 read1 连接或 read2 连接读取
await db.select().from(usersTable)

// 使用主数据库进行删除操作
await db.delete(usersTable).where(eq(usersTable.id, 1))

您可以使用 $primary 键强制即使在读取操作中也使用主实例。

// 从主实例读取
await db.$primary.select().from(usersTable);

使用 Drizzle，您还可以指定选择读取副本的自定义逻辑。您可以做出加权决定或任何其他自定义选择方法以随机选择读取副本。以下是选择读取副本的自定义逻辑实现示例，第一个副本被选择的概率为 70%，第二个副本被选择的概率为 30%。

请记住，您可以实施任何类型的随机选择方法来选择读取副本。

const db = withReplicas(primaryDb, [read1, read2], (replicas) => {
    const weight = [0.7, 0.3];
    let cumulativeProbability = 0;
    const rand = Math.random();

    for (const [i, replica] of replicas.entries()) {
      cumulativeProbability += weight[i]!;
      if (rand < cumulativeProbability) return replica;
    }
    return replicas[0]!
});

await db.select().from(usersTable)