NestJS에서 Drizzle ORM으로 PostgreSQL DB 설정하는 방법

• https://tsx.is/
• https://www.npmjs.com/package/dotenv
• https://node-postgres.com/

DB 만들기 - Docker

https://orm.drizzle.team/docs/guides/postgresql-local-setup
해당 문서 참고

drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

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

Database 모듈 설정

// db.module.ts
import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Module, Global } from '@nestjs/common';
import { Pool } from 'pg';

import { NodePgDatabase } from 'drizzle-orm/node-postgres';
import * as schema from './schema'; // Your Drizzle schema files

// The type for your fully configured Drizzle DB instance
export type DB = NodePgDatabase<typeof schema>;

const dbProvider = {
  provide: 'DATABASE',
  useFactory: () =>
    drizzle({
      connection: {
        connectionString: process.env.DATABASE_URL,
        ssl: false, // 로컬 Docker에서는 SSL 지원 X 
      },
    }),
};

@Global()
@Module({
  providers: [dbProvider],
  exports: ['DATABASE'],
})
export class DbModule {}

schema - user

import { integer, pgTable, varchar } from 'drizzle-orm/pg-core';

export const usersTable = pgTable('users', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar({ length: 255 }).notNull(),
  age: integer().notNull(),
  email: varchar({ length: 255 }).notNull().unique(),
});

뽀나스

import { drizzle } from "drizzle-orm/node-postgres"
const db = drizzle(process.env.DATABASE_URL);
const pool = db.$client;

// above is equivalent to
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
const db = drizzle({ client: pool });

첫번째 방식은 기본설정을 그대로 쓰는 것 튜닝 X

두번째는 Pool을 만들어서 세부 설정(튜닝)함
해당 튜닝 config 정보들은 링크 참고

• https://node-postgres.com/apis/client

type Config = {
  user?: string, // default process.env.PGUSER || process.env.USER
  password?: string or function, //default process.env.PGPASSWORD
  host?: string, // default process.env.PGHOST
  port?: number, // default process.env.PGPORT
  database?: string, // default process.env.PGDATABASE || user
  connectionString?: string, // e.g. postgres://user:password@host:5432/database
  ssl?: any, // passed directly to node.TLSSocket, supports all tls.connect options
  types?: any, // custom type parsers
  statement_timeout?: number, // number of milliseconds before a statement in query will time out, default is no timeout
  query_timeout?: number, // number of milliseconds before a query call will timeout, default is no timeout
  lock_timeout?: number, // number of milliseconds a query is allowed to be en lock state before it's cancelled due to lock timeout
  application_name?: string, // The name of the application that created this Client instance
  connectionTimeoutMillis?: number, // number of milliseconds to wait for connection, default is no timeout
  keepAliveInitialDelayMillis?: number, // set the initial delay before the first keepalive probe is sent on an idle socket
  idle_in_transaction_session_timeout?: number, // number of milliseconds before terminating any session with an open idle transaction, default is no timeout
  client_encoding?: string, // specifies the character set encoding that the database uses for sending data to the client
  fallback_application_name?: string, // provide an application name to use if application_name is not set
  options?: string // command-line options to be sent to the server
}

참고자료

• https://docs.nestjs.com/fundamentals/custom-providers

• https://orm.drizzle.team/docs/get-started/postgresql-new

• https://orm.drizzle.team/docs/get-started-postgresql#node-postgres