pg-extension

pg-extension

A high-performance PostgreSQL toolkit for Node.js built on top of the official pg driver.

pg-extension is the PostgreSQL adapter for the sql-core ecosystem. It provides execution utilities, repositories, batch processing, stream processing, health checks, and PostgreSQL-specific implementations while reusing the database-independent abstractions from sql-core.

Example

• admin: SSR Admin Application
• admin-service: Admin Backend Microservice
• sql-simple-modular-sample: RESI API with express and postgres

Features

• Built on the official pg driver
• Reuses Repository and CRUDRepository from sql-core
• Works seamlessly with query-mappers
• Metadata-driven CRUD operations
• Batch insert and update
• Stream processing for large datasets
• Optimistic locking
• Transaction support
• PostgreSQL health check for Kubernetes
• TypeScript first
• Lightweight with no ORM

Installation

npm install pg-extension

or together with the ecosystem:

npm install sql-core query-mappers pg-extension

Why pg-extension?

Most SQL libraries are either:

• Low-level drivers (pg)
• Full-featured ORMs (TypeORM, Prisma, Sequelize)

This library focuses on infrastructure.

It provides:

• Connection management
• Transactions
• Repository integration
• Batch execution
• Streaming

without hiding SQL from developers.

Moreover, pg-extension can work with sql-core and query-mappers. They separate responsibilities into independent layers.

• SQL generation belongs to sql-core
• Object mapping belongs to query-mappers
• PostgreSQL execution belongs to pg-extension

This architecture keeps applications lightweight, modular, and easy to maintain.

Ecosystem

                    Application
                          │
            ┌─────────────┴─────────────┐
            │                           │
            ▼                           ▼
       Repository                 CRUDRepository
       (sql-core)
            │
            ▼
      query-mappers
            │
            ▼
      pg-extension
            │
            ▼
       PostgreSQL

Responsibilities

Package	Responsibility
sql-core	Database-independent repositories, CRUD, SQL builders, transactions
query-mappers	Maps database rows to TypeScript models
pg-extension	PostgreSQL execution, repositories, writers, streaming, health checks

---

Core Components

PostgreSQL Execution

Execute SQL statements using PostgreSQL.

• Transaction support
• Query execution
• Command execution
• Prepared statements

Transactions

const tx = await db.beginTransaction()

try {
    await tx.execute(
        `INSERT INTO users(name) VALUES($1)`,
        ["John"]
    )
    await tx.commit()
}
catch (err) {
    await tx.rollback()
}

Query

Rows are automatically mapped into TypeScript objects.

interface User {

    id: number

    name: string

    active: boolean

}

const users = await db.query<User>(sql)

Automatically converts database values.

0  -> false

1  -> true

NULL -> null

Execute

await db.execute(
    `UPDATE users SET active = $1 WHERE id = $2`
    [true, 10]
)

---

Batch Execution

await db.executeBatch([
    {
        query: "INSERT INTO users(name) VALUES($1)",
        params: ["John"]
    },
    {
        query: "INSERT INTO users(name) VALUES($1)",
        params: ["Jane"]
    }
])

Repository

pg-extension provides PostgreSQL implementations that reuse the generic repositories fromsql-core.

Features include:

• Create
• Update
• Delete
• Find by id
• Search
• Paging
• Sorting
• Optimistic locking

Batch Processing

Efficiently execute multiple operations.

Suitable for:

• Data migration
• Import jobs
• Synchronization
• ETL

Writer

Insert data efficiently.

const writer = new PostgreSQLWriter(pool)

await writer.write(users)

Batch Writer

const writer = new PostgreSQLBatchWriter(pool)

await writer.write(users)

Stream Processing

Designed for processing very large datasets without loading everything into memory.

Typical use cases:

• CSV import
• Excel import
• Background jobs
• Large database synchronization

Stream Writer

const writer = new PostgreSQLStreamWriter(pool)

await writer.write(user)

Health Check

Built-in PostgreSQL health checker.

Designed for cloud-native deployments.

Features:

• Connection validation
• Query validation
• Response time measurement
• Configurable timeout
• Kubernetes readiness and liveness probes

Example:

const checker = new PostgreSQLChecker(pool);

const result = await checker.check();

Metadata-driven Persistence

CRUD operations are generated from metadata instead of handwritten SQL.

Supports:

• Primary keys
• Version fields
• Read-only fields
• Insert-only fields
• Update-only fields
• Automatic SQL generation

Optimistic Locking

Version columns are automatically detected from metadata.

No additional repository code is required.

Transactions

Supports PostgreSQL transactions using the abstractions defined in sql-core.

Begin Transaction

       ↓

Execute Commands

       ↓

Commit / Rollback

Integration with query-mappers

query-mappers converts PostgreSQL rows into strongly typed TypeScript models.

PostgreSQL Row

       ↓

 query-mappers

       ↓

TypeScript Object

Designed for Enterprise Applications

pg-extension is suitable for:

• REST APIs
• Microservices
• Batch processing
• ETL pipelines
• Event-driven systems
• Cloud-native applications

Advantages

• No ORM overhead
• Reusable repositories
• Modular architecture
• High performance
• Strong TypeScript support
• Easy to test
• Clean separation of responsibilities

Related Packages

• sql-core — Common SQL abstractions, repositories, CRUD, and SQL builders.
• query-mappers — Object mapping between SQL rows and TypeScript models.
• mysql2-core — MySQL adapter built on top of mysql2.

License

MIT