Wednesday, September 13, 2023
HomeJavascriptAPI with NestJS #119. Sort-safe SQL queries with Kysely and PostgreSQL

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

Object-Relational Mapping (ORM) libraries resembling Prisma and TypeORM will help us produce code quicker by avoiding writing SQL queries. They’ve a smaller studying curve as a result of we don’t have to be taught a brand new language and dive deep into understanding how the database works. Sadly, ORM libraries typically generate SQL queries which might be removed from optimum and take away management from us. Nevertheless, writing uncooked SQL just isn’t an ideal answer both as a result of we don’t have the benefit of kind security that TypeScript offers when working with ORM libraries.

Kysely is a question builder that gives a set of capabilities that create SQL queries. We nonetheless want to know SQL, however Kysely integrates tightly with TypeScript and ensures we don’t make any typos alongside the way in which. On this article, we begin a brand new NestJS venture and learn to combine Kysely with PostgreSQL.

Try this repository if you wish to see the total code from this text.

Defining the database

On this sequence, we depend on Docker Compose to create an occasion of the PostgreSQL database.


To supply our Docker container with the required credentials, we should create the


We have now to offer the same set of variables for our NestJS software too.


We must always validate the atmosphere variables to forestall the NestJS software from beginning if they’re invalid.


To run our PostgreSQL database, we have to have Docker put in and run the

Managing migrations

Step one is to create a SQL desk we are able to work with. Whereas Kysely offers migration functionalities, it doesn’t ship with a command-line interface. Let’s comply with the official documentation and create a perform that runs our migrations.


Above, we use the
library to ensure the
has all the atmosphere variables loaded and able to use.

In our
perform, we level to the
listing that ought to comprise our migrations. Let’s use it to create our first desk.


Kysely runs our migrations within the alphabetical order of the filenames. An excellent means of approaching that’s to prefix the migration recordsdata with the creation date.

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


Now, at any time when we run
, Kysely executes all our migrations and brings the database to the newest model.

Utilizing Kysely with NestJS

First, we have to let Kysely know the construction of our database. Let’s begin with defining the desk we created earlier than utilizing the migration.


We will now use the above interface with the


Normally, we must always solely create one occasion of the above class. To realize that with NestJS and Dependency Injection, let’s create a dynamic module that exports an occasion of the
class we outlined.

If you wish to know extra about dynamic modules, take a look at API with NestJS #70. Defining dynamic modules


Above we use
as a result of we wish the
to be world.

When our module is imported, we wish a specific set of choices to be offered.


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


The final step is to import our module and supply the configuration utilizing the


The repository sample and fashions

We must always maintain the logic of interacting with a specific desk from the database in a single place. A quite common means of doing that’s utilizing the repository sample.


Due to Kysely being type-safe, we wouldn’t be capable to name the
methodology with an incorrect title of the desk.

When executing the above question, we get the information within the format that was saved into the database:

Nevertheless, we regularly wish to remodel the uncooked information we get from the database. A typical means of doing that’s to outline fashions.


We will now use the above mannequin in our repository.


Due to doing the above, the objects returned by the
methodology at the moment are cases of the

Parametrized queries

We fairly often want to make use of the enter offered by the consumer as part of our SQL question. When writing SQL queries manually and never using parameterized queries, we open ourselves to SQL injections.

Since we merely concatenate a string, we enable for the next SQL injection:

Operating the above question would destroy our desk.

Fortuitously, Kysely makes use of parametrized queries. Let’s create a way that retrieves an article with a specific id.


After we add easy logging performance to the occasion of our database, we are able to see for ourselves that Kysely is utilizing parametrized queries.


Question: choose * from “articles” the place “id” = $1
Parameters: [ ‘1’ ]

Since we ship the parameters individually from the question, the database is aware of to deal with them as parameters to keep away from potential SQL injections.

Including rows to the desk

So as to add rows to our desk, we first have to outline the category that holds the information despatched by the consumer.


If you wish to know extra about validating information, take a look at API with NestJS #4. Error dealing with and information validation

We will now add new strategies to our repository.


Utilizing providers

It is extremely widespread to have a layer of providers that use our repositories. For the reason that logic in our software could be very easy to this point, our
largely calls the strategies from the repository.


Utilizing the service in our controller

The final step is to make use of the above service in our controller.



On this article, we’ve realized easy methods to use the Kysely question builder with NestJS. It included managing migrations and making a dynamic module to share the database configuration utilizing dependency injection. When engaged on that, we created a totally practical software that lists, creates, and modifies articles.

There may be nonetheless much more to study utilizing Kysely with NestJS, so keep tuned!

Sequence Navigation

<< API with NestJS #118. Importing and streaming movies


Most Popular

Recent Comments