Node.js定时查询MSSQL表并更新bit值问题求助

Hey there! Let's break down your problem step by step and fix those issues one by one.

First, let's figure out why your server keeps crashing after 3-4 runs. The most common culprits here are unhandled async errors, mismanaged database connections, or task stacking from setInterval. Here's how to fix it:

Key Issues & Solutions #

a. Always Catch Async Errors #

MSSQL operations are async, and any uncaught promise rejection will crash your Node.js process. Wrap all database logic in try/catch blocks to handle errors gracefully.

b. Reuse Database Connection Pools #

Don't create a new database connection every time your interval runs—this leads to connection leaks and resource exhaustion. MSSQL uses connection pools by default; just initialize it once and reuse it.

c. Use a Reliable Scheduling Library #

setInterval can cause task stacking if your database operation takes longer than the interval itself. Instead, use node-schedule for precise, non-stacking scheduled tasks.

Working Code Example #

First install dependencies:

npm install mssql node-schedule

Then implement the logic:

const sql = require('mssql');
const schedule = require('node-schedule');

// MSSQL Connection Config
const dbConfig = {
    user: 'your_db_user',
    password: 'your_db_password',
    server: 'your_db_server',
    database: 'your_db_name',
    options: {
        encrypt: true, // Enable if using Azure SQL
        trustServerCertificate: true // Safe for dev; disable in production
    },
    pool: { max: 10, min: 0, idleTimeoutMillis: 30000 }
};

// Initialize Connection Pool
async function initDbPool() {
    try {
        await sql.connect(dbConfig);
        console.log('Database pool initialized successfully');
    } catch (err) {
        console.error('Failed to connect to database:', err);
        process.exit(1); // Exit if connection fails
    }
}

// Core Logic: Check & Update Records
async function checkAndUpdate() {
    try {
        // Format current time to match MSSQL datetime (no milliseconds)
        const now = new Date();
        const formattedNow = now.toISOString().slice(0, 19).replace('T', ' ');

        const request = new sql.Request();
        // Fetch records that need updating
        const queryResult = await request.query(`
            SELECT id FROM your_table 
            WHERE datetime_column = '${formattedNow}' AND bit_column = 0
        `);

        if (queryResult.recordset.length > 0) {
            // Batch update matching records
            await request.query(`
                UPDATE your_table 
                SET bit_column = 1 
                WHERE datetime_column = '${formattedNow}' AND bit_column = 0
            `);
            console.log(`Updated ${queryResult.recordset.length} records at ${formattedNow}`);
            
            // We'll add Socket.io emit here later
        } else {
            console.log(`No records to update at ${formattedNow}`);
        }
    } catch (err) {
        console.error('Error during check/update:', err);
        // Add retry logic here if needed (e.g., setTimeout to re-run the function)
    }
}

// Start Everything
initDbPool().then(() => {
    // Run check every second (adjust cron syntax for other intervals)
    schedule.scheduleJob('* * * * * *', checkAndUpdate);
    console.log('Scheduled check task started');
});

If you want to notify frontends immediately when records are updated, Socket.io is perfect for this. Here's how to integrate it:

First install Socket.io:

npm install socket.io express

Update your code to include Socket.io:

const http = require('http');
const express = require('express');
const { Server } = require('socket.io');

// Set up Express & HTTP server
const app = express();
const server = http.createServer(app);

// Initialize Socket.io with CORS (allow your frontend origin)
const io = new Server(server, {
    cors: {
        origin: "http://your_frontend_domain",
        methods: ["GET", "POST"]
    }
});

// Handle client connections
io.on('connection', (socket) => {
    console.log('Frontend client connected');
    socket.on('disconnect', () => {
        console.log('Frontend client disconnected');
    });
});

// ... Keep your existing database/schedule code ...

// Modify the checkAndUpdate function to emit updates
async function checkAndUpdate() {
    try {
        // ... Existing logic ...
        
        if (queryResult.recordset.length > 0) {
            // ... Existing update logic ...
            
            // Emit update event to all connected clients
            io.emit('records-updated', {
                count: queryResult.recordset.length,
                timestamp: formattedNow
            });
        }
    } catch (err) {
        // ... Existing error handling ...
    }
}

// Start the HTTP server
const PORT = process.env.PORT || 3000;
server.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
});

Frontend Example (Native JS) #

Add this to your frontend to listen for updates:

<script src="/socket.io/socket.io.js"></script>
<script>
    const socket = io('http://your_server_domain:3000');
    
    socket.on('records-updated', (data) => {
        alert(`Updated ${data.count} records at ${data.timestamp}`);
        // Update your UI here (e.g., refresh a table)
    });
</script>

• Timezone Alignment: Make sure your Node.js server and MSSQL database use the same timezone (e.g., UTC) to avoid mismatches. Set the TZ environment variable in Node.js if needed.
• Indexing: Add a composite index on datetime_column and bit_column to speed up your query, especially if your table is large.
• Logging: Use a library like winston to log detailed activity, making it easier to debug issues later.

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