Logging and Auditing Database Queries

In this lesson, you will learn how to instrument your database layer to monitor performance and maintain an audit trail of data access.

Why Monitor Database Queries?

As your application grows, database performance becomes a common bottleneck. "Slow queries" are operations that take longer than expected, often due to missing indexes or inefficient logic. By logging your queries, you gain visibility into what your database is doing, who requested the data, and how long the operation took.

Auditing, on the other hand, is about accountability. It involves recording what data was changed and who changed it—a critical requirement for security and debugging in production environments.

Implementation Strategy

The most effective way to log queries is to intercept them at the database driver level. Since you are already managing connection pools, you can wrap your execution method to time the operation and log the details.

Here is a simplified example of how you might instrument a query function:

async function executeQuery(sql, params) {
  const startTime = Date.now();
  
  try {
    const result = await pool.query(sql, params);
    const duration = Date.now() - startTime;

    // Log slow queries (e.g., taking longer than 100ms)
    if (duration > 100) {
      console.warn(`Slow query detected: ${duration}ms | SQL: ${sql}`);
    }

    // Audit log (simplified)
    console.log(`Query executed successfully: ${sql}`);
    
    return result;
  } catch (err) {
    console.error(`Database error: ${err.message} | SQL: ${sql}`);
    throw err;
  }
}

Best Practices for Production

When logging queries, avoid logging sensitive information (like user passwords or PII) in plain text. Always ensure that your logs are sent to a centralized system rather than just the standard output, as production logs can become massive very quickly. By tracking query duration, you can identify which queries require better indexing or further normalization to maintain application speed.

Key Takeaway: Instrumenting your database layer with time-tracking and logging provides the essential observability needed to diagnose performance bottlenecks and ensure data accountability.