The Repository pattern

Summary: To access the database, StartER adopts the Repository pattern. This pattern encapsulates SQL queries in dedicated classes and provides a clear interface for performing CRUD operations.

Why the Repository pattern?

The Repository pattern enforces a strict separation of concerns by keeping data access logic isolated from business logic.

Without a repository, Express actions quickly become cluttered with raw SQL queries. By encapsulating database interactions within a dedicated class:

• Your route handlers remain clean and focused solely on HTTP concerns.
• You avoid scattering SQL queries throughout the codebase.
• You centralize all interactions with a specific table in one place, making the data layer much easier to reason about, maintain, and test.

All communications with a specific table must pass through its Repository.

Implementing the Repository

A sample implementation is provided in src/express/modules/item/itemRepository.ts. StartER takes advantage of SQLite's native synchronous API, which greatly simplifies database interactions: you don't need async or await when executing SQL queries.

Important

While the SQLite calls are synchronous, you may use asynchronous code in repositories. You can still mark a repository method as async if you need to perform asynchronous operations in addition to data logic, such as calling an external HTTP API or reading a file from the disk.

import database from "../../../database";

class ItemRepository {
  // The C of CRUD - Create operation
  create(item: Omit<Item, "id">): number | bigint {
    const query = database.prepare(
      "insert into item (title, user_id) values (?, ?)",
    );

    const result = query.run(item.title, item.user_id);

    return result.lastInsertRowid;
  }

  // The R of CRUD - Read operation
  find(byId: number): Item | null {
    const query = database.prepare(
      "select id, title, user_id from item where id = ? and deleted_at is null",
    );

    const row = query.get(byId);

    return row ? itemSchema.parse(row) : null;
  }

  // ...
}

export default new ItemRepository();

This way, each Express module can have its own repository, ensuring a clear and extensible organization of the code.

A word on TypeScript and robustness

SQLite does not return strict TypeScript types, but basic SQL values (string | number | bigint | null, etc.). Instead of using TypeScript type assertions (like as Item which would hide potential errors at compile-time without protecting execution), StartER uses Zod for explicit runtime output parsing:

import { z } from "zod";

const itemSchema: z.ZodType<Item> = z.object({
  id: z.number(),
  title: z.string(),
  user_id: z.number()
});

// ...

return itemSchema.parse(row);

By binding the Zod schema to the TypeScript type (z.ZodType<Item>), this approach guarantees that the returned object matches exactly the contract expected by the rest of the application. If the database returns unexpected data, Zod prevents unpredictable behaviors (silent bugs) by throwing a clear error.

Important

The Output Schema in the Repository serves a completely different purpose than the Input Schema in the Validator. The Repository schema only casts raw primitives to match the TypeScript type. It does NOT enforce business constraints (like .min(1) or .email()) which belong in the Validator.

Usage in Express (Actions)

An Express action manages the request/response cycle and calls the repository for data. When the action only interacts with the synchronous SQLite repository, it does not need to be async. Mark an action as async only when it performs genuinely asynchronous operations (e.g., sending emails or calling external APIs).

import itemRepository from "./itemRepository";

const browse = (req, res) => {
  const items = itemRepository.findAll(10, 0); // Direct and synchronous call
  res.json(items);
};

export default { browse };

All item actions and the complete repository (linked to the database) are available in the following files:

• src/express/modules/item/itemActions.ts
• src/express/modules/item/itemRepository.ts

Going further: pagination

The repository provides built-in pagination via findAll(limit, offset):

  findAll(limit: number, offset: number): Item[] {
    const query = database.prepare(
      "select id, title, user_id from item where deleted_at is null limit ? offset ?",
    );
    
    const rows = query.all(limit, offset);

    return rows.map((row) => itemSchema.parse(row));
  }

The browse action in itemActions.ts uses an offset calculated from the ?start= query parameter:

const offset = Number(req.query.start ?? "0");

const items = itemRepository.findAll(10, offset);

Tip

This basic pagination mechanism is sufficient to get started. For more advanced use cases, you can add a total count, configurable sorting, or cursor-based pagination.

Going further: soft delete

StartER uses a soft delete strategy: records are not physically removed from the database, but marked with a deleted_at timestamp.

The repository provides three complementary methods:

Method	Behavior
softDelete	Marks the record as deleted (deleted_at = datetime('now'))
softUndelete	Restores a deleted record (deleted_at = null)
hardDelete	Permanently removes the record from the database

  softDelete(id: number): boolean {
    const query = database.prepare(
      "update item set deleted_at = datetime('now') where id = ?",
    );
    const result = query.run(id);

    return result.changes > 0;
  }

Read queries (findAll, find) automatically filter out deleted records using the where deleted_at is null clause.

Important

By default, the destroy action in itemActions.ts uses softDelete. To permanently delete a record, use hardDelete with full awareness of the consequences.

Best practices and use cases

• Favor Zod output parsing: avoid blind TypeScript assertions (as Type). Parse your DB outputs through a Zod schema to guarantee data security and integrity at runtime.
• Default pagination: remember to limit SQL queries to avoid overloading the application on large tables.
• Favor soft delete: keeping history with deleted_at secures data and simplifies restoration in case of mistakes.

See also

• Database
• Creating your first resource
• Many-to-many and transactions