@sklv-labs/ts-nestjs-database

A comprehensive Drizzle ORM integration module for NestJS with transaction management, error handling, and logging support.

Features

• 🎯 Type-Safe - Full TypeScript support with comprehensive type definitions and schema typing
• 🚀 Drizzle ORM v0 - Latest stable version with full feature support
• 🔄 Transaction Management - CLS-based transaction propagation via @nestjs-cls/transactional
• 🛠️ Multi-Dialect Support - PostgreSQL, MySQL, SQLite, and more
• 📦 Error Integration - Database errors extend BaseError from @sklv-labs/ts-nestjs-error
• 🔌 CLS Integration - Automatic request-scoped context via @sklv-labs/ts-nestjs-cls
• 📊 Logging Ready - Works seamlessly with @sklv-labs/ts-nestjs-logger
• 📝 Query Logging - Automatic query logging with CLS context and slow query detection

Table of Contents

1. Installation
2. Quick Start
3. Module Setup
4. Service Patterns
5. Transaction Management
6. Schema Patterns and Type Safety
7. Error Handling
8. Logging Integration
9. Advanced Patterns
10. Configuration Options
11. API Reference
12. Best Practices

Installation

npm install @sklv-labs/ts-nestjs-database

Peer Dependencies

This package requires the following peer dependencies:

npm install \
  @nestjs/common@^11.1.11 \
  @nestjs/core@^11.1.11 \
  drizzle-orm@^0 \
  @nestjs-cls/transactional@^3.1.0 \
  @nestjs-cls/transactional-adapter-drizzle-orm@^1.2.0 \
  @sklv-labs/ts-nestjs-error@^0.1.0 \
  @sklv-labs/ts-nestjs-logger@^0.1.0 \
  @sklv-labs/ts-nestjs-cls@^0.1.0 \
  nestjs-cls@^6.1.0

Dialect-Specific Dependencies (Optional)

Important: You only need to install the driver for your chosen database dialect. These are optional peer dependencies.

PostgreSQL (node-postgres):

npm install pg @types/pg

PostgreSQL (postgres-js):

npm install postgres

MySQL:

npm install mysql2 @types/mysql2

SQLite:

npm install better-sqlite3 @types/better-sqlite3

If you don't install the required driver, you'll get a helpful error message when initializing the module.

Note: This package requires Node.js 24 LTS or higher.

Package Exports

This package uses subpath exports for better organization:

• Main package (@sklv-labs/ts-nestjs-database) - Exports database errors
• Drizzle subpath (@sklv-labs/ts-nestjs-database/drizzle) - Exports all Drizzle-related functionality

// Errors from main package
import { DatabaseError, DatabaseConnectionError } from '@sklv-labs/ts-nestjs-database';

// Drizzle from subpath
import { 
  DrizzleModule, 
  InjectDrizzle, 
  DrizzleDatabase,
  createDrizzleTransactionPlugin 
} from '@sklv-labs/ts-nestjs-database/drizzle';

Quick Start

// app.module.ts
import { Module } from '@nestjs/common';
import { ClsModule } from '@sklv-labs/ts-nestjs-cls';
import { LoggerModule } from '@sklv-labs/ts-nestjs-logger';
import { DrizzleModule } from '@sklv-labs/ts-nestjs-database/drizzle';

@Module({
  imports: [
    ClsModule.forRoot({
      plugins: [DrizzleModule.getTransactionPlugin()],
    }), // MUST be imported first
    LoggerModule.forRoot(),
    DrizzleModule.forRoot({
      dialect: 'postgresql',
      connection: {
        connectionString: process.env.DATABASE_URL,
      },
    }),
  ],
})
export class AppModule {}

With Async Configuration

// app.module.ts
import { Module } from '@nestjs/common';
import { ConfigModule, ConfigService } from '@nestjs/config';
import { ClsModule } from '@sklv-labs/ts-nestjs-cls';
import { LoggerModule } from '@sklv-labs/ts-nestjs-logger';
import { DrizzleModule } from '@sklv-labs/ts-nestjs-database/drizzle';

@Module({
  imports: [
    ConfigModule.forRoot(),
    ClsModule.forRoot({
      plugins: [DrizzleModule.getTransactionPlugin()],
    }),
    LoggerModule.forRoot(),
    DrizzleModule.forRootAsync({
      imports: [ConfigModule],
      inject: [ConfigService],
      useFactory: (config: ConfigService) => ({
        dialect: 'postgresql',
        connection: {
          connectionString: config.get('DATABASE_URL'),
          pool: {
            max: 20,
            min: 5,
          },
        },
      }),
    }),
  ],
})
export class AppModule {}

Module Setup

Basic Setup

// app.module.ts
import { Module } from '@nestjs/common';
import { ClsModule } from '@sklv-labs/ts-nestjs-cls';
import { LoggerModule } from '@sklv-labs/ts-nestjs-logger';
import { DrizzleModule } from '@sklv-labs/ts-nestjs-database/drizzle';

@Module({
  imports: [
    ClsModule.forRoot({
      plugins: [DrizzleModule.getTransactionPlugin()],
    }), // MUST be imported first
    LoggerModule.forRoot(),
    DrizzleModule.forRoot({
      dialect: 'postgresql',
      connection: {
        connectionString: process.env.DATABASE_URL,
      },
    }),
  ],
})
export class AppModule {}

With Schema

import { Module } from '@nestjs/common';
import { ClsModule } from '@sklv-labs/ts-nestjs-cls';
import { DrizzleModule } from '@sklv-labs/ts-nestjs-database/drizzle';
import * as schema from './schema';

@Module({
  imports: [
    ClsModule.forRoot({
      plugins: [DrizzleModule.getTransactionPlugin()],
    }),
    DrizzleModule.forRoot({
      dialect: 'postgresql',
      connection: process.env.DATABASE_URL,
      schema, // Pass your schema object
    }),
  ],
})
export class AppModule {}

With Custom Factory

import { Module } from '@nestjs/common';
import { ClsModule } from '@sklv-labs/ts-nestjs-cls';
import { DrizzleModule } from '@sklv-labs/ts-nestjs-database/drizzle';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

@Module({
  imports: [
    ClsModule.forRoot({
      plugins: [DrizzleModule.getTransactionPlugin()],
    }),
    DrizzleModule.forRoot({
      drizzleFactory: () => {
        const pool = new Pool({
          connectionString: process.env.DATABASE_URL,
          max: 20,
        });
        return drizzle(pool, { schema });
      },
    }),
  ],
})
export class AppModule {}

With Query Logging

import { Module } from '@nestjs/common';
import { ClsModule } from '@sklv-labs/ts-nestjs-cls';
import { LoggerModule } from '@sklv-labs/ts-nestjs-logger';
import { DrizzleModule } from '@sklv-labs/ts-nestjs-database/drizzle';

@Module({
  imports: [
    ClsModule.forRoot({
      plugins: [DrizzleModule.getTransactionPlugin()],
    }),
    LoggerModule.forRoot(), // Required for query logging
    DrizzleModule.forRoot({
      dialect: 'postgresql',
      connection: {
        connectionString: process.env.DATABASE_URL,
      },
      logging: {
        logQueries: true,
        logParams: false, // Don't log params in production (security)
        slowQueryThreshold: 1000, // Log queries > 1s as warnings
        queryLogLevel: 'debug',
      },
    }),
  ],
})
export class AppModule {}

Service Patterns

Basic Service

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { eq } from 'drizzle-orm';
import { users } from './schema';

@Injectable()
export class UserService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  async findById(id: number) {
    return this.db.query.users.findFirst({
      where: eq(users.id, id),
    });
  }

  async findByEmail(email: string) {
    return this.db.query.users.findFirst({
      where: eq(users.email, email),
});
  }

  async create(data: { name: string; email: string }) {
    const [created] = await this.db
      .insert(users)
      .values(data)
      .returning();
    return created;
  }

  async update(id: number, data: Partial<{ name: string; email: string }>) {
    const [updated] = await this.db
      .update(users)
      .set(data)
      .where(eq(users.id, id))
      .returning();
    return updated;
  }

  async delete(id: number) {
    await this.db
      .delete(users)
      .where(eq(users.id, id));
  }
}

Service with Relations

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { eq } from 'drizzle-orm';
import { users, posts } from './schema';

@Injectable()
export class UserService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  async findByIdWithPosts(id: number) {
    return this.db.query.users.findFirst({
      where: eq(users.id, id),
      with: {
        posts: true, // Load related posts
  },
});
  }
}

Transaction Management

Basic Transaction Usage

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase, Transactional } from '@sklv-labs/ts-nestjs-database/drizzle';
import { users, orders } from './schema';

@Injectable()
export class OrderService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  // Simple transaction - all operations share the same transaction
  @Transactional()
  async createOrderWithUser(userData: any, orderData: any) {
    // All these operations are in the same transaction
    const [user] = await this.db.insert(users).values(userData).returning();
    const [order] = await this.db
      .insert(orders)
      .values({ ...orderData, userId: user.id })
      .returning();

    // If any operation fails, entire transaction rolls back
    return { user, order };
  }
}

Transaction Propagation Across Services

When using @Transactional(), the transaction context is automatically shared across all services in the same request. This means you don't need to pass transaction objects explicitly:

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase, Transactional } from '@sklv-labs/ts-nestjs-database/drizzle';
import { sql, eq } from 'drizzle-orm';
import { accounts, orders } from './schema';

@Injectable()
export class UsersService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  // This method participates in the transaction if called from a @Transactional method
  async updateBalance(userId: string, amount: number) {
    // Automatically uses the transaction from the calling method
    await this.db
      .update(accounts)
      .set({ balance: sql`balance + ${amount}` })
      .where(eq(accounts.id, userId));
  }
}

@Injectable()
export class OrderService {
  constructor(
    @InjectDrizzle() private readonly db: DrizzleDatabase,
    private readonly usersService: UsersService
  ) {}

  @Transactional()
  async createOrder(orderData: any, userId: string, amount: number) {
    // All operations in these services share the same transaction
    await this.usersService.updateBalance(userId, -amount);
    const [order] = await this.db.insert(orders).values(orderData).returning();
    
    // If any operation fails, everything rolls back
    return order;
  }
}

Transaction Isolation Levels

You can specify different isolation levels for transactions to control how concurrent transactions interact:

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase, Transactional } from '@sklv-labs/ts-nestjs-database/drizzle';

@Injectable()
export class PaymentService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  // Read Committed - Default in PostgreSQL, prevents dirty reads
  @Transactional({
    isolationLevel: 'read committed',
  })
  async standardOperation() {
    // Most common isolation level
    // Prevents reading uncommitted data from other transactions
  }

  // Repeatable Read - Prevents non-repeatable reads
  @Transactional({
    isolationLevel: 'repeatable read',
  })
  async consistentRead() {
    // Ensures that if you read a row twice, you get the same data
    // Prevents phantom reads in most cases
  }

  // Serializable - Highest isolation, prevents all anomalies
  @Transactional({
    isolationLevel: 'serializable',
  })
  async criticalOperation() {
    // Most strict - transactions execute as if they were serial
    // Best for critical financial operations
  }
}

Isolation Level Comparison:

| Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads | Performance | |-------|-------------|---------------------|---------------|-------------| | Read Uncommitted | ✅ Possible | ✅ Possible | ✅ Possible | Fastest | | Read Committed | ❌ Prevented | ✅ Possible | ✅ Possible | Fast | | Repeatable Read | ❌ Prevented | ❌ Prevented | ⚠️ Mostly Prevented | Medium | | Serializable | ❌ Prevented | ❌ Prevented | ❌ Prevented | Slowest |

Nested Transactions (Savepoints)

When a @Transactional() method calls another @Transactional() method, the inner transaction creates a savepoint instead of a new transaction:

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase, Transactional } from '@sklv-labs/ts-nestjs-database/drizzle';
import { orders } from './schema';

@Injectable()
export class OrderService {
  constructor(
    @InjectDrizzle() private readonly db: DrizzleDatabase,
    private readonly paymentService: PaymentService
  ) {}

  @Transactional()
  async createOrder(orderData: any) {
    // Outer transaction
    const [order] = await this.db.insert(orders).values(orderData).returning();

    try {
      // This creates a savepoint (nested transaction)
      await this.paymentService.processPayment(order.id, amount);
} catch (error) {
      // If payment fails, only the payment operations roll back
      // The order creation remains committed
      throw error;
    }

    return order;
  }
}

@Injectable()
export class PaymentService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  @Transactional()
  async processPayment(orderId: string, amount: number) {
    // This runs in a savepoint (nested transaction)
    // If this fails, only these operations roll back
    await this.db.insert(payments).values({ orderId, amount });
  }
}

Schema Patterns and Type Safety

Organizing Schemas Across Domains

When building large applications, it's common to split schemas across multiple domain files. Here's how to properly combine them:

Domain-Based Schema Files

// schemas/users.schema.ts
import { pgTable, uuid, varchar, timestamp, text } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 255 }).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export const userProfiles = pgTable('user_profiles', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  bio: text('bio'),
  avatarUrl: varchar('avatar_url', { length: 500 }),
});

// Relations
export const usersRelations = relations(users, ({ one, many }) => ({
  profile: one(userProfiles, {
    fields: [users.id],
    references: [userProfiles.userId],
  }),
  posts: many(posts), // Assuming posts schema exists
}));

export const userProfilesRelations = relations(userProfiles, ({ one }) => ({
  user: one(users, {
    fields: [userProfiles.userId],
    references: [users.id],
  }),
}));

// schemas/payments.schema.ts
import { pgTable, uuid, decimal, timestamp, varchar } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { users } from './users.schema';

export const payments = pgTable('payments', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  amount: decimal('amount', { precision: 10, scale: 2 }).notNull(),
  currency: varchar('currency', { length: 3 }).notNull().default('USD'),
  status: varchar('status', { length: 50 }).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const transactions = pgTable('transactions', {
  id: uuid('id').primaryKey().defaultRandom(),
  paymentId: uuid('payment_id').references(() => payments.id).notNull(),
  type: varchar('type', { length: 50 }).notNull(),
  amount: decimal('amount', { precision: 10, scale: 2 }).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

// Relations
export const paymentsRelations = relations(payments, ({ one, many }) => ({
  user: one(users, {
    fields: [payments.userId],
    references: [users.id],
  }),
  transactions: many(transactions),
}));

export const transactionsRelations = relations(transactions, ({ one }) => ({
  payment: one(payments, {
    fields: [transactions.paymentId],
    references: [payments.id],
  }),
}));

Combining Schemas

// schemas/index.ts
import * as usersSchema from './users.schema';
import * as paymentsSchema from './payments.schema';

// Combine all schemas into a single object
export const schema = {
  ...usersSchema,
  ...paymentsSchema,
};

// Export the schema type for use with DrizzleDatabase
export type DatabaseSchema = typeof schema;

// Export individual tables for convenience
export const { users, userProfiles } = usersSchema;
export const { payments, transactions } = paymentsSchema;

Using combineSchemas Helper (Alternative)

// schemas/index.ts
import { combineSchemas } from '@sklv-labs/ts-nestjs-database/drizzle';
import * as usersSchema from './users.schema';
import * as paymentsSchema from './payments.schema';

// Combine schemas using the helper function
export const schema = combineSchemas([usersSchema, paymentsSchema]);

// Export the schema type
export type DatabaseSchema = typeof schema;

Typed Database Instance

Module Configuration

// app.module.ts
import { Module } from '@nestjs/common';
import { ClsModule } from '@sklv-labs/ts-nestjs-cls';
import { DrizzleModule } from '@sklv-labs/ts-nestjs-database/drizzle';
import { schema } from './schemas';

@Module({
  imports: [
    ClsModule.forRoot({
      plugins: [DrizzleModule.getTransactionPlugin()],
    }),
    DrizzleModule.forRoot({
      dialect: 'postgresql',
      connection: {
        connectionString: process.env.DATABASE_URL,
      },
      schema, // Pass the combined schema
    }),
  ],
})
export class AppModule {}

Typed Service Injection

You have two options for typing your database instance:

Option 1: Using the Union Type (Recommended for Multi-Dialect Support)

The DrizzleDatabase<TSchema> union type works well when you need to support multiple database dialects or want maximum flexibility:

// users.service.ts
import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { eq } from 'drizzle-orm';
import { users, userProfiles } from './schemas';
import type { DatabaseSchema } from './schemas';

@Injectable()
export class UsersService {
  constructor(
    @InjectDrizzle() private readonly db: DrizzleDatabase<DatabaseSchema>
  ) {}

  async findById(id: string) {
    // Full type safety with relations
    return this.db.query.users.findFirst({
      where: eq(users.id, id),
      with: {
        profile: true, // TypeScript knows about this relation
        posts: true,   // TypeScript knows about this relation
      },
    });
  }

  async findWithProfile(id: string) {
    // Type-safe relation queries
    return this.db.query.users.findFirst({
      where: eq(users.id, id),
      with: {
        profile: true,
      },
    });
  }
}

Option 2: Using Explicit Database Types (Recommended for Single Dialect)

When you're using a specific database dialect (e.g., PostgreSQL), you can use the explicit database type for better type inference and access to dialect-specific features:

// users.service.ts
import { Injectable } from '@nestjs/common';
import { InjectDrizzle, NodePgDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { eq } from 'drizzle-orm';
import { users, userProfiles } from './schemas';
import type { DatabaseSchema } from './schemas';

@Injectable()
export class UsersService {
  constructor(
    @InjectDrizzle() private readonly db: NodePgDatabase<DatabaseSchema>
  ) {}

  async findById(id: string) {
    // Full type safety with relations + PostgreSQL-specific features
    return this.db.query.users.findFirst({
      where: eq(users.id, id),
      with: {
        posts: true,
        profile: true,
      },
    });
  }
}

Available Explicit Types:

• NodePgDatabase<TSchema> - For PostgreSQL using pg driver
• PostgresJsDatabase<TSchema> - For PostgreSQL using postgres driver
• MySql2Database<TSchema> - For MySQL
• BetterSQLite3Database<TSchema> - For SQLite

Using the Dialect Helper Type:

You can also use the DrizzleDatabaseByDialect helper type to map your dialect to the correct type:

import type { DrizzleDatabaseByDialect } from '@sklv-labs/ts-nestjs-database/drizzle';
import type { DatabaseSchema } from './schemas';

// Automatically resolves to NodePgDatabase<DatabaseSchema> for 'postgresql'
type MyDatabase = DrizzleDatabaseByDialect<'postgresql', DatabaseSchema>;

@Injectable()
export class UsersService {
  constructor(
    @InjectDrizzle() private readonly db: MyDatabase
  ) {}
}

When to Use Each Approach:

• Union Type (DrizzleDatabase): Use when you need to support multiple dialects or want maximum flexibility
• Explicit Type (NodePgDatabase, etc.): Use when you're committed to a single dialect and want:
  • Better type inference
  • Access to dialect-specific features
  • More precise IntelliSense
  • Better error messages

Typed Payment Service

// payments.service.ts
import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase, Transactional } from '@sklv-labs/ts-nestjs-database/drizzle';
import { eq } from 'drizzle-orm';
import { payments, transactions } from './schemas';
import type { DatabaseSchema } from './schemas';

@Injectable()
export class PaymentsService {
  constructor(
    @InjectDrizzle() private readonly db: DrizzleDatabase<DatabaseSchema>
  ) {}

  async findPaymentWithTransactions(paymentId: string) {
    // Full type safety with nested relations
    return this.db.query.payments.findFirst({
      where: eq(payments.id, paymentId),
      with: {
        user: true,        // TypeScript knows about user relation
        transactions: true, // TypeScript knows about transactions relation
      },
    });
  }

  @Transactional()
  async createPaymentWithTransaction(data: {
    userId: string;
    amount: string;
    transactionType: string;
  }) {
    // Type-safe inserts with relations
    const [payment] = await this.db
      .insert(payments)
      .values({
        userId: data.userId,
        amount: data.amount,
        status: 'pending',
      })
      .returning();

    const [transaction] = await this.db
      .insert(transactions)
      .values({
        paymentId: payment.id,
        type: data.transactionType,
        amount: data.amount,
      })
      .returning();

    return { payment, transaction };
  }
}

Type Safety Benefits

With typed schemas, you get:

1. Autocomplete for table names: db.query.users is fully typed
2. Type-safe relations: with: { profile: true } is validated
3. Type-safe column access: users.email is typed
4. Type-safe inserts/updates: Column types are enforced
5. IntelliSense support: Full IDE support for all operations

Error Handling

Basic Error Handling

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { DatabaseError } from '@sklv-labs/ts-nestjs-database';
import { eq } from 'drizzle-orm';
import { users } from './schema';

@Injectable()
export class UserService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  async findById(id: number) {
    try {
      const user = await this.db.query.users.findFirst({
        where: eq(users.id, id),
      });
      
      if (!user) {
        throw new DatabaseError('User not found', 'USER_NOT_FOUND', {
          statusCode: 404,
          metadata: { userId: id },
          loggable: false,
});
      }
      
      return user;
    } catch (error) {
      if (error instanceof DatabaseError) {
        throw error;
      }
      throw DatabaseError.fromDrizzleError(error, 'USER_FETCH_FAILED', {
        metadata: { userId: id },
      });
    }
  }
}

Handling Constraint Violations

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { DatabaseError, DatabaseConstraintError } from '@sklv-labs/ts-nestjs-database';
import { users } from './schema';

@Injectable()
export class UserService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  async create(data: { name: string; email: string }) {
    try {
      const [created] = await this.db.insert(users).values(data).returning();
      return created;
    } catch (error) {
      // Check for unique constraint violation
      const err = error as { code?: string };
      if (err.code === '23505') { // PostgreSQL unique violation
        throw new DatabaseConstraintError('Email already exists', {
          metadata: { email: data.email },
        });
      }
      throw DatabaseError.fromDrizzleError(error, 'USER_CREATE_FAILED');
    }
  }
}

Error Handling in Transactions

When an error occurs in a transaction, it's automatically rolled back. You can catch and handle errors:

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase, Transactional } from '@sklv-labs/ts-nestjs-database/drizzle';
import { DatabaseError } from '@sklv-labs/ts-nestjs-database';
import { orders } from './schema';

@Injectable()
export class OrderService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  @Transactional()
  async createOrder(orderData: any) {
    try {
      const [order] = await this.db.insert(orders).values(orderData).returning();
      await this.processPayment(order.id);
      return order;
    } catch (error) {
      // Transaction is automatically rolled back
      // Wrap error with DatabaseError - will be automatically logged by BaseErrorExceptionFilter
      throw DatabaseError.fromDrizzleError(error, 'ORDER_CREATE_FAILED', {
        metadata: { orderData },
        loggable: true, // Will be logged automatically
      });
    }
  }
}

Automatic Error Handling

All database errors extend BaseError from @sklv-labs/ts-nestjs-error, which means they automatically work with the BaseErrorExceptionFilter from @sklv-labs/ts-nestjs-logger.

Setup

// main.ts
import { NestFactory } from '@nestjs/core';
import { APP_FILTER } from '@nestjs/core';
import { BaseErrorExceptionFilter } from '@sklv-labs/ts-nestjs-logger';
import { ClsService } from '@sklv-labs/ts-nestjs-cls';
import { LoggerService } from '@sklv-labs/ts-nestjs-logger';
import { AppModule } from './app.module';

async function bootstrap() {
  const app = await NestFactory.create(AppModule);

  // Register BaseErrorExceptionFilter globally
  const logger = app.get(LoggerService);
  const cls = app.get(ClsService);
  app.useGlobalFilters(new BaseErrorExceptionFilter(logger, cls));
  
  await app.listen(3000);
}
bootstrap();

How Error Logging Works

1. DatabaseError extends BaseError - All database errors inherit BaseError features
2. Automatic Exception Filter - BaseErrorExceptionFilter catches all BaseError instances
3. Respects loggable Flag - Only logs errors where loggable: true
4. CLS Context - All error logs automatically include requestId, traceId, etc.

Error Types and Logging

| Error Class | Default loggable | Default exposeToClient | Use Case | |------------|------------------|------------------------|----------| | DatabaseConnectionError | true | false | Connection failures (should be logged) | | DatabaseQueryError | true | false | Query failures (should be logged) | | DatabaseTransactionError | true | false | Transaction failures (should be logged) | | DatabaseConstraintError | false | true | Constraint violations (expected, don't log) |

Logging Integration

Query Logging

The module automatically logs database queries when logging is enabled:

// app.module.ts
DrizzleModule.forRoot({
  dialect: 'postgresql',
  connection: { connectionString: process.env.DATABASE_URL },
  logging: {
    logQueries: true,           // Enable query logging
    logParams: false,            // Don't log params (security)
    logDuration: true,           // Log execution time
    slowQueryThreshold: 1000,    // Log queries > 1s as warnings
    queryLogLevel: 'debug',      // Use 'debug' or 'info'
  },
})

Manual Logging with LoggerService

You can also manually inject LoggerService for application-level logging:

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { LoggerService } from '@sklv-labs/ts-nestjs-logger';
import { eq } from 'drizzle-orm';
import { users } from './schema';

@Injectable()
export class UserService {
  constructor(
    @InjectDrizzle() private readonly db: DrizzleDatabase,
    private readonly logger: LoggerService,
  ) {}

  async findById(id: number) {
    // Manual logging with CLS context automatically included
    this.logger.debug('Fetching user', { userId: id });
    
    try {
      const user = await this.db.query.users.findFirst({
        where: eq(users.id, id),
      });
      
      if (user) {
        this.logger.info('User found', { userId: id });
      } else {
        this.logger.warn('User not found', { userId: id });
      }
      
      return user;
    } catch (error) {
      // Error will be logged by BaseErrorExceptionFilter
      this.logger.error('Failed to fetch user', { userId: id, error });
      throw error;
    }
  }
}

CLS Context in Logs

All logs (both manual and automatic) automatically include CLS context:

• requestId - Unique request identifier
• traceId - OpenTelemetry trace ID (if configured)
• userId - Current user ID (if set in CLS)
• correlationId - Correlation ID for distributed tracing
• spanId - OpenTelemetry span ID
• And more...

This context is automatically extracted by LoggerService and BaseErrorExceptionFilter from the CLS store.

Advanced Patterns

Service with Pagination

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { desc, sql } from 'drizzle-orm';
import { users } from './schema';

@Injectable()
export class UserService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  async findMany(options: { page: number; limit: number }) {
    const offset = (options.page - 1) * options.limit;
    
    const [data, countResult] = await Promise.all([
      this.db.query.users.findMany({
        limit: options.limit,
        offset,
        orderBy: desc(users.createdAt),
      }),
      this.db
        .select({ count: sql<number>`count(*)` })
        .from(users),
    ]);

    return {
      data,
      total: countResult[0].count,
      page: options.page,
      limit: options.limit,
    };
  }
}

Complex Queries with Relations

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';
import { eq } from 'drizzle-orm';
import { users, posts, comments } from './schema';

@Injectable()
export class UserService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  async findUserWithPostsAndComments(userId: number) {
    return this.db.query.users.findFirst({
      where: eq(users.id, userId),
      with: {
        posts: {
          with: {
            comments: true,
          },
        },
      },
    });
  }
}

Batch Operations

import { Injectable } from '@nestjs/common';
import { InjectDrizzle, DrizzleDatabase, Transactional } from '@sklv-labs/ts-nestjs-database/drizzle';
import { users } from './schema';

@Injectable()
export class UserService {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  @Transactional()
  async createManyUsers(usersData: Array<{ name: string; email: string }>) {
    // All inserts run in a single transaction
    const created = await Promise.all(
      usersData.map((data) => this.db.insert(users).values(data).returning())
    );
    return created.flat();
  }
}

Configuration Options

DrizzleModuleOptions

interface DrizzleModuleOptions {
  // Database dialect
  dialect: 'postgresql' | 'mysql' | 'sqlite' | 'postgres-js';
  
  // Connection configuration (string or object)
  connection: string | {
    connectionString: string;
    pool?: {
      min?: number;
      max?: number;
      idleTimeoutMillis?: number;
    };
    ssl?: boolean | Record<string, unknown>;
  };
  
  // Drizzle schema object (optional)
  schema?: Record<string, unknown>;
  
  // Custom Drizzle client factory (overrides default)
  drizzleFactory?: () => DrizzleDatabase | Promise<DrizzleDatabase>;
  
  // Database logging configuration
  logging?: {
    logQueries?: boolean;           // Default: true
    logParams?: boolean;             // Default: false
    logDuration?: boolean;           // Default: true
    slowQueryThreshold?: number;     // Default: 1000 (ms)
    queryLogLevel?: 'debug' | 'info'; // Default: 'debug'
    logConnectionEvents?: boolean;   // Default: false
    logTransactionEvents?: boolean;  // Default: false
  };
}

Supported Dialects

• PostgreSQL (postgresql) - Using pg (node-postgres)
• PostgreSQL (postgres-js) - Using postgres-js
• MySQL (mysql) - Using mysql2
• SQLite (sqlite) - Using better-sqlite3

Migrations with Drizzle Kit

This package focuses on runtime database operations. For migrations and schema management, use drizzle-kit directly in your project.

Installation

npm install -D drizzle-kit

Configuration

Create a drizzle.config.ts file in your project root:

// drizzle.config.ts
import type { Config } from 'drizzle-kit';
import * as dotenv from 'dotenv';

dotenv.config();

export default {
  schema: './src/schemas/**/*.ts', // Path to your schema files
  out: './drizzle', // Migration output directory
  dialect: 'postgresql', // or 'mysql', 'sqlite', 'postgres-js'
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

Migration Commands

Add these scripts to your package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}

Running Migrations

• Generate migrations: npm run db:generate - Creates migration files from schema changes
• Push schema: npm run db:push - Pushes schema changes directly to database (development)
• Run migrations: npm run db:migrate - Executes migration files
• Open Studio: npm run db:studio - Opens Drizzle Studio for database inspection

Programmatic Migrations (Optional)

If you need to run migrations programmatically in NestJS, you can create a migration service:

// migration.service.ts
import { Injectable, OnModuleInit } from '@nestjs/common';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { InjectDrizzle, DrizzleDatabase } from '@sklv-labs/ts-nestjs-database/drizzle';

@Injectable()
export class MigrationService implements OnModuleInit {
  constructor(@InjectDrizzle() private readonly db: DrizzleDatabase) {}

  async onModuleInit() {
    // Only run migrations in non-production or when explicitly enabled
    if (process.env.RUN_MIGRATIONS === 'true') {
      await migrate(this.db as any, { migrationsFolder: './drizzle' });
    }
  }
}

Note: This package does not include drizzle-kit as a dependency. Install and configure it separately in your project as shown above.

API Reference

DrizzleModule

Main module for Drizzle ORM integration.

• DrizzleModule.forRoot(options: DrizzleModuleOptions) - Synchronous configuration
• DrizzleModule.forRootAsync(options: DrizzleModuleAsyncOptions) - Async configuration

Exports from @sklv-labs/ts-nestjs-database/drizzle

• DrizzleModule - Main module class
• InjectDrizzle() - Decorator to inject Drizzle database instance (automatically uses transactions)
• DrizzleModule.getTransactionPlugin() - Static method to get the transaction plugin for ClsModule
• DrizzleDatabase<TSchema> - Type for the Drizzle database instance (generic over schema)
• Transactional - Decorator for transaction methods (re-exported from @nestjs-cls/transactional)
• Transaction - Transaction type (re-exported from @nestjs-cls/transactional)
• DrizzleLogger - Query logger class
• combineSchemas() - Helper function to combine multiple schema objects
• DRIZZLE_INSTANCE - Injection token for Drizzle client

Exports from @sklv-labs/ts-nestjs-database (Main Package)

• DatabaseError - Base database error class
• DatabaseConnectionError - Connection failure error
• DatabaseQueryError - Query failure error
• DatabaseTransactionError - Transaction failure error
• DatabaseConstraintError - Constraint violation error
• DatabaseErrorOptions - Error options type

Best Practices

1. Use @InjectDrizzle() for database access - The injected instance automatically uses transactions when available
2. Use @Transactional() for service methods - Keeps transaction boundaries clear
3. Handle errors with DatabaseError - Provides consistent error handling
4. Log important operations - Use LoggerService for observability
5. Use relations for data loading - Leverage Drizzle's relation query builder
6. Keep services focused - One service per domain/aggregate
7. Use proper error codes - Follow consistent error code patterns
8. Type your schemas - Use DrizzleDatabase<DatabaseSchema> for full type safety
9. Organize schemas by domain - Split schemas across domain files and combine them
10. Enable query logging in development - Use logQueries: true for debugging

Development

# Build
npm run build

# Lint
npm run lint

# Format
npm run format

# Test
npm run test

# Type check
npm run type-check

License

MIT © sklv-labs