A friendly, step-by-step tour of our infrastructure: start with a tiny model, add shared columns, then grow into a full repository with Drizzle and Effect — explained in plain language.

Infrastructure Evolution

Start with a Minimal Host Model

Begin with a tiny hosts table containing only the most essential columns.
Keep it simple first, then evolve the model as needs grow.

Introduce Shared Columns Utility

Almost every table needs create/updatetimestamps and unique IDs.

Instead of copying the same code everywhere, we'll create a reusable "blueprint" (sharedColumns).
This keeps our data consistent and our code clean!

Apply Shared Columns and FK

Now we plug our helper into the hosts table.

We spread ...SharedColumns to get id, createdAt, and updatedAt , and then add a userId column that references the users table.
This step shows how to reuse shared columns and wire up a foreign key (userId) in Drizzle.

Finalize Model: Address and Social Links

The real domain isn’t just an ID and email – hosts have profile data.

Here we expand the hosts table with optional details: date of birth, phone number, address fields, and social links.

Repository: Skeleton and Serialization

With the table done, we switch to the repository.

The key idea: before writing a domain entity to the database, we call toDbSerialized to flatten any nested value objects into simple columns.

Add: Insert Method (add)

Let’s add the first write method.

add serializes the entity and inserts it into the database, with clear Effect-based error handling.

Add: Update Method

Next up, let's add the update method.

We make sure the record exists, serialize the new data, and persist the changes.

Fetch: By User ID

Fetch one host by user id.

We call a reusable helper fetchSingle that runs the query, maps the row to a Host entity, and returns an Option.

Fetch: By Phone Number

Fetch many hosts by phone number.

We call a reusable helper fetchMultiple that runs the query, maps the rows to Host entities, and returns a list.

Fetch: Complete Profiles

Not every host has a fully filled profile.

This step builds a query that returns only “complete profiles” and applies pagination.

Fetch: Flexible Query

Here we combine text search, filters, and pagination into one flexible search function.

The repository builds a dynamic query, executes it, and returns a paginated list of Host entities.

Remove: Delete by ID

Finally, delete a host by id: check if it exists, then remove it.

import { pgTable, text, uuid } from "drizzle-orm/pg-core";

// Minimal model (we'll replace inline UUID/timestamps with shared utils soon)
export const hosts = pgTable("hosts", {
  id: uuid("id").primaryKey().notNull(), 
Branded UUID PK in DB
  createdAt: text("createdAt").notNull(), // placeholder; will be timestamp via shared cols
  updatedAt: text("updatedAt").notNull(), // placeholder; will be timestamp via shared cols
  userId: uuid("userId").notNull().unique() // FK relation added next
});

Code Organization

The infrastructure layer is organized around concrete implementations of persistence and integration concerns: repositories, database setup (schema & tables), and configuration.

user.repository.impl.ts

game.repository.impl.ts

todo.repository.impl.ts

schema.ts

user.table.ts

document.table.ts

...

migrations/

seeders/

utils/

db-health.service.ts

run-migrations.ts

env.config.ts

orm.config.ts

index.ts

Key Lessons

Shared Column Patterns: Use utility functions like UuidCol() and SharedColumns to ensure consistency across all tables and reduce duplication of common fields like ID, timestamps.
Domain-to-DB Serialization: Flatten nested domain value objects (like address and socialLinks) into individual table columns using toDbSerialized() for optimal database storage.
Effect-Based Repository Pattern: Compose operations using Effect's pipe, E.tryPromise, and E.flatMap for type-safe error handling and predictable data flow throughout the repository layer.
Helper Method Composition: Break complex operations into focused helpers (executeQuery, createEntity, fetchSingle, fetchMultiple) that can be composed for different query patterns.
Entity Reconstruction: Transform flat database rows back into rich domain entities by reconstructing nested value objects during the createEntity process.
Validation at Boundaries: Use ensureExists() for write operations and proper error types (HostNotFoundError, HostUpdateError) to maintain data integrity and clear error semantics.

Overview

Infrastructure Evolution

Code Organization

Key Lessons

On this page