How can I configure postgreSQL in the Nestjs way?-CodePudding

So I'm in the process of learning NestJs ways. I have a small NestJs backend with only a few routes. Some of them call postgreSQL. I don't want to use any ORM and directly use pg package. So my next step is learning how to use ConfigService. I have successfully used it to configure all env vars in the backend, but I'm struggling to use it in a small file I use to configure postgreSQL. This is the configuration file (pgconnect.ts):

import { Pool } from 'pg';
import configJson from './config/database.json';
import dotenv from 'dotenv';
dotenv.config();

const config = configJson[process.env.NODE_ENV];

const poolConfig = {
  user: config.username,
  host: config.host,
  database: config.database,
  password: config.password,
  port: config.port,
  max: config.maxClients
};

export const pool = new Pool(poolConfig)

database.json is a json file where I have all connect values divided by environment. Then in service classes I just:

import { Injectable } from '@nestjs/common';
import { Response } from 'express';
import { pool } from 'src/database/pgconnect';

@Injectable()
export class MyService {

    getDocumentByName(res: Response, name: string) {
        pool.query(
               <query, error treatment, etc>
            });
    }

    <...> more queries for insert, update, other selects, etc
}

So how could I use ConfigService inside my configuration file ? I already tried to instance class like this:

let configService = new ConfigService();

and what I would like to do is:

const config = configJson[configService.get<string>('NODE_ENV')];

but it didn't work. You have to pass .env file path to new ConfigService(). And I need to use NODE_ENV var to get it, because it depends on environment. To get NODE_ENV without using ConfigService I would have to use dotenv, but if I'm going to use dotenv I don't need ConfigService in the first place.

So then I tried to create a class:

import { Injectable, HttpException, HttpStatus } from '@nestjs/common';
import { ConfigService } from '@nestjs/config'
const { Pool } = require('pg');

import configJson from './config/database.json';

@Injectable()
export class PgPool {
    constructor(private configService: ConfigService) { };

    config = configJson[this.configService.get<string>('NODE_ENV')];

    poolConfig = {
        user: this.config.username,
        host: this.config.host,
        database: this.config.database,
        password: this.config.password,
        port: this.config.port,
        max: this.config.maxClients
    };
    
    static pool = new Pool(this.poolConfig);
}

export const PgPool.pool;

But this doesn't work in several ways. If I use non-static members, I can´t export pool member which is the only thing I need. If I use static members one can't access the other or at least I'm not understanding how one access the other.

So, the questions are: How do I use ConfigService outside of a class or how can I change pgconnect.ts file to do it's job ? If it's through a class the best would be to export only pool method.

Also if you think there's a better way to configure postgreSQL I would be glad to hear.

CodePudding user response：

What I would do, if you're going to be using the pg package directly, is create a PgModule that exposes the Pool you create as a provider that can be injected. Then you can also create a provider for the options specifically for ease of swapping in test. Something like this:

@Module({
  imports: [ConfigModule],
  providers: [
    {
      provide: 'PG_OPTIONS',
      inject: [ConfigService],
      useFactory: (config) => ({
        host: config.get('DB_HOST'),
        port:  config.get('DB_PORT'),
        ...etc
      }),
    },
    {
      provide: 'PG_POOL',
      inject: ['PG_OPTIONS'],
      useFactory: (options) => new Pool(options),
    }
  ],
  exports: ['PG_POOL'],
})
export class PgModule {}

Now, when you need to use the Pool in another service you add PgModule to that service's module's imports and you add @Inject('PG_POOL') private readonly pg: Pool to the service's constructor.

If you want to see an overly engineered solution, you can take a look at my old implementation here

CodePudding user response：

I normally have my own pg module handling the pool with either an additional config file (json) or via processing a .env file:

node-pg-sql.js:

/* INFO: Require json config file */
const fileNameConfigPGSQL = require('./config/pgconfig.json');

/* INFO: Require file operations package */
const { Pool } = require('pg');

const pool = new Pool(fileNameConfigPGSQL);

module.exports = {
  query: (text, params, callback) => {
    const start = Date.now()
    return pool.query(text, params, (err, res) => {
      const duration = Date.now() - start
  //    console.log('executed query', { text, duration, rows: res.rowCount })
      callback(err, res)
    })
  },
  getClient: (callback) => {
    pool.connect((err, client, done) => {
      const query = client.query.bind(client)

      // monkey patch for the query method to track last queries
      client.query = () => {
        client.lastQuery = arguments
        client.query.apply(client, arguments)
      }

      // Timeout of 5 secs,then last query is logged
      const timeout = setTimeout(() => {
     //   console.error('A client has been checked out for more than 5 seconds!')
     //   console.error(`The last executed query on this client was: ${client.lastQuery}`)
      }, 5000)

      const release = (err) => {
        // calling 'done'-method to return client to pool
        done(err)

        // cleat timeout
        clearTimeout(timeout)

        // reset query-methode before the Monkey Patch
        client.query = query
      }

      callback(err, client, done)
    })
  }
}

pgconfig.json:

{
    "user":"postgres",
    "host":"localhost",
    "database":"mydb",
    "password":"mypwd",
    "port":"5432",
    "ssl":true
}

If you prefer processing a .env file:

NODE_ENV=develepment
NODE_PORT=45500
HOST_POSTGRESQL='localhost'
PORT_POSTGRESQL='5432'
DB_POSTGRESQL='mydb'
USER_POSTGRESQL='postgres'
PWD_POSTGRESQL='mypwd'

and process the file and export vars:

var path = require('path');


const dotenvAbsolutePath = path.join(__dirname, '.env');

/* INFO: Require dotenv package for retieving and setting env-vars at runtime via absolute path due to pkg */

  const dotenv = require('dotenv').config({
    path: dotenvAbsolutePath
  });
  if (dotenv.error) {
    console.log(`ERROR WHILE READING ENV-VARS:${dotenv.error}`);
    throw dotenv.error;
  }

module.exports = {
  nodeEnv: process.env.NODE_ENV,
  nodePort: process.env.NODE_PORT,
  hostPostgresql: process.env.HOST_POSTGRESQL,
  portPostgresql: process.env.PORT_POSTGRESQL,
  dbPostgresql: process.env.DB_POSTGRESQL,
  userPostgresql: process.env.USER_POSTGRESQL,
  pwdPostgresql: process.env.PWD_POSTGRESQL,
};