NestJS Drizzle module forRoot options

Configure the NestJS Drizzle module with forRoot. Set the connection, dialect, soft delete, timestamps, pagination and SQL defaults once in AppModule.

DrizzleCrudModule.forRoot(config) sets up the NestJS Drizzle module once, in your AppModule. It takes the dialect and either a connection string or a Drizzle instance, plus project-wide defaults for soft delete, timestamps, pagination, SQL behaviour and write events. The module is global, so every feature module can inject the database and register services with forFeature.

Pass exactly one of connectionString or db

With neither, startup fails. When both are set, db wins and connectionString is ignored. connectionString works for PostgreSQL and MySQL. On SQLite, libSQL and Cloudflare D1, build the Drizzle instance yourself and pass it as db.

What does forRoot take?

DrizzleCrudModule.forRoot(config: DrizzleCrudConfig): DynamicModule

Every option, with the default normalizeConfig fills in (drizzle-crud.module.ts, types in drizzle-crud-config.interface.ts):

dialectRequired

"postgresql" | "mysql" | "sqlite"

The database dialect. sqlite covers SQLite, libSQL (Turso) and Cloudflare D1.
connectionStringOptional

string

The module builds the connection (postgres on PostgreSQL, a mysql2 pool on MySQL) and closes it on shutdown. Not for sqlite.
connection.poolSizeOptional

number

Most connections in the pool the module builds: postgres-js max, mysql2 connectionLimit. Unset, the driver default applies.
connection.idleTimeoutOptional

number

Seconds an unused connection stays open: postgres-js idle_timeout, mysql2 idleTimeout (the module converts it to milliseconds and sets maxIdle one below the pool size, so mysql2 actually closes them).
connection.sslOptional

boolean | "require" | "allow" | "prefer" | "verify-full" | object

TLS. true turns it on with certificate checks, an object is handed to the driver as Node tls options. The string modes are postgres-js only; on MySQL they are a startup error.
dbOptional

Drizzle instance

A Drizzle instance you built yourself, any dialect. You own its lifecycle.
schemaOptional

Record<string, unknown>

Your Drizzle schema, passed to drizzle() when the module builds the connection from connectionString.
defaults.softDeleteOptional

boolean

Soft delete for every entity, on a deleted_at column. Turn it on or off per entity in forFeature.

Defaults to true

defaults.timestampsOptional

boolean

Stamp created_at and updated_at on writes. false turns them off unless an entity sets timestamps in forFeature.

Defaults to true

defaults.pagination.defaultLimitOptional

number

Rows per page when the caller sends no limit.

Defaults to 20

defaults.pagination.maxLimitOptional

number

The highest limit a caller may ask for. Set one pagination key and the other keeps its default.

Defaults to 100

defaults.sortOrderOptional

"asc" | "desc"

Opt in. Entities without a defaultSort order findAll by their createdAt column in this direction. Unset, findAll orders by the primary key.
sql.caseSensitiveOptional

boolean

false compares a bare string filter on a text column with lower() on both sides.

Defaults to false

sql.useReturningOptional

boolean

Read the written row back with RETURNING. MySQL has no RETURNING, so the service turns it off there and warns.

Defaults to true on PostgreSQL and SQLite, false on MySQL

sql.jsonSupportOptional

boolean

Deprecated, no effect. Removed in the next major version.

Defaults to true

sql.enableFullTextSearchOptional

boolean

Deprecated, no effect. Full-text search works on PostgreSQL whatever this is set to.

Defaults to false

hooks.enableGlobalHooksOptional

boolean

Every service raises a before and an after event for each write on the module's CrudEventBus.

Defaults to false

hooks.subscribersOptional

CrudSubscriber[]

Subscribed to the bus when it is created. Called only with enableGlobalHooks: true; without it the module logs a warning.

Defaults to []

How do I connect to PostgreSQL?

Pass the connection string and your schema. The module imports the postgres driver, builds the Drizzle instance and closes the connection on shutdown:

pnpm add postgres
app.module.ts
import { Module } from '@nestjs/common';
import { DrizzleCrudModule } from '@avytrix/nestjs-drizzle-crud';
import * as schema from './db/schema';

@Module({
  imports: [
    DrizzleCrudModule.forRoot({
      dialect: 'postgresql',
      connectionString: process.env.DATABASE_URL!,
      schema,
      defaults: {
        softDelete: true,
        timestamps: true,
        pagination: { defaultLimit: 20, maxLimit: 100 },
      },
      sql: { caseSensitive: false },
    }),
  ],
})
export class AppModule {}

The module opens the client with prepare: false, which keeps it working behind a transaction pooler such as PgBouncer. Shutdown runs in onModuleDestroy, so call app.enableShutdownHooks() in main.ts if you want the connection closed when the process gets a signal such as SIGTERM.

How do I connect to MySQL with a connection string?

Since 3.7.0, pass dialect: 'mysql' and a mysql:// URL. The module loads mysql2 when it starts, builds a pool, wraps it with drizzle-orm/mysql2 and ends the pool on shutdown. mysql2 is an optional peer, so install it yourself:

pnpm add mysql2
app.module.ts
DrizzleCrudModule.forRoot({
  dialect: 'mysql',
  connectionString: process.env.DATABASE_URL!, // mysql://user:pass@host:3306/app
  schema,
});

With schema set, the module passes mode: 'default' to Drizzle, which is right for MySQL itself. For PlanetScale, build the instance yourself and pass it as db.

How do I set the pool size, SSL or idle timeout?

Add connection next to connectionString. Each key goes to the driver under its own name, and a key you leave out keeps the driver's default:

app.module.ts
DrizzleCrudModule.forRoot({
  dialect: 'postgresql',
  connectionString: process.env.DATABASE_URL!,
  connection: {
    poolSize: 20,     // postgres-js max, mysql2 connectionLimit
    idleTimeout: 30,  // seconds; mysql2 gets 30000 ms
    ssl: true,        // TLS with certificate checks
  },
});
Optionpostgres-jsmysql2
poolSizemaxconnectionLimit
idleTimeout (seconds)idle_timeoutidleTimeout, in milliseconds, plus maxIdle one below the pool size
ssl: truessl: truessl: {}, which mysql2 verifies by default
ssl: { ... }passed as ispassed as is
ssl: "require" and the other modespassed as isstartup error

mysql2 only closes idle connections when maxIdle is below connectionLimit, and maxIdle defaults to connectionLimit, so before 3.7.2 idleTimeout did nothing on MySQL. The module now sets maxIdle to the pool size minus one (9 with mysql2's default of 10) whenever you give idleTimeout. One consequence of how mysql2 works: when every connection in the pool is idle, it closes one of them within about a second, and the rest after idleTimeout. With poolSize: 1 the single connection closes about a second after it goes idle.

connection only applies to a connection the module builds. With db, configure the driver yourself. Anything else the driver takes, such as a statement timeout, also means building it yourself.

How do I pass my own Drizzle instance?

Build it yourself and pass it as db. Use this for SQLite, libSQL and Cloudflare D1, and on PostgreSQL or MySQL when you want a driver option connection does not cover. SQLite, libSQL and Cloudflare D1 shows the SQLite setups.

app.module.ts
import { Module } from '@nestjs/common';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { DrizzleCrudModule } from '@avytrix/nestjs-drizzle-crud';
import * as schema from './db/schema';

const client = postgres(process.env.DATABASE_URL!, { max: 10 });
const db = drizzle(client, { schema });

@Module({
  imports: [DrizzleCrudModule.forRoot({ dialect: 'postgresql', db })],
})
export class AppModule {}

When you pass db, the module does not close it on shutdown. That is your job, and it lets you share the same pool with migrations, raw queries and other Drizzle code.

What does the module export?

The module is global, so these tokens are injectable anywhere after forRoot:

TokenValue
DRIZZLE_DBThe Drizzle instance, built or passed in
DRIZZLE_CRUD_CONFIGThe normalized project config: dialect, defaults, sql and hooks.enableGlobalHooks, defaults filled in
DRIZZLE_CONNECTIONThe connection manager that owns shutdown
CrudEventBusThe bus for write events; subscribe to it to react to writes

Which defaults should I change first?

Two settings trip people up, and both fail loudly at startup since 3.3.1:

  • Tables without created_at, updated_at or deleted_at. Set defaults: { softDelete: false, timestamps: false }, then turn them on per entity in forFeature where the columns exist.
  • camelCase schema keys such as deletedAt: timestamp('deleted_at'). The settings name the key in your Drizzle table object, so set softDelete.column and the timestamps keys per entity. The startup error names the key to use.

caseSensitive: false applies lower() only to text columns (text, varchar, char). A string filter on an integer, uuid or enum column stays a plain =, so a query string value such as ?author_id=1 works. For pattern matching, use the explicit like and ilike operators.

When should I use forRootAsync instead?

When the config depends on another provider, typically ConfigService after env validation, or a secret you fetch at startup. forRootAsync takes the same DrizzleCrudConfig, returned from a factory.

Common questions

Next steps

Last updated View change history

On this page