Step 9 of 12 75% complete

Step 9: Repository Layer with SQLStack

What You'll Learn

Create a repository with @QueryBinder decorator
Write co-located SQL files for type-safe queries
Use :insert, :filter, and :update SQL helpers for dynamic queries
Understand @Only and @Single decorators for result types
Handle parameter binding with positional and named parameters
Transform flat JOIN results into nested objects with DTO

What You'll Build

A TodosRepo with type-safe database operations using co-located SQL files.

Understanding the Repository Pattern

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.

Repository Files

Click through the files below to see how SQLStack organizes repositories. Each SQL file corresponds to a method in index.ts - the filename must match the method name exactly.

todos_repo

server/repo/todos_repo/index.ts NEW

server

repo

todos_repo

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

// Define the shape of a Todo row from the database
export interface Todo {
  id: number;
  title: string;
  description: string | null;
  completed: number;  // SQLite uses 0/1 for boolean
  created_at: string;
  updated_at: string;
  deleted_at: string | null;  // Soft delete timestamp
}

@QueryBinder()  // Enables SQL file binding for this class
@Component()    // Registers this class with the IoC container
export default class TodosRepo {

  // Returns all todos - maps to getAllTodos.sql
  @Query()
  getAllTodos(): Promise<Todo[]> {
    throw new SqlStackError("Not implemented");
  }

  // Returns single todo or null - maps to getTodoById.sql
  // @Single() returns first result or null (vs array)
  @Single()
  @Query()
  getTodoById(id: number): Promise<Todo | null> {
    throw new SqlStackError("Not implemented");
  }

  // Insert using :insert helper - maps to createTodo.sql
  // WriteResult contains lastInsertId and rowsAffected
  @Query()
  createTodo(data: {
    title: string;
    description?: string;
  }): Promise<WriteResult> {
    throw new SqlStackError("Not implemented");
  }

  // Update using :update helper - maps to updateTodo.sql
  @Query()
  updateTodo(data: {
    id: number;
    title?: string;
    description?: string;
    completed?: number;
  }): Promise<WriteResult> {
    throw new SqlStackError("Not implemented");
  }

  // Search with :filter helper - maps to findTodos.sql
  @Query()
  findTodos(filters: {
    completed?: number;
    title?: string;
  }): Promise<Todo[]> {
    throw new SqlStackError("Not implemented");
  }

  // Simple delete - maps to deleteTodo.sql
  @Query()
  deleteTodo(id: number): Promise<WriteResult> {
    throw new SqlStackError("Not implemented");
  }
}

SQL Helpers Overview

SQLStack provides three powerful helpers for building dynamic SQL. Each helper requires you to specify the columns/conditions in parentheses:

:insert(columns...) — Dynamic INSERT, list columns to insert, skip undefined, use = for defaults
:update(columns...) — Dynamic SET, list columns to update, skip undefined, use = for timestamps
:filter(conditions...) — Dynamic WHERE, list conditions, skip undefined, supports LIKE and IN operators

Parameter Binding

SQLStack supports two styles of parameter binding:

Positional Parameters

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

-- Method: getTodoById(id: number)

WHERE id = :arg1

Named Parameters

Use :fieldName to bind object properties by name.

-- Method: update(data: { id, title })

WHERE id = :id

Result Type Decorators

Decorators control how query results are shaped and returned:

@Query() — Returns All Rows

Default behavior. Returns an array of all matching rows, or empty array if none.

@Query example

@Query()
getAllTodos(): Promise<Todo[]> {
  throw new SqlStackError("Not implemented");
}

// Returns: [{ id: 1, title: "..." }, { id: 2, title: "..." }]
// Or: [] if no rows match

@Single() — Returns First Row or Null

Returns the first row if found, or null if no rows match. Use for lookups by ID.

@Single example

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

// Returns: { id: 1, title: "Buy groceries", ... }
// Or: null if not found

@Only() — Exactly One Row Required

Returns exactly one row. Throws an error if zero or multiple rows are found. Use when the row must exist.

@Only example

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

// Returns: { key: "app_name", value: "MyApp" }
// Throws: SqlStackError if 0 rows or 2+ rows

@Page() — Pagination Support

Automatically injects :limit and :offset parameters for paginated queries.

@Page example

@Page(20)  // 20 items per page
@Query()
getTodosPaged(page: number): Promise<Todo[]> {
  throw new SqlStackError("Not implemented");
}

// SQL file uses :limit and :offset
// SELECT * FROM todos ORDER BY id LIMIT :limit OFFSET :offset;

// Usage:
const page1 = await repo.getTodosPaged(1);  // rows 0-19
const page2 = await repo.getTodosPaged(2);  // rows 20-39

@Defaults() — Default Parameter Values

Provide default values for optional parameters.

@Defaults example

@Defaults({ status: "active", limit: 10 })
@Query()
findUsers(filters: { status?: string; limit?: number }): Promise<User[]> {
  throw new SqlStackError("Not implemented");
}

// Called with: findUsers({})
// Uses: { status: "active", limit: 10 }

@Transform() — Shape Results with DTO

Transform flat SQL rows into nested object structures. See the DTO section below for details.

@Transform example

import { DTO, Transform } from "sqlstack";

const toTodoWithAuthor = DTO({
  id: "todo_id",
  title: "todo_title",
  "author.id": "author_id",       // Creates nested object
  "author.name": "author_name"
}, { single: true });

@Transform(toTodoWithAuthor)
@Query()
getTodoWithAuthor(id: number): Promise<TodoWithAuthor | null> {
  throw new SqlStackError("Not implemented");
}

// Transforms flat rows: { todo_id, author_id, author_name }
// Into nested:          { id, title, author: { id, name } }

WriteResult — For INSERT/UPDATE/DELETE

Write operations return WriteResult with lastInsertId and rowsAffected.

WriteResult example

import type { WriteResult } from "sqlstack";

@Query()
createTodo(data: { title: string }): Promise<WriteResult> {
  throw new SqlStackError("Not implemented");
}

// Returns: { lastInsertId: 42, rowsAffected: 1 }

// Usage:
const result = await repo.createTodo({ title: "New todo" });
console.log("Created with ID:", result.lastInsertId);

Transforming Results with DTO

SQL returns flat rows, but your application often needs nested objects. The DTO() helper transforms flat column data into structured objects with the @Transform decorator.

Example: Nesting Author Data

1. The SQL Query — Returns flat columns:

getTodoWithAuthor.sql

SELECT
  t.id AS todo_id,
  t.title AS todo_title,
  u.id AS author_id,
  u.name AS author_name,
  u.email AS author_email
FROM todos t
JOIN users u ON u.id = t.user_id
WHERE t.id = :arg1;

2. Raw SQL Result — All data is flat:

todo_id	todo_title	author_id	author_name	author_email
1	Buy groceries	5	Alice	alice@example.com

3. Define the DTO — Map flat columns to nested structure:

DTO definition

import { DTO, Transform } from "sqlstack";

const toTodoWithAuthor = DTO({
  id: "todo_id",                    // column → property
  title: "todo_title",
  "author.id": "author_id",         // dot notation creates nested object
  "author.name": "author_name",
  "author.email": "author_email"
}, { single: true });

4. Transformed Result — Nested object structure:

Final result

{
  id: 1,
  title: "Buy groceries",
  author: {
    id: 5,
    name: "Alice",
    email: "alice@example.com"
  }
}

Applying @Transform

Repository method

@Transform(toTodoWithAuthor)
@Query()
async getTodoWithAuthor(id: number): Promise<TodoWithAuthor | null> {
  throw new SqlStackError("Not implemented");
}

Two Nesting Patterns

Dot notation — "author.name": "column" creates a nested object
Object value — tags: { id: "tag_id" } creates a nested array (with deduplication)

Key Behaviors

Column mapping: String values map SQL columns to output properties
Deduplication: When using arrays, rows with same id merge automatically
NULL skipping: Child entries where all columns are NULL are omitted
Options: { single: true } returns one object or null; default returns array

Transactions with @transaction

Wrap multiple database operations in a transaction. If any query fails, all changes are rolled back automatically.

Basic Usage

Mark a method with @transaction to wrap it in a transaction:

@transaction example

import { transaction, currentTransaction } from "sqlstack";

class UserService {
  @transaction
  async createUserWithProfile(data: { email: string; name: string }) {
    // Both operations succeed or both are rolled back
    await this.usersRepo.insert(data);
    await this.profilesRepo.insert({ userId: data.id, bio: "" });
  }
}

// If either insert fails, both are rolled back

Automatic Rollback on Error

Any thrown error triggers a rollback:

Rollback on error

@transaction
async createUser() {
  await this.usersRepo.insert({ email: "bob@example.com" });
  throw new Error("something went wrong");
  // Rollback happens here; insert is undone
}

Manual Rollback

Use currentTransaction() to mark for rollback without throwing:

Manual rollback

import { currentTransaction } from "sqlstack";

@transaction
async createUserWithValidation(data: { email: string }) {
  const user = await this.usersRepo.insert(data);

  const check = await this.usersRepo.findById(user.id);
  if (!check) {
    // Mark for rollback with custom error
    currentTransaction()?.rollbackOnly(new Error("User not found after insert"));
    return;
  }

  return user;
}

withTransaction() Function

For programmatic control, use the function version:

withTransaction function

import { withTransaction } from "sqlstack";

const user = await withTransaction(async () => {
  const user = await this.usersRepo.insert({ email: "charlie@example.com" });
  await this.profilesRepo.insert({ userId: user.id });
  return user;
}, { db: "primary" });

Transaction Options

db — Target a specific database: @transaction({ db: "analytics" })
lazy — Default true. Transaction starts on first query, not immediately
nested — Nested @transaction decorators join the outer transaction

More SQLStack Features

Additional features for advanced use cases:

Pagination

@Page(limit) injects :limit/:offset for paged queries

Multiple Databases

@QueryBinder({ db: "analytics" }) to target different databases

Dialect Variants

Use .postgres.sql or .sqlite.sql for database-specific syntax

What's Next?

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

Troubleshooting

Previous Step All Steps Next Step