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): DynamicModuleEvery option, with the default normalizeConfig fills in (drizzle-crud.module.ts, types in drizzle-crud-config.interface.ts):
dialectRequired"postgresql" | "mysql" | "sqlite"
connectionStringOptionalstring
connection.poolSizeOptionalnumber
connection.idleTimeoutOptionalnumber
connection.sslOptionalboolean | "require" | "allow" | "prefer" | "verify-full" | object
dbOptionalDrizzle instance
schemaOptionalRecord<string, unknown>
defaults.softDeleteOptionalboolean
Defaults to true
defaults.timestampsOptionalboolean
Defaults to true
defaults.pagination.defaultLimitOptionalnumber
Defaults to 20
defaults.pagination.maxLimitOptionalnumber
Defaults to 100
defaults.sortOrderOptional"asc" | "desc"
sql.caseSensitiveOptionalboolean
Defaults to false
sql.useReturningOptionalboolean
Defaults to true on PostgreSQL and SQLite, false on MySQL
sql.jsonSupportOptionalboolean
Defaults to true
sql.enableFullTextSearchOptionalboolean
Defaults to false
hooks.enableGlobalHooksOptionalboolean
Defaults to false
hooks.subscribersOptionalCrudSubscriber[]
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 postgresimport { 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 mysql2DrizzleCrudModule.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:
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
},
});| Option | postgres-js | mysql2 |
|---|---|---|
poolSize | max | connectionLimit |
idleTimeout (seconds) | idle_timeout | idleTimeout, in milliseconds, plus maxIdle one below the pool size |
ssl: true | ssl: true | ssl: {}, which mysql2 verifies by default |
ssl: { ... } | passed as is | passed as is |
ssl: "require" and the other modes | passed as is | startup 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.
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:
| Token | Value |
|---|---|
DRIZZLE_DB | The Drizzle instance, built or passed in |
DRIZZLE_CRUD_CONFIG | The normalized project config: dialect, defaults, sql and hooks.enableGlobalHooks, defaults filled in |
DRIZZLE_CONNECTION | The connection manager that owns shutdown |
CrudEventBus | The 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_atordeleted_at. Setdefaults: { softDelete: false, timestamps: false }, then turn them on per entity inforFeaturewhere the columns exist. - camelCase schema keys such as
deletedAt: timestamp('deleted_at'). The settings name the key in your Drizzle table object, so setsoftDelete.columnand thetimestampskeys 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
Yes, since 3.7.0. With dialect: "mysql" the module builds a mysql2 pool from the URL and ends it on shutdown. Install mysql2 in your app.
No. On dialect: "sqlite" a connectionString is a startup error. Build the Drizzle instance with your driver and pass it as db.
Only when it built the connection from connectionString. A db you pass in is yours to close. Call app.enableShutdownHooks() so a signal triggers the shutdown.
defaultLimit is 20 and maxLimit is 100. Setting one of them keeps the other at its default.
By the entity's defaultSort if set, else by createdAt in the defaults.sortOrder direction if you set one, else by the primary key. The primary key is always the last tiebreaker.
No. Since 3.5.0 it turns on module-level write events on the CrudEventBus. Per-service hooks run whether it is on or off.
Next steps
forRootAsync with ConfigService
Build the same config from injected providers.
forFeature
Bind each service to its table and override defaults per entity.
Config types
The full DrizzleCrudConfig and SqlCrudConfig types.
NestJS Drizzle Swagger docs for lists
ApiCrudList documents NestJS Drizzle list routes in Swagger. It covers paging, sort, cursor, search, relations and every filter column from your allowlists.
Drizzle forRootAsync with ConfigService
NestJS Drizzle forRootAsync with ConfigService builds the database config at startup, from useFactory, useClass or useExisting and any provider you inject.