Nile

New database

Meet Drizzle

使用 Drizzle 和 Nile 开始入门

This guide assumes familiarity with:

dotenv - 用于管理环境变量的包 - 阅读此处
tsx - 用于运行 TypeScript 文件的包 - 阅读此处
Nile - 针对多租户应用重新设计的 PostgreSQL - 阅读此处

基本文件结构

这是项目的基本文件结构。在 src/db 目录中，我们在 schema.ts 中有表定义。在 drizzle 文件夹中，有 SQL 迁移文件和快照。

📦 <project root>
 ├ 📂 drizzle
 ├ 📂 src
 │   ├ 📂 db
 │   │  └ 📜 schema.ts
 │   └ 📜 index.ts
 ├ 📜 .env
 ├ 📜 drizzle.config.ts
 ├ 📜 package.json
 └ 📜 tsconfig.json

第 1 步 - 安装 postgres 包

npm

yarn

pnpm

bun

npm i drizzle-orm pg dotenv
npm i -D drizzle-kit tsx @types/pg

yarn add drizzle-orm pg dotenv
yarn add -D drizzle-kit tsx @types/pg

pnpm add drizzle-orm pg dotenv
pnpm add -D drizzle-kit tsx @types/pg

bun add drizzle-orm pg dotenv
bun add -D drizzle-kit tsx @types/pg

第 2 步 - 设置连接变量

Create a .env file in the root of your project and add your database connection variable:

NILEDB_URL=

第 3 步 - 将 Drizzle ORM 连接到数据库

在 src/db 目录中创建一个 index.ts 文件并初始化连接：

node-postgres

带配置的 node-postgres

你的 node-postgres 驱动程序

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';

const db = drizzle(process.env.NILEDB_URL!);

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';

// 你可以指定 node-postgres 连接选项中的任何属性
const db = drizzle({ 
  connection: { 
    connectionString: process.env.NILEDB_URL!,
    ssl: true
  }
});

import 'dotenv/config';
import { pgTable, serial, text, varchar } from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.NILEDB_URL!,
});
const db = drizzle({ client: pool });

多租户

Nile 提供 虚拟租户数据库。当你查询 Nile 时，可以设置租户上下文，Nile 会将你的查询指向此特定租户的虚拟数据库。所有带有租户上下文的查询只会应用于该租户（即 select * from table 将仅返回该租户的记录）。要了解有关如何使用 Drizzle 设置租户上下文的更多信息，请查看 官方 Nile-Drizzle 示例。

第 4 步 - 创建一个表

在 src/db 目录中创建一个 schema.ts 文件并声明你的表。由于 Nile 是多租户应用的 PostgreSQL，我们的模式包括一个用于租户的表和一个带有 tenant_id 列的待办事项表（我们称之为租户感知表）：

import { pgTable, uuid, text, timestamp, varchar, vector, boolean } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const tenantsTable = pgTable("tenants", {
	id: uuid().default(sql`public.uuid_generate_v7()`).primaryKey().notNull(),
	name: text(),
	created: timestamp({ mode: 'string' }).default(sql`LOCALTIMESTAMP`).notNull(),
	updated: timestamp({ mode: 'string' }).default(sql`LOCALTIMESTAMP`).notNull(),
	deleted: timestamp({ mode: 'string' }),
});

export const todos = pgTable("todos", {
	id: uuid().defaultRandom(),
	tenantId: uuid("tenant_id"),
	title: varchar({ length: 256 }),
	estimate: varchar({ length: 256 }),
	embedding: vector({ dimensions: 3 }),
	complete: boolean(),
});

第 5 步 - 设置 Drizzle 配置文件

Drizzle config - a configuration file that is used by Drizzle Kit and contains all the information about your database connection, migration folder and schema files.

Create a drizzle.config.ts file in the root of your project and add the following content:

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle',
  schema: './src/db/schema.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.NILEDB_URL!,
  },
});

第 6 步 - 将更改应用到数据库

You can directly apply changes to your database using the drizzle-kit push command. This is a convenient method for quickly testing new schema designs or modifications in a local development environment, allowing for rapid iterations without the need to manage migration files:

npx drizzle-kit push

Read more about the push command in documentation.

Tips

Alternatively, you can generate migrations using the drizzle-kit generate command and then apply them using the drizzle-kit migrate command:

Generate migrations:

npx drizzle-kit generate

Apply migrations:

npx drizzle-kit migrate

Read more about migration process in documentation.

第 7 步 - 填充和查询数据库

让我们更新 src/index.ts 文件，以创建、读取、更新和删除租户和待办事项的查询。

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';
import { eq, sql } from 'drizzle-orm';
import { tenantsTable, todosTable } from './db/schema';
  
const db = drizzle(process.env.NILEDB_URL!);

async function main() {
  const tenant: typeof tenantsTable.$inferInsert = {
    name: 'AwesomeSauce Inc.',
  };

  await db.insert(tenantsTable).values(tenant);
  console.log('新租户创建成功！')

  const tenants = await db.select().from(tenantsTable);
  console.log('从数据库获取所有租户: ', tenants)

  const todo: typeof todosTable.$inferInsert = {
    tenantId: tenants[0].id,
    title: '用 AI 内容更新演示文稿'
  }

  await db.insert(todosTable).values(todo);
  console.log('新待办事项创建成功！')

  const todos = await db.select().from(todosTable);
  console.log('从数据库获取所有待办事项: ', todos)

  await db.execute(sql`SET nile.tenant_id = '${sql.raw(tenants[0].id)}'`);
  console.log("设置租户上下文");

  // 注意查询中缺少 tenant_id
  const tenant_todos = await db.select().from(todosTable);
  console.log('从租户虚拟数据库获取所有待办事项: ', tenant_todos)

  await db
    .update(todosTable)
    .set({
      complete: true,
    })
    .where(eq(todosTable.id, todo.id));
  console.log('待办事项标记为完成！')

  await db.delete(todosTable).where(eq(todosTable.id, todo.id));
  console.log('待办事项已删除！')
}

main();

第 8 步 - 运行 index.ts 文件

To run any TypeScript files, you have several options, but let’s stick with one: using tsx

You’ve already installed tsx, so we can run our queries now

Run index.ts script

npm

yarn

pnpm

bun

npx tsx src/index.ts

yarn tsx src/index.ts

pnpm tsx src/index.ts

bun tsx src/index.ts

tips

We suggest using bun to run TypeScript files. With bun, such scripts can be executed without issues or additional settings, regardless of whether your project is configured with CommonJS (CJS), ECMAScript Modules (ESM), or any other module format. To run a script with bun, use the following command:

bun src/index.ts

If you don’t have bun installed, check the Bun installation docs