Database Monitoring

You are a backend engineer implementing database query monitoring. The user wants to add comprehensive query logging, performance tracking, and execution analysis to their database layer.

What to check first

• Verify your ORM/database driver supports query hooks or middleware (check sequelize.addHook(), mongoose.plugin(), or native driver events)
• Confirm logging library is installed: npm list winston or npm list pino
• Check if you have database connection access in your application entry point

Steps

1. Install a monitoring package: npm install winston for structured logging (or use pino for faster performance)
2. Create a dedicated monitoring module that wraps your database driver's query event system
3. Implement a hook/middleware in your ORM that intercepts every query execution
4. Log the query string, parameters, execution time, and result status to the monitoring module
5. Add performance thresholds to flag slow queries (e.g., queries over 1000ms)
6. Implement error logging that captures query failures with stack traces and context
7. Add metrics collection (query count, average execution time) for dashboarding
8. Set up log rotation and persistence to prevent disk space issues

Code

// database-monitor.js
const winston = require('winston');

const logger = winston.createLogger({
  level: 'info',
  format: winston.format.json(),
  transports: [
    new winston.transports.File({ filename: 'logs/db-error.log', level: 'error' }),
    new winston.transports.File({ filename: 'logs/db-all.log' }),
  ],
});

const SLOW_QUERY_THRESHOLD = 1000; // milliseconds
const metrics = {
  totalQueries: 0,
  totalTime: 0,
  slowQueries: 0,
};

function logQuery(query, params, duration, status, error = null) {
  const logEntry = {
    timestamp: new Date().toISOString(),
    query: query.substring(0, 200),
    parameters: JSON.stringify(params).substring(0, 100),
    duration,
    status,
    isSlow: duration > SLOW_QUERY_THRESHOLD,
    error: error ? error.message : null,
  };

  if (error) {
    logger.error('Database query failed', logEntry);
  } else if (duration > SLOW_QUERY_THRESHOLD) {
    logger.warn('Slow query detected', logEntry);
    metrics.slowQueries += 1;
  } else {
    logger.info('Query executed', logEntry);
  }

  metrics.totalQueries += 1;
  metrics.totalTime += duration;
}

function attachMonitoring(sequelize) {
  sequelize.addHook('beforeConnect', (config) => {
    config.logging = false; // Disable built-in logging
  });

  sequelize.addHook('beforeFind', (

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change