Back to Tutorials
8 min read

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…

November 25, 2024

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.

npm 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.

main.ts#
import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import * as Joi from 'joi';
import { ArticlesModule } from './articles/articles.module';
import { DatabaseModule } from './database/database.module';
import { EnvironmentVariables } from './utilities/environment-variables';
 
@Module({
  imports: [
    ArticlesModule,
    DatabaseModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (
        configService: ConfigService<EnvironmentVariables, true>,
      ) => ({
        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'),
      }),
    }),
    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(),
        POSTGRES_IS_SSL_ON: Joi.bool❯ ean().required(),
      }),
    }),
  ],
})
export class AppModule {}

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

environment-variables.ts#
export interface EnvironmentVariables {
  POSTGRES_HOST: string;
  POSTGRES_PORT: string;
  POSTGRES_USER: string;
  POSTGRES_PASSWORD: string;
  POSTGRES_DB: string;
  POSTGRES_IS_SSL_ON: string;
}

Environment variables are always strings

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

.env#
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USER=admin
POSTGRES_PASSWORD=admin
POSTGRES_DB=nestjs
POSTGRES_IS_SSL_ON=false

Configuring Drizzle#

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

drizzle.config.ts#
import { ConfigService } from "@nestjs/config"
import { defineConfig } from "drizzle-kit"
 
import "dotenv/config"
 
import { EnvironmentVariables } from "./src/utilities/environment-variables"
 
const configService = new ConfigService<EnvironmentVariables, true>()
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"),
    ssl: configService.get("POSTGRES_IS_SSL_ON") === "true",
  },
})

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.

database-schema.ts#
import { pgTable, serial, text } from "drizzle-orm/pg-core"
 
export const articles = pgTable("articles", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
})
export const databaseSchema = {
  articles,
}

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.

npx 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.

0000_create-articles-table.sql#
CREATE TABLE IF NOT EXISTS "articles" (
  "id" serial PRIMARY KEY NOT NULL,
  "title" text NOT NULL,
  "content" text
);

Running the migration#

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

npx 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.

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.

database-schema.ts#
import { pgTable, serial, text } from "drizzle-orm/pg-core"
 
export const articles = pgTable("articles", {
  id: serial("id").primaryKey(),
  topic: text("topic").notNull(),
  content: text("content"),
})
export const databaseSchema = {
  articles,
}

Now, we can generate the migration.

npx 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.

ALTER TABLE "articles" ADD COLUMN "topic" text NOT NULL;--> statement-breakpoint
ALTER 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.

0001_rename-article-title-to-topic.sql#
ALTER 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.

database-schema.ts#
import { pgTable, serial, text } from "drizzle-orm/pg-core"
 
export const articles = pgTable("articles", {
  id: serial("id").primaryKey(),
  paragraphs: text("paragraphs").array().notNull(),
  content: text("content"),
})
export const databaseSchema = {
  articles,
}

Now, let’s generate a migration.

npx 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.

0002_add-paragraphs-to-articles.sql#
-- Step 1: Add the "paragraphs" column as nullable
ALTER TABLE articles
ADD COLUMN paragraphs TEXT[];
 
-- Step 2: Populate "paragraphs" with values from "topic"
UPDATE articles
SET paragraphs = ARRAY[topic];
 
-- Step 3: Drop the "topic" column
ALTER TABLE articles
DROP COLUMN topic;
 
-- Step 4: Ensure "paragraphs" is non-nullable
ALTER TABLE articles
ALTER COLUMN paragraphs SET NOT NULL;
  1. First, we add the paragraphs column as nullable. Thanks to this, it can temporarily accept null values.
  2. Then, we ensure that the values that were in the topic column are now the first element in the paragraphs array.
  3. Now, we safely remove the topic column since we preserved its data.
  4. 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.

Database migrations with the Drizzle ORM | NestJS.io