Step 7: Repository Layer with SQLStack

The repository pattern separates data access logic from business logic. With SQLStack, you write plain SQL files that live alongside your repository code. This keeps your queries close to where they're used while maintaining full SQL power.

Create a folder for your repository with the SQL files co-located. Each SQL file corresponds to a method in your repository - the filename must match the method name exactly. Click on files in the tree to explore. Files marked with NEW are the ones you'll create.

import { Component } from "@noego/ioc";
import { QueryBinder, Query, SqlStackError } from "@noego/sqlstack";

export interface Todo {
  id: number;
  title: string;
  description: string | null;
  completed: number;
  created_at: string;
}

@QueryBinder()
@Component()
export default class TodosRepo {
  @Query()
  getAllTodos(): Promise<Todo[]> {
    throw new SqlStackError("Not implemented");
  }

  @Query()
  getTodoById(id: number): Promise<Todo | undefined> {
    throw new SqlStackError("Not implemented");
  }

  @Query()
  createTodo(title: string, description: string | null): Promise<Todo> {
    throw new SqlStackError("Not implemented");
  }

  @Query()
  deleteTodo(id: number): Promise<void> {
    throw new SqlStackError("Not implemented");
  }
}

Use the @QueryBinder() decorator to enable SQL file binding, and @Component() to register it with IoC:

import { Component } from "@noego/ioc";
import { QueryBinder, Query, SqlStackError } from "@noego/sqlstack";

export interface Todo {
  id: number;
  title: string;
  description: string | null;
  completed: number;
  created_at: string;
}

@QueryBinder()
@Component()
export default class TodosRepo {
  @Query()
  getAllTodos(): Promise<Todo[]> {
    throw new SqlStackError("Not implemented");
  }

  @Query()
  getTodoById(id: number): Promise<Todo | undefined> {
    throw new SqlStackError("Not implemented");
  }

  @Query()
  createTodo(title: string, description: string | null): Promise<Todo> {
    throw new SqlStackError("Not implemented");
  }

  @Query()
  deleteTodo(id: number): Promise<void> {
    throw new SqlStackError("Not implemented");
  }
}

The method bodies throw SqlStackError as stubs. At runtime, SQLStack replaces these with the actual SQL query execution.

Create the SQL files alongside your repository. Each file contains plain SQL:

SELECT id, title, description, completed, created_at
FROM todos
ORDER BY created_at DESC;

SELECT id, title, description, completed, created_at
FROM todos
WHERE id = :arg1;

The :arg1 syntax binds to the first method argument. For multiple arguments, use :arg1, :arg2, etc.

INSERT INTO todos (title, description)
VALUES (:arg1, :arg2)
RETURNING id, title, description, completed, created_at;

DELETE FROM todos
WHERE id = :arg1;

SQLStack supports two styles of parameter binding:

Positional Parameters

Use :arg1, :arg2, etc. to bind method arguments by position:

// Method signature:
updateTodo(id: number, title: string): Promise<void>

-- SQL file:
UPDATE todos SET title = :arg2 WHERE id = :arg1;

Named Parameters

When passing an object, use :fieldName to bind object properties:

// Method signature:
updateTodo(data: { id: number; title: string }): Promise<void>

-- SQL file:
UPDATE todos SET title = :title WHERE id = :id;

SQLStack provides decorators to control how results are returned:

@Only - Expect Exactly One Result

Use @Only() when you expect exactly one row. Throws an error if zero or multiple rows are returned:

import { Query, Only } from "@noego/sqlstack";

@Query()
@Only()
getConfigValue(key: string): Promise<Config> {
  throw new SqlStackError("Not implemented");
}

@Single - Return First or Undefined

Use @Single() when you want the first result or undefined if none:

import { Query, Single } from "@noego/sqlstack";

@Query()
@Single()
findTodoByTitle(title: string): Promise<Todo | undefined> {
  throw new SqlStackError("Not implemented");
}

• One entity per repository: Keep repositories focused on a single database entity
• Use RETURNING: SQLite supports RETURNING to get inserted/updated rows
• Handle NULL: Use string | null for nullable columns
• Co-locate files: Keep SQL files next to the repository for easy maintenance

Now that we have our repository layer defined, let's set up the database with migrations so our queries have tables to work with.