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.
1version: "3"
2services:
3 postgres:
4 container_name: nestjs-kysely-postgres
5 image: postgres:15.3
6 ports:
7 - "5432:5432"
8 networks:
9 - postgres
10 volumes:
11 - /data/postgres:/data/postgres
12 env_file:
13 - docker.env
14
15 pgadmin:
16 container_name: nestjs-kysely-pgadmin
17 image: dpage/pgadmin4:7.5
18 networks:
19 - postgres
20 ports:
21 - "8080:80"
22 volumes:
23 - /data/pgadmin:/root/.pgadmin
24 env_file:
25 - docker.env
26
27networks:
28 postgres:
29 driver: bridgeTo provide our Docker container with the necessary credentials, we must create the docker.env file.
1POSTGRES_USER=admin
2POSTGRES_PASSWORD=admin
3POSTGRES_DB=nestjs
4PGADMIN_DEFAULT_EMAIL=admin@admin.com
5PGADMIN_DEFAULT_PASSWORD=adminWe have to provide a similar set of variables for our NestJS application too.
1POSTGRES_HOST=localhost
2POSTGRES_PORT=5432
3POSTGRES_USER=admin
4POSTGRES_PASSWORD=admin
5POSTGRES_DB=nestjsWe should validate the environment variables to prevent the NestJS application from starting if they are invalid.
1import { Module } from '@nestjs/common';
2import { PostsModule } from './posts/posts.module';
3import { ConfigModule } from '@nestjs/config';
4import * as Joi from 'joi';
5
6@Module({
7 imports: [
8 PostsModule,
9 ConfigModule.forRoot({
10 validationSchema: Joi.object({
11 POSTGRES_HOST: Joi.string().required(),
12 POSTGRES_PORT: Joi.number().required(),
13 POSTGRES_USER: Joi.string().required(),
14 POSTGRES_PASSWORD: Joi.string().required(),
15 POSTGRES_DB: Joi.string().required(),
16 }),
17 }),
18 ],
19})
20export 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.
1npm install dotenv1import * as path from 'path';
2import { Pool } from 'pg';
3import { promises as fs } from 'fs';
4import {
5 Kysely,
6 Migrator,
7 PostgresDialect,
8 FileMigrationProvider,
9} from 'kysely';
10import { config } from 'dotenv';
11import { ConfigService } from '@nestjs/config';
12
13config();
14
15const configService = new ConfigService();
16
17async function migrateToLatest() {
18 const database = new Kysely({
19 dialect: new PostgresDialect({
20 pool: new Pool({
21 host: configService.get('POSTGRES_HOST'),
22 port: configService.get('POSTGRES_PORT'),
23 user: configService.get('POSTGRES_USER'),
24 password: configService.get('POSTGRES_PASSWORD'),
25 database: configService.get('POSTGRES_DB'),
26 }),
27 }),
28 });
29
30 const migrator = new Migrator({
31 db: database,
32 provider: new FileMigrationProvider({
33 fs,
34 path,
35 migrationFolder: path.join(__dirname, 'migrations'),
36 }),
37 });
38
39 const { error, results } = await migrator.migrateToLatest();
40
41 results?.forEach((migrationResult) => {
42 if (migrationResult.status === 'Success') {
43 console.log(
44 `migration "${migrationResult.migrationName}" was executed successfully`,
45 );
46 } else if (migrationResult.status === 'Error') {
47 console.error(
48 `failed to execute migration "${migrationResult.migrationName}"`,
49 );
50 }
51 });
52
53 if (error) {
54 console.error('Failed to migrate');
55 console.error(error);
56 process.exit(1);
57 }
58
59 await database.destroy();
60}
61
62migrateToLatest();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.
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .createTable('articles')
6 .addColumn('id', 'serial', (column) => column.primaryKey())
7 .addColumn('title', 'text', (column) => column.notNull())
8 .addColumn('article_content', 'text', (column) => column.notNull())
9 .execute();
10}
11
12export async function down(database: Kysely<unknown>): Promise<void> {
13 await database.schema.dropTable('articles');
14}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.
1{
2 "name": "nestjs-kysely",
3 "scripts": {
4 "migrations": "ts-node ./src/runMigrations.ts",
5 ...
6 },
7 ...
8}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.
1import { Generated } from 'kysely';
2
3export interface ArticlesTable {
4 id: Generated<number>;
5 title: string;
6 article_content: string;
7}We can now use the above interface with the Kysely class.
1import { ArticlesTable } from '../articles/articlesTable';
2import { Kysely } from 'kysely';
3
4interface Tables {
5 articles: ArticlesTable;
6}
7
8export 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
1import { ConfigurableModuleBuilder } from '@nestjs/common';
2import { DatabaseOptions } from './databaseOptions';
3
4export const {
5 ConfigurableModuleClass: ConfigurableDatabaseModule,
6 MODULE_OPTIONS_TOKEN: DATABASE_OPTIONS,
7} = new ConfigurableModuleBuilder<DatabaseOptions>()
8 .setClassMethodName('forRoot')
9 .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.
1export interface DatabaseOptions {
2 host: string;
3 port: number;
4 user: string;
5 password: string;
6 database: string;
7}We now can define the DatabaseModule that creates a connection pool and exports it.
1import { Global, Module } from '@nestjs/common';
2import {
3 ConfigurableDatabaseModule,
4 DATABASE_OPTIONS,
5} from './database.module-definition';
6import { DatabaseOptions } from './databaseOptions';
7import { Pool } from 'pg';
8import { PostgresDialect } from 'kysely';
9import { Database } from './database';
10
11@Global()
12@Module({
13 exports: [Database],
14 providers: [
15 {
16 provide: Database,
17 inject: [DATABASE_OPTIONS],
18 useFactory: (databaseOptions: DatabaseOptions) => {
19 const dialect = new PostgresDialect({
20 pool: new Pool({
21 host: databaseOptions.host,
22 port: databaseOptions.port,
23 user: databaseOptions.user,
24 password: databaseOptions.password,
25 database: databaseOptions.database,
26 }),
27 });
28
29 return new Database({
30 dialect,
31 });
32 },
33 },
34 ],
35})
36export class DatabaseModule extends ConfigurableDatabaseModule {}The last step is to import our module and provide the configuration using the ConfigService.
1import { Module } from '@nestjs/common';
2import { ConfigModule, ConfigService } from '@nestjs/config';
3import * as Joi from 'joi';
4import { DatabaseModule } from './database/database.module';
5
6@Module({
7 imports: [
8 DatabaseModule.forRootAsync({
9 imports: [ConfigModule],
10 inject: [ConfigService],
11 useFactory: (configService: ConfigService) => ({
12 host: configService.get('POSTGRES_HOST'),
13 port: configService.get('POSTGRES_PORT'),
14 user: configService.get('POSTGRES_USER'),
15 password: configService.get('POSTGRES_PASSWORD'),
16 database: configService.get('POSTGRES_DB'),
17 }),
18 }),
19 ConfigModule.forRoot({
20 validationSchema: Joi.object({
21 POSTGRES_HOST: Joi.string().required(),
22 POSTGRES_PORT: Joi.number().required(),
23 POSTGRES_USER: Joi.string().required(),
24 POSTGRES_PASSWORD: Joi.string().required(),
25 POSTGRES_DB: Joi.string().required(),
26 }),
27 }),
28 ],
29})
30export 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.
1import { Database } from '../database/database';
2
3export class ArticlesRepository {
4 constructor(private readonly database: Database) {}
5
6 async getAll() {
7 return this.database.selectFrom('articles').selectAll().execute();
8 }
9}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:
1[
2 {
3 id: 1,
4 title: 'Hello world',
5 article_content: 'Lorem ipsum'
6 }
7]However, we often want to transform the raw data we get from the database. A common way of doing that is to define models.
1interface ArticleModelData {
2 id: number;
3 title: string;
4 article_content: string;
5}
6
7export class Article {
8 id: number;
9 title: string;
10 content: string;
11 constructor({ id, title, article_content }: ArticleModelData) {
12 this.id = id;
13 this.title = title;
14 this.content = article_content;
15 }
16}We can now use the above model in our repository.
1import { Database } from '../database/database';
2import { Article } from './article.model';
3import { Injectable } from '@nestjs/common';
4
5@Injectable()
6export class ArticlesRepository {
7 constructor(private readonly database: Database) {}
8
9 async getAll() {
10 const databaseResponse = await this.database
11 .selectFrom('articles')
12 .selectAll()
13 .execute();
14 return databaseResponse.map((articleData) => new Article(articleData));
15 }
16}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.
1const query = `SELECT * FROM articles WHERE id=${id}`;Since we simply concatenate a string, we allow for the following SQL injection:
1const id = '1; DROP TABLE articles;
2const 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.
1import { Database } from '../database/database';
2import { Article } from './article.model';
3import { Injectable } from '@nestjs/common';
4
5@Injectable()
6export class ArticlesRepository {
7 constructor(private readonly database: Database) {}
8
9 // ...
10
11 async getById(id: number) {
12 const databaseResponse = await this.database
13 .selectFrom('articles')
14 .where('id', '=', id)
15 .selectAll()
16 .executeTakeFirst();
17
18 if (databaseResponse) {
19 return new Article(databaseResponse);
20 }
21 }
22}When we add simple logging functionality to the instance of our database, we can see for ourselves that Kysely is using parametrized queries.
1import { Global, Module } from '@nestjs/common';
2import {
3 ConfigurableDatabaseModule,
4 DATABASE_OPTIONS,
5} from './database.module-definition';
6import { DatabaseOptions } from './databaseOptions';
7import { Pool } from 'pg';
8import { PostgresDialect } from 'kysely';
9import { Database } from './database';
10
11@Global()
12@Module({
13 exports: [Database],
14 providers: [
15 {
16 provide: Database,
17 inject: [DATABASE_OPTIONS],
18 useFactory: (databaseOptions: DatabaseOptions) => {
19 const dialect = new PostgresDialect({
20 pool: new Pool({
21 host: databaseOptions.host,
22 port: databaseOptions.port,
23 user: databaseOptions.user,
24 password: databaseOptions.password,
25 database: databaseOptions.database,
26 }),
27 });
28
29 return new Database({
30 dialect,
31 log(event) {
32 if (event.level === 'query') {
33 console.log('Query:', event.query.sql);
34 console.log('Parameters:', event.query.parameters);
35 }
36 },
37 });
38 },
39 },
40 ],
41})
42export 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.
1import { IsString, IsNotEmpty } from 'class-validator';
2
3class ArticleDto {
4 @IsString()
5 @IsNotEmpty()
6 title: string;
7
8 @IsString()
9 @IsNotEmpty()
10 content: string;
11}
12
13export 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.
1import { Database } from '../database/database';
2import { Article } from './article.model';
3import { Injectable } from '@nestjs/common';
4import ArticleDto from './dto/article.dto';
5
6@Injectable()
7export class ArticlesRepository {
8 constructor(private readonly database: Database) {}
9
10 // ...
11
12 async create(data: ArticleDto) {
13 const databaseResponse = await this.database
14 .insertInto('articles')
15 .values({
16 title: data.title,
17 article_content: data.content,
18 })
19 .returningAll()
20 .executeTakeFirst();
21
22 return new Article(databaseResponse);
23 }
24
25 async update(id: number, data: ArticleDto) {
26 const databaseResponse = await this.database
27 .updateTable('articles')
28 .set({
29 title: data.title,
30 article_content: data.content,
31 })
32 .where('id', '=', id)
33 .returningAll()
34 .executeTakeFirst();
35
36 if (databaseResponse) {
37 return new Article(databaseResponse);
38 }
39 }
40}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.
1import { Article } from './article.model';
2import { Injectable, NotFoundException } from '@nestjs/common';
3import ArticleDto from './dto/article.dto';
4import { ArticlesRepository } from './articles.repository';
5
6@Injectable()
7export class ArticlesService {
8 constructor(private readonly articlesRepository: ArticlesRepository) {}
9
10 getAll() {
11 return this.articlesRepository.getAll();
12 }
13
14 async getById(id: number) {
15 const article = await this.articlesRepository.getById(id);
16
17 if (!article) {
18 throw new NotFoundException();
19 }
20
21 return article;
22 }
23
24 async create(data: ArticleDto) {
25 return this.articlesRepository.create(data);
26 }
27
28 async update(id: number, data: ArticleDto) {
29 const article = await this.articlesRepository.update(id, data);
30
31 if (!article) {
32 throw new NotFoundException();
33 }
34
35 return article;
36 }
37}Using the service in our controller
The last step is to use the above service in our controller.
1import { Controller, Get, Param, Post, Body, Put } from '@nestjs/common';
2import FindOneParams from '../utils/findOneParams';
3import ArticleDto from './dto/article.dto';
4import { ArticlesService } from './articles.service';
5
6@Controller('articles')
7export class ArticlesController {
8 constructor(private readonly articlesService: ArticlesService) {}
9
10 @Get()
11 getAll() {
12 return this.articlesService.getAll();
13 }
14
15 @Get(':id')
16 getById(@Param() { id }: FindOneParams) {
17 return this.articlesService.getById(id);
18 }
19
20 @Post()
21 create(@Body() data: ArticleDto) {
22 return this.articlesService.create(data);
23 }
24
25 @Put(':id')
26 update(@Param() { id }: FindOneParams, @Body() data: ArticleDto) {
27 return this.articlesService.update(id, data);
28 }
29}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!