@betsys-nestjs/postgres
v6.2.0
Published
This library is responsible for handling PosgreSQL connections and queries.
Downloads
7
Maintainers
Keywords
Readme
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 ofstartTimerExecutionTime
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,
},
})