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

August 7, 2023

This entry is part 119 of 121 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

version: "3"

services:

postgres:

container_name: nestjs-kysely-postgres

image: postgres:15.3

ports:

- "5432:5432"

networks:

- postgres

volumes:

- /data/postgres:/data/postgres

env_file:

- docker.env

pgadmin:

container_name: nestjs-kysely-pgadmin

image: dpage/pgadmin4:7.5

networks:

- postgres

ports:

- "8080:80"

volumes:

- /data/pgadmin:/root/.pgadmin

env_file:

- docker.env

networks:

postgres:

driver: bridge

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

docker.env

POSTGRES_USER=admin

POSTGRES_PASSWORD=admin

POSTGRES_DB=nestjs

PGADMIN_DEFAULT_EMAIL=admin@admin.com

PGADMIN_DEFAULT_PASSWORD=admin

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

.env

POSTGRES_HOST=localhost

POSTGRES_PORT=5432

POSTGRES_USER=admin

POSTGRES_PASSWORD=admin

POSTGRES_DB=nestjs

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

app.module.ts

import { Module } from '@nestjs/common';

import { PostsModule } from './posts/posts.module';

import { ConfigModule } from '@nestjs/config';

import * as Joi from 'joi';

@Module({

imports: [

PostsModule,

ConfigModule.forRoot({

validationSchema: Joi.object({

POSTGRES_HOST: Joi.string().required(),

POSTGRES_PORT: Joi.number().required(),

POSTGRES_USER: Joi.string().required(),

POSTGRES_PASSWORD: Joi.string().required(),

POSTGRES_DB: Joi.string().required(),

}),

})

export class AppModule {}

To run our PostgreSQL database, we need to have Docker installed and run the docker compose up 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.

1	npm install dotenv

runMigrations.ts

import * as path from 'path';

import { Pool } from 'pg';

import { promises as fs } from 'fs';

import {

Kysely,

Migrator,

PostgresDialect,

FileMigrationProvider,

} from 'kysely';

import { config } from 'dotenv';

import { ConfigService } from '@nestjs/config';

config();

const configService = new ConfigService();

async function migrateToLatest() {

const database = new Kysely({

dialect: new PostgresDialect({

pool: new Pool({

host: configService.get('POSTGRES_HOST'),

port: configService.get('POSTGRES_PORT'),

user: configService.get('POSTGRES_USER'),

password: configService.get('POSTGRES_PASSWORD'),

database: configService.get('POSTGRES_DB'),

}),

});

const migrator = new Migrator({

db: database,

provider: new FileMigrationProvider({

fs,

path,

migrationFolder: path.join(__dirname, 'migrations'),

}),

});

const { error, results } = await migrator.migrateToLatest();

results?.forEach((migrationResult) => {

if (migrationResult.status === 'Success') {

console.log(

`migration "${migrationResult.migrationName}" was executed successfully`,

);

} else if (migrationResult.status === 'Error') {

console.error(

`failed to execute migration "${migrationResult.migrationName}"`,

);

}

});

if (error) {

console.error('Failed to migrate');

console.error(error);

process.exit(1);

}

await database.destroy();

}

migrateToLatest();

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

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

migrations/20230806213313_add_articles_table.ts

import { Kysely } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.createTable('articles')

.addColumn('id', 'serial', (column) => column.primaryKey())

.addColumn('title', 'text', (column) => column.notNull())

.addColumn('article_content', 'text', (column) => column.notNull())

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.dropTable('articles');

}

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 package.json to run our migrations.

package.json

{

"name": "nestjs-kysely",

"scripts": {

"migrations": "ts-node ./src/runMigrations.ts",

...

}

Now, whenever we run npm run migrations, 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

import { Generated } from 'kysely';

export interface ArticlesTable {

id: Generated<number>;

title: string;

article_content: string;

}

We can now use the above interface with the Kysely class.

database/database.ts

import { ArticlesTable } from '../articles/articlesTable';

import { Kysely } from 'kysely';

interface Tables {

articles: ArticlesTable;

}

export class Database extends Kysely<Tables> {}

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 Database 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

import { ConfigurableModuleBuilder } from '@nestjs/common';

import { DatabaseOptions } from './databaseOptions';

export const {

ConfigurableModuleClass: ConfigurableDatabaseModule,

MODULE_OPTIONS_TOKEN: DATABASE_OPTIONS,

} = new ConfigurableModuleBuilder<DatabaseOptions>()

.setClassMethodName('forRoot')

.build();

Above we use forRoot because we want the DatabaseModule to be global.

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

database/databaseOptions.ts

export interface DatabaseOptions {

host: string;

port: number;

user: string;

password: string;

database: string;

}

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

database/database.module.ts

import { Global, Module } from '@nestjs/common';

import {

ConfigurableDatabaseModule,

DATABASE_OPTIONS,

} from './database.module-definition';

import { DatabaseOptions } from './databaseOptions';

import { Pool } from 'pg';

import { PostgresDialect } from 'kysely';

import { Database } from './database';

@Global()

@Module({

exports: [Database],

providers: [

{

provide: Database,

inject: [DATABASE_OPTIONS],

useFactory: (databaseOptions: DatabaseOptions) => {

const dialect = new PostgresDialect({

pool: new Pool({

host: databaseOptions.host,

port: databaseOptions.port,

user: databaseOptions.user,

password: databaseOptions.password,

database: databaseOptions.database,

}),

});

return new Database({

dialect,

});

})

export class DatabaseModule extends ConfigurableDatabaseModule {}

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

app.module.ts

import { Module } from '@nestjs/common';

import { ConfigModule, ConfigService } from '@nestjs/config';

import * as Joi from 'joi';

import { DatabaseModule } from './database/database.module';

@Module({

imports: [

DatabaseModule.forRootAsync({

imports: [ConfigModule],

inject: [ConfigService],

useFactory: (configService: ConfigService) => ({

host: configService.get('POSTGRES_HOST'),

port: configService.get('POSTGRES_PORT'),

user: configService.get('POSTGRES_USER'),

password: configService.get('POSTGRES_PASSWORD'),

database: configService.get('POSTGRES_DB'),

}),

ConfigModule.forRoot({

validationSchema: Joi.object({

POSTGRES_HOST: Joi.string().required(),

POSTGRES_PORT: Joi.number().required(),

POSTGRES_USER: Joi.string().required(),

POSTGRES_PASSWORD: Joi.string().required(),

POSTGRES_DB: Joi.string().required(),

}),

})

export class AppModule {}

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

import { Database } from '../database/database';

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async getAll() {

return this.database.selectFrom('articles').selectAll().execute();

}

Thanks to Kysely being type-safe, we wouldn’t be able to call the selectFrom 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:

[

{

id: 1,

title: 'Hello world',

article_content: 'Lorem ipsum'

}

]

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

interface ArticleModelData {

id: number;

title: string;

article_content: string;

}

export class Article {

id: number;

title: string;

content: string;

constructor({ id, title, article_content }: ArticleModelData) {

this.id = id;

this.title = title;

this.content = article_content;

}

We can now use the above model in our repository.

articles/articles.repository.ts

import { Database } from '../database/database';

import { Article } from './article.model';

import { Injectable } from '@nestjs/common';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

async getAll() {

const databaseResponse = await this.database

.selectFrom('articles')

.selectAll()

.execute();

return databaseResponse.map((articleData) => new Article(articleData));

}

Thanks to doing the above, the objects returned by the getAll method are now instances of the Article 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.

1	const query = `SELECT * FROM articles WHERE id=${id}`;

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

1 2	const id = '1; DROP TABLE articles; const query = `SELECT * FROM articles WHERE id=${id}`;

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

import { Database } from '../database/database';

import { Article } from './article.model';

import { Injectable } from '@nestjs/common';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

// ...

async getById(id: number) {

const databaseResponse = await this.database

.selectFrom('articles')

.where('id', '=', id)

.selectAll()

.executeTakeFirst();

if (databaseResponse) {

return new Article(databaseResponse);

}

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

import { Global, Module } from '@nestjs/common';

import {

ConfigurableDatabaseModule,

DATABASE_OPTIONS,

} from './database.module-definition';

import { DatabaseOptions } from './databaseOptions';

import { Pool } from 'pg';

import { PostgresDialect } from 'kysely';

import { Database } from './database';

@Global()

@Module({

exports: [Database],

providers: [

{

provide: Database,

inject: [DATABASE_OPTIONS],

useFactory: (databaseOptions: DatabaseOptions) => {

const dialect = new PostgresDialect({

pool: new Pool({

host: databaseOptions.host,

port: databaseOptions.port,

user: databaseOptions.user,

password: databaseOptions.password,

database: databaseOptions.database,

}),

});

return new Database({

dialect,

log(event) {

if (event.level === 'query') {

console.log('Query:', event.query.sql);

console.log('Parameters:', event.query.parameters);

}

});

})

export class DatabaseModule extends ConfigurableDatabaseModule {}

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

import { IsString, IsNotEmpty } from 'class-validator';

class ArticleDto {

@IsString()

@IsNotEmpty()

title: string;

@IsString()

@IsNotEmpty()

content: string;

}

export default ArticleDto;

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

import { Database } from '../database/database';

import { Article } from './article.model';

import { Injectable } from '@nestjs/common';

import ArticleDto from './dto/article.dto';

@Injectable()

export class ArticlesRepository {

constructor(private readonly database: Database) {}

// ...

async create(data: ArticleDto) {

const databaseResponse = await this.database

.insertInto('articles')

.values({

title: data.title,

article_content: data.content,

})

.returningAll()

.executeTakeFirst();

return new Article(databaseResponse);

}

async update(id: number, data: ArticleDto) {

const databaseResponse = await this.database

.updateTable('articles')

.set({

title: data.title,

article_content: data.content,

})

.where('id', '=', id)

.returningAll()

.executeTakeFirst();

if (databaseResponse) {

return new Article(databaseResponse);

}

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 ArticlesService mostly calls the methods from the repository.

articles/dto/articles.service.ts

import { Article } from './article.model';

import { Injectable, NotFoundException } from '@nestjs/common';

import ArticleDto from './dto/article.dto';

import { ArticlesRepository } from './articles.repository';

@Injectable()

export class ArticlesService {

constructor(private readonly articlesRepository: ArticlesRepository) {}

getAll() {

return this.articlesRepository.getAll();

}

async getById(id: number) {

const article = await this.articlesRepository.getById(id);

if (!article) {

throw new NotFoundException();

}

return article;

}

async create(data: ArticleDto) {

return this.articlesRepository.create(data);

}

async update(id: number, data: ArticleDto) {

const article = await this.articlesRepository.update(id, data);

if (!article) {

throw new NotFoundException();

}

return article;

}

Using the service in our controller

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

articles/dto/articles.controller.ts

import { Controller, Get, Param, Post, Body, Put } from '@nestjs/common';

import FindOneParams from '../utils/findOneParams';

import ArticleDto from './dto/article.dto';

import { ArticlesService } from './articles.service';

@Controller('articles')

export class ArticlesController {

constructor(private readonly articlesService: ArticlesService) {}

@Get()

getAll() {

return this.articlesService.getAll();

}

@Get(':id')

getById(@Param() { id }: FindOneParams) {

return this.articlesService.getById(id);

}

@Post()

create(@Body() data: ArticleDto) {

return this.articlesService.create(data);

}

@Put(':id')

update(@Param() { id }: FindOneParams, @Body() data: ArticleDto) {

return this.articlesService.update(id, data);

}

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 >>