import { QueryResultRow } from 'pg';
import { pool, Queryable } from './db';
export abstract class BaseRepository<T extends QueryResultRow> {
protected abstract readonly table: string;
constructor(protected readonly db: Queryable = pool) {}
protected async many(text: string, values: unknown[] = []): Promise<T[]> {
const res = await this.db.query<T>(text, values);
return res.rows;
}
protected async one(text: string, values: unknown[] = []): Promise<T | null> {
const res = await this.db.query<T>(text, values);
return res.rows[0] ?? null;
}
forTransaction(client: Queryable): this {
const Ctor = this.constructor as new (db: Queryable) => this;
return new Ctor(client);
}
}
import { Pool, PoolClient, QueryResult, QueryResultRow } from 'pg';
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 10,
});
export interface Queryable {
query<T extends QueryResultRow>(text: string, values?: unknown[]): Promise<QueryResult<T>>;
}
export async function withTransaction<T>(
fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await fn(client);
await client.query('COMMIT');
return result;
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
import { QueryResultRow } from 'pg';
import { BaseRepository } from './BaseRepository';
export interface UserRow extends QueryResultRow {
id: string;
email: string;
display_name: string;
created_at: Date;
}
export class UserRepository extends BaseRepository<UserRow> {
protected readonly table = 'users';
findById(id: string): Promise<UserRow | null> {
return this.one('SELECT * FROM users WHERE id = $1', [id]);
}
findByEmail(email: string): Promise<UserRow | null> {
return this.one('SELECT * FROM users WHERE lower(email) = lower($1)', [email]);
}
insert(email: string, displayName: string): Promise<UserRow | null> {
return this.one(
`INSERT INTO users (email, display_name)
VALUES ($1, $2)
RETURNING *`,
[email, displayName],
);
}
}
import { withTransaction } from './db';
import { UserRepository } from './UserRepository';
import { AuditRepository } from './AuditRepository';
const users = new UserRepository();
const audits = new AuditRepository();
export async function registerUser(email: string, displayName: string) {
return withTransaction(async (client) => {
const txUsers = users.forTransaction(client);
const txAudits = audits.forTransaction(client);
const existing = await txUsers.findByEmail(email);
if (existing) {
throw new Error('email already registered');
}
const user = await txUsers.insert(email, displayName);
if (!user) {
throw new Error('failed to create user');
}
await txAudits.record('user.registered', user.id);
return user;
});
}
The repository pattern isolates persistence details behind a small, intention-revealing interface so the rest of the application talks about domain concepts (findById, insert) rather than SQL strings and connection pools. This example keeps the pattern deliberately pragmatic: no ORM, no heavy abstraction layer, just a thin base class that owns query execution and a concrete repository that owns table-specific SQL.
In db.ts, a single pg Pool is created and wrapped by a query helper and a withTransaction helper. The Queryable type is the key seam — it accepts either the pool or a checked-out PoolClient, which lets the same repository methods run inside or outside a transaction without duplication. withTransaction checks out a client, issues BEGIN, runs the callback, and commits, rolling back on any thrown error before releasing the client back to the pool. This guarantees connections are never leaked even on failure.
In BaseRepository.ts, the abstract class captures what every repository shares: a Queryable (defaulting to the shared pool) and a one/many pair that run a query and shape the result rows. The forTransaction method returns a new instance of the same repository bound to a transaction client, which is how callers opt a repository into an existing transaction. Subclasses only declare their table name and any custom row mapping.
In UserRepository.ts, the concrete repository writes plain parameterized SQL against the users table. Parameterization ($1, $2) is non-negotiable — it prevents SQL injection and lets Postgres cache query plans. insert uses RETURNING * so the created row, including database-generated defaults like id and created_at, comes back in one round trip.
In usage.ts, withTransaction composes two repositories over one transaction: a user is inserted and an audit row written atomically, so either both persist or neither does. The trade-off of this lightweight approach is that complex object graphs and relationships must be assembled by hand, but for small services it keeps data access explicit, testable by swapping the Queryable, and free of ORM surprises.
Related snips
class CommentsController < ApplicationController
before_action :set_post
def create
@comment = @post.comments.build(comment_params)System test: asserting Turbo Stream responses
export interface RetryOptions {
retries: number;
baseMs: number;
maxMs: number;
signal?: AbortSignal;
onRetry?: (attempt: number, delay: number, err: unknown) => void;Exponential backoff with jitter for retries
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
class SignupForm
include ActiveModel::Model
include ActiveModel::Attributes
attribute :account_name, :string
attribute :email, :stringShallow Controller, Deep Params: Form Object Pattern
export type Settled<R> =
| { status: 'fulfilled'; value: R }
| { status: 'rejected'; reason: unknown };
export interface ConcurrencyOptions {
limit: number;Simple concurrency limiter for batch operations
Share this code
Here's the card — post it anywhere.
