API with NestJS #119. Type-safe SQL queries with Kysely and PostgreSQL

NestJS SQL

This entry is part 119 of 158 in the API with NestJS

Object-Relational Mapping (ORM) libraries such as Prisma and TypeORM can help us produce code faster by avoiding writing SQL queries. They have a smaller learning curve because we don’t need to learn a new language and dive deep into understanding how the database works. Unfortunately, ORM libraries often generate SQL queries that are far from optimal and take away control from us. However, writing raw SQL is not a perfect solution either because we don’t have the advantage of type safety that TypeScript provides when working with ORM libraries.

Kysely is a query builder that provides a set of functions that create SQL queries. We still need to understand SQL, but Kysely integrates tightly with TypeScript and ensures we don’t make any typos along the way. In this article, we start a new NestJS project and learn how to integrate Kysely with PostgreSQL.

Check out this repository if you want to see the full code from this article.

Defining the database

In this series, we rely on Docker Compose to create an instance of the PostgreSQL database.

docker-compose.yml

To provide our Docker container with the necessary credentials, we must create the file.

docker.env

We have to provide a similar set of variables for our NestJS application too.

.env

We should validate the environment variables to prevent the NestJS application from starting if they are invalid.

app.module.ts

To run our PostgreSQL database, we need to have Docker installed and run the command.

Managing migrations

The first step is to create a SQL table we can work with. While Kysely provides migration functionalities, it does not ship with a command-line interface. Let’s follow the official documentation and create a function that runs our migrations.

runMigrations.ts

Above, we use the library to make sure the has all of the environment variables loaded and ready to use.

In our function, we point to the directory that should contain our migrations. Let’s use it to create our first table.

migrations/20230806213313_add_articles_table.ts

Kysely runs our migrations in the alphabetical order of the filenames. A very good way of approaching that is to prefix the migration files with the creation date.

The last step is to create a script in our to run our migrations.

package.json

Now, whenever we run , Kysely executes all our migrations and brings the database to the latest version.

Using Kysely with NestJS

First, we need to let Kysely know the structure of our database. Let’s start with defining the table we created before using the migration.

articles/articlesTable.ts

We can now use the above interface with the class.

database/database.ts

Usually, we should only create one instance of the above class. To achieve that with NestJS and Dependency Injection, let’s create a dynamic module that exports an instance of the class we defined.

If you want to know more about dynamic modules, check out API with NestJS #70. Defining dynamic modules

database/database.module-definition.ts

Above we use because we want the to be global.

When our module is imported, we want a particular set of options to be provided.

database/databaseOptions.ts

We now can define the  that creates a connection pool and exports it.

database/database.module.ts

The last step is to import our module and provide the configuration using the .

app.module.ts

The repository pattern and models

We should keep the logic of interacting with a particular table from the database in a single place. A very common way of doing that is using the repository pattern.

articles/articles.repository.ts

Thanks to Kysely being type-safe, we wouldn’t be able to call the method with an incorrect name of the table.

When executing the above query, we get the data in the format that was saved into the database:

However, we often want to transform the raw data we get from the database. A common way of doing that is to define models.

articles/article.model.ts

We can now use the above model in our repository.

articles/articles.repository.ts

Thanks to doing the above, the objects returned by the method are now instances of the class.

Parametrized queries

We very often need to use the input provided by the user as a part of our SQL query. When writing SQL queries manually and not utilizing parameterized queries, we open ourselves to SQL injections.

Since we simply concatenate a string, we allow for the following SQL injection:

Running the above query would destroy our table.

Fortunately, Kysely uses parametrized queries. Let’s create a method that retrieves an article with a particular id.

articles/articles.repository.ts

When we add simple logging functionality to the instance of our database, we can see for ourselves that Kysely is using parametrized queries.

database/database.module.ts

Query: select * from “articles” where “id” = $1
Parameters: [ ‘1’ ]

Since we send the parameters separately from the query, the database knows to treat them as parameters to avoid potential SQL injections.

Adding rows to the table

To add rows to our table, we first need to define the class that holds the data sent by the user.

articles/dto/article.dto.ts

If you want to know more about validating data, check out API with NestJS #4. Error handling and data validation

We can now add new methods to our repository.

articles/dto/articles.repository.ts

Using services

It is very common to have a layer of services that use our repositories. Since the logic in our application is very simple so far, our mostly calls the methods from the repository.

articles/dto/articles.service.ts

Using the service in our controller

The last step is to use the above service in our controller.

articles/dto/articles.controller.ts

Summary

In this article, we’ve learned how to use the Kysely query builder with NestJS. It included managing migrations and creating a dynamic module to share the database configuration using dependency injection. When working on that, we created a fully functional application that lists, creates, and modifies articles.

There is still a lot more to learn about using Kysely with NestJS, so stay tuned!

Series Navigation<< API with NestJS #118. Uploading and streaming videosAPI with NestJS #120. One-to-one relationships with the Kysely query builder >>
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Michael
Michael
9 months ago

Do you have working code sample for this article? Cannot get it to work. After Database module initialisation connection to Postgres not happened. Database instance injected into service is undefined.