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.
1version: "3"
2services:
3 postgres:
4 container_name: postgres-nestjs-drizzle
5 image: postgres:13.15
6 ports:
7 - "5432:5432"
8 volumes:
9 - /data/postgres:/data/postgres
10 env_file:
11 - docker.env
12 networks:
13 - postgres
14
15 pgadmin:
16 links:
17 - postgres:postgres
18 container_name: pgadmin-nestjs-drizzle
19 image: dpage/pgadmin4:8.6
20 ports:
21 - "8080:80"
22 volumes:
23 - /data/pgadmin:/root/.pgadmin
24 env_file:
25 - docker.env
26 networks:
27 - postgres
28
29networks:
30 postgres:
31 driver: bridgeLet’s create the docker.env file to provide Docker with the necessary environment variables.
1POSTGRES_USER=admin
2POSTGRES_PASSWORD=admin
3POSTGRES_DB=nestjs
4PGADMIN_DEFAULT_EMAIL=admin@admin.com
5PGADMIN_DEFAULT_PASSWORD=adminWe must also add a matching set of variables to our NestJS application.
1POSTGRES_HOST=localhost
2POSTGRES_PORT=5432
3POSTGRES_USER=admin
4POSTGRES_PASSWORD=admin
5POSTGRES_DB=nestjsIt makes sense to check if the environment variables are available when the application starts.
1import { Module } from '@nestjs/common';
2import { ConfigModule } from '@nestjs/config';
3import * as Joi from 'joi';
4
5@Module({
6 imports: [
7 ConfigModule.forRoot({
8 validationSchema: Joi.object({
9 POSTGRES_HOST: Joi.string().required(),
10 POSTGRES_PORT: Joi.number().required(),
11 POSTGRES_USER: Joi.string().required(),
12 POSTGRES_PASSWORD: Joi.string().required(),
13 POSTGRES_DB: Joi.string().required(),
14 }),
15 }),
16 ],
17 controllers: [],
18 providers: [],
19})
20export class AppModule {}Setting up the connection
Drizzle can use the node-postgres library under the hood to establish a connection to the PostgreSQL database.
1npm install pg @types/pgTo 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
1import { ConfigurableModuleBuilder } from '@nestjs/common';
2import { DatabaseOptions } from './database-options';
3
4export const CONNECTION_POOL = 'CONNECTION_POOL';
5
6export const {
7 ConfigurableModuleClass: ConfigurableDatabaseModule,
8 MODULE_OPTIONS_TOKEN: DATABASE_OPTIONS,
9} = new ConfigurableModuleBuilder<DatabaseOptions>()
10 .setClassMethodName('forRoot')
11 .build();Since we want our DatabaseModule to be global, we use forRoot above.
When importing the DatabaseModule, we expect specific options to be provided.
1export interface DatabaseOptions {
2 host: string;
3 port: number;
4 user: string;
5 password: string;
6 database: string;
7}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.
1import { Global, Module } from '@nestjs/common';
2import {
3 ConfigurableDatabaseModule,
4 CONNECTION_POOL,
5 DATABASE_OPTIONS,
6} from './database.module-definition';
7import { DatabaseOptions } from './database-options';
8import { Pool } from 'pg';
9import { DrizzleService } from './drizzle.service';
10
11@Global()
12@Module({
13 exports: [DrizzleService],
14 providers: [
15 DrizzleService,
16 {
17 provide: CONNECTION_POOL,
18 inject: [DATABASE_OPTIONS],
19 useFactory: (databaseOptions: DatabaseOptions) => {
20 return 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 ],
30})
31export 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.
1import { Module } from '@nestjs/common';
2import { ConfigModule, ConfigService } from '@nestjs/config';
3import { DatabaseModule } from './database/database.module';
4
5@Module({
6 imports: [
7 DatabaseModule.forRootAsync({
8 imports: [ConfigModule],
9 inject: [ConfigService],
10 useFactory: (configService: ConfigService) => ({
11 host: configService.get('POSTGRES_HOST'),
12 port: configService.get('POSTGRES_PORT'),
13 user: configService.get('POSTGRES_USER'),
14 password: configService.get('POSTGRES_PASSWORD'),
15 database: configService.get('POSTGRES_DB'),
16 }),
17 }),
18 // ...
19 ],
20})
21export 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.
1npm install drizzle-orm1import { Inject, Injectable } from '@nestjs/common';
2import { Pool } from 'pg';
3import { CONNECTION_POOL } from './database.module-definition';
4import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';
5import { databaseSchema } from './database-schema';
6
7@Injectable()
8export class DrizzleService {
9 public db: NodePgDatabase<typeof databaseSchema>;
10 constructor(@Inject(CONNECTION_POOL) private readonly pool: Pool) {
11 this.db = drizzle(this.pool, { schema: databaseSchema });
12 }
13}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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2
3export const articles = pgTable('articles', {
4 id: serial('id').primaryKey(),
5 title: text('title'),
6 content: text('content'),
7});
8
9export const databaseSchema = {
10 articles,
11};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.
1npm install drizzle-kit dotenvWe also need to create a config file at the root of our project.
1import { defineConfig } from 'drizzle-kit';
2import { ConfigService } from '@nestjs/config';
3import 'dotenv/config';
4
5const configService = new ConfigService();
6
7export default defineConfig({
8 schema: './src/database/database-schema.ts',
9 out: './drizzle',
10 dialect: 'postgresql',
11 dbCredentials: {
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})Now, we can generate a migration.
1npx drizzle-kit generate --name create-articles-tableWhen 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.
1CREATE TABLE IF NOT EXISTS "articles" (
2 "id" serial PRIMARY KEY NOT NULL,
3 "title" text,
4 "content" text
5);The last step is to run the migration.
1npx drizzle-kit migrateWhen 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.
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4
5@Injectable()
6export class ArticlesService {
7 constructor(private readonly drizzleService: DrizzleService) {}
8
9 getAll() {
10 return this.drizzleService.db.select().from(databaseSchema.articles);
11 }
12
13 // ...
14}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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5
6@Injectable()
7export class ArticlesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async getById(id: number) {
11 const articles = await this.drizzleService.db
12 .select()
13 .from(databaseSchema.articles)
14 .where(eq(databaseSchema.articles.id, id));
15 const article = articles.pop();
16 if (!article) {
17 throw new NotFoundException();
18 }
19 return article;
20 }
21
22 // ...
23}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.
1import { Injectable } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { CreateArticleDto } from './dto/create-article.dto';
5
6@Injectable()
7export class ArticlesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async create(article: CreateArticleDto) {
11 const createdArticles = await this.drizzleService.db
12 .insert(databaseSchema.articles)
13 .values(article)
14 .returning();
15
16 return createdArticles.pop();
17 }
18
19 // ...
20}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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5import { UpdateArticleDto } from './dto/update-article.dto';
6
7@Injectable()
8export class ArticlesService {
9 constructor(private readonly drizzleService: DrizzleService) {}
10
11 async update(id: number, article: UpdateArticleDto) {
12 const updatedArticles = await this.drizzleService.db
13 .update(databaseSchema.articles)
14 .set(article)
15 .where(eq(databaseSchema.articles.id, id))
16 .returning();
17
18 if (updatedArticles.length === 0) {
19 throw new NotFoundException();
20 }
21
22 return updatedArticles.pop();
23 }
24
25 // ...
26}Deleting entities
To delete an entity, we need the delete function. If the entity wasn’t deleted, we throw the NotFoundException.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5
6@Injectable()
7export class ArticlesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async delete(id: number) {
11 const deletedArticles = await this.drizzleService.db
12 .delete(databaseSchema.articles)
13 .where(eq(databaseSchema.articles.id, id))
14 .returning();
15
16 if (deletedArticles.length === 0) {
17 throw new NotFoundException();
18 }
19 }
20
21 // ...
22}Creating the controller
We can now use our service in a controller to allow users to create, read, update, and delete entities.
1import {
2 Body,
3 Controller,
4 Delete,
5 Get,
6 Param,
7 ParseIntPipe,
8 Patch,
9 Post,
10} from '@nestjs/common';
11import { ArticlesService } from './articles.service';
12import { CreateArticleDto } from './dto/create-article.dto';
13import { UpdateArticleDto } from './dto/update-article.dto';
14
15@Controller('articles')
16export class ArticlesController {
17 constructor(private readonly articlesService: ArticlesService) {}
18
19 @Get()
20 getAll() {
21 return this.articlesService.getAll();
22 }
23
24 @Get(':id')
25 getById(@Param('id', ParseIntPipe) id: number) {
26 return this.articlesService.getById(id);
27 }
28
29 @Post()
30 create(@Body() article: CreateArticleDto) {
31 return this.articlesService.create(article);
32 }
33
34 @Patch(':id')
35 update(
36 @Param('id', ParseIntPipe) id: number,
37 @Body() article: UpdateArticleDto,
38 ) {
39 return this.articlesService.update(id, article);
40 }
41
42 @Delete(':id')
43 async delete(@Param('id', ParseIntPipe) id: number) {
44 await this.articlesService.delete(id);
45 }
46}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.