API with NestJS #176. Database migrations with the Drizzle ORM

NestJS SQL

This entry is part 176 of 177 in the API with NestJS

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.

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

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

environment-variables.ts

Environment variables are always strings

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

.env

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 file to configure the database connection.

drizzle.config.ts

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

Creating the database schema

Our configuration provides a path to the file that needs to describe all the tables in our database. Let’s start with a simple table that contains articles.

database-schema.ts

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

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

Our first migration

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

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

Running the migration

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

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

What’s interesting, the table is in a separate schema called . 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 column to .

database-schema.ts

Now, we can generate the migration.

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

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

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

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 column, we also remove all the titles stored in our database. Then, we add the new column to existing articles. The  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 to . When we do that, it generates a different migration.

0001_rename-article-title-to-topic.sql

Now, Drizzle Kit renames the column to 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 column to 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

Now, let’s generate a migration.

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

0002_add-paragraphs-to-articles.sql

  1. First, we add the column as nullable. Thanks to this, it can temporarily accept null values.
  2. Then, we ensure that the values that were in the column are now the first element in the array.
  3. Now, we safely remove the column since we preserved its data.
  4. As the final step, we configure the 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.

Series Navigation<< API with NestJS #175. PUT and PATCH requests with PostgreSQL and Drizzle ORMAPI with NestJS #177. Response serialization with the Drizzle ORM >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments