Introduction to the Drizzle ORM with PostgreSQL

May 20, 2024

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.

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.

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.

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 { pgTable, serial, text } 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 dotenv

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

drizzle.config.ts#

import { ConfigService } from "@nestjs/config"
import { defineConfig } from "drizzle-kit"
 
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-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.

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.