Skip to content

Backend Drizzle Usage

This document covers how Drizzle ORM is integrated and used within the NestJS backend application. The backend uses Drizzle for all database operations, from user authentication to document processing and vector storage.

Architecture Overview

The backend implements Drizzle ORM as a service layer that provides type-safe database access across all NestJS modules. The architecture follows NestJS patterns with dependency injection and modular design.

Database Connection

The backend uses a singleton database connection pattern configured in src/db/database.ts:

import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";
import { Client } from "pg";

if (!process.env.POSTGRES_URL) {
  throw new Error(
    "POSTGRES_URL environment variable is not defined. Please check your .env file."
  );
}

// Create the client
const client = new Client({
  connectionString: process.env.POSTGRES_URL,
  ssl:
    process.env.NODE_ENV === "production"
      ? { rejectUnauthorized: false }
      : false,
});

// Connect the client
client.connect();

// Create the drizzle database instance
export const db = drizzle(client);

Schema Organisation

The backend shares schema definitions with the frontend, located in src/db/ directory:

src/db/
├── index.ts           # Schema exports
├── database.ts        # Database connection
├── users.ts           # User table schema
├── company.ts         # Company table schema
├── projects.ts        # Projects table schema
├── themes.ts          # Themes table schema
├── fileStore.ts       # Document storage schema
├── session.ts         # Session management
├── account.ts         # Account linking (NextAuth)
└── ...               # Other table schemas

NestJS Integration

Service Pattern

Backend services inject and use the database connection directly:

import { Injectable, Logger } from "@nestjs/common";
import { db } from "../db/database";
import { users, company } from "../db";
import { eq, and } from "drizzle-orm";

@Injectable()
export default class UserService {
  private readonly logger = new Logger(UserService.name);

  async findUserByEmail(email: string) {
    try {
      const result = await db
        .select()
        .from(users)
        .where(eq(users.UserEmail, email))
        .limit(1);

      return result[0] || null;
    } catch (error) {
      this.logger.error(`Error finding user by email: ${error.message}`);
      throw error;
    }
  }

  async createUser(userData: CreateUserDto) {
    try {
      const result = await db
        .insert(users)
        .values({
          UserName: userData.name,
          UserEmail: userData.email,
          UserPassword: userData.hashedPassword,
          CompanyID: userData.companyId,
        })
        .returning();

      return result[0];
    } catch (error) {
      this.logger.error(`Error creating user: ${error.message}`);
      throw error;
    }
  }
}

Module Configuration

Services that use Drizzle are configured as standard NestJS providers:

// src/user/user.module.ts
import { Module } from "@nestjs/common";
import { UserService } from "./user.service";
import { UserController } from "./user.controller";

@Module({
  providers: [UserService],
  controllers: [UserController],
  exports: [UserService],
})
export class UserModule {}

Database Operations

Query Patterns

Basic CRUD Operations

// SELECT operations
const getAllUsers = async () => {
  return await db.select().from(users);
};

const getUserWithCompany = async (userId: number) => {
  return await db
    .select()
    .from(users)
    .leftJoin(company, eq(users.CompanyID, company.companyID))
    .where(eq(users.UserID, userId));
};

// INSERT operations
const createProject = async (projectData: CreateProjectDto) => {
  return await db
    .insert(projects)
    .values({
      ProjectID: projectData.id,
      Name: projectData.name,
      ClientName: projectData.clientName,
      DateCreated: new Date(),
      userID: projectData.userId,
    })
    .returning();
};

// UPDATE operations
const updateUserProfile = async (userId: number, updates: UpdateUserDto) => {
  return await db
    .update(users)
    .set({
      UserName: updates.name,
      UserEmail: updates.email,
      ...updates,
    })
    .where(eq(users.UserID, userId))
    .returning();
};

// DELETE operations
const deleteProject = async (projectId: string) => {
  return await db
    .delete(projects)
    .where(eq(projects.ProjectID, projectId))
    .returning();
};

Complex Queries

// Joins and aggregations
const getUserProjectsWithStats = async (userId: number) => {
  return await db
    .select({
      projectId: projects.ProjectID,
      projectName: projects.Name,
      clientName: projects.ClientName,
      dateCreated: projects.DateCreated,
      documentCount: count(fileStore.DocumentID),
    })
    .from(projects)
    .leftJoin(fileStore, eq(projects.ProjectID, fileStore.ProjectID))
    .where(eq(projects.userID, userId))
    .groupBy(projects.ProjectID)
    .orderBy(desc(projects.DateCreated));
};

// Conditional queries
const searchUsers = async (searchTerm: string, companyId?: string) => {
  const conditions = [
    or(
      ilike(users.UserName, `%${searchTerm}%`),
      ilike(users.UserEmail, `%${searchTerm}%`)
    ),
  ];

  if (companyId) {
    conditions.push(eq(users.CompanyID, companyId));
  }

  return await db
    .select()
    .from(users)
    .where(and(...conditions))
    .limit(50);
};

Transaction Support

// Transaction example
const transferUserToCompany = async (
  userId: number,
  newCompanyId: string,
  transferData: TransferDto
) => {
  return await db.transaction(async (tx) => {
    // Update user company
    await tx
      .update(users)
      .set({ CompanyID: newCompanyId })
      .where(eq(users.UserID, userId));

    // Update user projects
    await tx
      .update(projects)
      .set({ userID: transferData.newOwnerId })
      .where(eq(projects.userID, userId));

    // Log the transfer
    await tx.insert(auditLog).values({
      action: "user_transfer",
      userId: userId,
      details: JSON.stringify(transferData),
      timestamp: new Date(),
    });
  });
};

Service Examples

Chat Service Integration

// src/chat/chat.service.ts
import { Injectable, Logger } from "@nestjs/common";
import { db } from "../db/database";
import { conversations, users } from "../db";
import { eq } from "drizzle-orm";

@Injectable()
export default class ChatService {
  private readonly logger = new Logger(ChatService.name);

  async getUserConversations(userId: number) {
    return await db
      .select({
        uuid: conversations.UUID,
        containerName: conversations.ContainerName,
        blobName: conversations.BlobName,
        updatedAt: conversations.UpdatedAt,
      })
      .from(conversations)
      .where(eq(conversations.UserID, userId))
      .orderBy(desc(conversations.UpdatedAt));
  }

  async createConversation(createChatDto: CreateChatDto) {
    const conversationId = uuidv4();

    try {
      const result = await db
        .insert(conversations)
        .values({
          UUID: conversationId,
          UserID: createChatDto.userId,
          ContainerName: createChatDto.containerName,
          BlobName: createChatDto.blobName,
          UpdatedAt: new Date(),
        })
        .returning();

      return result[0];
    } catch (error) {
      this.logger.error(`Error creating conversation: ${error.message}`);
      throw error;
    }
  }
}

Document Service Integration

// src/document/document.service.ts
import { Injectable } from "@nestjs/common";
import { db } from "../db/database";
import { fileStore, projects, users } from "../db";
import { eq, and, desc } from "drizzle-orm";

@Injectable()
export class DocumentService {
  async getUserDocuments(userId: number, projectId?: string) {
    const conditions = [eq(fileStore.userID, userId)];

    if (projectId) {
      conditions.push(eq(fileStore.ProjectID, projectId));
    }

    return await db
      .select({
        documentId: fileStore.DocumentID,
        documentName: fileStore.DocumentName,
        status: fileStore.Status,
        dateCreated: fileStore.DateCreated,
        lastModified: fileStore.LastModified,
        projectName: projects.Name,
      })
      .from(fileStore)
      .leftJoin(projects, eq(fileStore.ProjectID, projects.ProjectID))
      .where(and(...conditions))
      .orderBy(desc(fileStore.LastModified));
  }

  async updateDocumentStatus(documentId: string, status: string) {
    return await db
      .update(fileStore)
      .set({
        Status: status,
        LastModified: new Date(),
      })
      .where(eq(fileStore.DocumentID, documentId))
      .returning();
  }
}

Migration Management

Running Migrations

The backend includes migration support for schema changes:

// src/db/migrate.ts
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { db } from "./database";

async function runMigrations() {
  try {
    console.log("Running migrations...");
    await migrate(db, { migrationsFolder: "./drizzle" });
    console.log("Migrations completed successfully");
  } catch (error) {
    console.error("Migration failed:", error);
    process.exit(1);
  }
}

if (require.main === module) {
  runMigrations();
}

Migration Scripts

Migrations are generated automatically based on schema changes:

# Generate new migration
npx drizzle-kit generate:pg

# Run migrations
npx drizzle-kit push:pg

Performance Optimisations

Connection Pooling

The backend uses PostgreSQL connection pooling for optimal performance:

// Production connection configuration
const pool = new Pool({
  connectionString: process.env.POSTGRES_URL,
  max: 20, // Maximum number of connections
  min: 5, // Minimum number of connections
  idle: 10000, // Close connections after 10 seconds of inactivity
  connectionTimeoutMillis: 2000,
});

export const db = drizzle(pool);

Query Optimisation

  • Indexes: Proper indexing on frequently queried columns
  • Batch Operations: Using batch inserts for multiple records
  • Prepared Statements: Drizzle automatically prepares statements
  • Connection Reuse: Singleton database instance across services

Monitoring and Logging

// Query logging in development
const db = drizzle(client, {
  logger: process.env.NODE_ENV === "development",
});

// Custom logging for production
const logQuery = (query: string, params: any[]) => {
  if (process.env.LOG_QUERIES === "true") {
    console.log("Query:", query);
    console.log("Params:", params);
  }
};

Error Handling

Service-Level Error Handling

@Injectable()
export class UserService {
  private readonly logger = new Logger(UserService.name);

  async findUser(userId: number) {
    try {
      const result = await db
        .select()
        .from(users)
        .where(eq(users.UserID, userId))
        .limit(1);

      if (!result.length) {
        throw new NotFoundException(`User with ID ${userId} not found`);
      }

      return result[0];
    } catch (error) {
      if (error instanceof NotFoundException) {
        throw error;
      }

      this.logger.error(`Database error finding user ${userId}:`, error);
      throw new InternalServerErrorException("Database operation failed");
    }
  }
}

Global Exception Handling

// Database exception filter
@Catch()
export class DatabaseExceptionFilter implements ExceptionFilter {
  catch(exception: any, host: ArgumentsHost) {
    const response = host.switchToHttp().getResponse();

    if (exception.code === "23505") {
      // Unique constraint violation
      response.status(409).json({
        statusCode: 409,
        message: "Resource already exists",
        error: "Conflict",
      });
    } else if (exception.code === "23503") {
      // Foreign key constraint
      response.status(400).json({
        statusCode: 400,
        message: "Invalid reference",
        error: "Bad Request",
      });
    } else {
      response.status(500).json({
        statusCode: 500,
        message: "Internal server error",
        error: "Internal Server Error",
      });
    }
  }
}

Environment Configuration

Required Environment Variables

# Database connection
POSTGRES_URL=postgresql://user:password@host:port/database

# SSL configuration for production
NODE_ENV=production

# Optional: Query logging
LOG_QUERIES=false
DB_DEBUG=false

Configuration Service Integration

// src/config/database.config.ts
import { ConfigService } from "@nestjs/config";

export const getDatabaseConfig = (configService: ConfigService) => ({
  url: configService.get<string>("POSTGRES_URL"),
  ssl:
    configService.get<string>("NODE_ENV") === "production"
      ? { rejectUnauthorized: false }
      : false,
  logging: configService.get<string>("DB_DEBUG") === "true",
});

Testing with Drizzle

Unit Testing

// Mock database for testing
const mockDb = {
  select: jest.fn().mockReturnThis(),
  from: jest.fn().mockReturnThis(),
  where: jest.fn().mockReturnThis(),
  limit: jest.fn().mockResolvedValue([mockUser]),
  insert: jest.fn().mockReturnThis(),
  values: jest.fn().mockReturnThis(),
  returning: jest.fn().mockResolvedValue([mockUser]),
};

jest.mock("../db/database", () => ({
  db: mockDb,
}));

describe("UserService", () => {
  it("should find user by email", async () => {
    const user = await userService.findUserByEmail("test@example.com");
    expect(user).toEqual(mockUser);
    expect(mockDb.select).toHaveBeenCalled();
  });
});

Integration Testing

// Integration test with test database
import { Test } from "@nestjs/testing";
import { drizzle } from "drizzle-orm/node-postgres";
import { Client } from "pg";

describe("UserService Integration", () => {
  let testDb: ReturnType<typeof drizzle>;
  let client: Client;

  beforeAll(async () => {
    client = new Client({
      connectionString: process.env.TEST_POSTGRES_URL,
    });
    await client.connect();
    testDb = drizzle(client);
  });

  afterAll(async () => {
    await client.end();
  });

  it("should create and retrieve user", async () => {
    // Test implementation
  });
});

Best Practices

Schema Design

  • Use descriptive table and column names
  • Implement proper foreign key relationships
  • Add appropriate indexes for query performance
  • Use consistent data types across related fields

Query Patterns

  • Use parameterized queries to prevent SQL injection
  • Implement proper error handling and logging
  • Use transactions for multi-step operations
  • Batch operations when possible for performance

Service Architecture

  • Keep database logic in dedicated service classes
  • Use dependency injection for testability
  • Implement proper error handling and validation
  • Log database operations for debugging

Performance

  • Use connection pooling in production
  • Implement query result caching where appropriate
  • Monitor query performance and optimise as needed
  • Use database indexes effectively

This backend integration provides a robust, type-safe, and performant database layer that scales with the application's needs while maintaining clean separation of concerns and following NestJS best practices.