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.
docker-compose.yml
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
model: “3” providers: postgres: container_name: nestjs-kysely-postgres picture: postgres:15.3 ports: – “5432:5432” networks: – postgres volumes: – /information/postgres:/information/postgres env_file: – docker.env
pgadmin: container_name: nestjs-kysely-pgadmin picture: dpage/pgadmin4:7.5 networks: – postgres ports: – “8080:80” volumes: – /information/pgadmin:/root/.pgadmin env_file: – docker.env
networks: postgres: driver: bridge |
To supply our Docker container with the required credentials, we should 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 now to offer the same set of variables for our NestJS software too.
.env
|
POSTGRES_HOST=localhost POSTGRES_PORT=5432 POSTGRES_USER=admin POSTGRES_PASSWORD=admin POSTGRES_DB=nestjs |
We must always validate the atmosphere variables to forestall the NestJS software from beginning if they’re invalid.
app.module.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
import { Module } from ‘@nestjs/widespread’; 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 have to have Docker put in and run the
docker compose up command.
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.
runMigrations.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
import * as path from ‘path’; import { Pool } from ‘pg’; import { guarantees 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 perform migrateToLatest() { const database = new Kysely({ dialect: new PostgresDialect({ pool: new Pool({ host: configService.get(‘POSTGRES_HOST’), port: configService.get(‘POSTGRES_PORT’), consumer: configService.get(‘POSTGRES_USER’), password: configService.get(‘POSTGRES_PASSWORD’), database: configService.get(‘POSTGRES_DB’), }), }), });
const migrator = new Migrator({ db: database, supplier: new FileMigrationProvider({ fs, path, migrationFolder: path.be a part of(__dirname, ‘migrations’), }), });
const { error, outcomes } = await migrator.migrateToLatest();
outcomes?.forEach((migrationResult) => { if (migrationResult.standing === ‘Success’) { console.log( `migration “${migrationResult.migrationName}” was executed efficiently`, ); } else if (migrationResult.standing === ‘Error’) { console.error( `failed to execute migration “${migrationResult.migrationName}”`, ); } });
if (error) { console.error(‘Did not migrate’); console.error(error); course of.exit(1); }
await database.destroy(); }
migrateToLatest(); |
Above, we use the
dotenv library to ensure the
ConfigService has all the atmosphere variables loaded and able to use.
In our
migrateToLatest perform, we level to the
migrations listing that ought to comprise our migrations. Let’s use it to create our first desk.
migrations/20230806213313_add_articles_table.ts
|
import { Kysely } from ‘kysely’;
export async perform up(database: Kysely<unknown>): Promise<void> { await database.schema .createTable(‘articles’) .addColumn(‘id’, ‘serial’, (column) => column.primaryKey()) .addColumn(‘title’, ‘textual content’, (column) => column.notNull()) .addColumn(‘article_content’, ‘textual content’, (column) => column.notNull()) .execute(); }
export async perform down(database: Kysely<unknown>): Promise<void> { await database.schema.dropTable(‘articles’); } |
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
bundle.json to run our migrations.
bundle.json
|
{ “title”: “nestjs-kysely”, “scripts”: { “migrations”: “ts-node ./src/runMigrations.ts”, ... }, ... } |
Now, at any time when we run
npm run migrations, 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.
articles/articlesTable.ts
|
import { Generated } from ‘kysely’;
export interface ArticlesTable { id: Generated<quantity>; title: string; article_content: string; } |
We will 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> {} |
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
Database class we outlined.
If you wish to know extra about dynamic modules, take a look at API with NestJS #70. Defining dynamic modules
database/database.module-definition.ts
|
import { ConfigurableModuleBuilder } from ‘@nestjs/widespread’; import { DatabaseOptions } from ‘./databaseOptions’;
export const { ConfigurableModuleClass: ConfigurableDatabaseModule, MODULE_OPTIONS_TOKEN: DATABASE_OPTIONS, } = new ConfigurableModuleBuilder<DatabaseOptions>() .setClassMethodName(‘forRoot’) .construct(); |
Above we use
forRoot as a result of we wish the
DatabaseModule to be world.
When our module is imported, we wish a specific set of choices to be offered.
database/databaseOptions.ts
|
export interface DatabaseOptions { host: string; port: quantity; consumer: string; password: string; database: string; } |
We now can outline the
DatabaseModule that creates a connection pool and exports it.
database/database.module.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
import { International, Module } from ‘@nestjs/widespread’; import { ConfigurableDatabaseModule, DATABASE_OPTIONS, } from ‘./database.module-definition’; import { DatabaseOptions } from ‘./databaseOptions’; import { Pool } from ‘pg’; import { PostgresDialect } from ‘kysely’; import { Database } from ‘./database’;
@International() @Module({ exports: [Database], suppliers: [ { provide: Database, inject: [DATABASE_OPTIONS], useFactory: (databaseOptions: DatabaseOptions) => { const dialect = new PostgresDialect({ pool: new Pool({ host: databaseOptions.host, port: databaseOptions.port, consumer: databaseOptions.consumer, password: databaseOptions.password, database: databaseOptions.database, }), });
return new Database({ dialect, }); }, }, ], }) export class DatabaseModule extends ConfigurableDatabaseModule {} |
The final step is to import our module and supply the configuration utilizing the
ConfigService.
app.module.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
import { Module } from ‘@nestjs/widespread’; 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’), consumer: 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.quantity().required(), POSTGRES_USER: Joi.string().required(), POSTGRES_PASSWORD: Joi.string().required(), POSTGRES_DB: Joi.string().required(), }), }), ], }) export class AppModule {} |
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.
articles/articles.repository.ts
|
import { Database } from ‘../database/database’;
export class ArticlesRepository { constructor(personal readonly database: Database) {}
async getAll() { return this.database.selectFrom(‘articles’).selectAll().execute(); } } |
Due to Kysely being type-safe, we wouldn’t be capable to name the
selectFrom 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:
|
[ { id: 1, title: ‘Hello world’, article_content: ‘Lorem ipsum’ } ] |
Nevertheless, we regularly wish to remodel the uncooked information we get from the database. A typical means of doing that’s to outline fashions.
articles/article.mannequin.ts
|
interface ArticleModelData { id: quantity; title: string; article_content: string; }
export class Article { id: quantity; title: string; content material: string; constructor({ id, title, article_content material }: ArticleModelData) { this.id = id; this.title = title; this.content material = article_content; } } |
We will now use the above mannequin in our repository.
articles/articles.repository.ts
|
import { Database } from ‘../database/database’; import { Article } from ‘./article.mannequin’; import { Injectable } from ‘@nestjs/widespread’;
@Injectable() export class ArticlesRepository { constructor(personal readonly database: Database) {}
async getAll() { const databaseResponse = await this.database .selectFrom(‘articles’) .selectAll() .execute(); return databaseResponse.map((articleData) => new Article(articleData)); } } |
Due to doing the above, the objects returned by the
getAll methodology at the moment are cases of the
Article class.
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.
|
const question = `SELECT * FROM articles WHERE id=${id}`; |
Since we merely concatenate a string, we enable for the next SQL injection:
|
const id = ‘1; DROP TABLE articles; const question = `SELECT * FROM articles WHERE id=${id}`; |
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.
articles/articles.repository.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import { Database } from ‘../database/database’; import { Article } from ‘./article.mannequin’; import { Injectable } from ‘@nestjs/widespread’;
@Injectable() export class ArticlesRepository { constructor(personal readonly database: Database) {}
// …
async getById(id: quantity) { const databaseResponse = await this.database .selectFrom(‘articles’) .the place(‘id’, ‘=’, id) .selectAll() .executeTakeFirst();
if (databaseResponse) { return new Article(databaseResponse); } } } |
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.
database/database.module.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
import { International, Module } from ‘@nestjs/widespread’; import { ConfigurableDatabaseModule, DATABASE_OPTIONS, } from ‘./database.module-definition’; import { DatabaseOptions } from ‘./databaseOptions’; import { Pool } from ‘pg’; import { PostgresDialect } from ‘kysely’; import { Database } from ‘./database’;
@International() @Module({ exports: [Database], suppliers: [ { provide: Database, inject: [DATABASE_OPTIONS], useFactory: (databaseOptions: DatabaseOptions) => { const dialect = new PostgresDialect({ pool: new Pool({ host: databaseOptions.host, port: databaseOptions.port, consumer: databaseOptions.consumer, password: databaseOptions.password, database: databaseOptions.database, }), });
return new Database({ dialect, log(occasion) { if (occasion.degree === ‘question’) { console.log(‘Question:’, occasion.question.sql); console.log(‘Parameters:’, occasion.question.parameters); } }, }); }, }, ], }) export class DatabaseModule extends ConfigurableDatabaseModule {} |
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.
articles/dto/article.dto.ts
|
import { IsString, IsNotEmpty } from ‘class-validator’;
class ArticleDto { @IsString() @IsNotEmpty() title: string;
@IsString() @IsNotEmpty() content material: string; }
export default ArticleDto; |
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.
articles/dto/articles.repository.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
import { Database } from ‘../database/database’; import { Article } from ‘./article.mannequin’; import { Injectable } from ‘@nestjs/widespread’; import ArticleDto from ‘./dto/article.dto’;
@Injectable() export class ArticlesRepository { constructor(personal readonly database: Database) {}
// …
async create(information: ArticleDto) { const databaseResponse = await this.database .insertInto(‘articles’) .values({ title: information.title, article_content: information.content material, }) .returningAll() .executeTakeFirst();
return new Article(databaseResponse); }
async replace(id: quantity, information: ArticleDto) { const databaseResponse = await this.database .updateTable(‘articles’) .set({ title: information.title, article_content: information.content material, }) .the place(‘id’, ‘=’, id) .returningAll() .executeTakeFirst();
if (databaseResponse) { return new Article(databaseResponse); } } } |
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
ArticlesService largely calls the strategies from the repository.
articles/dto/articles.service.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
import { Article } from ‘./article.mannequin’; import { Injectable, NotFoundException } from ‘@nestjs/widespread’; import ArticleDto from ‘./dto/article.dto’; import { ArticlesRepository } from ‘./articles.repository’;
@Injectable() export class ArticlesService { constructor(personal readonly articlesRepository: ArticlesRepository) {}
getAll() { return this.articlesRepository.getAll(); }
async getById(id: quantity) { const article = await this.articlesRepository.getById(id);
if (!article) { throw new NotFoundException(); }
return article; }
async create(information: ArticleDto) { return this.articlesRepository.create(information); }
async replace(id: quantity, information: ArticleDto) { const article = await this.articlesRepository.replace(id, information);
if (!article) { throw new NotFoundException(); }
return article; } } |
Utilizing the service in our controller
The final step is to make use of the above service in our controller.
articles/dto/articles.controller.ts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
import { Controller, Get, Param, Submit, Physique, Put } from ‘@nestjs/widespread’; import FindOneParams from ‘../utils/findOneParams’; import ArticleDto from ‘./dto/article.dto’; import { ArticlesService } from ‘./articles.service’;
@Controller(‘articles’) export class ArticlesController { constructor(personal readonly articlesService: ArticlesService) {}
@Get() getAll() { return this.articlesService.getAll(); }
@Get(‘:id’) getById(@Param() { id }: FindOneParams) { return this.articlesService.getById(id); }
@Submit() create(@Physique() information: ArticleDto) { return this.articlesService.create(information); }
@Put(‘:id’) replace(@Param() { id }: FindOneParams, @Physique() information: ArticleDto) { return this.articlesService.replace(id, information); } } |
Abstract
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!
