Copy page

SDK Library

Database Migrations

Database Migrations

Relevant source files

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

• src/migrations/tests/apply-patches.test.ts
• src/migrations/apply-patches.ts
• src/migrations/patch-naming.ts
• src/migrations/patch-registry.ts
• src/ports/database-port.ts

The migrations module provides a robust, idempotent mechanism for applying SQL patches to a database. It is designed to be database-agnostic by relying on the DatabasePort interface, allowing consuming microservices to use any SQL driver while maintaining a consistent migration strategy.

Patch Registry and DDL

Before any patches are processed, the system ensures the existence of a registry table. This table tracks which patches have been applied and records a SHA-256 hash of their contents to guarantee immutability.

The registry is defined in src/migrations/patch-registry.ts and consists of:

• Table Name: public.primebrick_database_patches src/migrations/patch-registry.ts:3-3
• Primary Key: patch_id (the filename without extension) src/migrations/patch-registry.ts:6-6
• Content Tracking: content_sha256 (SHA-256 hex digest of the file body) src/migrations/patch-registry.ts:7-7
• Metadata: applied_at timestamp src/migrations/patch-registry.ts:8-8
• Index: An index on content_sha256 to speed up duplicate detection src/migrations/patch-registry.ts:10-11

Data Flow: Initialization

The applyPatches function always executes the PATCH_REGISTRY_DDL statement as its first action to ensure the environment is ready src/migrations/apply-patches.ts:31-31.

Code
graph TD
    subgraph "Natural Language Space"
        A["Ensure Registry Table Exists"]
        B["Read Patch Files"]
    end

    subgraph "Code Entity Space"
        A1["applyPatches()"]
        A2["PATCH_REGISTRY_DDL"]
        A3["DatabasePort.query()"]
        
        A1 -->|calls| A3
        A2 -.->|SQL Content| A3
    end
    
    A --> A1
    B --> A1

Sources: src/migrations/apply-patches.ts:30-31, src/migrations/patch-registry.ts:5-12

Patch Naming and Identification

The SDK enforces specific naming conventions to ensure patches are applied in a predictable, chronological order.

Function	Purpose	Implementation Detail
utcTimestampForFilename	Generates a sortable timestamp	Formats current date as YYYYMMDDHHMMSS src/migrations/patch-naming.ts:3-9
slugifyPatchSegment	Sanitizes descriptions	Replaces non-alphanumeric chars with _, limits to 72 chars src/migrations/patch-naming.ts:11-18
patchIdFromFilename	Derives the DB ID	Removes the .sql extension from the filename src/migrations/patch-naming.ts:20-22
sha256Hex	Calculates file integrity	Uses node:crypto to create a hex digest of the SQL body src/migrations/patch-naming.ts:24-26

Sources: src/migrations/patch-naming.ts:1-27

The Four-Case Patch Strategy

The applyPatches function implements a specific logic flow for every .sql file found in the target directory src/migrations/apply-patches.ts:15-21. Files are sorted alphabetically by filename before processing src/migrations/apply-patches.ts:35-35.

1. Skip (Already Applied)

If the patch_id exists in the registry and the content_sha256 matches the current file, the patch is skipped src/migrations/apply-patches.ts:60-63.

2. Reject (SHA Mismatch)

If the patch_id exists but the content_sha256 is different, the system throws an error. This prevents "floating" migrations where a previously applied file is modified src/migrations/apply-patches.ts:65-67.

3. Register-Only (Duplicate Content)

If the patch_id is new, but the content_sha256 already exists under a different ID, the system inserts the new patch_id into the registry without re-executing the SQL. This handles cases where the same schema change is introduced under a new filename src/migrations/apply-patches.ts:74-83.

4. Apply (New Patch)

If neither the ID nor the SHA exists, the system:

1. Starts a transaction (BEGIN) src/migrations/apply-patches.ts:87-87.
2. Executes the raw SQL content src/migrations/apply-patches.ts:88-88.
3. Inserts the registry row src/migrations/apply-patches.ts:89-92.
4. Commits the transaction (COMMIT) src/migrations/apply-patches.ts:93-93.

If any step fails, it performs a ROLLBACK src/migrations/apply-patches.ts:96-96.

Strategy Logic Diagram

Code
flowchart TD
    START["applyPatches(dir, db)"] --> READ["Read & Sort *.sql files"]
    READ --> LOOP["For each filename"]
    LOOP --> CALC["Calculate SHA-256 & patch_id"]
    CALC --> CHECK_ID{"patch_id exists?"}
    
    CHECK_ID -- "Yes" --> CHECK_SHA{"SHA matches?"}
    CHECK_SHA -- "Yes" --> SKIP["Skip (Case 1)"]
    CHECK_SHA -- "No" --> FAIL["Throw Error (Case 2)"]
    
    CHECK_ID -- "No" --> CHECK_SHA_GLOBAL{"SHA exists globally?"}
    CHECK_SHA_GLOBAL -- "Yes" --> REG_ONLY["Register ID only (Case 3)"]
    CHECK_SHA_GLOBAL -- "No" --> APPLY["BEGIN -> EXEC SQL -> INSERT -> COMMIT (Case 4)"]
    
    APPLY --> NEXT["Next file"]
    SKIP --> NEXT
    REG_ONLY --> NEXT
    NEXT --> LOOP

Sources: src/migrations/apply-patches.ts:47-99

Database Port Integration

The migration runner does not depend on a specific database driver like pg. Instead, it consumes the DatabasePort interface src/ports/database-port.ts:11-17.

DatabasePort Interface

Code
export interface DatabasePort {
  query<T = unknown>(text: string, params?: unknown[]): Promise<{ rows: T[] }>;
}

The applyPatches function uses this port to:

• Run the registry DDL src/migrations/apply-patches.ts:31-31.
• Check for existing patches src/migrations/apply-patches.ts:53-56.
• Manage transactions (BEGIN, COMMIT, ROLLBACK) src/migrations/apply-patches.ts:87-96.

Sources: src/ports/database-port.ts:1-17, src/migrations/apply-patches.ts:23-27

---