Sequelize
The tasks API on PostgreSQL with Sequelize: connection, typed model, controller and module.
This page finishes the Build a project guide with Sequelize 6 and PostgreSQL, using a typed model class.
Install
Terminal
bun add sequelize pg pg-hstorebun add -d @types/pgSet DATABASE_URL=postgres://postgres:postgres@localhost:5432/riserbob_tasks in .env. The database itself must already exist.
The connection
src/db/sequelize.ts
import { Sequelize } from "sequelize"; const databaseUrl = process.env.DATABASE_URL; if (!databaseUrl) { throw new Error("DATABASE_URL is missing from environment variables");} export const sequelize = new Sequelize(databaseUrl, { dialect: "postgres", logging: false,});The model
Declare the fields with declare so the class does not shadow the getters that Sequelize creates. CreationOptional marks the fields the database fills in for you.
src/modules/tasks/models/task.model.ts
import { DataTypes, Model, type CreationOptional, type InferAttributes, type InferCreationAttributes,} from "sequelize";import { sequelize } from "../../../db/sequelize"; export class Task extends Model< InferAttributes<Task>, InferCreationAttributes<Task>> { declare id: CreationOptional<string>; declare title: string; declare content: string | null; declare isCompleted: CreationOptional<boolean>; declare createdAt: CreationOptional<Date>; declare updatedAt: CreationOptional<Date>;} Task.init( { id: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, primaryKey: true, }, title: { type: DataTypes.STRING(50), allowNull: false, }, content: { type: DataTypes.TEXT, allowNull: true, }, isCompleted: { type: DataTypes.BOOLEAN, allowNull: false, defaultValue: false, }, createdAt: DataTypes.DATE, updatedAt: DataTypes.DATE, }, { sequelize, tableName: "tasks", },);The controller
The controller receives the model class through its constructor. As with any uuid column, an invalid id makes PostgreSQL throw, so each handler checks it and answers 400 first.
src/modules/tasks/tasks.controller.ts
import { Validator } from "riser-bob";import type { SpongHandler } from "riser-bob";import { isUuid } from "../../utils/is-uuid";import { buildPagination, getPagination } from "../../utils/pagination";import type { Task } from "./models/task.model";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 taskModel: typeof Task) {} 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.taskModel.create({ title: result.data.title!, content: result.data.content ?? null, isCompleted: result.data.isCompleted ?? false, }); return spongRes.SpongCreated({ success: true, data: task }); }; readonly findAll: SpongHandler = async (spongReq, spongRes) => { const { page, limit, skip } = getPagination(spongReq); const { rows, count } = await this.taskModel.findAndCountAll({ order: [["createdAt", "DESC"]], limit, offset: skip, }); return spongRes.SpongOK({ success: true, data: rows, pagination: buildPagination(count, 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.taskModel.findByPk(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: { title?: string; content?: string | null; isCompleted?: boolean } = {}; 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.taskModel.findByPk(id); if (!task) { return spongRes.SpongNotFound({ success: false, message: "Task not found", }); } await task.update(changes); 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.taskModel.findByPk(id); if (!task) { return spongRes.SpongNotFound({ success: false, message: "Task not found", }); } await task.destroy(); return spongRes.SpongOK({ success: true, message: "Task deleted" }); };}The module
src/modules/tasks/tasks.module.ts
import { RiserBob } from "riser-bob";import { Task } from "./models/task.model";import { TasksController } from "./tasks.controller";import { TasksRouter } from "./tasks.route"; export class TasksModule { register(bob: RiserBob): void { const controller = new TasksController(Task); new TasksRouter(bob, controller).register(); }}The entry file
The entry file checks the connection and creates the table before the server starts listening. The model must be imported first, which the module import already does.
src/index.ts
import { RiserBob, corsMiddleware, loggerMiddleware } from "riser-bob";import { sequelize } from "./db/sequelize";import { TasksModule } from "./modules/tasks/tasks.module"; async function bootstrap() { await sequelize.authenticate(); await sequelize.sync(); // development only, see the note below 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);});