- 1. API with NestJS #1. Controllers, routing and the module structure
- 2. API with NestJS #2. Setting up a PostgreSQL database with TypeORM
- 3. API with NestJS #3. Authenticating users with bcrypt, Passport, JWT, and cookies
- 4. API with NestJS #4. Error handling and data validation
- 5. API with NestJS #5. Serializing the response with interceptors
- 6. API with NestJS #6. Looking into dependency injection and modules
- 7. API with NestJS #7. Creating relationships with Postgres and TypeORM
- 8. API with NestJS #8. Writing unit tests
- 9. API with NestJS #9. Testing services and controllers with integration tests
- 10. API with NestJS #10. Uploading public files to Amazon S3
- 11. API with NestJS #11. Managing private files with Amazon S3
- 12. API with NestJS #12. Introduction to Elasticsearch
- 13. API with NestJS #13. Implementing refresh tokens using JWT
- 14. API with NestJS #14. Improving performance of our Postgres database with indexes
- 15. API with NestJS #15. Defining transactions with PostgreSQL and TypeORM
- 16. API with NestJS #16. Using the array data type with PostgreSQL and TypeORM
- 17. API with NestJS #17. Offset and keyset pagination with PostgreSQL and TypeORM
- 18. API with NestJS #18. Exploring the idea of microservices
- 19. API with NestJS #19. Using RabbitMQ to communicate with microservices
- 20. API with NestJS #20. Communicating with microservices using the gRPC framework
- 21. API with NestJS #21. An introduction to CQRS
- 22. API with NestJS #22. Storing JSON with PostgreSQL and TypeORM
- 23. API with NestJS #23. Implementing in-memory cache to increase the performance
- 24. API with NestJS #24. Cache with Redis. Running the app in a Node.js cluster
- 25. API with NestJS #25. Sending scheduled emails with cron and Nodemailer
- 26. API with NestJS #26. Real-time chat with WebSockets
- 27. API with NestJS #27. Introduction to GraphQL. Queries, mutations, and authentication
- 28. API with NestJS #28. Dealing in the N + 1 problem in GraphQL
- 29. API with NestJS #29. Real-time updates with GraphQL subscriptions
- 30. API with NestJS #30. Scalar types in GraphQL
- 31. API with NestJS #31. Two-factor authentication
- 32. API with NestJS #32. Introduction to Prisma with PostgreSQL
- 33. API with NestJS #33. Managing PostgreSQL relationships with Prisma
- 34. API with NestJS #34. Handling CPU-intensive tasks with queues
- 35. API with NestJS #35. Using server-side sessions instead of JSON Web Tokens
- 36. API with NestJS #36. Introduction to Stripe with React
- 37. API with NestJS #37. Using Stripe to save credit cards for future use
- 38. API with NestJS #38. Setting up recurring payments via subscriptions with Stripe
- 39. API with NestJS #39. Reacting to Stripe events with webhooks
- 40. API with NestJS #40. Confirming the email address
- 41. API with NestJS #41. Verifying phone numbers and sending SMS messages with Twilio
- 42. API with NestJS #42. Authenticating users with Google
- 43. API with NestJS #43. Introduction to MongoDB
- 44. API with NestJS #44. Implementing relationships with MongoDB
- 45. API with NestJS #45. Virtual properties with MongoDB and Mongoose
- 46. API with NestJS #46. Managing transactions with MongoDB and Mongoose
- 47. API with NestJS #47. Implementing pagination with MongoDB and Mongoose
- 48. API with NestJS #48. Definining indexes with MongoDB and Mongoose
- 49. API with NestJS #49. Updating with PUT and PATCH with MongoDB and Mongoose
- 50. API with NestJS #50. Introduction to logging with the built-in logger and TypeORM
- 51. API with NestJS #51. Health checks with Terminus and Datadog
- 52. API with NestJS #52. Generating documentation with Compodoc and JSDoc
- 53. API with NestJS #53. Implementing soft deletes with PostgreSQL and TypeORM
- 54. API with NestJS #54. Storing files inside a PostgreSQL database
- 55. API with NestJS #55. Uploading files to the server
- 56. API with NestJS #56. Authorization with roles and claims
- 57. API with NestJS #57. Composing classes with the mixin pattern
- 58. API with NestJS #58. Using ETag to implement cache and save bandwidth
- 59. API with NestJS #59. Introduction to a monorepo with Lerna and Yarn workspaces
- 60. API with NestJS #60. The OpenAPI specification and Swagger
- 61. API with NestJS #61. Dealing with circular dependencies
- 62. API with NestJS #62. Introduction to MikroORM with PostgreSQL
- 63. API with NestJS #63. Relationships with PostgreSQL and MikroORM
- 64. API with NestJS #64. Transactions with PostgreSQL and MikroORM
- 65. API with NestJS #65. Implementing soft deletes using MikroORM and filters
- 66. API with NestJS #66. Improving PostgreSQL performance with indexes using MikroORM
- 67. API with NestJS #67. Migrating to TypeORM 0.3
- 68. API with NestJS #68. Interacting with the application through REPL
- 69. API with NestJS #69. Database migrations with TypeORM
- 70. API with NestJS #70. Defining dynamic modules
- 71. API with NestJS #71. Introduction to feature flags
- 72. API with NestJS #72. Working with PostgreSQL using raw SQL queries
- 73. API with NestJS #73. One-to-one relationships with raw SQL queries
- 74. API with NestJS #74. Designing many-to-one relationships using raw SQL queries
- 75. API with NestJS #75. Many-to-many relationships using raw SQL queries
- 76. API with NestJS #76. Working with transactions using raw SQL queries
- 77. API with NestJS #77. Offset and keyset pagination with raw SQL queries
- 78. API with NestJS #78. Generating statistics using aggregate functions in raw SQL
- 79. API with NestJS #79. Implementing searching with pattern matching and raw SQL
- 80. API with NestJS #80. Updating entities with PUT and PATCH using raw SQL queries
- 81. API with NestJS #81. Soft deletes with raw SQL queries
- 82. API with NestJS #82. Introduction to indexes with raw SQL queries
- 83. API with NestJS #83. Text search with tsvector and raw SQL
- 84. API with NestJS #84. Implementing filtering using subqueries with raw SQL
- 85. API with NestJS #85. Defining constraints with raw SQL
- 86. API with NestJS #86. Logging with the built-in logger when using raw SQL
- 87. API with NestJS #87. Writing unit tests in a project with raw SQL
- 88. API with NestJS #88. Testing a project with raw SQL using integration tests
- 89. API with NestJS #89. Replacing Express with Fastify
- 90. API with NestJS #90. Using various types of SQL joins
- 91. API with NestJS #91. Dockerizing a NestJS API with Docker Compose
- 92. API with NestJS #92. Increasing the developer experience with Docker Compose
- 93. API with NestJS #93. Deploying a NestJS app with Amazon ECS and RDS
- 94. API with NestJS #94. Deploying multiple instances on AWS with a load balancer
- 95. API with NestJS #95. CI/CD with Amazon ECS and GitHub Actions
- 96. API with NestJS #96. Running unit tests with CI/CD and GitHub Actions
- 97. API with NestJS #97. Introduction to managing logs with Amazon CloudWatch
- 98. API with NestJS #98. Health checks with Terminus and Amazon ECS
- 99. API with NestJS #99. Scaling the number of application instances with Amazon ECS
- 100. API with NestJS #100. The HTTPS protocol with Route 53 and AWS Certificate Manager
- 101. API with NestJS #101. Managing sensitive data using the AWS Secrets Manager
- 102. API with NestJS #102. Writing unit tests with Prisma
- 103. API with NestJS #103. Integration tests with Prisma
- 104. API with NestJS #104. Writing transactions with Prisma
- 105. API with NestJS #105. Implementing soft deletes with Prisma and middleware
- 106. API with NestJS #106. Improving performance through indexes with Prisma
- 107. API with NestJS #107. Offset and keyset pagination with Prisma
- 108. API with NestJS #108. Date and time with Prisma and PostgreSQL
- 109. API with NestJS #109. Arrays with PostgreSQL and Prisma
- 110. API with NestJS #110. Managing JSON data with PostgreSQL and Prisma
- 111. API with NestJS #111. Constraints with PostgreSQL and Prisma
- 112. API with NestJS #112. Serializing the response with Prisma
- 113. API with NestJS #113. Logging with Prisma
- 114. API with NestJS #114. Modifying data using PUT and PATCH methods with Prisma
- 115. API with NestJS #115. Database migrations with Prisma
- 116. API with NestJS #116. REST API versioning
- 117. API with NestJS #117. CORS – Cross-Origin Resource Sharing
- 118. API with NestJS #118. Uploading and streaming videos
- 119. API with NestJS #119. Type-safe SQL queries with Kysely and PostgreSQL
- 120. API with NestJS #120. One-to-one relationships with the Kysely query builder
- 121. API with NestJS #121. Many-to-one relationships with PostgreSQL and Kysely
- 122. API with NestJS #122. Many-to-many relationships with Kysely and PostgreSQL
- 123. API with NestJS #123. SQL transactions with Kysely
- 124. API with NestJS #124. Handling SQL constraints with Kysely
- 125. API with NestJS #125. Offset and keyset pagination with Kysely
- 126. API with NestJS #126. Improving the database performance with indexes and Kysely
- 127. API with NestJS #127. Arrays with PostgreSQL and Kysely
- 128. API with NestJS #128. Managing JSON data with PostgreSQL and Kysely
- 129. API with NestJS #129. Implementing soft deletes with SQL and Kysely
- 130. API with NestJS #130. Avoiding storing sensitive information in API logs
- 131. API with NestJS #131. Unit tests with PostgreSQL and Kysely
- 132. API with NestJS #132. Handling date and time in PostgreSQL with Kysely
- 133. API with NestJS #133. Introducing database normalization with PostgreSQL and Prisma
- 134. API with NestJS #134. Aggregating statistics with PostgreSQL and Prisma
- 135. API with NestJS #135. Referential actions and foreign keys in PostgreSQL with Prisma
- 136. API with NestJS #136. Raw SQL queries with Prisma and PostgreSQL range types
- 137. API with NestJS #137. Recursive relationships with Prisma and PostgreSQL
- 138. API with NestJS #138. Filtering records with Prisma
- 139. API with NestJS #139. Using UUID as primary keys with Prisma and PostgreSQL
- 140. API with NestJS #140. Using multiple PostgreSQL schemas with Prisma
- 141. API with NestJS #141. Getting distinct records with Prisma and PostgreSQL
- 142. API with NestJS #142. A video chat with WebRTC and React
- 143. API with NestJS #143. Optimizing queries with views using PostgreSQL and Kysely
- 144. API with NestJS #144. Creating CLI applications with the Nest Commander
- 145. API with NestJS #145. Securing applications with Helmet
- 146. API with NestJS #146. Polymorphic associations with PostgreSQL and Prisma
- 147. API with NestJS #147. The data types to store money with PostgreSQL and Prisma
- 148. API with NestJS #148. Understanding the injection scopes
- 149. API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL
- 150. API with NestJS #150. One-to-one relationships with the Drizzle ORM
- 151. API with NestJS #151. Implementing many-to-one relationships with Drizzle ORM
- 152. API with NestJS #152. SQL constraints with the Drizzle ORM
- 153. API with NestJS #153. SQL transactions with the Drizzle ORM
- 154. API with NestJS #154. Many-to-many relationships with Drizzle ORM and PostgreSQL
- 155. API with NestJS #155. Offset and keyset pagination with the Drizzle ORM
- 156. API with NestJS #156. Arrays with PostgreSQL and the Drizzle ORM
- 157. API with NestJS #157. Handling JSON data with PostgreSQL and the Drizzle ORM
- 158. API with NestJS #158. Soft deletes with the Drizzle ORM
- 159. API with NestJS #159. Date and time with PostgreSQL and the Drizzle ORM
- 160. API with NestJS #160. Using views with the Drizzle ORM and PostgreSQL
- 161. API with NestJS #161. Generated columns with the Drizzle ORM and PostgreSQL
- 162. API with NestJS #162. Identity columns with the Drizzle ORM and PostgreSQL
- 163. API with NestJS #163. Full-text search with the Drizzle ORM and PostgreSQL
- 164. API with NestJS #164. Improving the performance with indexes using Drizzle ORM
- 165. API with NestJS #165. Time intervals with the Drizzle ORM and PostgreSQL
- 166. API with NestJS #166. Logging with the Drizzle ORM
- 167. API with NestJS #167. Unit tests with the Drizzle ORM
- 168. API with NestJS #168. Integration tests with the Drizzle ORM
- 169. API with NestJS #169. Unique IDs with UUIDs using Drizzle ORM and PostgreSQL
- 170. API with NestJS #170. Polymorphic associations with PostgreSQL and Drizzle ORM
- 171. API with NestJS #171. Recursive relationships with Drizzle ORM and PostgreSQL
- 172. API with NestJS #172. Database normalization with Drizzle ORM and PostgreSQL
- 173. API with NestJS #173. Storing money with Drizzle ORM and PostgreSQL
- 174. API with NestJS #174. Multiple PostgreSQL schemas with Drizzle ORM
- 175. API with NestJS #175. PUT and PATCH requests with PostgreSQL and Drizzle ORM
- 176. API with NestJS #176. Database migrations with the Drizzle ORM
- 177. API with NestJS #177. Response serialization 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.
1 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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
1 2 3 4 5 6 7 8 |
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
1 2 3 4 5 6 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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
1 2 3 4 5 6 7 8 9 10 11 |
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.
1 |
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
1 2 3 4 5 |
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.
1 |
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.
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
1 2 3 4 5 6 7 8 9 10 11 |
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.
1 |
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.
1 2 |
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
1 |
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
1 2 3 4 5 6 7 8 9 10 11 |
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.
1 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 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 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.