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: bridgeLet’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=adminWe 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=nestjsIt 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.npm 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 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() .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.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; 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.npm install drizzle-kit dotenvWe 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.npx 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. 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.npx 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. 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 t