Drizzle ORM Integration

Metadata

Created	Updated	Status	Type
2026-01-31	2026-01-31	✅ Done	Guide

Overview

Drizzle ORM integration with PostgreSQL for type-safe database operations. Multi-tenant architecture with shop_id foreign keys, BIGINT for VND currency, and JSONB for flexible data.

Why Drizzle?

• Type-safe queries with TypeScript inference
• Zero runtime overhead (SQL-like syntax)
• Lightweight (~35KB vs Prisma ~2MB)
• Direct SQL control when needed

Architecture

apps/api/
├── src/db/
│   ├── index.ts           # Drizzle connection instance
│   ├── connection.ts      # PostgreSQL pool (pg library)
│   ├── schema/            # Table definitions
│   │   ├── index.ts       # Barrel export
│   │   ├── users.ts
│   │   ├── shops.ts
│   │   ├── shop-settings.ts
│   │   ├── daily-records.ts
│   │   ├── weekly-diagnostics.ts
│   │   ├── action-items.ts
│   │   ├── monthly-goals.ts
│   │   ├── product-presets.ts
│   │   ├── ad-decisions.ts
│   │   └── growth-simulations.ts
│   └── seed/              # Development data
│       ├── index.ts
│       ├── seed-users.ts
│       ├── seed-shops.ts
│       └── ...
├── drizzle/
│   └── migrations/        # Generated SQL migrations
└── drizzle.config.ts      # Drizzle Kit configuration

Database Schema

10 tables supporting 9 business modules:

Table	Purpose	Key Fields
users	Auth users (external_id from Clerk/Auth0)	external_id, email
shops	Multi-tenant shops	owner_id → users
shop_settings	Business config (25+ fields)	cogs_ratio, alert thresholds
daily_records	Daily metrics (Module 1)	orders, revenue, ad_spend, leads
weekly_diagnostics	Weekly P&L issues (Module 2)	issues JSONB, user_responses JSONB
action_items	Diagnostic actions	diagnostic_id → weekly_diagnostics
monthly_goals	Revenue targets (Module 9)	revenue_target, profit_target
product_presets	Quick COGS selection (Module 5)	sell_price, cogs
ad_decisions	SCALE/KEEP/KILL history (Module 4)	decision, early_kill_signals JSONB
growth_simulations	Scaling projections (Module 8)	target_revenue, feasibility_level

Entity Relationships

users 1───* shops
shops 1───1 shop_settings
shops 1───* daily_records
shops 1───* weekly_diagnostics ───* action_items
shops 1───* monthly_goals
shops 1───* product_presets
shops 1───* ad_decisions
shops 1───* growth_simulations

Commands

# Development (from apps/api)
pnpm db:push        # Push schema directly (fast, may drop data)
pnpm db:studio      # Open Drizzle Studio GUI
pnpm db:seed        # Populate dev data

# Production (safe migrations)
pnpm db:generate    # Generate SQL migration files
pnpm db:migrate     # Apply pending migrations

Workflow: Development

1. Start PostgreSQL

# From project root
docker compose -f docker-compose.dev.yml up -d

2. Push Schema Changes

cd apps/api
npx drizzle-kit push --force

3. Verify Tables

psql "postgresql://flowershop:devpassword@localhost:5432/flowershop_dev" -c "\dt"

4. Seed Data (Optional)

pnpm db:seed

Workflow: Production

1. Generate Migration

cd apps/api
pnpm db:generate

Creates timestamped SQL file in drizzle/migrations/.

2. Review Migration

Check the generated SQL for destructive changes (DROP, ALTER).

3. Apply Migration

pnpm db:migrate

Drizzle tracks applied migrations in __drizzle_migrations table.

Schema Examples

Define a Table

// apps/api/src/db/schema/daily-records.ts
import { pgTable, uuid, date, integer, bigint, numeric, text, timestamp, uniqueIndex, index } from 'drizzle-orm/pg-core';
import { shops } from './shops';

export const dailyRecords = pgTable('daily_records', {
  id: uuid('id').primaryKey().defaultRandom(),
  shopId: uuid('shop_id').notNull().references(() => shops.id, { onDelete: 'cascade' }),
  recordDate: date('record_date').notNull(),
  orders: integer('orders').notNull(),
  revenue: bigint('revenue', { mode: 'number' }).notNull(),
  adSpend: bigint('ad_spend', { mode: 'number' }).notNull(),
  leads: integer('leads').notNull(),
  // Calculated fields (denormalized)
  aov: bigint('aov', { mode: 'number' }),
  cpl: bigint('cpl', { mode: 'number' }),
  cvr: numeric('cvr', { precision: 5, scale: 2 }),
  roas: numeric('roas', { precision: 6, scale: 2 }),
  netProfit: bigint('net_profit', { mode: 'number' }),
  createdAt: timestamp('created_at').notNull().defaultNow(),
}, (table) => [
  uniqueIndex('uq_daily_records_shop_date').on(table.shopId, table.recordDate),
  index('idx_daily_records_shop_date').on(table.shopId, table.recordDate),
]);

Query Examples

import { db } from '../db';
import { dailyRecords, shops } from '../db/schema';
import { eq, and, gte, lte, desc } from 'drizzle-orm';

// Insert
const record = await db.insert(dailyRecords).values({
  shopId: '...',
  recordDate: '2026-01-31',
  orders: 15,
  revenue: 8500000,
  adSpend: 2000000,
  leads: 85,
}).returning();

// Select with filter
const records = await db
  .select()
  .from(dailyRecords)
  .where(and(
    eq(dailyRecords.shopId, shopId),
    gte(dailyRecords.recordDate, '2026-01-01'),
    lte(dailyRecords.recordDate, '2026-01-31')
  ))
  .orderBy(desc(dailyRecords.recordDate));

// Join
const shopRecords = await db
  .select({
    shopName: shops.name,
    revenue: dailyRecords.revenue,
    recordDate: dailyRecords.recordDate,
  })
  .from(dailyRecords)
  .innerJoin(shops, eq(dailyRecords.shopId, shops.id));

// Update
await db.update(dailyRecords)
  .set({ orders: 20, revenue: 12000000 })
  .where(eq(dailyRecords.id, recordId));

// Delete
await db.delete(dailyRecords)
  .where(eq(dailyRecords.id, recordId));

Aggregations

import { sql, sum, avg, count } from 'drizzle-orm';

// Weekly summary (on-the-fly calculation)
const weeklySummary = await db
  .select({
    totalRevenue: sum(dailyRecords.revenue),
    totalOrders: sum(dailyRecords.orders),
    avgAov: avg(dailyRecords.aov),
    daysRecorded: count(dailyRecords.id),
  })
  .from(dailyRecords)
  .where(and(
    eq(dailyRecords.shopId, shopId),
    gte(dailyRecords.recordDate, weekStart),
    lte(dailyRecords.recordDate, weekEnd)
  ));

Environment Setup

apps/api/.env

# Dev (localhost because running from host machine)
DATABASE_URL=postgresql://flowershop:devpassword@localhost:5432/flowershop_dev

.env.prod (VPS)

# Prod (postgres hostname because inside Docker network)
DATABASE_URL=postgresql://flowershop:<password>@postgres:5432/flowershop

Drizzle Studio

Visual database browser:

cd apps/api
pnpm db:studio

Opens at https://local.drizzle.studio - browse tables, run queries, edit data.

Troubleshooting

Issue	Solution
"client password must be a string"	DATABASE_URL not set - check .env file
Connection refused	PostgreSQL not running - docker compose up -d
Schema out of sync	Run pnpm db:push --force (dev only)
Migration conflict	Check __drizzle_migrations table, remove if stuck
Type errors after schema change	Restart TypeScript server in IDE

Best Practices

1. BIGINT for VND - No decimals needed, avoids floating-point issues
2. Denormalize calculated fields - Read-heavy app, write once daily
3. JSONB for flexible data - Diagnostics, recommendations, early_kill_signals
4. UUID primary keys - Multi-tenant safe, client-side generation possible
5. Cascade deletes - Clean up related records when shop deleted
6. Indexes on shop_id - Every query filters by shop for multi-tenancy

Files Reference

File	Purpose
apps/api/drizzle.config.ts	Drizzle Kit configuration
apps/api/src/db/index.ts	Drizzle instance with schema
apps/api/src/db/connection.ts	PostgreSQL pool for health checks
apps/api/src/db/schema/*.ts	Table definitions
apps/api/src/db/seed/*.ts	Development seed data
packages/utils/src/validators/*.ts	Zod input validators
packages/utils/src/types/*.ts	Domain types

Related

• Docker Integration
• VPS Setup