API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL

May 20, 2024

This entry is part 149 of 175 in the API with NestJS

Drizzle is a lightweight TypeScript ORM that lets us manage our database schema. Interestingly, it allows us to manage our data through a relational API or an SQL query builder.

In this article, we learn how to set up NestJS with Drizzle to implement create, read, update, and delete operations. We also learn how to use Drizzle to manage migrations.

Check out this repository if you want to see the full code from this article.

Connecting to the database

Let’s use Docker Compose to create a PostgreSQL database for us.

docker-compose.yml

version: "3"

services:

postgres:

container_name: postgres-nestjs-drizzle

image: postgres:13.15

ports:

- "5432:5432"

volumes:

- /data/postgres:/data/postgres

env_file:

- docker.env

networks:

- postgres

pgadmin:

links:

- postgres:postgres

container_name: pgadmin-nestjs-drizzle

image: dpage/pgadmin4:8.6

ports:

- "8080:80"

volumes:

- /data/pgadmin:/root/.pgadmin

env_file:

- docker.env

networks:

- postgres

networks:

postgres:

driver: bridge

Let’s create the docker.env file to provide Docker with the necessary environment variables.

docker.env

POSTGRES_USER=admin

POSTGRES_PASSWORD=admin

POSTGRES_DB=nestjs

PGADMIN_DEFAULT_EMAIL=admin@admin.com

PGADMIN_DEFAULT_PASSWORD=admin

We must also add a matching set of variables to our NestJS application.

.env

POSTGRES_HOST=localhost

POSTGRES_PORT=5432

POSTGRES_USER=admin

POSTGRES_PASSWORD=admin

POSTGRES_DB=nestjs

It makes sense to check if the environment variables are available when the application starts.

app.module.ts

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

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

import * as Joi from 'joi';

@Module({

imports: [

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(),

}),

controllers: [],

providers: [],

})

export class AppModule {}

Setting up the connection

Drizzle can use the node-postgres library under the hood to establish a connection to the PostgreSQL database.

1	npm install pg @types/pg

To handle a database connection, we can develop a dynamic module. This way, it can be easily copied and pasted into another project or maintained in a separate library.

If you’re new to dynamic modules, consider taking a look at API with NestJS #70. Defining dynamic modules

database.module-definition.ts

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

import { DatabaseOptions } from './database-options';

export const CONNECTION_POOL = 'CONNECTION_POOL';

export const {

ConfigurableModuleClass: ConfigurableDatabaseModule,

MODULE_OPTIONS_TOKEN: DATABASE_OPTIONS,

} = new ConfigurableModuleBuilder<DatabaseOptions>()

.setClassMethodName('forRoot')

.build();

Since we want our DatabaseModule to be global, we use forRoot above.

When importing the DatabaseModule, we expect specific options to be provided.

database-options.ts

export interface DatabaseOptions {

host: string;

port: number;

user: string;

password: string;

database: string;

}

Creating a connection pool

The node-postgres library suggests using a connection pool. Since we’re building a dynamic module, we can set up our pool as a provider.

database.module.ts

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

import {

ConfigurableDatabaseModule,

CONNECTION_POOL,

DATABASE_OPTIONS,

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

import { DatabaseOptions } from './database-options';

import { Pool } from 'pg';

import { DrizzleService } from './drizzle.service';

@Global()

@Module({

exports: [DrizzleService],

providers: [

DrizzleService,

{

provide: CONNECTION_POOL,

inject: [DATABASE_OPTIONS],

useFactory: (databaseOptions: DatabaseOptions) => {

return new Pool({

host: databaseOptions.host,

port: databaseOptions.port,

user: databaseOptions.user,

password: databaseOptions.password,

database: databaseOptions.database,

});

})

export class DatabaseModule extends ConfigurableDatabaseModule {}

There’s a benefit to setting up the connection pool as a provider. It’s a great spot to add any extra asynchronous configuration if needed. We should provide the necessary configuration when importing our module.

app.module.ts

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

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

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'),

}),

// ...

})

export class AppModule {}

Because we defined a provider above using the CONNECTION_POOL string, we can now utilize it in our Drizzle service. However, before creating this service, we will need to install Drizzle.

1	npm install drizzle-orm

drizzle.service.ts

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

import { Pool } from 'pg';

import { CONNECTION_POOL } from './database.module-definition';

import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';

import { databaseSchema } from './database-schema';

@Injectable()

export class DrizzleService {

public db: NodePgDatabase<typeof databaseSchema>;

constructor(@Inject(CONNECTION_POOL) private readonly pool: Pool) {

this.db = drizzle(this.pool, { schema: databaseSchema });

}

Creating a schema and generating migrations

Above, we provide Drizzle with a database schema. It should describe all tables in our database. Let’s start with a simple table containing articles.

database-schema.ts

import { serial, text, pgTable } from 'drizzle-orm/pg-core';

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title'),

content: text('content'),

});

export const databaseSchema = {

articles,

};

With the pgTable function, we create a new table and give it a name. We also define all of the columns using the serial and text functions.

Managing migrations

We now need to modify our PostgreSQL database to match the above schema.

Relational databases are known for their strict data structures. We must clearly define each table’s structure, including fields, indexes, and relationships. Even with a well-designed database, our application’s evolving requirements mean the database must adapt, too. It’s critical to modify the database carefully to preserve existing data.

Manually executing SQL queries to update the structure of the database database isn’t practical across various environments. Database migrations offer a more systematic approach, allowing us to implement controlled changes like adding tables or altering columns. Modifying a database structure is a sensitive task that could potentially compromise data integrity. Database migrations involve committing SQL queries to the repository, enabling thorough reviews before they are integrated into the main branch.

To manage migrations with Drizzle, we need to install the drizzle-kit library. We will also need the dotenv library to work with environment variables.

1	npm install drizzle-kit dotenv

We also need to create a config file at the root of our project.

drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

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

import 'dotenv/config';

const configService = new ConfigService();

export default defineConfig({

schema: './src/database/database-schema.ts',

out: './drizzle',

dialect: 'postgresql',

dbCredentials: {

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'),

})

Now, we can generate a migration.

1	npx drizzle-kit generate --name create-articles-table

When we do that, Drizzle compares the schema with our database and creates the SQL migration file.

It’s crucial to export all of the tables from the database-schema.ts so that the Drizzle Kit can recognize them.

0000_create-articles-table.sql

CREATE TABLE IF NOT EXISTS "articles" (

"id" serial PRIMARY KEY NOT NULL,

"title" text,

"content" text

);

The last step is to run the migration.

1	npx drizzle-kit migrate

When we do that, Drizzle applies the changes and creates the __drizzle_migrations table. This table holds information about the executed migrations.

Interacting with the database

We now have everything set up, and we can start interacting with our database through the DrizzleService we created.

Fetching all records

To fetch all records from a given table, we can use the select() method and provide the table from the schema we want.

articles.service.ts

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

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

getAll() {

return this.drizzleService.db.select().from(databaseSchema.articles);

}

// ...

}

An alternative would be to use the Query API. We will cover it in a separate article.

Fetching a record with a given ID

To fetch a single record with a given ID, we must use the where function and provide a filtering condition. We throw an error if an entity with a given ID does not exist.

articles.service.ts

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

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

async getById(id: number) {

const articles = await this.drizzleService.db

.select()

.from(databaseSchema.articles)

.where(eq(databaseSchema.articles.id, id));

const article = articles.pop();

if (!article) {

throw new NotFoundException();

}

return article;

}

// ...

}

Creating new entities

To create new entities, we need the insert() function. We need to call the returning() function to ensure we can access the created entity.

articles.service.ts

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

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { CreateArticleDto } from './dto/create-article.dto';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(article: CreateArticleDto) {

const createdArticles = await this.drizzleService.db

.insert(databaseSchema.articles)

.values(article)

.returning();

return createdArticles.pop();

}

// ...

}

Updating existing entities

To update an existing entity, we need the update() and set() functions. If the entity wasn’t updated, we throw an error.

articles.service.ts

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

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

import { UpdateArticleDto } from './dto/update-article.dto';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

async update(id: number, article: UpdateArticleDto) {

const updatedArticles = await this.drizzleService.db

.update(databaseSchema.articles)

.set(article)

.where(eq(databaseSchema.articles.id, id))

.returning();

if (updatedArticles.length === 0) {

throw new NotFoundException();

}

return updatedArticles.pop();

}

// ...

}

Deleting entities

To delete an entity, we need the delete function. If the entity wasn’t deleted, we throw the NotFoundException.

articles.service.ts

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

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

async delete(id: number) {

const deletedArticles = await this.drizzleService.db

.delete(databaseSchema.articles)

.where(eq(databaseSchema.articles.id, id))

.returning();

if (deletedArticles.length === 0) {

throw new NotFoundException();

}

// ...

}

Creating the controller

We can now use our service in a controller to allow users to create, read, update, and delete entities.

articles.controller.ts

import {

Body,

Controller,

Delete,

Get,

Param,

ParseIntPipe,

Patch,

Post,

} from '@nestjs/common';

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

import { CreateArticleDto } from './dto/create-article.dto';

import { UpdateArticleDto } from './dto/update-article.dto';

@Controller('articles')

export class ArticlesController {

constructor(private readonly articlesService: ArticlesService) {}

@Get()

getAll() {

return this.articlesService.getAll();

}

@Get(':id')

getById(@Param('id', ParseIntPipe) id: number) {

return this.articlesService.getById(id);

}

@Post()

create(@Body() article: CreateArticleDto) {

return this.articlesService.create(article);

}

@Patch(':id')

update(

@Param('id', ParseIntPipe) id: number,

@Body() article: UpdateArticleDto,

) {

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

}

@Delete(':id')

async delete(@Param('id', ParseIntPipe) id: number) {

await this.articlesService.delete(id);

}

Summary

Thanks to the above, we now have a fully working application that allows us to manage the database schema through Drizzle and interact with the created tables. To implement that, we had to learn how to manage migrations through the Drizzle Kit and understand the basics of accessing our data with Drizzle.

There is still more to learn when it comes to using Drizzle with NestJS, so stay tuned.

Series Navigation<< API with NestJS #148. Understanding the injection scopesAPI with NestJS #150. One-to-one relationships with the Drizzle ORM >>

2 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

John Doe

5 months ago

Incredible. Thank you for the tutorial, this helped me tremendously. have

ctc

4 months ago

Thanks, this helped a lot, but I have a problem if the db is downed, the nestjs only logs “AggregateError” while executing a query. How can I log the proper error for this?