sqlstack

SQL-first data access for Node.js and TypeScript.

Write real SQL next to your code, and use small, composable decorators to bind, execute, and shape results—without ORM complexity. If you already like dropping down to “raw queries”, this is for you.

Table of Contents

• Install
• Quick Start (5 minutes)
• Writing and Extending SQL
• Configuration
• SQL Parameters
• How It Works
• Result Types
• Error Handling
• Transactions
• Inline SQL
• Advanced: Direct Database Access
• Database Support
• Philosophy
• Contributing
• License

Install

Prerequisites

• Node.js 18+ (or a modern LTS runtime).
• Works with both TypeScript and JavaScript; examples use TypeScript syntax.

npm install sqlstack
# or
yarn add sqlstack
# or
pnpm add sqlstack

Install a database driver as needed:

npm install pg             # Postgres
npm install mysql2         # MySQL / MariaDB
npm install better-sqlite3 # SQLite

Quick Start (5 minutes)

At a high level, you:

• Register one or more database connections with SqlStackDB.
• Write normal classes with methods, decorate them with @QueryBinder and @Query.
• Put SQL in .sql files next to those classes.

When you call a decorated method, sqlstack loads the SQL, binds parameters from your method arguments, executes it via the selected adapter, then optionally shapes and validates the results via decorators.

1. Register Your Database

Create a boot.ts (or similar) file at your app's entry point:

// boot.ts
import { SqlStackDB } from "sqlstack/registry";
import { createPgDb } from "sqlstack/adapters";

SqlStackDB
  .register("primary", createPgDb(process.env.DATABASE_URL!))
  .setDefault("primary");

Load this file once during app startup.

2. Create a Repository Class

// src/users/index.ts
import { QueryBinder, Query, SqlStackError } from "sqlstack";

@QueryBinder() // looks for .sql files in the same folder by default
export class UsersRepository {
  @Query()
  async findByEmail(params: { email: string }): Promise<User[]> {
    // Implementation bodies are never called; this method exists
    // only to define the TypeScript signature for @Query.
    throw new SqlStackError("replaced by @Query");
  }
}

3. Create a .sql File

Place the SQL file next to your class:

users/
  index.ts
  findByEmail.sql

users/findByEmail.sql

SELECT id, email, name, status, created_at
FROM users
WHERE email = :email;

4. Use Your Repository

const repo = new UsersRepository();
const users = await repo.findByEmail({ email: "[email protected]" });
console.log(users); // User[]

That's it! The @Query decorator intercepts the method, loads and executes the SQL, and returns results.

You can then add more methods on the same repository that use sqlstack’s SQL helpers:

@QueryBinder()
export class UsersRepository {
  @Query()
  async findByEmail(params: { email: string }): Promise<User[]> {
    throw new SqlStackError("replaced by @Query");
  }

  @Query()
  async updateProfile(params: { id: string; name?: string; status?: string }): Promise<WriteResult> {
    throw new SqlStackError("replaced by @Query");
  }

  @Query()
  async createUser(params: { id: string; email: string; name?: string }): Promise<WriteResult> {
    throw new SqlStackError("replaced by @Query");
  }

  @Query()
  async search(filters: { name?: string; status?: string }): Promise<User[]> {
    throw new SqlStackError("replaced by @Query");
  }
}

users/updateProfile.sql — partial update with :update:

UPDATE users
  :update(name, status, updated_at = now())
WHERE id = :id;

users/createUser.sql — partial insert with :insert:

INSERT INTO users :insert(id, email, name, status = 'active');

users/search.sql — dynamic filters with :filter:

SELECT id, email, name, status
FROM users
WHERE :filter(name, status);

As your needs grow, you evolve the SQL in these files (and use helpers like :update, :insert, and :filter) while keeping the TypeScript method interfaces—and all call sites—unchanged.

5. Go Further with SQL Helpers

Once you’re comfortable with basic .sql files, you can start using:

• :insert(...) to build column lists and VALUES from your named parameters.
• :update(...) to generate SET clauses for partial updates.
• :filter(...) to build dynamic WHERE / HAVING conditions from whichever filters are defined.

These helpers expand into plain SQL; the full behavior and examples are covered in the Writing and Extending SQL section below.

Writing and Extending SQL

sqlstack is SQL-first: you write normal SQL, and sqlstack gives you a few helpers to make it easier to evolve queries over time without changing your method interface.

Plain SQL in Files

Start with regular SQL in .sql files:

-- users/findByEmail.sql
SELECT id, email, name, status
FROM users
WHERE email = :email;

Later, you can refactor this query (add joins, filters, or switch to a CTE) without changing the TypeScript method that calls it, as long as the returned columns still match your declared return type.

Extending SQL with Helpers

sqlstack adds a small set of macros that expand into standard SQL, so you keep full control. They are designed to feel like “obvious SQL you would have written by hand.”

:insert(...)

-- posts/createPost.sql
INSERT INTO posts :insert(id, author_id, title, body, status = 'published');

await repo.createPost({
  id: 'post_1',
  author_id: 'user_1',
  title: 'Hello world',
  body: 'First post!',
  // status: undefined → defaults to 'published' from SQL literal
});

-- expands to
INSERT INTO posts (id, author_id, title, body, status)
VALUES (?, ?, ?, ?, 'published');

Additional behaviors and rules

• Columns whose values are undefined are skipped; at least one non-literal column must be defined or a literal must be present.
• Pure literal expressions (e.g. created_at = now()) are allowed and included even with no params.
• null values are allowed and are bound as NULL.
• Column order in the generated INSERT follows the order listed in :insert(...), regardless of object key order.
• :insert() only works with named parameters; using positional args throws.

:batch_insert(...)

Use :batch_insert(...) to build an efficient multi-row VALUES clause from one or more array arguments (plus optional scalars):

-- comments/createComments.sql
INSERT INTO comments (post_id, author_id, body)
:batch_insert(post_id, comment.author_id, comment.body);

await repo.createComments({
  post_id: 'post_1',
  comment: [
    { author_id: 'user_1', body: 'Nice post!' },
    { author_id: 'user_2', body: 'Subscribed.' },
  ],
});

-- expands (MySQL/SQLite)
INSERT INTO comments (post_id, author_id, body)
VALUES (?, ?, ?), (?, ?, ?);

-- expands (Postgres)
INSERT INTO comments (post_id, author_id, body)
VALUES ($1, $2, $3), ($4, $5, $6);

• At least one argument must resolve to an array; all array arguments must have the same length and be non-empty.
• Arguments can be scalars (reused for every row) or dot-paths into nested objects/arrays (e.g. comment.body, root.ids).
• :batch_insert() only works with named parameters, not positional ones.

:update(...)

-- posts/updatePost.sql
UPDATE posts
  :update(updated_at = now(), title, body)
WHERE id = :id;

await repo.updatePost({
  id: 'post_1',
  title: 'Updated title',
  // body: undefined → not updated
});

-- expands to
UPDATE posts
SET updated_at = now(), title = ?
WHERE id = ?;

:filter(...)

-- users/search.sql
SELECT id, email, name, status
FROM users
WHERE :filter(
  name,              -- name = ?
  age >= :min_age,   -- age >= ?
  status = 'active', -- literal
  name LIKE :search, -- name LIKE ?
  id IN (:ids)       -- id IN (?, ?, ?)
);

await repo.search({
  name: "Alice",
  min_age: 18,
  search: "%ali%",
  ids: [1, 2, 3],
});

-- expands to
SELECT id, email, name, status
FROM users
WHERE name = ?
  AND age >= ?
  AND status = 'active'
  AND name LIKE ?
  AND id IN (?, ?, ?);

You can gradually adopt these helpers as your queries become more dynamic; they never hide the SQL that is ultimately sent to the database, they just generate the repetitive parts.

Validation Controls

Validation (via @ValidateResult) is enabled by default. Control it with environment variables:

• SQLSTACK_ENABLE_VALIDATION=true → force validation ON
• SQLSTACK_DISABLE_VALIDATION=true → force validation OFF
• Otherwise → enabled

How It Works

Execution Pipeline

When you invoke a decorated method, sqlstack:

• Merges any default values into your arguments.
• Loads SQL (from a file or inline), binds placeholders, and executes it via the chosen adapter.
• If the adapter returned rows (a SELECT/WITH), it shapes and optionally validates those rows.
• If the adapter returned a write result (INSERT/UPDATE/DELETE/DDL), it returns that result object as-is.

Decorators run in a fixed, internal order regardless of how you stack them:

1. @Defaults        (fill in missing parameter values)
   ↓
2. @Query           (execute the SQL)
   ↓
3. @Single/@Only/@Exist/@None  (shape the results)
   ↓
4. @ValidateResult  (validate against JSON Schema)
   ↓
5. @Transform       (map flat rows to nested objects)

You can list decorators in any order; they always execute in this pipeline. See docs/decorators.md for details.

Configuration

Register Databases

Use SqlStackDB.register(name, dbInstance) to set up connections:

import { SqlStackDB } from "sqlstack/registry";
import { createPgDb, createMysqlDb, createSqliteDb } from "sqlstack/adapters";

SqlStackDB
  .register("primary",   createPgDb(process.env.PG_URL!))
  .register("analytics", createMysqlDb(process.env.MYSQL_URL!))
  .register("test",      createSqliteDb(":memory:"))
  .setDefault("primary");

Adapters accept either a connection string or an existing driver instance:

import { Pool as PgPool } from 'pg';
import mysql from 'mysql2/promise';
import BetterSqlite3 from 'better-sqlite3';

// Postgres: pass an existing pool
const pgPool = new PgPool({ connectionString: process.env.PG_URL, max: 4 });
SqlStackDB.register('pg-existing', createPgDb(pgPool));

// MySQL: pass an existing pool
const mysqlPool = mysql.createPool({ uri: process.env.MYSQL_URL!, connectionLimit: 4 });
SqlStackDB.register('mysql-existing', createMysqlDb(mysqlPool));

// SQLite: pass an existing better-sqlite3 connection
const sqlite = new BetterSqlite3(':memory:');
SqlStackDB.register('sqlite-existing', createSqliteDb(sqlite));

Set Database per Class

Use @QueryBinder({ db: "name" }) to set a default database for all methods in a class:

import { QueryBinder, Query, SqlStackError } from "sqlstack";

@QueryBinder({ db: "analytics" }) // all methods use "analytics"
export class ReportsRepo {
  @Query()
  async topPages(_a: { since: string }): Promise<Row[]> {
    throw new SqlStackError("replaced");
  }
}

Override Database per Method

Use @Query({ db: "name" }) to override the class default for a specific method:

@QueryBinder({ db: "primary" }) // default for this class
export class MixedRepo {
  @Query() // uses "primary"
  async dailySummary(_a: { date: string }): Promise<Row[]> {
    throw new SqlStackError("replaced");
  }

  @Query({ db: "analytics" }) // override to use "analytics"
  async topPages(_a: { since: string }): Promise<Row[]> {
    throw new SqlStackError("replaced");
  }
}

SQL Parameters

Pass parameters from your method call to the SQL using named or positional placeholders.

Named Parameters

Use :name placeholders in SQL; pass an object from your method:

-- findByEmail.sql
SELECT id, email, name, status
FROM users
WHERE email = :email AND status = :status;

await repo.findByEmail({ email: "[email protected]", status: "active" });

Positional Parameters

Use :arg1, :arg2, etc. (1-based indexing); pass values by position:

-- findById.sql
SELECT *
FROM users
WHERE id = :arg1 AND org_id = :arg2;

await repo.findById("user-123", "org-456");

Do not mix named and positional parameters in a single query.

Array Parameters

Pass arrays to expand into IN (...) lists:

-- listByIds.sql
SELECT *
FROM users
WHERE id IN (:ids);

await repo.listByIds({ ids: ["user-1", "user-2", "user-3"] });

Tip: For empty arrays, return early from your method to avoid invalid SQL:

if (!ids.length) return [];
await repo.listByIds({ ids });

Null and Undefined

• null → SQL NULL
• undefined → throws an error; always provide a value or omit the parameter

SQL File Resolution

The @Query() decorator locates .sql files using a simple, predictable rule. See docs/file_resolution.md for complete details.

Default: Same Folder

No argument to @Query() → looks in the same folder as the class file:

@QueryBinder()
class UsersRepo {
  @Query()
  async findByEmail(_a: { email: string }): Promise<User[]> {
    throw new Error();
  }
}

File structure:

users/
  index.ts
  findByEmail.sql

Dialect-Specific Files

sqlstack looks for <method>.<dialect>.sql first, then <method>.sql:

users/
  findByEmail.pg.sql    ← Postgres
  findByEmail.mysql.sql ← MySQL
  findByEmail.sql       ← fallback (all dialects)

Dialect suffixes: .pg.sql, .mysql.sql, .sqlite.sql.

Explicit Path

Pass a file or folder path to @Query():

@Query(__filename)  // uses this file's directory
@Query(__dirname)   // uses this folder
@Query('./sql')     // uses ./sql folder relative to cwd

Decorators

Use decorators to add behavior to your methods. They compose freely and always execute in the same internal order.

@Defaults

Provide default parameter values:

import { QueryBinder, Defaults, Query, SqlStackError } from "sqlstack";

@QueryBinder()
class UsersRepo {
  @Defaults({ status: "active", limit: 10 })
  @Query()
  async listUsers(_a: { status?: string; limit?: number }): Promise<User[]> {
    throw new SqlStackError();
  }
}

Call with partial arguments:

await repo.listUsers({});  // { status: "active", limit: 10 }
await repo.listUsers({ status: "inactive" }); // { status: "inactive", limit: 10 }

@Page

Add pagination with :offset and :limit parameters. Pages are 1-based by default:

import { QueryBinder, Page, Query, SqlStackError } from "sqlstack";

@QueryBinder()
class UsersRepo {
  @Page(10) // limit = 10, pages 1-based
  @Query()
  async listUsers(params: { status?: string; page?: number }): Promise<User[]> {
    throw new SqlStackError();
  }
}

Use :offset and :limit in your SQL:

SELECT id, email, name, status
FROM users
WHERE status = :status
ORDER BY created_at DESC
LIMIT :limit OFFSET :offset;

Call with page:

await repo.listUsers({ status: "active", page: 2 }); // page 2 of 10 per page

Zero-based pages: Use @Page(10, { zeroBase: true }).

Trailing options (multiple parameters): When your method has more than one parameter, pass { page: N } as the last argument:

await repo.list({ user_id: 1 }, { page: 2 }); // named-style params + trailing options
await repo.list(userId, { page: 2 });         // positional id + trailing options

Result Shape Decorators

Shape single-query results without parameters:

import { QueryBinder, Single, Only, Exist, None, Query, SqlStackError } from "sqlstack";

@QueryBinder()
class UsersRepo {
  // @Single → return the first row or null
  @Single
  @Query()
  async firstOrNull(_a: { email: string }): Promise<User | null> {
    throw new SqlStackError();
  }

  // @Only → require exactly one row; throw if 0 or >1
  @Only
  @Query()
  async exactlyOne(_a: { id: string }): Promise<User> {
    throw new SqlStackError();
  }

  // @Exist → require at least one row; return first row; throw if none
  @Exist
  @Query()
  async requireOne(_a: { email: string }): Promise<User> {
    throw new SqlStackError();
  }

  // @None → require zero rows; return null; throw if any rows
  @None
  @Query()
  async ensureNone(_a: { status: string }): Promise<null> {
    throw new SqlStackError();
  }
}

| Decorator | Input | Output | Throws if | |-----------|-------|--------|-----------| | @Single | [Row, Row, ...] | Row \| null | — (never) | | @Only | [Row] | Row | ≠ 1 row | | @Exist | [Row, Row, ...] | Row | 0 rows | | @None | [] | null | > 0 rows |

@ValidateResult

Validate query results against a JSON Schema:

import schema from "./user.row.schema.json" assert { type: "json" };
import { QueryBinder, ValidateResult, Single, Query, SqlStackError } from "sqlstack";

@QueryBinder()
class UsersRepo {
  @ValidateResult(schema)
  @Single
  @Query()
  async findByEmail(_a: { email: string }): Promise<User | null> {
    throw new SqlStackError();
  }
}

Example schema:

{
  "type": "object",
  "properties": {
    "id": { "type": "string" },
    "email": { "type": "string", "format": "email" },
    "status": { "enum": ["active", "inactive"] }
  },
  "required": ["id", "email", "status"],
  "additionalProperties": false
}

@Transform

Use DTO() with @Transform to turn flat joined rows into nested objects, automatically deduplicating parents and children.

import { DTO, Transform, Query, QueryBinder } from 'sqlstack';

// Define a nesting spec using column names
const toMuscleGroup = DTO(
  {
    id: 'group_id',
    name: 'group_name',
    muscles: {
      id: 'muscle_id',
      name: 'muscle_name',
      exercises: {
        id: 'exercise_id',
        name: 'exercise_name',
      },
    },
  },
  { single: true } // default: array; set to return a single root object or null
);

@QueryBinder()
class Repo {
  @Transform(toMuscleGroup)
  @Query({ sql: `
    SELECT
      g.id   AS group_id,   g.name   AS group_name,
      m.id   AS muscle_id,  m.name   AS muscle_name,
      e.id   AS exercise_id,e.name   AS exercise_name
    FROM groups g
    LEFT JOIN muscles m   ON m.group_id = g.id
    LEFT JOIN exercises e ON e.muscle_id = m.id
    WHERE g.id = :id
    ORDER BY m.name, e.name
  ` })
  async getGroup(_a: { id: string }): Promise<any | null> { throw new Error('replaced'); }
}

Given flat rows like:

[
  { group_id: 'g1', group_name: 'Chest', muscle_id: 'm1', muscle_name: 'Pecs',    exercise_id: 'e1', exercise_name: 'Bench'   },
  { group_id: 'g1', group_name: 'Chest', muscle_id: 'm1', muscle_name: 'Pecs',    exercise_id: 'e2', exercise_name: 'Incline' },
  { group_id: 'g1', group_name: 'Chest', muscle_id: 'm2', muscle_name: 'Delts',   exercise_id: 'e3', exercise_name: 'Raise'   },
]

the transform returns:

[
  {
    id: 'g1',
    name: 'Chest',
    muscles: [
      {
        id: 'm1',
        name: 'Pecs',
        exercises: [
          { id: 'e1', name: 'Bench' },
          { id: 'e2', name: 'Incline' },
        ],
      },
      {
        id: 'm2',
        name: 'Delts',
        exercises: [
          { id: 'e3', name: 'Raise' },
        ],
      },
    ],
  },
];

Direct mapping with dot paths

// Row: { id: '1', muscle: 'biceps', group: 'arms' }
const toSimple = DTO({ id: 'id', name: 'muscle', 'group.name': 'group' } as any, { single: true });
// Result: { id: '1', name: 'biceps', group: { name: 'arms' } }

More behaviors

• If you omit id at a level, DTO uses all mapped fields there as a composite identity to dedupe children.
• Child branches where all identifying columns are NULL are skipped (common with LEFT JOINs).
• Ordering of children follows your SQL ORDER BY; sort in SQL, not in the DTO.

See docs/DTO.md for a full guide with more patterns and edge cases.

Result Types

SELECT / WITH CTE

Returns an array of rows, which can be shaped via decorators:

@QueryBinder()
class Repo {
  @Query()
  async allUsers(): Promise<User[]> {
    throw new Error();
  }

  @Single
  @Query()
  async firstUser(): Promise<User | null> {
    throw new Error();
  }
}

INSERT / UPDATE / DELETE

Returns a result object:

import type { WriteResult } from 'sqlstack';

const result: WriteResult = await db.query(
  'UPDATE users SET status = ? WHERE id = ?',
  ['inactive', 'u1']
);
console.log(result.rowsAffected); // number

Postgres with RETURNING:

const pgResult = await db.query(
  'INSERT INTO users(id, email) VALUES($1, $2) RETURNING id',
  ['u9', '[email protected]']
);
if (pgResult.returning) {
  console.log(pgResult.returning[0].id); // 'u9'
}

Type definition:

type WriteResult = {
  rowsAffected: number;
  lastInsertId?: unknown;
};

type PostgresWriteResult = WriteResult & {
  returning?: Row[];
};

Evolving Queries Without Breaking Callers

Your method signature is the contract; the SQL is an implementation detail.

• Call sites depend only on the TypeScript interface (args and return type), not on the shape of the underlying SQL file.
• You can freely refactor a .sql file to add joins, filters, or pagination, or switch from a simple query to a CTE, without touching call sites—as long as the returned columns still match your declared return type (and any validation schema).
• Decorators like @Transform make it easy to map new or reorganized SQL results back into the same nested DTO shape, so you can take advantage of more advanced SQL while keeping your public interface stable.

This lets you start with very simple queries and incrementally adopt more complex SQL over time, with TypeScript and optional JSON Schema validation guarding the boundary.

Error Handling

sqlstack provides a base error class and specific error types. Import and catch as needed:

import { SqlStackError } from "sqlstack";
import { ExactlyOneRowError, NoRowsError, ValidationError } from "sqlstack/errors";

try {
  const user = await repo.findByEmail({ email: "[email protected]" });
} catch (err) {
  if (err instanceof ExactlyOneRowError) {
    console.log("@Only: expected exactly one row, got a different number");
  } else if (err instanceof NoRowsError) {
    console.log("@Exist: expected at least one row, got none");
  } else if (err instanceof ValidationError) {
    console.log("@ValidateResult: row did not match schema");
  } else if (err instanceof SqlStackError) {
    console.log("Generic sqlstack error");
  } else {
    console.log("Unknown error:", err);
  }
}

Available error classes:

• SqlStackError — base class for all sqlstack errors
• ExactlyOneRowError — thrown by @Only when row count ≠ 1
• NoRowsError — thrown by @Exist when no rows found
• ValidationError — thrown by @ValidateResult on schema mismatch
• Database driver errors — pass through unchanged

Transactions

Run multiple queries as a single atomic unit. If any query fails, all changes are rolled back automatically.

Basic Usage with @transaction Decorator

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

import { transaction, currentTransaction } from "sqlstack";

class UserService {
  @transaction
  async createUserWithProfile(data: { email: string; name: string }) {
    await this.usersRepo.insert(data);
    await this.profilesRepo.insert({ userId: data.id, bio: "" });
  }
}

// If either insert fails, both are rolled back
await new UserService().createUserWithProfile({ email: "[email protected]", name: "Alice" });

Queries in @Query decorated methods automatically participate in the active transaction:

@QueryBinder()
class UsersRepo {
  @Query({ sql: 'INSERT INTO users (email, name) VALUES (:email, :name)' })
  async insert(data: { email: string; name: string }): Promise<WriteResult> {
    throw new Error("replaced by @Query");
  }
}

@QueryBinder()
class ProfilesRepo {
  @Query({ sql: 'INSERT INTO profiles (user_id, bio) VALUES (:userId, :bio)' })
  async insert(data: { userId: string; bio: string }): Promise<WriteResult> {
    throw new Error("replaced by @Query");
  }
}

Commit and Rollback

Commit on success: If the transaction method returns normally, all queries are committed.

Rollback on error: If the method throws, all queries are rolled back and the error is rethrown:

class UserService {
  @transaction
  async createUser() {
    await this.usersRepo.insert({ email: "[email protected]" });
    throw new Error("something went wrong");
    // Rollback happens here; insert is undone
  }
}

await new UserService().createUser(); // throws "something went wrong"

Lazy Transactions

By default, transactions use lazy mode: they don't start (no BEGIN) until the first query runs. This avoids overhead if the method never queries:

@transaction({ lazy: true })  // default behavior
async readOnlyOperation() {
  // No database transaction started yet
  const data = await this.repo.fetch();

  // Process data without holding a transaction
  return processData(data);
}

Disable lazy mode with lazy: false to start the transaction immediately:

@transaction({ lazy: false })
async mustStartTransaction() {
  // BEGIN is executed immediately, even before first query
}

Accessing the Current Transaction

Use currentTransaction() to access the active transaction context within a method:

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

    // Validate user was inserted
    const check = await this.usersRepo.findById(user.id);
    if (!check) {
      // Mark transaction for rollback and throw a custom error
      currentTransaction()?.rollbackOnly(new Error("User not found after insert"));
      return; // exit gracefully
    }

    return user;
  }
}

Rollback with Custom Error

Use rollbackOnly(error) to mark a transaction for rollback while specifying which error should be thrown:

class UserService {
  @transaction
  async createUser(email: string) {
    try {
      await this.usersRepo.insert({ email });
    } catch (dbError) {
      // Roll back and throw a sanitized error
      currentTransaction()?.rollbackOnly(new Error("Failed to create user"));
      return; // rethrow happens automatically
    }
  }
}

You can also throw RollbackTransactionError to explicitly signal rollback while optionally wrapping another error:

import { RollbackTransactionError } from "sqlstack";

class UserService {
  @transaction
  async createUser(email: string) {
    await this.usersRepo.insert({ email });

    // Roll back and throw the wrapped error
    throw new RollbackTransactionError("Transaction cancelled", new Error("User rejected"));
    // Caller sees: Error("User rejected")
  }
}

Using withTransaction() Function

For programmatic control, use the withTransaction() function:

import { withTransaction } from "sqlstack";

const result = await withTransaction(async () => {
  const user = await this.usersRepo.insert({ email: "[email protected]" });
  await this.profilesRepo.insert({ userId: user.id });
  return user;
}, { db: "primary" });

Multiple Databases

Transactions are per-database. Run transactions on different databases simultaneously:

class MultiDbService {
  @transaction({ db: "primary" })
  async insertUser() {
    await this.usersRepo.insert({ email: "[email protected]" });
  }

  @transaction({ db: "analytics" })
  async logAnalytics() {
    await this.analyticsRepo.insert({ event: "user_created" });
  }
}

// Both transactions run independently
await Promise.all([
  new MultiDbService().insertUser(),
  new MultiDbService().logAnalytics(),
]);

Nested Transactions (Savepoints)

Multiple @transaction decorators stack safely. Only the outermost transaction commits/rolls back:

class UserService {
  @transaction
  async createUser(email: string) {
    await this.usersRepo.insert({ email });
    await this.createDefaultProfile();
  }

  @transaction  // nested decorator
  async createDefaultProfile() {
    await this.profilesRepo.insert({ name: "Default Profile" });
  }
}

// Outer transaction commits both inserts
await new UserService().createUser("[email protected]");

All queries use the same transaction context. If the inner method throws, the outer transaction rolls back everything.

Connection Pooling and Resource Cleanup

For Postgres and MySQL, transactions acquire connections from the pool and release them when complete:

Postgres:

import { createPgDb } from "sqlstack/adapters";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.PG_URL });
SqlStackDB.register("primary", createPgDb(pool));

class UserService {
  @transaction({ db: "primary" })
  async createUser() {
    // A client is acquired from the pool for this transaction
    await this.usersRepo.insert({ email: "[email protected]" });
    // Client is released back to the pool after commit/rollback
  }
}

MySQL:

import { createMysqlDb } from "sqlstack/adapters";
import mysql from "mysql2/promise";

const pool = mysql.createPool({ uri: process.env.MYSQL_URL });
SqlStackDB.register("primary", createMysqlDb(pool));

class UserService {
  @transaction({ db: "primary" })
  async createUser() {
    // A connection is acquired from the pool
    await this.usersRepo.insert({ email: "[email protected]" });
    // Connection is released back to the pool
  }
}

SQLite uses the existing connection (better-sqlite3 doesn't support pooling).

Error Handling in Transactions

Errors are always rethrown after rollback:

class UserService {
  @transaction
  async createUser() {
    await this.usersRepo.insert({ email: "[email protected]" });
    throw new Error("oops");
    // Rollback happens, then "oops" is thrown to caller
  }
}

try {
  await new UserService().createUser();
} catch (err) {
  console.error(err.message); // "oops"
}

If rollback itself fails, that error is thrown (and the original error is lost):

@transaction
async createUser() {
  await this.usersRepo.insert({ email: "[email protected]" });
  throw new Error("original error");
  // If ROLLBACK SQL fails, the rollback error is thrown instead
}

Inline SQL

For simple or dynamic queries, use @Query({ sql: "..." }) with inline SQL:

@QueryBinder()
class Repo {
  @Query({ sql: `
    SELECT id, email, name
    FROM users
    WHERE email = :email
  ` })
  async findByEmail(_a: { email: string }): Promise<User[]> {
    throw new Error("replaced");
  }
}

sqlstack applies the same parameter binding and result shaping as file-based SQL.

Advanced: Direct Database Access

Every Database instance exposes the underlying driver via .conn, so you can drop down to raw queries when needed:

import { SqlStackDB } from 'sqlstack/registry';

const db = SqlStackDB.get(); // get the default database

// Driver shape depends on adapter:
// - Postgres: pg.Pool
// - MySQL: mysql2/promise Pool
// - SQLite: better-sqlite3 Database
const raw = db.conn;

// Execute raw queries (driver-specific)
const result = await raw.query('SELECT 1');
console.log(result);

Note: close() only disposes connections created by the adapter. If you supplied an existing connection/pool, close() is a no-op.

Database Support

• Postgres — adapter: createPgDb(), driver: pg
• MySQL / MariaDB — adapter: createMysqlDb(), driver: mysql2/promise
• SQLite — adapter: createSqliteDb(), driver: better-sqlite3

More adapters are planned.

Dialect Inference

The placeholder style is inferred from the selected database. Override per-method with @Query({ dialect: "pg" }) if needed.

Philosophy

SQL-first: SQL is the source of truth. No hidden queries.

Decorators as metadata: Decorators (@Defaults, @Single, @Only, @Exist, @ValidateResult, @Transform) are metadata labels. They always execute in a fixed internal order, so decorator order doesn't matter on your class.

Escape hatches: Use inline SQL for simple queries; keep complex SQL in .sql files. You stay in control.

Contributing

Found a bug? Have a feature idea? Please open an issue or submit a pull request.

Development

1. Clone the repository
2. Install dependencies: npm install
3. Run tests: npm test
4. Build: npm run build

See CONTRIBUTING.md (if available) and AGENTS.md for detailed contributor guidance.

License

ISC