How to integrate Drizzle ORM with Nest Js

This tutorial is for how to integrate Dirzzle orm with Nest js. The example uses postgres as connection database

Add Database url to env file -

# DATABASE_URL='postgresql://<host>:<password>@localhost:5432/<database>'

If you encounter some problems regarding connecting to database use these -

# DATABASE_URL='postgresql://<host>:<password>@localhost:5432/<database>?sslmode=require&connect_timeout=10&pool_timeout=15

Create drizzle.config.ts file inside root folder

import { defineConfig } from 'drizzle-kit';
export default defineConfig({
  dialect: 'postgresql',
  schema: './src/schema.ts',
  out: './drizzle',
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
});

Create schema file inside root directory -

//schema.ts
import {
  pgTable,
  pgEnum,
  serial,
  timestamp,
  text,
  integer,
  primaryKey,
  boolean,
  uuid,
} from 'drizzle-orm/pg-core';
import { InferSelectModel } from 'drizzle-orm';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, {
      onDelete: 'cascade',
      onUpdate: 'no action',
    }),
  content: text('content'),
  mediaUrl: text('media_url'),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

Create a drizzle.provider.ts file inside drizzle repository folder-

//drizzle.provider.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from '../../schema';
import { ConfigService } from '@nestjs/config';
import { NodePgDatabase } from 'drizzle-orm/node-postgres';

export const DrizzleAsyncProvider = 'DrizzleAsyncProvider';

export const drizzleProvider = [
  {
    provide: DrizzleAsyncProvider,
    inject: [ConfigService],
    useFactory: async (configService: ConfigService) => {
      const connectionString = configService.get<string>('DATABASE_URL');
      const pool = new Pool({
        connectionString,
      });

      return drizzle(pool, { schema }) as NodePgDatabase<typeof schema>;
    },
  },
];

//drizzle.module.ts
import { Module } from '@nestjs/common';
import { DrizzleAsyncProvider, drizzleProvider } from './drizzle.provider';

@Module({
  providers: [...drizzleProvider],
  exports: [DrizzleAsyncProvider],
})
export class DrizzleModule {}

How to use it ?

//post.service.ts
import { Injectable, NotFoundException } from '@nestjs/common';
import { Inject } from '@nestjs/common';
import { NodePgDatabase } from 'drizzle-orm/node-postgres';
import * as sc from '../../schema';
import { DrizzleAsyncProvider } from 'src/core-modules/drizzle/drizzle.provider';
import * as q from 'drizzle-orm';

@Injectable()
export class PostService {
  constructor(
    @Inject(DrizzleAsyncProvider)
    private db: NodePgDatabase<typeof sc>,
    private cloudinaryService: CloudinaryService,
  ) {}

 async createPost(userId: number, createPostDto: CreatePostDto) {
    const [post] = await this.db
      .insert(sc.posts)
      .values({ authorId: userId, ...createPostDto })
      .returning();
    const [newpost] = await this.selectPostsWithAuthor(userId).where(
      q.eq(sc.posts.id, post.id),
    );
    return newpost;
  }

Inside package.json add

"scripts": {
    "generate": "drizzle-kit generate",
    "migrate": "drizzle-kit migrate",
    "studio": "drizzle-kit studio"
  },

Generate - create migration folder containing SQL code for the schema file you created

Migrate - apply those migrations

Studio - connect with the drizzle studio

Now run

npm run generate

Then,

npm run migrate