Database Repository Layer

This Boilerplate relies on Drizzle ORM coupled with postgres.js to handle database interactions. However, instead of importing Drizzle directly inside your Domain Actions, the infra layer abstracts the connection and provides powerful out-of-the-box optimizations via src/infrastructure/repositories/repository.ts.

---

1. The Connection Manager (repository.ts)

This file is responsible for creating a high-performance Singleton connection to your PostgreSQL database.

It implements several enterprise-level configurations automatically:

• prepare: true: Instructs postgres.js to use Prepared Statements globally. This ensures the database query engine compiles the SQL just once, drastically reducing CPU overhead for repeated queries.
• Connection Pooling: Uses the .env variable POSTGRES_POOL to dictate the max number of connections.
• idle_timeout and max_lifetime: Automatically closes idle connections after 5 seconds to prevent memory leaks in the PgBouncer/Serverless environments.
• Integrated Logging: Pipes all generated Drizzle SQL queries directly into the central Fastify/Pino Logger handler via DefaultLogger.

How to use it in Actions

Instead of creating new clients, simply import the manager.

import manager from "@infrastructure/repositories/repository";
import { usersTable } from "../entity";

export default async function getUsers() {
    // Uses the optimized singleton connection
    return await manager.select().from(usersTable);
}

---

2. Pagination Helper

The repository layer exports a strongly-typed withPagination helper. In traditional SQL, implementing pagination (Limit and Offset) safely without polluting the code is tricky.

withPagination automatically translates a page number into the mathematical offset.

import manager, { withPagination } from "@infrastructure/repositories/repository";
import { post } from "../entity";

// Gets Page 2 (with 10 items per page limit by default)
const page = 2;

const query = manager
    .select()
    .from(post)
    .$dynamic(); // Required by Drizzle for dynamic query building

const results = await withPagination(query, page, 10);

---

3. Cache Tagging (Repository References)

To support the Redis "Graceful Degradation" strategy detailed in the Cache Actions Reference, the repository layer also includes a specific pattern for hashing DB Queries.

In src/infrastructure/repositories/references.ts, you use the tag() function. This creates a deterministic, non-colliding String key that you can use to save your Repositories Queries inside Redis.

Cache Degradation Flow

Press enter or space to select a node. You can then use the arrow keys to move the node around. You can then use the arrow keys to move the node around, press delete to remove it and press escape to cancel.

Press enter or space to select an edge. You can then press delete to remove it or press escape to cancel.

import { tag } from "@infrastructure/repositories/references";
import cache from "@infrastructure/cache/actions";

// Request params
const userId = "123";

// Generate a strict, safe Redis Key: "domain:method:conditionHash"
const cacheKey = tag("user", "getById", { id: userId });

// Try Cache first
let user = await cache.json.get(cacheKey);

if (!user) {
    // Query DB with Drizzle
    user = await manager.select().from(usersTable).where(eq(usersTable.id, userId));
    // Save to Cache
    await cache.json.set(cacheKey, user, 60); 
}