Type-safe SQL queries with Kysely and PostgreSQL

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: bridgeTo 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=adminWe 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=nestjsWe 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.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): Promise { 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): Promise { 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; 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 {}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() .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