Drizzle ORM

// src/db/schema.ts
import { pgTable, text, timestamp, integer } from "drizzle-orm/pg-core";
 
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});
 
export const orders = pgTable("orders", {
  id: text("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id),
  totalCents: integer("total_cents").notNull(),
});

import { drizzle } from "drizzle-orm/node-postgres";
import pg from "pg";
import { eq } from "drizzle-orm";
import { users } from "./schema";
 
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool);
 
export async function findUserByEmail(email: string) {
  const rows = await db.select().from(users).where(eq(users.email, email)).limit(1);
  return rows[0] ?? null;
}

// src/db/index.ts
import { drizzle } from "drizzle-orm/node-postgres";
import pg from "pg";
import * as schema from "./schema";
 
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});
 
export const db = drizzle(pool, { schema });
 
// src/repositories/order-repo.ts
import { db } from "../db";
import { orders, orderItems } from "../db/schema";
import { eq } from "drizzle-orm";
 
export async function createOrder(
  userId: string,
  items: { sku: string; qty: number; priceCents: number }[]
) {
  const totalCents = items.reduce((s, i) => s + i.qty * i.priceCents, 0);
 
  return db.transaction(async (tx) => {
    const [order] = await tx
      .insert(orders)
      .values({ id: crypto.randomUUID(), userId, totalCents })
      .returning();
 
    await tx.insert(orderItems).values(
      items.map((i) => ({
        id: crypto.randomUUID(),
        orderId: order.id,
        sku: i.sku,
        qty: i.qty,
        priceCents: i.priceCents,
      }))
    );
 
    return order;
  });
}

npx drizzle-kit generate
npx drizzle-kit migrate

import { db } from "./db";
import { users, orders } from "./schema";
import { eq } from "drizzle-orm";
 
const rows = await db
  .select({
    userId: users.id,
    email: users.email,
    orderId: orders.id,
    totalCents: orders.totalCents,
  })
  .from(users)
  .innerJoin(orders, eq(orders.userId, users.id))
  .where(eq(users.id, userId));

import { sql } from "drizzle-orm";
 
await db.execute(sql`
  SELECT date_trunc('day', created_at) AS day, COUNT(*)::int AS cnt
  FROM orders GROUP BY 1
`);

import fp from "fastify-plugin";
import { db } from "./db";
 
export const dbPlugin = fp(async (fastify) => {
  fastify.decorate("db", db);
});