Effect

Existing database

Meet Drizzle

기존 프로젝트에서 Drizzle과 Effect PostgreSQL 시작하기

WARNING

이 페이지는 Drizzle 버전 1.0.0-beta.9 이상에서 사용 가능한 기능을 설명합니다.

Effect는 현재 PostgreSQL에서만 사용 가능하며, 곧 다른 모든 데이터베이스에도 구현될 예정입니다.

업그레이드 방법은 여기를 참조하세요.

This guide assumes familiarity with:

Effect - 개발자가 복잡한 동기 및 비동기 프로그램을 쉽게 작성할 수 있도록 설계된 강력한 TS 라이브러리 - 자세히 보기
dotenv - 환경 변수 관리 패키지 - 자세히 보기
tsx - TypeScript 파일 실행 패키지 - 자세히 보기
@effect/sql-pg - Effect를 위한 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 - node-postgres 패키지 설치

npm

yarn

pnpm

bun

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

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

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

bun add drizzle-orm effect @effect/sql-pg 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:

DATABASE_URL=

팁

PostgreSQL 데이터베이스가 없고 테스트용으로 생성하려면, Docker에서 PostgreSQL 설정 가이드를 사용할 수 있습니다.

Docker에서 PostgreSQL 설정 가이드는 여기에서 확인할 수 있습니다. 가이드를 따라 설정하고 데이터베이스 URL을 생성한 후 다음 단계로 돌아오세요.

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

Step 4 - 데이터베이스 인트로스펙션

Drizzle Kit provides a CLI command to introspect your database and generate a schema file with migrations. The schema file contains all the information about your database tables, columns, relations, and indices.

For example, you have such table in your database:

CREATE TABLE IF NOT EXISTS "users" (
	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "users_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
	"name" varchar(255) NOT NULL,
	"age" integer NOT NULL,
	"email" varchar(255) NOT NULL,
	CONSTRAINT "users_email_unique" UNIQUE("email")
);

Pull your database schema:

npx drizzle-kit pull --init

The result of introspection will be a schema.ts file, meta folder with snapshots of your database schema, sql file with the migration and relations.ts file for relational queries.

Here is an example of the generated schema.ts file:

// table schema generated by introspection
import { pgTable, unique, integer, varchar } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const users = pgTable("users", {
	id: integer().primaryKey().generatedAlwaysAsIdentity({ name: "users_id_seq", startWith: 1, increment: 1, minValue: 1, maxValue: 2147483647, cache: 1 }),
	name: varchar({ length: 255 }).notNull(),
	age: integer().notNull(),
	email: varchar({ length: 255 }).notNull(),
}, (table) => [
	unique("users_email_unique").on(table.email)
]);

Learn more about introspection in the documentation.

Step 5 - 실제 스키마 파일로 코드 이동

We recommend transferring the generated code from drizzle/schema.ts and drizzle/relations.ts to the actual schema file. In this guide we transferred code to src/db/schema.ts. Generated files for schema and relations can be deleted. This way you can manage your schema in a more structured way.

 ├ 📂 drizzle
 │ ├ 📂 20242409125510_premium_mister_fear
 │ │ ├ 📜 snapshot.json
 │ │ └ 📜 migration.sql
 │ ├ 📜 relations.ts ────────┐
 │ └ 📜 schema.ts ───────────┤
 ├ 📂 src                    │ 
 │ ├ 📂 db                   │
 │ │ ├ 📜 relations.ts <─────┤
 │ │ └ 📜 schema.ts <────────┘
 │ └ 📜 index.ts         
 └ …

Step 6 - 데이터베이스에 Drizzle ORM 연결

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

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/effect-postgres';
import { Effect, Redacted } from 'effect';
import { PgClient } from '@effect/sql-pg'
import { types } from 'pg';

const clientLayer = PgClient.layer({
    url: Redacted.make(process.env.DATABASE_URL!);
	  types: {
		  getTypeParser: (typeId, format) => {
			  if ([1184, 1114, 1082, 1186, 1231, 1115, 1185, 1187, 1182].includes(typeId)) { 
          return (val: any) => val;
        }
			  return types.getTypeParser(typeId, format);
		  },
	  },
});

const program = Effect.gen(function*() {
	const client = yield* PgClient.PgClient;
	const db = drizzle(client, { logger: true, /*...*/ });
});

await Effect.runPromise(program).pipe(Effect.provide(clientLayer));

Step 7 - 데이터베이스 쿼리

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/effect-postgres';
import { Effect, Redacted } from 'effect';
import { PgClient } from '@effect/sql-pg'
import { types } from 'pg';
import { usersTable } from './db/schema'

const clientLayer = PgClient.layer({
    url: Redacted.make(process.env.DATABASE_URL!);
	  types: {
	  	getTypeParser: (typeId, format) => {
	  		if ([1184, 1114, 1082, 1186, 1231, 1115, 1185, 1187, 1182].includes(typeId)) return (val: any) => val;
	  		return types.getTypeParser(typeId, format);
	  	},
	  },
});

const program = Effect.gen(function*() {
	const client = yield* PgClient.PgClient;
	const db = drizzle(client, { logger: true, /*...*/ });

  const user: typeof usersTable.$inferInsert = {
    name: 'John',
    age: 30,
    email: 'john@example.com',
  };

  yield* db.insert(usersTable).values(user);
  console.log('New user created!')

  const users = yield* db.select().from(usersTable);
  console.log('Getting all users from the database: ', users)
  /*
  const users: {
    id: number;
    name: string;
    age: number;
    email: string;
  }[]
  */

  yield* db
    .update(usersTable)
    .set({
      age: 31,
    })
    .where(eq(usersTable.email, user.email));
  console.log('User info updated!')

  yield* db.delete(usersTable).where(eq(usersTable.email, user.email));
  console.log('User deleted!')
});

await Effect.runPromise(program).pipe(Effect.provide(clientLayer));

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

Step 9 - 테이블 스키마 업데이트 (선택사항)

If you want to update your table schema, you can do it in the schema.ts file. For example, let’s add a new column phone to the users_table:

import { pgTable, unique, integer, varchar } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const users = pgTable("users", {
	id: integer().primaryKey().generatedAlwaysAsIdentity({ name: "users_id_seq", startWith: 1, increment: 1, minValue: 1, maxValue: 2147483647, cache: 1 }),
	name: varchar({ length: 255 }).notNull(),
	age: integer().notNull(),
	email: varchar({ length: 255 }).notNull(),
  phone: varchar(),
}, (table) => [
	unique("users_email_unique").on(table.email)
]);

Step 10 - 데이터베이스에 변경사항 적용 (선택사항)

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 11 - 새 필드로 데이터베이스 쿼리 (선택사항)

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/effect-postgres';
import { Effect, Redacted } from 'effect';
import { PgClient } from '@effect/sql-pg'
import { types } from 'pg';
import { usersTable } from './db/schema'

const clientLayer = PgClient.layer({
    url: Redacted.make(process.env.DATABASE_URL!);
	  types: {
	  	getTypeParser: (typeId, format) => {
	  		if ([1184, 1114, 1082, 1186, 1231, 1115, 1185, 1187, 1182].includes(typeId)) return (val: any) => val;
	  		return types.getTypeParser(typeId, format);
	  	},
	  },
});

const program = Effect.gen(function*() {
	const client = yield* PgClient.PgClient;
	const db = drizzle(client, { logger: true, /*...*/ });

  const user: typeof usersTable.$inferInsert = {
    name: 'John',
    age: 30,
    email: 'john@example.com',
    phone: '123-456-7890',
  };

  yield* db.insert(usersTable).values(user);
  console.log('New user created!')

  const users = yield* db.select().from(usersTable);
  console.log('Getting all users from the database: ', users)
  /*
  const users: {
    id: number;
    name: string;
    age: number;
    email: string;
    phone: string | null;
  }[]
  */

  yield* db
    .update(usersTable)
    .set({
      age: 31,
    })
    .where(eq(usersTable.email, user.email));
  console.log('User info updated!')

  yield* db.delete(usersTable).where(eq(usersTable.email, user.email));
  console.log('User deleted!')
});

await Effect.runPromise(program).pipe(Effect.provide(clientLayer));