How to use Drizzle ORM with NestJS

In this tutorial we will learn how to use Drizzle ORM with NestJS to connect with all the major databases and serverless database providers such as PlanetScale, Turso.

To set up Drizzle ORM in NestJS with each type of databases, follow these steps:

1. Install Dependencies

Install Drizzle ORM, driver of the database that you wish to use and its NestJS integration module using your favorite package manager.

PlanetScale

npm install drizzle-orm
npm install @planetscale/database
npm install @knaadh/nestjs-drizzle-planetscale

Turso

npm install drizzle-orm
npm install @libsql/client
npm install @knaadh/nestjs-drizzle-turso

MySQL2

npm install drizzle-orm
npm install mysql2
npm install @knaadh/nestjs-drizzle-mysql2

Node-Postgres

npm install drizzle-orm
npm install pg
npm install @knaadh/nestjs-drizzle-pg

Better-SQLite3

npm install drizzle-orm
npm install better-sqlite3
npm install @knaadh/nestjs-drizzle-better-sqlite3

2. Create SQL Schema File

In Drizzle ORM, you need to define your database schema in TypeScript. You can declare your schema in a single schema.ts file or group them logically in multiple files. The schema declaration includes tables, indexes, constraints, foreign keys, and enums as explained here. Here’s an example of declaring tables in a schema.ts file for each of the database types.

MySQL

import { mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core';

export const books = mysqlTable('Books', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
});

export const authors = mysqlTable('Authors', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
});

PostgreSQL

import { pgTable, serial, varchar } from 'drizzle-orm/pg-core';

export const books = pgTable('Books', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
});

export const authors = pgTable('Authors', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
});

SQLite

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const books = sqliteTable('Books', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

export const authors = sqliteTable('Authors', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

3. Configure the Module:

Import the integration module of the respective database driver into the root AppModule and configure it with your credentials, schema file and injection tag as shown in the sample code below for each of the drivers.

PlanetScale

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import * as schema from '../db/schema';
import { DrizzlePlanetScaleModule } from '@knaadh/nestjs-drizzle-planetscale';

@Module({
  imports: [
    DrizzlePlanetScaleModule.register({
      tag: 'DB_PROD',
      planetscale: {
        config: {
          username: 'PLANETSCALE_USERNAME',
          password: 'PLANETSCALE_PASSWORD',
          host: 'PLANETSCALE_HOST',
        },
      },
      config: { schema: { ...schema } },
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Turso

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import * as schema from '../db/schema';
import { DrizzleTursoModule } from '@knaadh/nestjs-drizzle-turso';

@Module({
  imports: [
    DrizzleTursoModule.register({
      tag: 'DB_PROD',
      turso: {
        config: {
          url: 'DATABASE_URL',
          authToken: 'DATABASE_AUTH_TOKEN',
        },
      },
      config: { schema: { ...schema } },
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

MySQL2

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import * as schema from '../db/schema';
import { DrizzleMySqlModule } from '@knaadh/nestjs-drizzle-mysql2';

@Module({
  imports: [
    DrizzleMySqlModule.register({
      tag: 'DB_PROD',
      mysql: {
        connection: 'client',
        config: {
          host: DATABASE_HOST,
          user: DATABASE_USER,
          password : DATABASE_PASSWORD,
          database: DATABASE_NAME,
        },
      },
      config: { schema: { ...schema }, mode: 'default' },
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Node-Postgres

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import * as schema from '../db/schema';
import { DrizzlePGModule } from '@knaadh/nestjs-drizzle-pg';

@Module({
  imports: [
    DrizzlePGModule.register({
      tag: 'DB_PROD',
      pg: {
        connection: 'client',
        config: {
          connectionString: DATABASE_CONNECTION_URL,
        },
      },
      config: { schema: { ...schema } },
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Better-SQLite3

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import * as schema from '../db/schema';
import { DrizzleBetterSQLiteModule } from '@knaadh/nestjs-drizzle-better-sqlite3';

@Module({
  imports: [
    DrizzleBetterSQLiteModule.register({
      tag: 'DB_PROD',
      sqlite3: {
        filename: DATABASE_FILE,
      },
      config: { schema: { ...schema } },
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

4. Inject Drizzle Service:

You can inject the Drizzle instance anywhere using the tag specified in the module configuration of its respective database driver.

PlanetScale

import { Inject, Injectable } from '@nestjs/common';
import * as schema from '../db/schema';
import { PlanetScaleDatabase } from 'drizzle-orm/planetscale-serverless';
@Injectable()
export class AppService {
  constructor(
    @Inject('DB_PROD') private drizzleProd: PlanetScaleDatabase<typeof schema>
  ) {}
  async getData() {
    const authors = await this.drizzleProd.query.authors.findMany();
    return authors;
  }
}

Turso

import { Inject, Injectable } from '@nestjs/common';
import * as schema from '../db/schema';
import { LibSQLDatabase } from 'drizzle-orm/libsql';
@Injectable()
export class AppService {
  constructor(
    @Inject('DB_PROD') private drizzleProd: LibSQLDatabase<typeof schema>
  ) {}
  async getData() {
    const authors = await this.drizzleProd.query.authors.findMany();
    return authors;
  }
}

MySQL2

import { Inject, Injectable } from '@nestjs/common';
import * as schema from '../db/schema';
import { MySql2Database } from 'drizzle-orm/mysql2';
@Injectable()
export class AppService {
  constructor(
    @Inject('DB_PROD') private drizzleProd: MySql2Database<typeof schema>
  ) {}
  async getData() {
    const authors = await this.drizzleProd.query.authors.findMany();
    return authors;
  }
}

Node-Postgres

import { Inject, Injectable } from '@nestjs/common';
import * as schema from '../db/schema';
import { NodePgDatabase } from 'drizzle-orm/node-postgres';
@Injectable()
export class AppService {
  constructor(
    @Inject('DB_PROD') private drizzleProd: NodePgDatabase<typeof schema>
  ) {}
  async getData() {
    const authors = await this.drizzleProd.query.authors.findMany();
    return authors;
  }
}

Better-SQLite3

import { Inject, Injectable } from '@nestjs/common';
import * as schema from '../db/schema';
import { BetterSQLite3Database } from 'drizzle-orm/better-sqlite3';
@Injectable()
export class AppService {
  constructor(
    @Inject('DB_PROD') private drizzleProd: BetterSQLite3Database<typeof schema>
  ) {}
  async getData() {
    const authors = await this.drizzleProd.query.authors.findMany();
    return authors;
  }
}

Note: You can connect as many databases as you want, provided the tag should always be different for each database module.