Best Practices

Shared Column Utility Patterns

Don't

Repeat common columns manually in every table. This leads to:

Inconsistent column names and types across tables
Hard-to-apply global changes (e.g., changing timestamp mode)
Copy–paste errors when adding new tables

DoClick to view

Extract shared columns into a tiny helper module. This provides:

Consistent column definitions for ids and timestamps
Single place to change types/defaults for all tables
Clear intent about “technical” columns vs business columns

// ❌ Bad: Each table redefines common columns by hand

// File: infra/database/tables/user.table.ts
export const userTable = pgTable("users", {
  id: uuid("id").primaryKey().notNull(),
  createdAt: timestamp("createdAt", { mode: "date" }).notNull().defaultNow(),
  updatedAt: timestamp("updatedAt", { mode: "date" }).notNull().defaultNow(),
  email: varchar("email", { length: 320 }),
});

// File: infra/database/tables/document.table.ts
export const documentTable = pgTable("documents", {
  id: uuid("id").primaryKey().notNull(),                         // duplicated
  createdAt: timestamp("createdAt", { mode: "date" }).notNull().defaultNow(), // duplicated
  updatedAt: timestamp("updatedAt", { mode: "date" }).notNull().defaultNow(), // duplicated
  title: varchar("title", { length: 255 }),
});

Serialization & Flattening Patterns for nested objects

Don't

Manually map nested objects inline in each repository method. This causes:

Repeated, slightly different mapping logic in multiple places
Easy-to-miss fields when the domain model changes
Hidden coupling between DB shapes and domain shapes

DoClick to view

Use a single serialization / deserialization path (e.g. Effect Schema) to flatten and rebuild entities. This provides:

Centralized mapping between domain entity ↔ serialized shape ↔ DB row
Safer nested handling for value objects and aggregates
Reusable helpers in the repository instead of ad-hoc mapping

// ❌ Bad: Manual, scattered mapping logic in each repository method

const host = /* Host entity from domain */;

// Repository manually flattens nested objects into columns
const row = {
  id: host.id,
  firstName: host.firstName,
  lastName: host.lastName,

  // address flattened by hand
  country: host.address.country,
  city: host.address.city,
  // ... manually map each address field

  // socialLinks flattened by hand
  websiteLink: host.socialLinks.websiteLink,
  // ... manually map each social link
};

Query Helper Utility Patterns

Don't

Inline query execution and mapping in every repository method. This causes:

Duplicated error handling and logging
Slightly different behaviors for “single vs multiple” queries
Harder to introduce cross-cutting concerns (timeouts, metrics, etc.)

DoClick to view

Centralize query execution and mapping in small helpers. This provides:

Single place to handle driver errors and wrap them in repository errors
Consistent behavior for “single” and “multiple” fetch patterns
Cleaner repository methods focused only on what they query

// ❌ Bad: Each method does its own query + error + mapping

fetchByUserId(userId: UUID): RepositoryEffect<O.Option<Host>, HostNotFoundError> {
  return E.tryPromise({
    try: () =>
      this.db.select().from(hosts).where(eq(hosts.userId, userId)).limit(1),
    catch: () => new HostNotFoundError(),
  }).pipe(
    E.flatMap((rows) => {
      if (rows.length === 0) {
        return E.succeed(O.none());
      }

      // Inline DB row -> Host mapping
      const row = rows[0];
      const serializedHost = {
        ...row,
        address: {
          country: row.country,
          city: row.city,
          // ... manually reconstruct each address field
        },
        socialLinks: {
          websiteLink: row.websiteLink,
          // ... manually reconstruct each social link
        },
      };

      return Host.create(serializedHost).pipe(
        E.map(O.some),
        E.mapError(() => new HostNotFoundError()),
      );
    }),
  );
}

Best Practices

Shared Column Utility Patterns

Serialization & Flattening Patterns for nested objects

Query Helper Utility Patterns

On this page