npm package: @sklv-labs/ts-nestjs-database
Description: NestJS Drizzle ORM integration with transaction management, error handling, and logging
Author: sklv-labs
Latest version: 0.1.0

# @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](#installation)
2. [Quick Start](#quick-start)
3. [Module Setup](#module-setup)
4. [Service Patterns](#service-patterns)
5. [Transaction Management](#transaction-management)
6. [Schema Patterns and Type Safety](#schema-patterns-and-type-safety)
7. [Error Handling](#error-handling)
8. [Logging Integration](#logging-integration)
9. [Advanced Patterns](#advanced-patterns)
10. [Configuration Options](#configuration-options)
11. [API Reference](#api-reference)
12. [Best Practices](#best-practices)

## Installation

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

### Peer Dependencies

This package requires the following peer dependencies:

```bash
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):**
```bash
npm install pg @types/pg
```

**PostgreSQL (postgres-js):**
```bash
npm install postgres
```

**MySQL:**
```bash
npm install mysql2 @types/mysql2
```

**SQLite:**
```bash
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

```typescript
// 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

```typescript
// 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

```typescript
// 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

```typescript
// 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

```typescript
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

```typescript
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

```typescript
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

```typescript
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

```typescript
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

```typescript
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:

```typescript
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:

```typescript
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:

```typescript
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

```typescript
// 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],
  }),
}));
```

```typescript
// 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

```typescript
// 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)

```typescript
// 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

```typescript
// 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:

```typescript
// 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:

```typescript
// 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:

```typescript
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

```typescript
// 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

```typescript
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

```typescript
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:

```typescript
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

```typescript
// 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:

```typescript
// 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:

```typescript
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

```typescript
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

```typescript
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

```typescript
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

```typescript
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](https://orm.drizzle.team/kit-docs/overview) directly in your project.

### Installation

```bash
npm install -D drizzle-kit
```

### Configuration

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

```typescript
// 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`:

```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:

```typescript
// 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

```bash
# 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](https://github.com/sklv-labs)