The repositoryWhat is repository?A project folder tracked by Git that stores your files along with the complete history of every change, inside a hidden .git directory. Ask AI for more pattern

The repository pattern gives your business logic a clean interface to work with. Instead of the service calling db.query('SELECT * FROM users WHERE id = INLINE_CODE', [id]), it calls userRepo.findById(id). The SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more is hidden behind the repository.

// repositories/productRepository.ts
export class ProductRepository {
  constructor(private db: Database) {}

  async findById(id: string): Promise<Product | null> {
    const row = await this.db.query(
      'SELECT id, name, price, stock, category FROM products WHERE id = CODE_BLOCK',
      [id]
    );
    return row ? this.toDomain(row) : null;
  }

  async findByCategory(category: string): Promise<Product[]> {
    const rows = await this.db.query(
      'SELECT id, name, price, stock, category FROM products WHERE category = CODE_BLOCK ORDER BY name',
      [category]
    );
    return rows.map(this.toDomain);
  }

  async updateStock(id: string, newStock: number): Promise<void> {
    await this.db.query(
      'UPDATE products SET stock = CODE_BLOCK, updated_at = NOW() WHERE id = $2',
      [newStock, id]
    );
  }

  async create(data: CreateProductData): Promise<Product> {
    const row = await this.db.query(
      `INSERT INTO products (name, price, stock, category)
       VALUES (CODE_BLOCK, $2, $3, $4) RETURNING *`,
      [data.name, data.price, data.stock, data.category]
    );
    return this.toDomain(row);
  }

  private toDomain(row: any): Product {
    return {
      id: row.id,
      name: row.name,
      price: parseFloat(row.price),
      stock: row.stock,
      category: row.category,
    };
  }
}

The toDomain method is important. Database rows are raw data, prices might come back as strings, dates as ISO strings, booleans as 0/1. The repository translates these into proper domain objects that the restWhat is rest?An architectural style for web APIs where URLs represent resources (nouns) and HTTP methods (GET, POST, PUT, DELETE) represent actions on those resources. Ask AI for more of your application can trust.

Why not just pass db to the service?

You could. Many projects do. But the repository gives you:

1. A single place to change queries: when you optimize a slow query, you change one file
2. A clear interface to mockWhat is mock?A fake replacement for a real dependency in tests that records how it was called so you can verify interactions. Ask AI for more in tests: { findById: jest.fn() } is easier than mocking a database driver
3. Protection against SQL leaking upward: the service never sees a SQL string

ORMWhat is orm?Object-Relational Mapping - a library that lets you interact with a database using your programming language's objects instead of writing raw SQL. Ask AI for more vs query builderWhat is query builder?A library that lets you construct SQL queries by chaining method calls in code, giving more control than an ORM but less boilerplate than raw SQL. Ask AI for more vs raw SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more

This is one of the most debated choices in backend development. Here is an honest comparison:

ORM example (Prisma)

// Prisma - declarative, type-safe, high abstraction
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    orders: {
      where: { status: 'completed' },
      orderBy: { createdAt: 'desc' },
      take: 5,
    },
  },
});

Prisma generates the SQL behind the scenes. You get full TypeScript types for user and user.orders automatically. The tradeoff: when you need a complex aggregation or a window functionWhat is window function?An SQL function that performs a calculation across a set of related rows without collapsing them into a single output row. Ask AI for more, Prisma fights you.

Query builder example (Knex)

// Knex - SQL-like, composable, medium abstraction
const users = await knex('users')
  .select('users.id', 'users.name', knex.raw('COUNT(orders.id) as order_count'))
  .leftJoin('orders', 'users.id', 'orders.user_id')
  .where('orders.status', 'completed')
  .groupBy('users.id')
  .orderBy('order_count', 'desc')
  .limit(10);

Knex lets you compose queries programmatically while staying close to SQL. You can see exactly what query will run. The tradeoff: types are not generated automatically, you need to define them yourself.

Raw SQL example

// Raw SQL - full control, no abstraction
const { rows } = await pool.query(`
  SELECT
    u.id,
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.total) as lifetime_value
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
  GROUP BY u.id
  HAVING COUNT(o.id) > 0
  ORDER BY lifetime_value DESC
  LIMIT CODE_BLOCK
`, [10]);

Raw SQL gives you complete control. Complex joins, CTEs, window functions, recursive queries, nothing fights you. The tradeoff: no type safety, no migrations, and SQL injectionWhat is sql injection?An attack where user input is inserted directly into a database query, letting the attacker read, modify, or delete data. Parameterized queries prevent it. Ask AI for more is one string concatenation away.

Connection pooling

Every database query needs a connection. Opening a new connection for each request is expensive, it involves TCP handshakes, authenticationWhat is authentication?Verifying who a user is, typically through credentials like a password or token. Ask AI for more, and memory allocation. Under load, you exhaust the database's connection limit and everything crashes.

A connection poolWhat is connection pool?A set of pre-opened database connections that your app reuses instead of opening and closing a new one for every request. Ask AI for more maintains a set of reusable connections. Instead of opening/closing connections, you borrow one from the pool and return it when done.

import { Pool } from 'pg';

// Create pool once at application startup
const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: 'myapp',
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,          // Maximum connections in the pool
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail if no connection available in 2s
});

// Use the pool for every query
export async function query(text: string, params?: any[]) {
  const client = await pool.connect();
  try {
    return await client.query(text, params);
  } finally {
    client.release(); // Return connection to pool, do not close it
  }
}

TransactionWhat is transaction?A group of database operations that either all succeed together or all fail together, preventing partial updates. Ask AI for more management

Transactions ensure that a group of database operations either all succeed or all fail. Without transactions, a crash mid-operation can leave your data in an inconsistent state.

Classic example: transferring money between accounts.

async function transferFunds(
  fromId: string,
  toId: string,
  amount: number
): Promise<void> {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // Debit source account
    const { rows } = await client.query(
      'UPDATE accounts SET balance = balance - CODE_BLOCK WHERE id = $2 AND balance >= CODE_BLOCK RETURNING balance',
      [amount, fromId]
    );
    if (rows.length === 0) {
      throw new Error('Insufficient funds');
    }

    // Credit destination account
    await client.query(
      'UPDATE accounts SET balance = balance + CODE_BLOCK WHERE id = $2',
      [amount, toId]
    );

    // Record the transfer
    await client.query(
      'INSERT INTO transfers (from_id, to_id, amount) VALUES (CODE_BLOCK, $2, $3)',
      [fromId, toId, amount]
    );

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

If any step fails, the debit, the credit, or the record, the entire transaction rolls back. The source account keeps its money, the destination gets nothing, and no partial transfer is recorded.

Transactions with Prisma

// Prisma handles the BEGIN/COMMIT/ROLLBACK for you
const transfer = await prisma.$transaction(async (tx) => {
  const source = await tx.account.update({
    where: { id: fromId },
    data: { balance: { decrement: amount } },
  });

  if (source.balance < 0) {
    throw new Error('Insufficient funds');
  }

  await tx.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  });

  return tx.transfer.create({
    data: { fromId, toId, amount },
  });
});

When to use transactions

Not every database operation needs a transaction. Use them when:

• Multiple tables must stay in sync (orders + order items + inventory)
• A failure mid-operation would corrupt data (money transfers, booking systems)
• You need read-then-write consistency (check balance, then debit)

Single INSERT or UPDATE operations are already atomic by default, wrapping them in a transaction adds overhead without benefit.