Drizzle

https://orm.drizzle.team/docs/overview
https://orm.drizzle.team/docs/get-started
https://orm.drizzle.team/docs/sql-schema-declaration
https://orm.drizzle.team/docs/rqb
https://orm.drizzle.team/docs/select
https://orm.drizzle.team/docs/insert
https://orm.drizzle.team/docs/update
https://orm.drizzle.team/docs/delete
https://orm.drizzle.team/docs/transactions
https://orm.drizzle.team/docs/migrations
https://orm.drizzle.team/docs/drizzle-kit-generate
https://orm.drizzle.team/docs/drizzle-kit-migrate
https://orm.drizzle.team/docs/drizzle-kit-studio

Drizzle 是 TypeScript ORM:
    SQL-like API
    type-safe schema
    supports PostgreSQL / MySQL / SQLite
    migration with drizzle-kit
    good fit for TypeScript service

适合:
    team wants SQL control
    type-safe query is important
    schema lives in code
    migration should be explicit
    NestJS / Node.js API with relational database

不适合:
    team wants full Active Record style
    heavy dynamic query without type discipline
    no one owns SQL / schema design

核心原则:
    schema.ts is source of truth for application types
    migrations must be committed
    never run schema push directly in production
    keep database client as singleton provider
    use transaction for multi-write business operation
    validate external input before query
    use indexes based on real query patterns

npm install drizzle-orm postgres
npm install -D drizzle-kit

postgres:
    lightweight PostgreSQL client

drizzle-orm:
    runtime query builder and typed ORM

drizzle-kit:
    generate / migrate / studio tooling

import { defineConfig } from 'drizzle-kit';

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

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio",
    "db:check": "drizzle-kit check"
  }
}

workflow:
    edit schema.ts
    npm run db:generate
    review generated SQL
    commit schema + migration files
    run npm run db:migrate in deployment

import {
  integer,
  pgEnum,
  pgTable,
  text,
  timestamp,
  uuid,
  varchar,
  index,
} from 'drizzle-orm/pg-core';

export const orderStatus = pgEnum('order_status', [
  'PENDING',
  'PAID',
  'CANCELLED',
]);

export const orders = pgTable(
  'orders',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: varchar('user_id', { length: 64 }).notNull(),
    status: orderStatus('status').notNull().default('PENDING'),
    amountCents: integer('amount_cents').notNull(),
    note: text('note'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => ({
    userCreatedIdx: index('orders_user_created_idx').on(table.userId, table.createdAt),
    statusCreatedIdx: index('orders_status_created_idx').on(table.status, table.createdAt),
  }),
);

schema rules:
    use explicit column name
    keep database naming snake_case
    keep TypeScript property naming camelCase
    add indexes for real query shape
    use enum only when values are stable
    use timestamp with timezone for cross-region systems

import { InferInsertModel, InferSelectModel } from 'drizzle-orm';
import { orders } from './schema';

export type Order = InferSelectModel<typeof orders>;
export type NewOrder = InferInsertModel<typeof orders>;

type usage:
    Order:
        row returned from database

    NewOrder:
        insert input shape

注意:
    DTO type != database insert type
    request validation still required

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const client = postgres(process.env.DATABASE_URL!, {
  max: 10,
  idle_timeout: 20,
  connect_timeout: 10,
});

export const db = drizzle(client, { schema });

client rules:
    create once per process
    reuse connection pool
    configure max connections based on database capacity
    close client in shutdown hook when needed

import { eq } from 'drizzle-orm';
import { db } from './db';
import { orders } from './schema';

export async function findOrderById(id: string) {
  const rows = await db
    .select()
    .from(orders)
    .where(eq(orders.id, id))
    .limit(1);

  return rows[0] ?? null;
}

export async function createOrder(input: {
  userId: string;
  amountCents: number;
}) {
  const rows = await db
    .insert(orders)
    .values({
      userId: input.userId,
      amountCents: input.amountCents,
    })
    .returning();

  return rows[0];
}

export async function markOrderPaid(id: string) {
  const rows = await db
    .update(orders)
    .set({
      status: 'PAID',
      updatedAt: new Date(),
    })
    .where(eq(orders.id, id))
    .returning();

  return rows[0] ?? null;
}

await db.transaction(async (tx) => {
  const [order] = await tx
    .insert(orders)
    .values({
      userId: 'user_1',
      amountCents: 1000,
    })
    .returning();

  await tx.insert(orderEvents).values({
    orderId: order.id,
    type: 'ORDER_CREATED',
  });
});

use transaction when:
    multiple writes must succeed or fail together
    state transition writes audit event
    balance / inventory / quota is updated

avoid:
    long external API call inside transaction
    user interaction inside transaction
    unbounded batch in one transaction

import type { PostgresJsDatabase } from 'drizzle-orm/postgres-js';
import type * as schema from './schema';

export type Database = PostgresJsDatabase<typeof schema>;
export const DB = Symbol('DB');

import { Module } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';
import { DB } from './database.provider';

@Module({
  providers: [
    {
      provide: DB,
      inject: [ConfigService],
      useFactory: (config: ConfigService) => {
        const client = postgres(config.getOrThrow<string>('DATABASE_URL'), {
          max: 10,
        });
        return drizzle(client, { schema });
      },
    },
  ],
  exports: [DB],
})
export class DatabaseModule {}

import { Inject, Injectable } from '@nestjs/common';
import { eq } from 'drizzle-orm';
import { DB } from '../database/database.provider';
import type { Database } from '../database/database.provider';
import { orders } from '../database/schema';

@Injectable()
export class OrdersRepository {
  constructor(@Inject(DB) private readonly db: Database) {}

  async findById(id: string) {
    const rows = await this.db
      .select()
      .from(orders)
      .where(eq(orders.id, id))
      .limit(1);

    return rows[0] ?? null;
  }
}

production note:
    keep DB provider in one module
    repositories should not create their own connections

security:
    use parameterized query APIs
    avoid raw SQL string interpolation
    validate DTO before query
    use least privilege database user
    separate migration user from app runtime user when possible
    do not log DATABASE_URL
    use TLS to database in production

import { sql } from 'drizzle-orm';

await db.execute(sql`select now()`);

raw SQL rule:
    use sql tagged template
    never concatenate user input into raw SQL
    wrap raw query in repository method

migration rules:
    generated SQL must be reviewed
    run migrations in CI/staging before production
    backward-compatible migration first
    deploy app after schema is compatible
    destructive migration needs backup and rollback plan

npm run db:generate
npm run db:migrate

classic safe migration:
    1. add nullable column
    2. deploy app writing both old and new fields
    3. backfill
    4. deploy app reading new field
    5. add not-null / drop old field after verification

metrics:
    database_query_duration_seconds
    database_query_total
    database_error_total
    database_pool_used_connections
    database_pool_wait_count
    migration_duration_seconds

logs:
    query name / operation
    duration_ms
    rows affected
    error code
    request_id

alerts:
    query p95 high
    connection pool exhausted
    migration failed
    deadlock / serialization error spike

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: orders
      POSTGRES_USER: order_app
      POSTGRES_PASSWORD: order_app_password
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data

volumes:
  postgres-data:

export DATABASE_URL="postgres://order_app:order_app_password@localhost:5432/orders"

npm run db:generate
npm run db:migrate
npm run db:studio

schema:
    schema.ts reviewed
    indexes match query patterns
    generated SQL committed
    migration rollback plan exists

application:
    one database provider per process
    connection pool sized
    DTO validation enabled
    repositories hide Drizzle details

security:
    DATABASE_URL stored in secret manager
    runtime database user has least privilege
    TLS enabled for production database
    raw SQL reviewed

operations:
    migrations run before app traffic switch
    query duration monitored
    database errors alerted
    backup restore tested