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

vst profile image

Archiving PostgreSQL Backups on NixOS

Vehbi Sinan Tunalioglu -

ahmed_hyperov profile image

The Best way to configure environment variables and multi environments in Nest.js

Ahmed Nabil -

jehzucco profile image

Configurando Spring Boot com PostgreSQL no ambiente Linux: Passo a passo

Jéssica Zucco -

vaishak10 profile image

Using TTL Indexes in MongoDB to Automate Data Cleanup

Vaishak K -