Drizzle ORM PostgreSQL: Type-Safe Database Management

Drizzle ORM is a modern, type-safe ORM for TypeScript that provides excellent PostgreSQL integration. This comprehensive guide covers schema design, migrations, advanced queries, and production best practices for building robust database applications.

Why Choose Drizzle ORM?

• Type Safety: Full TypeScript integration with compile-time checks
• Performance: Minimal runtime overhead with efficient queries
• SQL-First: Direct SQL control with type safety
• Migration System: Robust schema migration management
• Serverless Ready: Perfect for edge and serverless environments

Step 1: Project Setup and Installation

Set up a new Drizzle ORM project with PostgreSQL:

# Create project directory
mkdir drizzle-postgresql-app
cd drizzle-postgresql-app

# Initialize package.json
npm init -y

# Install Drizzle ORM and PostgreSQL dependencies
npm install drizzle-orm postgres
npm install -D drizzle-kit @types/pg tsx nodemon

# Install TypeScript and development tools
npm install -D typescript @types/node
npm install -D eslint @typescript-eslint/eslint-plugin @typescript-eslint/parser
npm install -D prettier

# Install additional utilities
npm install dotenv zod bcryptjs jsonwebtoken
npm install -D @types/bcryptjs @types/jsonwebtoken

Step 2: TypeScript and Project Configuration

Configure TypeScript for optimal Drizzle integration:

{
  "compilerOptions": {
    "target": "ES2022",
    "lib": ["ES2022"],
    "module": "CommonJS",
    "moduleResolution": "node",
    "allowSyntheticDefaultImports": true,
    "esModuleInterop": true,
    "allowJs": true,
    "outDir": "./dist",
    "rootDir": "./src",
    "removeComments": true,
    "strict": true,
    "noImplicitAny": true,
    "strictNullChecks": true,
    "strictFunctionTypes": true,
    "noImplicitThis": true,
    "alwaysStrict": true,
    "noUnusedLocals": true,
    "noUnusedParameters": true,
    "noImplicitReturns": true,
    "noFallthroughCasesInSwitch": true,
    "declaration": true,
    "declarationMap": true,
    "sourceMap": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "resolveJsonModule": true,
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    "baseUrl": ".",
    "paths": {
      "@/*": ["src/*"],
      "@/db/*": ["src/db/*"],
      "@/schema/*": ["src/db/schema/*"],
      "@/types/*": ["src/types/*"]
    }
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist"]
}tsconfig.json

Configure Drizzle configuration:

import { defineConfig } from "drizzle-kit";
import { config } from "dotenv";

config();

export default defineConfig({
  schema: "./src/db/schema/*",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    host: process.env.DB_HOST!,
    port: parseInt(process.env.DB_PORT || "5432"),
    user: process.env.DB_USER!,
    password: process.env.DB_PASSWORD!,
    database: process.env.DB_NAME!,
    ssl:
      process.env.NODE_ENV === "production"
        ? { rejectUnauthorized: false }
        : false,
  },
  verbose: true,
  strict: true,
  migrations: {
    prefix: "timestamp",
    table: "__drizzle_migrations__",
    schema: "public",
  },
});drizzle.config.ts

Step 3: Database Schema Design

Create comprehensive database schemas:

import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  boolean,
  pgEnum,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";

// Enums
export const userRoleEnum = pgEnum("user_role", ["admin", "user", "moderator"]);
export const userStatusEnum = pgEnum("user_status", [
  "active",
  "inactive",
  "suspended",
  "pending",
]);

// Users table
export const users = pgTable(
  "users",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    username: varchar("username", { length: 100 }).notNull().unique(),
    firstName: varchar("first_name", { length: 100 }).notNull(),
    lastName: varchar("last_name", { length: 100 }).notNull(),
    passwordHash: varchar("password_hash", { length: 255 }).notNull(),
    role: userRoleEnum("role").default("user").notNull(),
    status: userStatusEnum("status").default("pending").notNull(),
    avatar: text("avatar"),
    bio: text("bio"),
    phone: varchar("phone", { length: 20 }),
    emailVerified: boolean("email_verified").default(false).notNull(),
    emailVerificationToken: varchar("email_verification_token", {
      length: 255,
    }),
    passwordResetToken: varchar("password_reset_token", { length: 255 }),
    passwordResetExpiresAt: timestamp("password_reset_expires_at", {
      withTimezone: true,
    }),
    lastLoginAt: timestamp("last_login_at", { withTimezone: true }),
    loginAttempts: varchar("login_attempts", { length: 10 }).default("0"),
    lockedUntil: timestamp("locked_until", { withTimezone: true }),
    preferences: text("preferences"), // JSON string
    metadata: text("metadata"), // JSON string for additional data
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
  },
  users => ({
    emailIdx: uniqueIndex("users_email_idx").on(users.email),
    usernameIdx: uniqueIndex("users_username_idx").on(users.username),
    roleIdx: index("users_role_idx").on(users.role),
    statusIdx: index("users_status_idx").on(users.status),
    createdAtIdx: index("users_created_at_idx").on(users.createdAt),
    deletedAtIdx: index("users_deleted_at_idx").on(users.deletedAt),
  })
);

// Zod schemas for validation
export const insertUserSchema = createInsertSchema(users, {
  email: z.string().email(),
  username: z.string().min(3).max(100),
  firstName: z.string().min(1).max(100),
  lastName: z.string().min(1).max(100),
  phone: z.string().min(10).max(20).optional(),
  bio: z.string().max(500).optional(),
});

export const selectUserSchema = createSelectSchema(users);

export const updateUserSchema = insertUserSchema.partial().omit({
  id: true,
  createdAt: true,
  passwordHash: true,
  emailVerificationToken: true,
  passwordResetToken: true,
});

// Types
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type UserRole = (typeof userRoleEnum.enumValues)[number];
export type UserStatus = (typeof userStatusEnum.enumValues)[number];src/db/schema/users.ts

import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  boolean,
  integer,
  pgEnum,
  index,
  foreignKey,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";
import { users } from "./users";

// Enums
export const postStatusEnum = pgEnum("post_status", [
  "draft",
  "published",
  "archived",
]);

// Posts table
export const posts = pgTable(
  "posts",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    title: varchar("title", { length: 255 }).notNull(),
    slug: varchar("slug", { length: 255 }).notNull().unique(),
    excerpt: text("excerpt"),
    content: text("content").notNull(),
    authorId: uuid("author_id").notNull(),
    status: postStatusEnum("status").default("draft").notNull(),
    featured: boolean("featured").default(false).notNull(),
    viewCount: integer("view_count").default(0).notNull(),
    likeCount: integer("like_count").default(0).notNull(),
    commentCount: integer("comment_count").default(0).notNull(),
    readingTime: integer("reading_time"), // in minutes
    seoTitle: varchar("seo_title", { length: 255 }),
    seoDescription: text("seo_description"),
    canonicalUrl: text("canonical_url"),
    featuredImage: text("featured_image"),
    featuredImageAlt: varchar("featured_image_alt", { length: 255 }),
    tags: text("tags"), // JSON array as string
    metadata: text("metadata"), // JSON string
    publishedAt: timestamp("published_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
  },
  posts => ({
    authorIdFk: foreignKey({
      columns: [posts.authorId],
      foreignColumns: [users.id],
    }),
    slugIdx: index("posts_slug_idx").on(posts.slug),
    authorIdx: index("posts_author_idx").on(posts.authorId),
    statusIdx: index("posts_status_idx").on(posts.status),
    featuredIdx: index("posts_featured_idx").on(posts.featured),
    publishedAtIdx: index("posts_published_at_idx").on(posts.publishedAt),
    createdAtIdx: index("posts_created_at_idx").on(posts.createdAt),
    deletedAtIdx: index("posts_deleted_at_idx").on(posts.deletedAt),
  })
);

// Categories table
export const categories = pgTable(
  "categories",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    name: varchar("name", { length: 100 }).notNull().unique(),
    slug: varchar("slug", { length: 100 }).notNull().unique(),
    description: text("description"),
    color: varchar("color", { length: 7 }), // Hex color code
    icon: varchar("icon", { length: 50 }),
    parentId: uuid("parent_id"),
    sortOrder: integer("sort_order").default(0),
    postCount: integer("post_count").default(0),
    isActive: boolean("is_active").default(true).notNull(),
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  categories => ({
    slugIdx: index("categories_slug_idx").on(categories.slug),
    parentIdx: index("categories_parent_idx").on(categories.parentId),
    activeIdx: index("categories_active_idx").on(categories.isActive),
  })
);

// Post-Category junction table
export const postCategories = pgTable(
  "post_categories",
  {
    postId: uuid("post_id").notNull(),
    categoryId: uuid("category_id").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  postCategories => ({
    postIdFk: foreignKey({
      columns: [postCategories.postId],
      foreignColumns: [posts.id],
    }),
    categoryIdFk: foreignKey({
      columns: [postCategories.categoryId],
      foreignColumns: [categories.id],
    }),
    postIdx: index("post_categories_post_idx").on(postCategories.postId),
    categoryIdx: index("post_categories_category_idx").on(
      postCategories.categoryId
    ),
  })
);

// Comments table
export const comments = pgTable(
  "comments",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    content: text("content").notNull(),
    postId: uuid("post_id").notNull(),
    authorId: uuid("author_id").notNull(),
    parentId: uuid("parent_id"), // For nested comments
    status: pgEnum("comment_status", ["pending", "approved", "rejected"])(
      "status"
    )
      .default("pending")
      .notNull(),
    likeCount: integer("like_count").default(0).notNull(),
    isEdited: boolean("is_edited").default(false).notNull(),
    ipAddress: varchar("ip_address", { length: 45 }),
    userAgent: text("user_agent"),
    createdAt: timestamp("created_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
  },
  comments => ({
    postIdFk: foreignKey({
      columns: [comments.postId],
      foreignColumns: [posts.id],
    }),
    authorIdFk: foreignKey({
      columns: [comments.authorId],
      foreignColumns: [users.id],
    }),
    postIdx: index("comments_post_idx").on(comments.postId),
    authorIdx: index("comments_author_idx").on(comments.authorId),
    parentIdx: index("comments_parent_idx").on(comments.parentId),
    statusIdx: index("comments_status_idx").on(comments.status),
    createdAtIdx: index("comments_created_at_idx").on(comments.createdAt),
  })
);

// Zod schemas
export const insertPostSchema = createInsertSchema(posts, {
  title: z.string().min(1).max(255),
  slug: z.string().min(1).max(255),
  content: z.string().min(1),
  excerpt: z.string().max(500).optional(),
  seoTitle: z.string().max(255).optional(),
  seoDescription: z.string().max(500).optional(),
  readingTime: z.number().min(1).optional(),
});

export const selectPostSchema = createSelectSchema(posts);
export const updatePostSchema = insertPostSchema
  .partial()
  .omit({ id: true, createdAt: true });

// Types
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;
export type Category = typeof categories.$inferSelect;
export type Comment = typeof comments.$inferSelect;src/db/schema/posts.ts

Step 4: Database Connection and Configuration

Set up the database connection:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import * as schema from "./schema";

// Database configuration
const connectionString =
  process.env.DATABASE_URL ||
  `postgresql://${process.env.DB_USER}:${process.env.DB_PASSWORD}@${process.env.DB_HOST}:${process.env.DB_PORT}/${process.env.DB_NAME}`;

// Connection pool configuration
const queryClient = postgres(connectionString, {
  max: 20, // Maximum number of connections
  idle_timeout: 20, // Close connections after 20 seconds of inactivity
  connect_timeout: 60, // Connection timeout
  prepare: false, // Disable prepared statements for better compatibility
  onnotice: process.env.NODE_ENV === "development" ? console.log : undefined,
});

// Create Drizzle instance
export const db = drizzle(queryClient, {
  schema,
  logger: process.env.NODE_ENV === "development",
});

// Migration client (separate connection for migrations)
const migrationClient = postgres(connectionString, { max: 1 });
export const migrationDb = drizzle(migrationClient);

// Database utilities
export class DatabaseManager {
  static async testConnection(): Promise<boolean> {
    try {
      await queryClient`SELECT 1`;
      console.log("✅ Database connection successful");
      return true;
    } catch (error) {
      console.error("❌ Database connection failed:", error);
      return false;
    }
  }

  static async runMigrations(): Promise<void> {
    try {
      console.log("🔄 Running database migrations...");
      await migrate(migrationDb, { migrationsFolder: "./drizzle" });
      console.log("✅ Migrations completed successfully");
    } catch (error) {
      console.error("❌ Migration failed:", error);
      throw error;
    }
  }

  static async closeConnections(): Promise<void> {
    try {
      await queryClient.end();
      await migrationClient.end();
      console.log("✅ Database connections closed");
    } catch (error) {
      console.error("❌ Error closing database connections:", error);
    }
  }

  static async getConnectionInfo(): Promise<{
    totalConnections: number;
    activeConnections: number;
    maxConnections: number;
  }> {
    try {
      const [connectionInfo] = await queryClient`
        SELECT 
          (SELECT count(*) FROM pg_stat_activity) as total_connections,
          (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') as active_connections,
          (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') as max_connections
      `;

      return {
        totalConnections: connectionInfo.total_connections,
        activeConnections: connectionInfo.active_connections,
        maxConnections: connectionInfo.max_connections,
      };
    } catch (error) {
      console.error("Error getting connection info:", error);
      throw error;
    }
  }
}

// Graceful shutdown
process.on("SIGINT", async () => {
  console.log("🔄 Gracefully shutting down...");
  await DatabaseManager.closeConnections();
  process.exit(0);
});

process.on("SIGTERM", async () => {
  console.log("🔄 Gracefully shutting down...");
  await DatabaseManager.closeConnections();
  process.exit(0);
});src/db/connection.ts

Step 5: Repository Pattern Implementation

Create repository classes for data access:

import { eq, and, or, like, desc, asc, count, sql, isNull } from "drizzle-orm";
import { db } from "../connection";
import {
  users,
  type User,
  type NewUser,
  insertUserSchema,
  updateUserSchema,
} from "../schema/users";
import bcrypt from "bcryptjs";

export interface UserFilters {
  role?: string;
  status?: string;
  emailVerified?: boolean;
  search?: string;
  createdAfter?: Date;
  createdBefore?: Date;
}

export interface PaginationOptions {
  page: number;
  limit: number;
  sortBy?: keyof User;
  sortOrder?: "asc" | "desc";
}

export interface PaginatedResult<T> {
  data: T[];
  pagination: {
    page: number;
    limit: number;
    total: number;
    totalPages: number;
    hasNext: boolean;
    hasPrev: boolean;
  };
}

export class UserRepository {
  // Create a new user
  async create(
    userData: Omit<NewUser, "id" | "createdAt" | "updatedAt">
  ): Promise<User> {
    // Validate input data
    const validatedData = insertUserSchema.parse(userData);

    // Hash password
    const hashedPassword = await bcrypt.hash(validatedData.passwordHash, 12);

    try {
      const [user] = await db
        .insert(users)
        .values({
          ...validatedData,
          passwordHash: hashedPassword,
        })
        .returning();

      return user;
    } catch (error: any) {
      if (error.code === "23505") {
        // Unique violation
        if (error.detail?.includes("email")) {
          throw new Error("Email already exists");
        }
        if (error.detail?.includes("username")) {
          throw new Error("Username already exists");
        }
      }
      throw new Error(`Failed to create user: ${error.message}`);
    }
  }

  // Find user by ID
  async findById(id: string): Promise<User | null> {
    try {
      const [user] = await db
        .select()
        .from(users)
        .where(and(eq(users.id, id), isNull(users.deletedAt)));

      return user || null;
    } catch (error: any) {
      throw new Error(`Failed to find user by ID: ${error.message}`);
    }
  }

  // Find user by email
  async findByEmail(email: string): Promise<User | null> {
    try {
      const [user] = await db
        .select()
        .from(users)
        .where(
          and(eq(users.email, email.toLowerCase()), isNull(users.deletedAt))
        );

      return user || null;
    } catch (error: any) {
      throw new Error(`Failed to find user by email: ${error.message}`);
    }
  }

  // Find user by username
  async findByUsername(username: string): Promise<User | null> {
    try {
      const [user] = await db
        .select()
        .from(users)
        .where(
          and(
            eq(users.username, username.toLowerCase()),
            isNull(users.deletedAt)
          )
        );

      return user || null;
    } catch (error: any) {
      throw new Error(`Failed to find user by username: ${error.message}`);
    }
  }

  // Update user
  async update(id: string, userData: Partial<User>): Promise<User | null> {
    // Validate input data
    const validatedData = updateUserSchema.parse(userData);

    try {
      const [updatedUser] = await db
        .update(users)
        .set({
          ...validatedData,
          updatedAt: new Date(),
        })
        .where(and(eq(users.id, id), isNull(users.deletedAt)))
        .returning();

      return updatedUser || null;
    } catch (error: any) {
      if (error.code === "23505") {
        // Unique violation
        if (error.detail?.includes("email")) {
          throw new Error("Email already exists");
        }
        if (error.detail?.includes("username")) {
          throw new Error("Username already exists");
        }
      }
      throw new Error(`Failed to update user: ${error.message}`);
    }
  }

  // Soft delete user
  async delete(id: string): Promise<boolean> {
    try {
      const [deletedUser] = await db
        .update(users)
        .set({
          deletedAt: new Date(),
          updatedAt: new Date(),
        })
        .where(and(eq(users.id, id), isNull(users.deletedAt)))
        .returning({ id: users.id });

      return !!deletedUser;
    } catch (error: any) {
      throw new Error(`Failed to delete user: ${error.message}`);
    }
  }

  // Find users with pagination and filters
  async findMany(
    filters: UserFilters = {},
    pagination: PaginationOptions = { page: 1, limit: 20 }
  ): Promise<PaginatedResult<User>> {
    try {
      const conditions = [isNull(users.deletedAt)];

      // Apply filters
      if (filters.role) {
        conditions.push(eq(users.role, filters.role as any));
      }

      if (filters.status) {
        conditions.push(eq(users.status, filters.status as any));
      }

      if (filters.emailVerified !== undefined) {
        conditions.push(eq(users.emailVerified, filters.emailVerified));
      }

      if (filters.search) {
        const searchTerm = `%${filters.search.toLowerCase()}%`;
        conditions.push(
          or(
            like(sql`lower(${users.firstName})`, searchTerm),
            like(sql`lower(${users.lastName})`, searchTerm),
            like(sql`lower(${users.email})`, searchTerm),
            like(sql`lower(${users.username})`, searchTerm)
          )!
        );
      }

      if (filters.createdAfter) {
        conditions.push(sql`${users.createdAt} >= ${filters.createdAfter}`);
      }

      if (filters.createdBefore) {
        conditions.push(sql`${users.createdAt} <= ${filters.createdBefore}`);
      }

      const whereClause = and(...conditions);

      // Get total count
      const [{ total }] = await db
        .select({ total: count() })
        .from(users)
        .where(whereClause);

      // Calculate pagination
      const offset = (pagination.page - 1) * pagination.limit;
      const totalPages = Math.ceil(total / pagination.limit);

      // Build order by clause
      const sortColumn = pagination.sortBy || "createdAt";
      const sortDirection = pagination.sortOrder || "desc";
      const orderBy =
        sortDirection === "asc"
          ? asc(users[sortColumn])
          : desc(users[sortColumn]);

      // Get users
      const userResults = await db
        .select()
        .from(users)
        .where(whereClause)
        .orderBy(orderBy)
        .limit(pagination.limit)
        .offset(offset);

      return {
        data: userResults,
        pagination: {
          page: pagination.page,
          limit: pagination.limit,
          total,
          totalPages,
          hasNext: pagination.page < totalPages,
          hasPrev: pagination.page > 1,
        },
      };
    } catch (error: any) {
      throw new Error(`Failed to find users: ${error.message}`);
    }
  }

  // Verify password
  async verifyPassword(user: User, password: string): Promise<boolean> {
    try {
      return await bcrypt.compare(password, user.passwordHash);
    } catch (error: any) {
      throw new Error(`Failed to verify password: ${error.message}`);
    }
  }

  // Update password
  async updatePassword(id: string, newPassword: string): Promise<boolean> {
    try {
      const hashedPassword = await bcrypt.hash(newPassword, 12);

      const [updatedUser] = await db
        .update(users)
        .set({
          passwordHash: hashedPassword,
          passwordResetToken: null,
          passwordResetExpiresAt: null,
          updatedAt: new Date(),
        })
        .where(and(eq(users.id, id), isNull(users.deletedAt)))
        .returning({ id: users.id });

      return !!updatedUser;
    } catch (error: any) {
      throw new Error(`Failed to update password: ${error.message}`);
    }
  }

  // Update last login
  async updateLastLogin(id: string): Promise<void> {
    try {
      await db
        .update(users)
        .set({
          lastLoginAt: new Date(),
          loginAttempts: "0",
          lockedUntil: null,
          updatedAt: new Date(),
        })
        .where(eq(users.id, id));
    } catch (error: any) {
      throw new Error(`Failed to update last login: ${error.message}`);
    }
  }

  // Get user statistics
  async getStatistics(): Promise<{
    total: number;
    active: number;
    pending: number;
    verified: number;
    recentSignups: number;
  }> {
    try {
      const [stats] = await db
        .select({
          total: count(),
          active: count(sql`CASE WHEN ${users.status} = 'active' THEN 1 END`),
          pending: count(sql`CASE WHEN ${users.status} = 'pending' THEN 1 END`),
          verified: count(
            sql`CASE WHEN ${users.emailVerified} = true THEN 1 END`
          ),
          recentSignups: count(
            sql`CASE WHEN ${users.createdAt} >= NOW() - INTERVAL '7 days' THEN 1 END`
          ),
        })
        .from(users)
        .where(isNull(users.deletedAt));

      return stats;
    } catch (error: any) {
      throw new Error(`Failed to get user statistics: ${error.message}`);
    }
  }
}

// Export singleton instance
export const userRepository = new UserRepository();src/db/repositories/userRepository.ts

Step 6: Service Layer Implementation

Create service classes for business logic:

import {
  userRepository,
  type UserFilters,
  type PaginationOptions,
} from "../db/repositories/userRepository";
import { type User, type NewUser } from "../db/schema/users";
import { generateToken, verifyToken } from "../utils/jwt";
import { sendEmail } from "../utils/email";
import crypto from "crypto";

export interface CreateUserRequest {
  email: string;
  username: string;
  firstName: string;
  lastName: string;
  password: string;
  phone?: string;
  bio?: string;
}

export interface LoginRequest {
  email: string;
  password: string;
}

export interface AuthResult {
  user: Omit<
    User,
    "passwordHash" | "passwordResetToken" | "emailVerificationToken"
  >;
  token: string;
  refreshToken: string;
}

export class UserService {
  // Register a new user
  async register(
    userData: CreateUserRequest
  ): Promise<{ user: User; verificationToken: string }> {
    // Check if user already exists
    const existingUser = await userRepository.findByEmail(userData.email);
    if (existingUser) {
      throw new Error("User with this email already exists");
    }

    const existingUsername = await userRepository.findByUsername(
      userData.username
    );
    if (existingUsername) {
      throw new Error("Username already taken");
    }

    // Generate email verification token
    const verificationToken = crypto.randomBytes(32).toString("hex");

    // Create user
    const newUser: Omit<NewUser, "id" | "createdAt" | "updatedAt"> = {
      email: userData.email.toLowerCase(),
      username: userData.username.toLowerCase(),
      firstName: userData.firstName,
      lastName: userData.lastName,
      passwordHash: userData.password, // Will be hashed in repository
      phone: userData.phone,
      bio: userData.bio,
      emailVerificationToken: verificationToken,
      role: "user",
      status: "pending",
    };

    const user = await userRepository.create(newUser);

    // Send verification email
    await this.sendVerificationEmail(user.email, verificationToken);

    return { user, verificationToken };
  }

  // Login user
  async login(credentials: LoginRequest): Promise<AuthResult> {
    const { email, password } = credentials;

    // Find user
    const user = await userRepository.findByEmail(email);
    if (!user) {
      throw new Error("Invalid credentials");
    }

    // Check if user is active
    if (user.status !== "active") {
      throw new Error("Account is not active. Please verify your email.");
    }

    // Check if account is locked
    if (user.lockedUntil && user.lockedUntil > new Date()) {
      throw new Error("Account is temporarily locked. Please try again later.");
    }

    // Verify password
    const isValidPassword = await userRepository.verifyPassword(user, password);
    if (!isValidPassword) {
      // Increment login attempts
      await this.handleFailedLogin(user.id);
      throw new Error("Invalid credentials");
    }

    // Update last login
    await userRepository.updateLastLogin(user.id);

    // Generate tokens
    const token = generateToken({
      userId: user.id,
      email: user.email,
      role: user.role,
    });
    const refreshToken = generateToken(
      { userId: user.id, type: "refresh" },
      { expiresIn: "7d" }
    );

    // Remove sensitive data
    const {
      passwordHash,
      passwordResetToken,
      emailVerificationToken,
      ...safeUser
    } = user;

    return {
      user: safeUser,
      token,
      refreshToken,
    };
  }

  // Verify email
  async verifyEmail(token: string): Promise<User> {
    // In a real implementation, you'd query by emailVerificationToken
    // For now, we'll implement a basic version
    const users = await userRepository.findMany({}, { page: 1, limit: 1000 });
    const user = users.data.find(u => u.emailVerificationToken === token);

    if (!user) {
      throw new Error("Invalid or expired verification token");
    }

    if (user.emailVerified) {
      throw new Error("Email is already verified");
    }

    // Update user
    const updatedUser = await userRepository.update(user.id, {
      emailVerified: true,
      emailVerificationToken: null,
      status: "active",
    });

    if (!updatedUser) {
      throw new Error("Failed to verify email");
    }

    return updatedUser;
  }

  // Request password reset
  async requestPasswordReset(email: string): Promise<void> {
    const user = await userRepository.findByEmail(email);
    if (!user) {
      // Don't reveal that user doesn't exist
      return;
    }

    // Generate reset token
    const resetToken = crypto.randomBytes(32).toString("hex");
    const resetExpiresAt = new Date(Date.now() + 3600000); // 1 hour

    // Update user with reset token
    await userRepository.update(user.id, {
      passwordResetToken: resetToken,
      passwordResetExpiresAt: resetExpiresAt,
    });

    // Send reset email
    await this.sendPasswordResetEmail(user.email, resetToken);
  }

  // Reset password
  async resetPassword(token: string, newPassword: string): Promise<void> {
    // Find user by reset token
    const users = await userRepository.findMany({}, { page: 1, limit: 1000 });
    const user = users.data.find(
      u =>
        u.passwordResetToken === token &&
        u.passwordResetExpiresAt &&
        u.passwordResetExpiresAt > new Date()
    );

    if (!user) {
      throw new Error("Invalid or expired reset token");
    }

    // Update password
    const success = await userRepository.updatePassword(user.id, newPassword);
    if (!success) {
      throw new Error("Failed to reset password");
    }
  }

  // Get user profile
  async getProfile(
    userId: string
  ): Promise<
    Omit<User, "passwordHash" | "passwordResetToken" | "emailVerificationToken">
  > {
    const user = await userRepository.findById(userId);
    if (!user) {
      throw new Error("User not found");
    }

    const {
      passwordHash,
      passwordResetToken,
      emailVerificationToken,
      ...safeUser
    } = user;
    return safeUser;
  }

  // Update user profile
  async updateProfile(
    userId: string,
    updateData: Partial<User>
  ): Promise<User> {
    // Remove sensitive fields that shouldn't be updated directly
    const {
      passwordHash,
      emailVerificationToken,
      passwordResetToken,
      ...allowedUpdates
    } = updateData;

    const updatedUser = await userRepository.update(userId, allowedUpdates);
    if (!updatedUser) {
      throw new Error("User not found or update failed");
    }

    return updatedUser;
  }

  // Get users with pagination and filters
  async getUsers(filters: UserFilters, pagination: PaginationOptions) {
    return await userRepository.findMany(filters, pagination);
  }

  // Get user statistics
  async getStatistics() {
    return await userRepository.getStatistics();
  }

  // Handle failed login attempts
  private async handleFailedLogin(userId: string): Promise<void> {
    const user = await userRepository.findById(userId);
    if (!user) return;

    const attempts = parseInt(user.loginAttempts || "0") + 1;
    const updates: Partial<User> = {
      loginAttempts: attempts.toString(),
    };

    // Lock account after 5 failed attempts
    if (attempts >= 5) {
      updates.lockedUntil = new Date(Date.now() + 30 * 60 * 1000); // 30 minutes
    }

    await userRepository.update(userId, updates);
  }

  // Send verification email
  private async sendVerificationEmail(
    email: string,
    token: string
  ): Promise<void> {
    const verificationUrl = `${process.env.FRONTEND_URL}/verify-email?token=${token}`;

    await sendEmail({
      to: email,
      subject: "Verify your email address",
      template: "email-verification",
      data: {
        verificationUrl,
        companyName: process.env.COMPANY_NAME || "Your Company",
      },
    });
  }

  // Send password reset email
  private async sendPasswordResetEmail(
    email: string,
    token: string
  ): Promise<void> {
    const resetUrl = `${process.env.FRONTEND_URL}/reset-password?token=${token}`;

    await sendEmail({
      to: email,
      subject: "Reset your password",
      template: "password-reset",
      data: {
        resetUrl,
        companyName: process.env.COMPANY_NAME || "Your Company",
      },
    });
  }
}

// Export singleton instance
export const userService = new UserService();src/services/userService.ts

Step 7: Advanced Query Examples

Create complex queries and optimizations:

import { db } from "../db/connection";
import {
  users,
  posts,
  comments,
  categories,
  postCategories,
} from "../db/schema";
import {
  eq,
  and,
  or,
  like,
  desc,
  asc,
  count,
  sum,
  avg,
  sql,
  exists,
  inArray,
} from "drizzle-orm";
import { alias } from "drizzle-orm/pg-core";

export class AdvancedQueries {
  // Get users with their post count and latest post
  async getUsersWithPostStats() {
    const result = await db
      .select({
        user: {
          id: users.id,
          username: users.username,
          email: users.email,
          firstName: users.firstName,
          lastName: users.lastName,
        },
        postCount: count(posts.id),
        latestPostTitle: sql<string>`(
          SELECT title 
          FROM ${posts} 
          WHERE ${posts.authorId} = ${users.id} 
            AND ${posts.status} = 'published'
          ORDER BY ${posts.publishedAt} DESC 
          LIMIT 1
        )`,
        totalViews: sum(posts.viewCount),
        avgReadingTime: avg(posts.readingTime),
      })
      .from(users)
      .leftJoin(
        posts,
        and(eq(posts.authorId, users.id), eq(posts.status, "published"))
      )
      .where(eq(users.status, "active"))
      .groupBy(
        users.id,
        users.username,
        users.email,
        users.firstName,
        users.lastName
      )
      .orderBy(desc(count(posts.id)));

    return result;
  }

  // Get popular posts with author info and category count
  async getPopularPosts(limit = 10) {
    const result = await db
      .select({
        post: {
          id: posts.id,
          title: posts.title,
          slug: posts.slug,
          excerpt: posts.excerpt,
          viewCount: posts.viewCount,
          likeCount: posts.likeCount,
          commentCount: posts.commentCount,
          publishedAt: posts.publishedAt,
        },
        author: {
          id: users.id,
          username: users.username,
          firstName: users.firstName,
          lastName: users.lastName,
          avatar: users.avatar,
        },
        categoryCount: sql<number>`(
          SELECT COUNT(*)::int
          FROM ${postCategories}
          WHERE ${postCategories.postId} = ${posts.id}
        )`,
        categories: sql<string[]>`(
          SELECT array_agg(${categories.name})
          FROM ${postCategories}
          JOIN ${categories} ON ${categories.id} = ${postCategories.categoryId}
          WHERE ${postCategories.postId} = ${posts.id}
        )`,
      })
      .from(posts)
      .innerJoin(users, eq(users.id, posts.authorId))
      .where(eq(posts.status, "published"))
      .orderBy(desc(posts.viewCount), desc(posts.likeCount))
      .limit(limit);

    return result;
  }

  // Get posts with comment statistics and recent comments
  async getPostsWithCommentStats() {
    const recentComments = alias(comments, "recentComments");

    const result = await db
      .select({
        post: {
          id: posts.id,
          title: posts.title,
          slug: posts.slug,
          publishedAt: posts.publishedAt,
        },
        commentStats: {
          total: count(comments.id),
          approved: sql<number>`COUNT(CASE WHEN ${comments.status} = 'approved' THEN 1 END)::int`,
          pending: sql<number>`COUNT(CASE WHEN ${comments.status} = 'pending' THEN 1 END)::int`,
        },
        recentComments: sql<any[]>`(
          SELECT json_agg(
            json_build_object(
              'id', c.id,
              'content', c.content,
              'authorName', u.first_name || ' ' || u.last_name,
              'createdAt', c.created_at
            )
            ORDER BY c.created_at DESC
          )
          FROM ${comments} c
          JOIN ${users} u ON u.id = c.author_id
          WHERE c.post_id = ${posts.id} 
            AND c.status = 'approved'
          LIMIT 3
        )`,
      })
      .from(posts)
      .leftJoin(comments, eq(comments.postId, posts.id))
      .where(eq(posts.status, "published"))
      .groupBy(posts.id, posts.title, posts.slug, posts.publishedAt)
      .orderBy(desc(posts.publishedAt));

    return result;
  }

  // Search posts with full-text search and filters
  async searchPosts(
    searchTerm: string,
    categoryIds?: string[],
    authorId?: string,
    dateRange?: { start: Date; end: Date },
    pagination = { page: 1, limit: 20 }
  ) {
    const conditions = [eq(posts.status, "published")];

    // Full-text search
    if (searchTerm) {
      conditions.push(
        or(
          like(posts.title, `%${searchTerm}%`),
          like(posts.content, `%${searchTerm}%`),
          like(posts.excerpt, `%${searchTerm}%`)
        )!
      );
    }

    // Category filter
    if (categoryIds && categoryIds.length > 0) {
      conditions.push(
        exists(
          db
            .select()
            .from(postCategories)
            .where(
              and(
                eq(postCategories.postId, posts.id),
                inArray(postCategories.categoryId, categoryIds)
              )
            )
        )
      );
    }

    // Author filter
    if (authorId) {
      conditions.push(eq(posts.authorId, authorId));
    }

    // Date range filter
    if (dateRange) {
      conditions.push(
        and(
          sql`${posts.publishedAt} >= ${dateRange.start}`,
          sql`${posts.publishedAt} <= ${dateRange.end}`
        )!
      );
    }

    const whereClause = and(...conditions);

    // Get total count
    const [{ total }] = await db
      .select({ total: count() })
      .from(posts)
      .where(whereClause);

    // Calculate pagination
    const offset = (pagination.page - 1) * pagination.limit;
    const totalPages = Math.ceil(total / pagination.limit);

    // Get posts with related data
    const results = await db
      .select({
        post: {
          id: posts.id,
          title: posts.title,
          slug: posts.slug,
          excerpt: posts.excerpt,
          viewCount: posts.viewCount,
          likeCount: posts.likeCount,
          commentCount: posts.commentCount,
          readingTime: posts.readingTime,
          featuredImage: posts.featuredImage,
          publishedAt: posts.publishedAt,
        },
        author: {
          id: users.id,
          username: users.username,
          firstName: users.firstName,
          lastName: users.lastName,
          avatar: users.avatar,
        },
        categories: sql<any[]>`(
          SELECT json_agg(
            json_build_object(
              'id', c.id,
              'name', c.name,
              'slug', c.slug,
              'color', c.color
            )
          )
          FROM ${postCategories} pc
          JOIN ${categories} c ON c.id = pc.category_id
          WHERE pc.post_id = ${posts.id}
        )`,
      })
      .from(posts)
      .innerJoin(users, eq(users.id, posts.authorId))
      .where(whereClause)
      .orderBy(desc(posts.publishedAt))
      .limit(pagination.limit)
      .offset(offset);

    return {
      posts: results,
      pagination: {
        page: pagination.page,
        limit: pagination.limit,
        total,
        totalPages,
        hasNext: pagination.page < totalPages,
        hasPrev: pagination.page > 1,
      },
    };
  }

  // Get comprehensive analytics
  async getAnalytics(dateRange: { start: Date; end: Date }) {
    const analytics = await db
      .select({
        period: sql<string>`DATE_TRUNC('day', ${posts.publishedAt})::date`,
        postsPublished: count(posts.id),
        totalViews: sum(posts.viewCount),
        totalLikes: sum(posts.likeCount),
        totalComments: sum(posts.commentCount),
        avgReadingTime: avg(posts.readingTime),
        uniqueAuthors: sql<number>`COUNT(DISTINCT ${posts.authorId})`,
      })
      .from(posts)
      .where(
        and(
          eq(posts.status, "published"),
          sql`${posts.publishedAt} >= ${dateRange.start}`,
          sql`${posts.publishedAt} <= ${dateRange.end}`
        )
      )
      .groupBy(sql`DATE_TRUNC('day', ${posts.publishedAt})`)
      .orderBy(sql`DATE_TRUNC('day', ${posts.publishedAt})`);

    return analytics;
  }

  // Get category performance
  async getCategoryPerformance() {
    const result = await db
      .select({
        category: {
          id: categories.id,
          name: categories.name,
          slug: categories.slug,
          color: categories.color,
        },
        stats: {
          postCount: count(posts.id),
          totalViews: sum(posts.viewCount),
          totalLikes: sum(posts.likeCount),
          avgViewsPerPost: avg(posts.viewCount),
          lastPostDate: sql<Date>`MAX(${posts.publishedAt})`,
        },
      })
      .from(categories)
      .leftJoin(postCategories, eq(postCategories.categoryId, categories.id))
      .leftJoin(
        posts,
        and(eq(posts.id, postCategories.postId), eq(posts.status, "published"))
      )
      .where(eq(categories.isActive, true))
      .groupBy(
        categories.id,
        categories.name,
        categories.slug,
        categories.color
      )
      .orderBy(desc(count(posts.id)));

    return result;
  }
}

// Export singleton instance
export const advancedQueries = new AdvancedQueries();src/examples/advancedQueries.ts