Database migrations with the Drizzle ORM

Relational databases are known for their strict data structures. Every table requires a defined schema, including columns, indexes, and relationships. Despite careful planning during database design, application requirements often evolve. As a result, the database must adapt to keep up with these new needs. However, it’s crucial to ensure that no existing data is lost during these updates.

While we could manually execute SQL queries to modify the database, this approach is impractical in reproducing reliably across different application environments. Database migrations offer a more structured solution and provide a structured and reliable way to implement changes, such as adding new tables or altering columns. When doing that, they can help minimize the risk of losing the integrity of our data. Moreover, by committing SQL changes to the repository, they undergo rigorous review before merging them into the main codebase.

In this article, we look into how we can manage migrations using the Drizzle ORM.

Setting up the Drizzle Kit

Drizzle offers the Drizzle Kit CLI tool to help us manage SQL migrations.

1npm install drizzle-kit

Environment variables

To use it, we need first to configure a database connection. To do that with NestJS, we should use environment variables. The first step is to set up a validation schema to prevent the developers from providing incorrect values.

1import { Module } from '@nestjs/common';
2import { ConfigModule, ConfigService } from '@nestjs/config';
3import * as Joi from 'joi';
4import { ArticlesModule } from './articles/articles.module';
5import { DatabaseModule } from './database/database.module';
6import { EnvironmentVariables } from './utilities/environment-variables';
7 
8@Module({
9  imports: [
10    ArticlesModule,
11    DatabaseModule.forRootAsync({
12      imports: [ConfigModule],
13      inject: [ConfigService],
14      useFactory: (
15        configService: ConfigService<EnvironmentVariables, true>,
16      ) => ({
17        host: configService.get('POSTGRES_HOST'),
18        port: configService.get('POSTGRES_PORT'),
19        user: configService.get('POSTGRES_USER'),
20        password: configService.get('POSTGRES_PASSWORD'),
21        database: configService.get('POSTGRES_DB'),
22      }),
23    }),
24    ConfigModule.forRoot({
25      validationSchema: Joi.object({
26        POSTGRES_HOST: Joi.string().required(),
27        POSTGRES_PORT: Joi.number().required(),
28        POSTGRES_USER: Joi.string().required(),
29        POSTGRES_PASSWORD: Joi.string().required(),
30        POSTGRES_DB: Joi.string().required(),
31        POSTGRES_IS_SSL_ON: Joi.bool❯ ean().required(),
32      }),
33    }),
34  ],
35})
36export class AppModule {}

We should also create an interface that describes the types of our environment variables.

1export interface EnvironmentVariables {
2  POSTGRES_HOST: string;
3  POSTGRES_PORT: string;
4  POSTGRES_USER: string;
5  POSTGRES_PASSWORD: string;
6  POSTGRES_DB: string;
7  POSTGRES_IS_SSL_ON: string;
8}

Environment variables are always strings

Finally, we need to provide the values Drizzle should use.

1POSTGRES_HOST=localhost
2POSTGRES_PORT=5432
3POSTGRES_USER=admin
4POSTGRES_PASSWORD=admin
5POSTGRES_DB=nestjs
6POSTGRES_IS_SSL_ON=false

In this series, we use Docker Compose to set up a local PostgreSQL database. If you want to know more, check out API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL

Configuring Drizzle

Now, we can create the drizzle.config.ts file to configure the database connection.

1import { defineConfig } from 'drizzle-kit';
2import { ConfigService } from '@nestjs/config';
3import 'dotenv/config';
4import { EnvironmentVariables } from './src/utilities/environment-variables';
5 
6const configService = new ConfigService<EnvironmentVariables, true>();
7 
8export default defineConfig({
9  schema: './src/database/database-schema.ts',
10  out: './drizzle',
11  dialect: 'postgresql',
12  dbCredentials: {
13    host: configService.get('POSTGRES_HOST'),
14    port: configService.get('POSTGRES_PORT'),
15    user: configService.get('POSTGRES_USER'),
16    password: configService.get('POSTGRES_PASSWORD'),
17    database: configService.get('POSTGRES_DB'),
18    ssl: configService.get('POSTGRES_IS_SSL_ON') === 'true',
19  },
20});

We use the dotenv library to load the .env file. Then, we create an instance of the ConfigService so that we can use it in our configuration.

Creating the database schema

Our configuration provides a path to the database-schema.ts file that needs to describe all the tables in our database. Let’s start with a simple table that contains 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').notNull(),
6  content: text('content'),
7});
8 
9export const databaseSchema = {
10  articles,
11};

Above, we use the pgTable function to create a new table and name it. We also define the columns using the serial and text functions.

It’s very important to export all the tables in the database-schema.ts so that the Drizzle Kit can detect them.

Our first migration

To create a migration with the Drizzle Kit, we need to use the generate command and provide a descriptive name for our migration.

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

No config path provided, using default ‘drizzle.config.ts’ Reading config file ‘/home/marcin/Documents/Projects/nestjs-drizzle/drizzle.config.ts’ 1 tables articles 3 columns 0 indexes 0 fks [✓] Your SQL migration file ➜ drizzle/0000_create-articles-table.sql 🚀

Running it causes Drizzle Kit to compare our database-schema.ts file with our database. If there is something new in our schema, Drizzle Kit creates the SQL migration file that we can use to modify our database to match the schema.

1CREATE TABLE IF NOT EXISTS "articles" (
2  "id" serial PRIMARY KEY NOT NULL,
3  "title" text NOT NULL,
4  "content" text
5);

Running the migration

To run all our migrations, we need to run the migrate command.

1npx drizzle-kit migrate

When we run it, Drizzle Kit applies the changes to our database based on the generated migration files. It also stores the information about the executed migration in the __drizzle_migrations table.

What’s interesting, the __drizzle_migrations table is in a separate schema called drizzle. If yoyu want to know more about using Drizzle with multiple schemas, check out API with NestJS #174. Multiple PostgreSQL schemas with Drizzle ORM

Renaming columns

The Drizzle Kit is good at intercepting what changes we made to our schema, but it can’t read our minds. Sometimes, we have to answer some additional questions so that Drizzle Kit can get it right.

Let’s try renaming the title column to topic.

1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2 
3export const articles = pgTable('articles', {
4  id: serial('id').primaryKey(),
5  topic: text('topic').notNull(),
6  content: text('content'),
7});
8 
9export const databaseSchema = {
10  articles,
11};

Now, we can generate the migration.

1npx drizzle-kit generate --name rename-article-title-to-topic

When we do that, Drizzle asks us the following question:

Is topic column in articles table created or renamed from another column?

If we answer that the topic column was created from scratch, Drizzle Kit generates a migration that removes the title column and adds the topic.

1ALTER TABLE "articles" ADD COLUMN "topic" text NOT NULL;--> statement-breakpoint
2ALTER TABLE "articles" DROP COLUMN IF EXISTS "title";

Running the above migration when no articles are in our database would work fine. However, if we already have some, this will result in an error:

[⣷] applying migrations…error: column “topic” of relation “articles” contains null values

When we remove the title column, we also remove all the titles stored in our database. Then, we add the new topic column to existing articles. The  topic column does not accept missing values, but we’re not providing any.

The most straightforward solution is to tell Drizzle Kit to rename the column title to topic. When we do that, it generates a different migration.

1ALTER TABLE "articles" RENAME COLUMN "title" TO "topic";

Now, Drizzle Kit renames the column title to topic and preserves all of the titles stored in the database.

Adjusting migrations manually

Sometimes, there are cases that require us to write SQL manually to fit our needs. Let’s rename the topic column to paragraphs and change it to an array.

If you want to know how to handle arrays in PostgreSQL with the Drizzle ORM, check out API with NestJS #156. Arrays with PostgreSQL and the Drizzle ORM

1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2 
3export const articles = pgTable('articles', {
4  id: serial('id').primaryKey(),
5  paragraphs: text('paragraphs').array().notNull(),
6  content: text('content'),
7});
8 
9export const databaseSchema = {
10  articles,
11};

Now, let’s generate a migration.

1npx drizzle-kit generate --name add-paragraphs-to-articles

When we do that, Drizzle ORM asks us if we want to create the paragraphs column from scratch or if we want to rename the topic column. However, we want to do something a bit more complex.

1-- Step 1: Add the "paragraphs" column as nullable
2ALTER TABLE articles
3ADD COLUMN paragraphs TEXT[];
4 
5-- Step 2: Populate "paragraphs" with values from "topic"
6UPDATE articles
7SET paragraphs = ARRAY[topic];
8 
9-- Step 3: Drop the "topic" column
10ALTER TABLE articles
11DROP COLUMN topic;
12 
13-- Step 4: Ensure "paragraphs" is non-nullable
14ALTER TABLE articles
15ALTER COLUMN paragraphs SET NOT NULL;

• First, we add the paragraphs column as nullable. Thanks to this, it can temporarily accept null values.
• Then, we ensure that the values that were in the topic column are now the first element in the paragraphs array.
• Now, we safely remove the topic column since we preserved its data.
• As the final step, we configure the paragraphs column to be non-nullable to ensure data integrity.

Summary

In this article, we learned what migrations are and how we can use them to change our database in a controlled way. Instead of modifying our database manually, we used the Drizzle Kit to create and run migrations.

Besides the most basic situations, we learned how to deal with more advanced cases, such as renaming existing columns or writing the migrations manually. Learning all of the above gives us a solid understanding of how migrations work and how to use them in a project with NestJS, PostgreSQL, and the Drizzle ORM.