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.

Separating data access

This separates the SQL code from the rest of the application and facilitates maintenance, testing, and model evolution. 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 the code: no async, no await.

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);

    if (row == null) {
      return null;
    }

    const { id, title, user_id } = row;

    return { id: Number(id), title: String(title), user_id: Number(user_id) };
  }

  // ...
}

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 favors explicit runtime "casting" when reconstructing the object:

return { id: Number(id), title: String(title), user_id: Number(user_id) };

This approach guarantees that the returned object matches exactly the contract expected by the rest of the application. If the database returns unexpected data, explicit casting prevents unpredictable behaviors (silent bugs) by enforcing the correct type.

Usage in Express (Actions)

An Express action manages the request/response cycle and calls the repository for data. Even if the repository is synchronous, the Express action generally remains asynchronous (async) to be able to interact with other modules (reading req.body, external requests, etc.).

import itemRepository from "./itemRepository";

const browse = async (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);

    // We use map() with explicit casting for each row
    return rows.map<Item>(({ id, title, user_id }) => ({
      id: Number(id),
      title: String(title),
      user_id: Number(user_id),
    }));
  }

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): number | bigint {
    const query = database.prepare(
      "update item set deleted_at = datetime('now') where id = ?",
    );
    const result = query.run(id);

    return result.changes; // Returns the number of affected rows
  }

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 explicit runtime casting: avoid blind TypeScript assertions (as Type). Reconstruct your objects with Number(), String(), etc., 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