Drizzle ORM

bun add drizzle-orm pgbun add -d drizzle-kit @types/pg

import {  boolean,  pgTable,  text,  timestamp,  uuid,  varchar,} from "drizzle-orm/pg-core"; export const tasks = pgTable("tasks", {  id: uuid("id").defaultRandom().primaryKey(),  title: varchar("title", { length: 50 }).notNull(),  content: text("content"),  isCompleted: boolean("is_completed").notNull().default(false),  createdAt: timestamp("created_at").notNull().defaultNow(),  updatedAt: timestamp("updated_at").notNull().defaultNow(),}); export type Task = typeof tasks.$inferSelect;export type NewTask = typeof tasks.$inferInsert;

import { drizzle } from "drizzle-orm/node-postgres";import { Pool } from "pg";import * as schema from "./schema"; const databaseUrl = process.env.DATABASE_URL; if (!databaseUrl) {  throw new Error("DATABASE_URL is missing from environment variables");} const pool = new Pool({ connectionString: databaseUrl }); export const db = drizzle({ client: pool, schema });

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

# quick start while developing: push the schema straight to the databasebunx drizzle-kit push # or keep a history of migrationsbunx drizzle-kit generatebunx drizzle-kit migrate

import { count, desc, eq } from "drizzle-orm";import { Validator } from "riser-bob";import type { SpongHandler } from "riser-bob";import type { db as Database } from "../../db/client";import { tasks } from "../../db/schema";import { isUuid } from "../../utils/is-uuid";import { buildPagination, getPagination } from "../../utils/pagination";import { CreateTaskRule } from "./rules/create.rule";import { UpdateTaskRule } from "./rules/update.rule"; type TaskInput = {  title?: string;  content?: string | null;  isCompleted?: boolean;}; export class TasksController {  constructor(private readonly db: typeof Database) {}   readonly create: SpongHandler = async (spongReq, spongRes) => {    const body = await spongReq.json<Record<string, unknown>>();    const result = Validator.validate<TaskInput>(body, CreateTaskRule);     if (!result.valid) {      return spongRes.SpongUnprocessableContent({        success: false,        message: "Validation failed",        errors: result.errors,      });    }     const [task] = await this.db      .insert(tasks)      .values({        title: result.data.title!,        content: result.data.content ?? null,        isCompleted: result.data.isCompleted ?? false,      })      .returning();     return spongRes.SpongCreated({ success: true, data: task });  };   readonly findAll: SpongHandler = async (spongReq, spongRes) => {    const { page, limit, skip } = getPagination(spongReq);     const [rows, totals] = await Promise.all([      this.db        .select()        .from(tasks)        .orderBy(desc(tasks.createdAt))        .limit(limit)        .offset(skip),      this.db.select({ total: count() }).from(tasks),    ]);     return spongRes.SpongOK({      success: true,      data: rows,      pagination: buildPagination(totals[0]?.total ?? 0, page, limit),    });  };   readonly findOne: SpongHandler = async (spongReq, spongRes) => {    const id = spongReq.params.id;     if (!id || !isUuid(id)) {      return spongRes.SpongBadRequest({        success: false,        message: "Invalid task id",      });    }     const [task] = await this.db.select().from(tasks).where(eq(tasks.id, id));     if (!task) {      return spongRes.SpongNotFound({        success: false,        message: "Task not found",      });    }     return spongRes.SpongOK({ success: true, data: task });  };   readonly update: SpongHandler = async (spongReq, spongRes) => {    const id = spongReq.params.id;     if (!id || !isUuid(id)) {      return spongRes.SpongBadRequest({        success: false,        message: "Invalid task id",      });    }     const body = await spongReq.json<Record<string, unknown>>();    const result = Validator.validate<TaskInput>(body, UpdateTaskRule);     if (!result.valid) {      return spongRes.SpongUnprocessableContent({        success: false,        message: "Validation failed",        errors: result.errors,      });    }     // Pick the fields explicitly. Never pass the whole body to the database.    const changes: Partial<typeof tasks.$inferInsert> = {};    if (result.data.title !== undefined) changes.title = result.data.title;    if (result.data.content !== undefined) changes.content = result.data.content;    if (result.data.isCompleted !== undefined) {      changes.isCompleted = result.data.isCompleted;    }     if (Object.keys(changes).length === 0) {      return spongRes.SpongBadRequest({        success: false,        message: "No valid fields provided for update",      });    }     const [task] = await this.db      .update(tasks)      .set({ ...changes, updatedAt: new Date() })      .where(eq(tasks.id, id))      .returning();     if (!task) {      return spongRes.SpongNotFound({        success: false,        message: "Task not found",      });    }     return spongRes.SpongOK({ success: true, data: task });  };   readonly delete: SpongHandler = async (spongReq, spongRes) => {    const id = spongReq.params.id;     if (!id || !isUuid(id)) {      return spongRes.SpongBadRequest({        success: false,        message: "Invalid task id",      });    }     const [task] = await this.db      .delete(tasks)      .where(eq(tasks.id, id))      .returning();     if (!task) {      return spongRes.SpongNotFound({        success: false,        message: "Task not found",      });    }     return spongRes.SpongOK({ success: true, message: "Task deleted" });  };}

import { RiserBob } from "riser-bob";import { db } from "../../db/client";import { TasksController } from "./tasks.controller";import { TasksRouter } from "./tasks.route"; export class TasksModule {  register(bob: RiserBob): void {    const controller = new TasksController(db);     new TasksRouter(bob, controller).register();  }}

import { RiserBob, corsMiddleware, loggerMiddleware } from "riser-bob";import { TasksModule } from "./modules/tasks/tasks.module"; async function bootstrap() {  const bob = new RiserBob();   bob.spongUse(    corsMiddleware({      origin: "http://localhost:5173",      credentials: true,    }),  );  bob.spongUse(loggerMiddleware);   bob.spongGet("/", (spongReq, spongRes) => {    return spongRes.send("Welcome to RiserBob APIs");  });   new TasksModule().register(bob);   bob.spongListen(4000, () => {    console.log("🧽 RiserBob is running at http://localhost:4000 🍍");  });} bootstrap().catch((error: unknown) => {  console.error("[RiserBob] Failed to start application:", error);  process.exit(1);});