Meet Drizzle
Get started

在现有项目中开始使用 Drizzle 和 Nile

This guide assumes familiarity with:

Basic file structure

This is the basic file structure of the project. In the src/db directory, we have table definition in schema.ts. In drizzle folder there are sql migration file and snapshots.

📦 <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

步骤 2 - 设置连接变量

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

NILEDB_URL=

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

Create a index.ts file in the src directory and initialize the connection:

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

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

Nile provides virtual tenant databases. When you query Nile, you can set the tenant context and Nile will direct your queries to the virtual database for this particular tenant. All queries sent with tenant context will apply to that tenant alone (i.e. select * from table will result records only for this tenant). To learn more about how to set tenant context with Drizzle, check the official Nile-Drizzle example.

步骤 4 - 创建表

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

src/db/schema.ts
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:

drizzle.config.ts
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 - 种子和查询数据库

Let’s update the src/index.ts file with queries to create, read, update, and delete tenants and todos.

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('New tenant created!')

  const tenants = await db.select().from(tenantsTable);
  console.log('Getting all tenants from the database: ', tenants)

  const todo: typeof todosTable.$inferInsert = {
    tenantId: tenants[0].id,
    title: 'Update pitch deck with AI stuff'
  }

  await db.insert(todosTable).values(todo);
  console.log('New todo created!')

  const todos = await db.select().from(todosTable);
  console.log('Getting all todos from the database: ', todos)

  await db.execute(sql`SET nile.tenant_id = '${sql.raw(tenants[0].id)}'`);
  console.log("Set tenant context");

  // note the lack of tenant_id in the query
  const tenant_todos = await db.select().from(todosTable);
  console.log('Getting all todos from the tenant virtual database: ', tenant_todos)

  await db
    .update(todosTable)
    .set({
      complete: true,
    })
    .where(eq(todosTable.id, todo.id));
  console.log('Todo marked as done!')

  await db.delete(todosTable).where(eq(todosTable.id, todo.id));
  console.log('Todo deleted!')
}

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