DEV Community

Anoop Singh
Anoop Singh

Posted on

How to integrate Drizzle ORM with Nest Js

#drizzle #database #postgres #nestjs

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>'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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,
  },
});
Enter fullscreen mode Exit fullscreen mode

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(),
});
Enter fullscreen mode Exit fullscreen mode

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>;
    },
  },
];
Enter fullscreen mode Exit fullscreen mode 
//drizzle.module.ts
import { Module } from '@nestjs/common';
import { DrizzleAsyncProvider, drizzleProvider } from './drizzle.provider';

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

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;
  }
Enter fullscreen mode Exit fullscreen mode

Inside package.json add

"scripts": {
    "generate": "drizzle-kit generate",
    "migrate": "drizzle-kit migrate",
    "studio": "drizzle-kit studio"
  },
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Then,

npm run migrate
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

gin_mitch profile image

Database Setup, JWT Implementation & API

Michael M. -

refifauzan profile image

Make your response consistent in NestJS using Interceptors

Refi Fauzan -

tobidelly profile image

A Step-by-Step Guide to Debugging and Setting Up a Turborepo-Driven Monorepo with Next.js and pnpm

TD! -

drupaladmin profile image

Top 8 MySQL Schema Checks to Boost Database Performance

Roman Agabekov -