How to Monitor Slow Database Queries

// lib/db.ts
import { PrismaClient } from "@prisma/client";
import { logit } from "./logit";

const SLOW_QUERY_MS = 1000;

const prisma = new PrismaClient();

prisma.$use(async (params, next) => {
  const start = Date.now();
  const result = await next(params);
  const duration = Date.now() - start;

  if (duration > SLOW_QUERY_MS) {
    await logit.now("db", {
      event: "Slow query detected",
      description: `${params.model}.${params.action} — ${duration}ms`,
      icon: "🐢",
      notify: duration > 5000,
      tags: { model: params.model ?? "unknown", action: params.action },
      metadata: {
        model: params.model,
        action: params.action,
        durationMs: duration,
      },
    });
  }

  return result;
});

export { prisma };

// lib/db.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { logit } from "./logit";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export const db = drizzle(pool, {
  logger: {
    logQuery: async (query, params) => {
      // Drizzle doesn't expose timing natively; use a wrapper
    },
  },
});

// Wrapper for timed queries
export async function timedQuery<T>(
  label: string,
  queryFn: () => Promise<T>
): Promise<T> {
  const start = Date.now();
  const result = await queryFn();
  const duration = Date.now() - start;

  if (duration > 1000) {
    await logit.now("db", {
      event: "Slow query",
      description: `${label} — ${duration}ms`,
      icon: "🐢",
      notify: false,
      metadata: { label, durationMs: duration },
    });
  }

  return result;
}

// Usage:
const users = await timedQuery("getUsersWithPosts", () =>
  db.select().from(usersTable).leftJoin(postsTable, eq(usersTable.id, postsTable.userId))
);

// Simple query counter middleware
let queryCount = 0;
const requestStart = Date.now();

// In your request context
if (queryCount > 20) {
  await logit.now("db", {
    event: "Possible N+1 query pattern",
    description: `${queryCount} queries in one request`,
    icon: "⚠️",
    notify: false,
    metadata: { queryCount, requestDurationMs: Date.now() - requestStart },
  });
}