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.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 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. drizzle.config.ts import { defineConfig } from 'drizzle-kit'; import { ConfigService } from '@nestjs/config'; 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 { serial, text, pgTable } 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 migrateWhen 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. database-schema.ts import { serial, text, pgTable } 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-topicWhen 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. 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 database-schema.ts import { serial, text, pgTable } 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-articlesWhen 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; First, we add the paragraphs column as nullable. Thanks to this, it can tempora