custom drizzle logger with pretty printing for queries

export function formatSqlQuery(query: string): string {
  const keywords = [
    "SELECT",
    "FROM",
    "WHERE",
    "LEFT JOIN",
    "RIGHT JOIN",
    "INNER JOIN",
    "OUTER JOIN",
    "ON",
    "AND",
    "OR",
    "GROUP BY",
    "ORDER BY",
    "HAVING",
    "LIMIT",
    "OFFSET",
    "LATERAL",
    "COALESCE",
    "JSON_AGG",
    "JSON_BUILD_ARRAY",
  ];

  let formattedQuery = query.replace(/\s+/g, " ").trim();

  formattedQuery = formattedQuery.replace(/"([^"]+)"/g, (match) => {
    return match.replace(/\s+/g, "_SPACE_");
  });

  formattedQuery = formattedQuery.replace(/\bSELECT\b/i, "SELECT\n");

  keywords.forEach((keyword) => {
    if (keyword !== "SELECT") {
      const regex = new RegExp(`\\b${keyword}\\b`, "gi");
      formattedQuery = formattedQuery.replace(regex, `\n${keyword}`);
    }
  });

  // Add newline after every comma
  formattedQuery = formattedQuery.replace(/,/g, ",\n");

  formattedQuery = formattedQuery.replace(/_SPACE_/g, " ");

  const lines = formattedQuery.split("\n");
  let indentLevel = 0;
  formattedQuery = lines
    .map((line) => {
      line = line.trim();

      if (line.startsWith(")")) {
        indentLevel = Math.max(0, indentLevel - 1);
      }

      const indentedLine = "  ".repeat(indentLevel) + line;

      if (line.includes("(") && !line.includes(")")) {
        indentLevel++;
      }

      return indentedLine;
    })
    .join("\n");

  return formattedQuery;
};

usage

import { drizzle } from "drizzle-orm/node-postgres";
import pg from "pg";
import * as userSchema from "./schema/users.ts";
import * as projectSchema from "./schema/project.ts";
import { Logger } from "drizzle-orm/logger";
import { formatSqlQuery } from "./helpers/query-logger.ts";

class MyLogger implements Logger {
  logQuery(query: string, params: unknown[]): void {
    console.log("=== DRIZZLE QUERY ===");
    console.log(`%c${formatSqlQuery(query)}`, "color:cyan");
    if (params && params.length > 0) {
      console.log("=== DRIZZLE PARAMS ===");
      console.log(`%c${JSON.stringify(params)}`, "color:blue");
    }
  }
}

// Use pg driver.
const { Pool } = pg;
// Instantiate Drizzle client with pg driver and schema.
export const db = drizzle({
  client: new Pool({
    connectionString: Deno.env.get("DATABASE_URL"),
  }),
  schema: { ...userSchema, ...projectSchema },
  logger: new MyLogger(),
});

tigawanna/drizzle_query_logger.md