如何在Node.js、Express中统计URL访问次数并存储（CRM场景）

Hey there! Let's walk through how to implement link visit tracking and storage in your Node.js/Express CRM step by step. This solution is lightweight, easy to maintain, and fits perfectly for a basic CRM system. We'll use SQLite for data storage since it doesn't require a separate database server—ideal for small to mid-sized projects.

First, let's get the packages we need. We'll use express for the web framework and sqlite3 for lightweight, file-based database storage:

npm install express sqlite3

We'll create a simple database table to store visit data. The table will track:

• id: Unique primary key
• link_path: The specific URL path we're tracking (e.g., /crm/lead/123)
• visit_count: Total number of visits to this link
• last_visited: Timestamp of the most recent visit

Here's how to set up the database connection and create the table on server startup:

const express = require('express');
const sqlite3 = require('sqlite3').verbose();

// Initialize Express app
const app = express();
const port = 3000;

// Connect to SQLite database (creates the file if it doesn't exist)
const db = new sqlite3.Database('./crm_visits.db', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the CRM visits database.');
});

// Create visits table if it doesn't exist
db.run(`CREATE TABLE IF NOT EXISTS link_visits (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  link_path TEXT NOT NULL UNIQUE,
  visit_count INTEGER DEFAULT 1,
  last_visited DATETIME DEFAULT CURRENT_TIMESTAMP
)`);

Next, we'll create a route that listens for requests to your target link. When a user visits the link, we'll update the visit count in the database:

// Track visits to a specific link (e.g., GET /crm/track/lead-contact)
app.get('/crm/track/:linkPath', (req, res) => {
  const { linkPath } = req.params;

  // First, check if the link already exists in the database
  db.get('SELECT * FROM link_visits WHERE link_path = ?', [linkPath], (err, row) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }

    if (row) {
      // Link exists: increment visit count and update last visited time
      db.run(`UPDATE link_visits 
              SET visit_count = visit_count + 1, 
                  last_visited = CURRENT_TIMESTAMP 
              WHERE link_path = ?`, 
              [linkPath], 
              (err) => {
        if (err) {
          return res.status(500).json({ error: err.message });
        }
        res.status(200).json({ 
          message: 'Visit tracked successfully',
          link: linkPath,
          total_visits: row.visit_count + 1
        });
      });
    } else {
      // Link doesn't exist: insert new record
      db.run(`INSERT INTO link_visits (link_path) VALUES (?)`, 
              [linkPath], 
              (err) => {
        if (err) {
          return res.status(500).json({ error: err.message });
        }
        res.status(200).json({ 
          message: 'New link tracked',
          link: linkPath,
          total_visits: 1
        });
      });
    }
  });
});

// Start the server
app.listen(port, () => {
  console.log(`CRM tracking server running on http://localhost:${port}`);
});

• Start your server with node server.js
• Visit a tracked link in your browser or use tools like curl/Postman:

  curl http://localhost:3000/crm/track/client-onboarding
  

• Visit the link multiple times, and you'll see the total_visits increment each time.
• To verify the data in the database, you can use a SQLite viewer or run a query directly in your code:

  db.all('SELECT * FROM link_visits', (err, rows) => {
    if (err) {
      console.error(err.message);
    }
    console.log('All tracked visits:', rows);
  });
  

If you want to expand this for your CRM, consider adding:

• User-specific tracking: If your CRM has authenticated users, add a user_id column to track which users are visiting links.
• IP tracking: Store the client's IP address with req.ip for additional context.
• MongoDB alternative: If you prefer NoSQL, replace SQLite with MongoDB using the mongoose package.
• Dashboard endpoint: Create a route to fetch all visit data for your CRM admin panel (e.g., /crm/visits/stats).

内容的提问来源于stack exchange，提问作者xetryDcoder