How to Execute Raw PostgreSQL Queries in NestJS
The official NestJS documentation has many examples showing how to use an ORM framework such as TypeORM to integrate with a database. However, what if you don’t want to use an ORM, but would rather execute raw queries on the database without the overhead of an ORM?
This tutorial will give you an example of how this could be done. In this tutorial, we will write a module that will responsible for executing SQL queries on a PostgreSQL database.
Required Dependencies
For this to work, we’ll need to install a library that will allow us to interface with a PostgreSQL database. node-postgres is the de facto standard library for this, so let’s install it:
npm install pgSince NestJS uses TypeScript, we can also install the TypeScript definitions for node-postgres:
npm install @types/pg --save-devConfiguration File
Database configuration is usually stored in an outside configuration file, and you will often have different files for different environments. For the purpose of this tutorial, we’ll store our database settings in a simple .env file:
Reading this file shouldn’t be a problem if we use NestJS’s built-in ConfigModule. We’ll import this module in our AppModule and make it global:
Making modules global is not common unless you really plan to use them in most of your application modules. Reading configuration is a common task, so it makes sense to make ConfigModule global. This way, we don’t have to import it explicitly in every application module. The other configuration property, envFilePath , is the path to our .env file.
With ConfigModule configured and imported, we can now use ConfigService throughout the application to retrieve values from our configuration file. For example, configService.get('POSTGRES_USER') will return my-user in our case.
Database Module
Now, let’s create a dedicated module that will be a container for our database integration functionality.
The purpose of this module is to connect to the database and expose a service for executing queries on the database.
The recommended way of connecting to the database from a NestJS application is through database pools. A database pool is simply a node-postgres object that is responsible for managing connections to the PostgreSQL database.
Since we will be injecting this pool into our database service, we can create it using a factory provider. Factory providers allow us to use dependency injection to inject other providers, such as the ConfigService .
In the above snippet, we defined our provider in the module’s providers array, named it DATABASE_POOL, and told NestJS to inject an instance of ConfigService to our factory so we can initialize the pool object with the configuration from our .env file.
Now let’s create a database service that will be used to execute queries. The executeQuery method is blank for now, and we are injecting our pool object named DATABASE_POOL to the service. We also created an instance of the NestJS logger so we can log database queries as they happen.
Now we need to export this DatabaseService from our DatabaseModule so other application modules can use it:
We can now finish the executeQuerymethod which will invoke pool.query() to execute queries on the database:
The method is simple: it accepts query text, optional parameters, and returns the result rowset.
Finally: Clean Up
The database pool we initialized in our DatabaseModule will not make any connections until the first query is executed. When this happens, a new connection client is created and is kept in the pool while the pool is alive. If the application is shut down, this pool is shut down abruptly without any clean-up.
To fix this, we can use the NestJS lifecycle hooks and react to termination signal. Listening to termination signals does consume a bit more resources, so this is an optional feature in NestJS. It can be enabled using the enableShutdownHooks() method of the application instance.
With shutdown hooks enabled, we can write the actual shutdown hook in our DatabaseModule .
We use a module reference to retrieve the instance of our pool object from the module context, then call its end() method to disconnect all active clients. Since this method returns a promise, NestJS will wait in the shutdown sequence until the promise is resolved or rejected.
Wrapping Up
Thanks for reading. I hope you found this tutorial useful. Directly interfacing with the database may not be so common use case in NestJS, but sometimes it can be useful when you want more control of the queries executed. If you run into any issues, please let me know in the comments.
