@betsys-nestjs/postgres

Postgres library

This package is an implementation of node-postgres library compatible with NestJS modules. It contains connection pooling, cursor queries logic, customizable logging and monitoring.

Dependencies

Package	Version
@types/pg	^8.0.0
pg	^8.0.0
pg-cursor	^2.0.0
@nestjs/common	^10.0.0
@nestjs/terminus	^10.0.0
reflect-metadata	^0.1.13
@nestjs/core	^10.0.0
rxjs	^7.8.0

Usage

To create a new PG pool of connection simply add this to your module imports and provide config as the first parameter:

import { Module } from '@nestjs/common';
import { PostgresqlModule } from '@betsys-nestjs/postgres'

@Module({
    imports: [
        PostgresqlModule.forFeature({
            uri: 'postgresql://postgres:pass@host:port/database',
            poolSize: 10,
            batchSize: 10,
        }),
    ],
})
class InfrastructureModule {}

And to use the connection just import PostgresConnection provider using the InjectConnectionProvider decorator:

class MyPostgreSQLOperation {
    constructor(
        @InjectConnectionProvider()
        private readonly postgresConnectionService: PostgresConnection,
    ) {}

    async execute(): Promise<Result> {
        const result = this.postgresConnectionService.executeWithPoolConnection((connection) => {
            return connection.query<Result>('SELECT * FROM get_my_data()');
        });

        if (result.rows.length === 0) {
            throw new NotFoundException(`Data not found.`);
        }

        return result.rows[0];
    }
}

To use cursor to load data in bulks (to lower the memory usage when working with big query results), you can import PostgresCursorUtils that returns AsyncGenerator:

@Injectable()
export class LoadDataCursorOperation {
    constructor(
        @InjectCursorUtils()
        private readonly postgresCursorUtils: PostgresCursorUtils,
    ) {
    }

    public getCursor(ticketIds: number[]): AsyncGenerator<ResultRow[]> {
        return this.postgresCursorUtils.queryCursor<ResultRow>(
            'SELECT * FROM get_big_data()',
            'LoadDataCursorOperation', // this is a key for monitoring purposes
            [], // and here you can pass query params  
        );
    }
}

Multiple connection support

To utilize multiple DB connections in the same module you need to name them by defining dbHandle value:

@Module({
    imports: [
        PostgresqlModule.forFeature({
            uri: 'postgresql://betsys@postgres/live',
            poolSize: 10,
            batchSize: 10,
            dbHandle: 'DB1'
        }),
        PostgresqlModule.forFeature({
            uri: 'postgresql://betsys@postgres/live',
            poolSize: 10,
            batchSize: 10,
            dbHandle: 'DB2'
        }),
    ],
})
class InfrastructureModule {}

And to inject particular connection just add the name to the annotation:

class MyPostgreSQLOperation {
    constructor(
        @InjectConnectionProvider('DB1')
        private readonly postgresConnectionService: PostgresConnection,
        @InjectCursorUtils('DB1')
        private readonly postgresCursorUtils: PostgresCursorUtils,
    ) {}
}

Monitoring and Logger support

The library is ready to work with monitoring and logger. To enable it you need to implement your own monitoring and logger service based on abstraction provided by this library.

Monitoring

There are two different monitoring parts that can be independently set in the config:

• Time monitoring - used for monitoring of query time, your provided service must implement PostgresTimeMonitoringInterface. Implementation of startTimerExecutionTime starts your custom timer returning a function which stops the timer.

• Connection monitoring - used for observing count of connections to database via this library. To use this monitoring type, you must implement PostgresConnectionMonitoringInterface.

Example of connection monitoring using @betsys-nestjs/monitoring:

import {
    AbstractMonitoringService,
    Gauge,
    InjectMonitoringConfig,
    InjectMonitoringRegistry, 
    MonitoringConfig,
    Registry,
} from '@betsys-nestjs/monitoring';
import { ConnectionMonitoringService } from '@betsys-nestjs/postgres';
import { Injectable } from '@nestjs/common';

@Injectable()
export class PostgresConnectionMonitoring extends AbstractMonitoringService implements PostgresConnectionMonitoringInterface {
    private readonly SYSTEM_LABEL = 'postgres';

    private readonly connectionGauge: Gauge<string>;

    constructor(
        @InjectMonitoringRegistry() protected readonly registry: Registry,
        @InjectMonitoringConfig() private readonly config: MonitoringConfig,
    ) {
        super(registry);
        this.connectionGauge = this.createMetric(Gauge, {
            name: this.config.getMetricsName('open_connection'),
            help: 'count of currently open connections to postgres DB',
            labelNames: ['system', 'handle'],
            registers: [registry],
        });
    }

    connectionOpened(handle: string): void {
        this.connectionGauge.inc({ system: this.SYSTEM_LABEL, handle }, 1);
    }

    connectionClosed(handle: string): void {
        this.connectionGauge.dec({ system: this.SYSTEM_LABEL, handle }, 1);
    }
}

connectionOpened is called when client is connected to pool and connectionClosed is called when connection is closed.

Logger

Similar to monitoring you can simply implement custom service following PostgresLoggerInterface.

Example using @betsys-nestjs/logger:

import { Injectable } from '@nestjs/common';
import { Logger as NestLogger } from '@betsys-nestjs/logger';
import { Logger } from '@betsys-nestjs/postgres';

@Injectable()
export class PostgresLogger implements PostgresLoggerInterface {
    constructor(private readonly logger: NestLogger) {}

    debug(message: string): void {
        // eslint-disable-next-line no-console
        this.logger.debug(message);
    }

    setContext(context: string): void {
        this.logger.setContext(context);
    }
}

In setContext you can define some context for further logging. debug method is responsible for logging itself so you can either use some console.log or any logger based on your preference like winston etc.

To start using Logger or Monitoring service, you simply insert class references to forFeature method of PostgresModule like this:

PostgresqlModule.forFeature({
    // other config values, 
    logger: PostgresTestLogger,
    monitoring: {
        connection: TestConnectionMonitoringService,
        time: TestTimeMonitoringService,
    },
})