Implementing Prisma Migrations and DrizzleORM Query in Action

Introduction

Effective database administration is essential in contemporary web development, and pairing Prisma Migrations with DrizzleORM offers an enticing solution. Prisma Migrations streamlines the evolution of database schemas, ensuring seamless transitions across application versions. With its user-friendly CLI and straightforward syntax, managing migrations becomes intuitive, significantly reducing the complexity of database maintenance. Prisma Migrations and DrizzleORM provide a powerful combination, simplifying the process of database schema evolution and data querying.

In tandem, DrizzleORM offers a smooth interface for interacting with databases, boasting an intuitive API and robust querying capabilities. Its lightweight and adaptable design makes it an attractive option for developers seeking an efficient ORM solution. Prisma Migrations and DrizzleORM empower developers to prioritize application development and iteration over wrestling with database intricacies. This blog will explore how this powerful combination simplifies database tasks, boosting developer productivity and enhancing code maintainability.

Prisma vs Drizzle

Here's a comparison of Prisma and Drizzle in a table:

Both Prisma and Drizzle excel in their respective areas, offering developers powerful tools to manage databases effectively in TypeScript projects. Prisma focuses on scalability and efficiency, providing a comprehensive suite of features for database management, while Drizzle emphasizes simplicity and performance, offering a lightweight and flexible solution for TypeScript-centric projects. The choice between them depends on the development team's specific requirements and priorities.

Setting Up the NestJS Project

First, create a NestJS project and install the necessary dependencies for Prisma and DrizzleORM.

npm install -g @nestjs/cli
nest new nest-prisma-drizzle-blog
cd nest-prisma-drizzle-blog
npm install @prisma/cli prisma drizzle-orm postgres

Configuring Prisma

Update the schema in prisma/schema.prisma to define models for Authors and Posts.

Initialize Prisma in your project and define your database schema.

npx prisma init

Update prisma/schema.prisma to define your models, for example:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Author {
  id    String @id @default(cuid())
  name  String
  email String @unique
  posts Post[]
}

model Post {
  id        String  @id @default(cuid())
  title     String
  content   String?
  published Boolean @default(false)
  authorId  String
  author    Author  @relation(fields: [authorId], references: [id])
}

Running Prisma Migrations

Generate and apply migrations to synchronize your database schema with your Prisma schema.

npx prisma migrate dev

Introspection with Drizzle Kit

Create a drizzle.config.ts file and run introspection to generate DrizzleORM models.

import { defineConfig } from 'drizzle-orm';

export default defineConfig({
  schema: 'public',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
});

Adding Records with Drizzle Studio

Drizzle Studio is a convenient way to interact with your database and add records without manually writing code. It provides a user-friendly interface for performing CRUD (Create, Read, Update, Delete) operations on your database tables.

How to Use Drizzle Studio

1. Access the Interface: Once Drizzle Studio is launched, it will open a browser window with the Drizzle Studio interface.
2. Connect to Database: Drizzle Studio will prompt you to connect to your database. Enter the necessary connection details, such as the database URL.
3. Explore Tables: After connecting, you'll see a list of tables in your database. You can select a table to view its contents and perform operations.
4. Perform CRUD Operations: Within Drizzle Studio, you can add new records, view existing records, update records, and delete records. Simply click on the relevant buttons or fields to perform these actions.
5. Save Changes: Drizzle Studio provides options to save your changes to the database once you've made changes.

Launch Drizzle Studio

Run the following command in your terminal to launch Drizzle Studio:

pnpm drizzle-kit studio

Examples

Let's say we have a users the table in our database with columns id, name, and email. Here's how you can use Drizzle Studio to add records:

1. Launch Drizzle Studio: Run pnpm drizzle-kit studio in your terminal and connect to your database.
2. Select Table: Choose the users table from the list of tables displayed.
3. Add Record: Click the "Add Record" button to add a new record.
4. Enter Data: Enter values for the name and email fields of the new user record.
5. Save Changes: Click the "Save" button to save the new record to the database.

Drizzle Studio provides a visual and intuitive way to manage database records, making it easy for developers to interact with their database without manually writing SQL queries or code.

Querying with DrizzleORM in NestJS

Now, let's query our database using DrizzleORM in a NestJS service.

import { Injectable } from '@nestjs/common';
import { drizzle } from 'drizzle-orm/postgres';
import * as schema from './drizzle-schema';

@Injectable()
export class PostsService {
  private readonly db = drizzle(process.env.DATABASE_URL!, { schema });

  async findAll(): Promise<Post[]> {
    return await this.db.query.post.findMany();
  }

  async findById(id: number): Promise<Post | null> {
    return await this.db.query.post.findOne({ where: { id } });
  }

  async create(data: CreatePostDto): Promise<Post> {
    return await this.db.command.post.create({ data });
  }

  async update(id: number, data: UpdatePostDto): Promise<Post | null> {
    return await this.db.command.post.update({ where: { id }, data });
  }

  async delete(id: number): Promise<void> {
    await this.db.command.post.delete({ where: { id } });
  }
}

Running the Application

Finally, run the application and navigate the homepage to view the authors.

pnpm dev

Conclusion

In conclusion, the symbiotic relationship between Prisma Migrations and DrizzleORM in the context of NestJS represents a powerful solution for modern database management. Prisma Migrations seamlessly handles schema evolution, ensuring smooth transitions across application versions with its user-friendly CLI and intuitive syntax, while DrizzleORM offers a sleek interface for efficient querying, boasting robust capabilities and adaptability. Together, they empower developers to focus on application development and iteration by simplifying database tasks, boosting productivity, and enhancing code maintainability. This combination streamlines database management in NestJS applications, leading to more scalable and robust software solutions.

Thank you for reading this article. Catch you in the next one!