Copy page

Microservices

Data Layer & Database Tooling

Data Layer & Database Tooling

Relevant source files

The following files were used as context for generating this wiki page:

• emailsender/src/db/pool.ts
• emailsender/src/db/schema-types.ts

The Primebrick v3 Data Layer is a specialized, lightweight ORM-like system designed for PostgreSQL. It avoids the overhead of heavy third-party ORMs by using a custom TypeScript decorator-based metadata registry to map code entities to database tables. The system is built around a centralized connection pool and a robust schema migration pipeline that ensures the physical database schema remains in sync with the TypeScript entity definitions.

Database Connectivity

Connectivity is managed through a singleton PostgreSQL connection pool. The system utilizes the pg library and enforces a specific schema context for all operations within a microservice.

• Connection Management: The getPool() function emailsender/src/db/pool.ts:6-21 initializes a Pool instance using the DATABASE_URL environment variable.
• Schema Scoping: Upon connection, the pool automatically executes SET search_path TO ... emailsender/src/db/pool.ts:16-18 using the DB_SCHEMA environment variable (defaulting to emailsender). This ensures that all queries are scoped to the correct namespace without requiring explicit schema prefixes in SQL strings.

Data Layer Architecture

The relationship between the TypeScript code and the PostgreSQL database is mediated by a shared metadata structure defined in SchemaSnapshot.

Natural Language to Code Entity Mapping

Concept	Code Entity / Symbol	Description
Entity Registry	ENTITY_REGISTRY	A WeakMap-based store for metadata collected from decorators.
Table Metadata	SchemaTableMeta emailsender/src/db/schema-types.ts:34-44	Represents the configuration of a table, including its audit status and columns.
Column Metadata	SchemaColumnMeta emailsender/src/db/schema-types.ts:5-32	Defines column properties like types, primary keys, and identity status.
Database Pool	getPool() emailsender/src/db/pool.ts:6-21	The entry point for obtaining a managed PostgreSQL client.

System Interaction Flow

The following diagram illustrates how the system bridges the gap between TypeScript Entity definitions and the live PostgreSQL Schema.

Code
graph TD
    subgraph "Code Entity Space"
        A["@Entity Decorators"] -- "Registers" --> B["ENTITY_REGISTRY"]
        B -- "Snapshot Generation" --> C["SchemaSnapshot (source: entities)"]
    end

    subgraph "Database Space"
        D["PostgreSQL Instance"] -- "Introspection" --> E["SchemaSnapshot (source: database)"]
        F["getPool()"] -- "Connects to" --> D
    end

    C -- "Compare" --> G["Migration Pipeline"]
    E -- "Compare" --> G
    G -- "Generates" --> H["SQL Patch Files"]
    H -- "Applies to" --> D

Sources: emailsender/src/db/schema-types.ts:46-51, emailsender/src/db/pool.ts:6-21

Subsystems

The Data Layer is divided into two primary functional areas: modeling and migrations.

Entity Model & Decorators

The system uses TypeScript decorators to define the database schema directly on class definitions. This "Code-First" approach allows developers to specify primary keys, unique constraints, and special behaviors (like auditing or soft-deletes) using metadata.

• Metadata Storage: Entity definitions are stored in a central registry using SchemaTableMeta emailsender/src/db/schema-types.ts:34-44.
• Special Fields: Support for identity columns (isPostgresIdentity) and audit trails (isAuditable) is baked into the metadata types emailsender/src/db/schema-types.ts:26-40.

For details on available decorators and how to model entities, see [Entity Model & Decorators (#3.1)].

Schema Snapshot & Migration Pipeline

The migration pipeline is responsible for keeping the database in sync with the code. It operates by comparing two SchemaSnapshot objects: one generated from the code entities and one generated by introspecting the live database.

• Heuristics: The system uses SchemaColumnMeta emailsender/src/db/schema-types.ts:5-32 to track data types, nullability, and default values.
• Patch Workflow: When a discrepancy is found, the pipeline generates a SQL patch file to resolve the difference.

For details on the migration scripts and the patch generation process, see [Schema Snapshot & Migration Pipeline (#3.2)].

---

Sources:

• emailsender/src/db/pool.ts:6-21
• emailsender/src/db/schema-types.ts:5-32
• emailsender/src/db/schema-types.ts:34-44
• emailsender/src/db/schema-types.ts:46-51

---