Nile

New database

Meet Drizzle

Drizzle과 Nile 시작하기

This guide assumes familiarity with:

dotenv - 환경 변수 관리 패키지 - 자세히 보기
tsx - TypeScript 파일 실행 패키지 - 자세히 보기
Nile - 멀티 테넌트 앱을 위해 재설계된 PostgreSQL - 자세히 보기

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

Step 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

Step 2 - 연결 변수 설정

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

NILEDB_URL=

Step 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!);

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

// You can specify any property from the node-postgres connection options
const db = drizzle({ 
  connection: { 
    connectionString: process.env.NILEDB_URL!,
    ssl: true
  }
});

import 'dotenv/config';
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 });

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.

Step 4 - 테이블 생성

src/db 디렉토리에 schema.ts 파일을 생성하고 테이블을 선언합니다. Nile은 멀티 테넌트 앱을 위한 Postgres이므로, 스키마에는 테넌트 테이블과 tenant_id 컬럼을 가진 todos 테이블이 포함됩니다 (이를 tenant-aware 테이블이라고 부릅니다):

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

Step 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!,
  },
});

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

Step 7 - 데이터베이스 시드 및 쿼리

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

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

Step 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

bunx 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