63 lines
1.9 KiB
JavaScript
63 lines
1.9 KiB
JavaScript
// db.js
|
|
// Handles PostgreSQL database connection and queries
|
|
|
|
const { Pool } = require('pg');
|
|
require('dotenv').config(); // Load environment variables from .env file
|
|
|
|
// Create a new pool instance using environment variables
|
|
// The pool manages multiple client connections
|
|
const pool = new Pool({
|
|
user: process.env.DB_USER,
|
|
host: process.env.DB_HOST,
|
|
database: process.env.DB_DATABASE,
|
|
password: process.env.DB_PASSWORD,
|
|
port: process.env.DB_PORT,
|
|
// Add connection pool settings for better performance
|
|
max: 20, // Maximum number of clients
|
|
idleTimeoutMillis: 30000, // How long a client is allowed to remain idle before being closed
|
|
connectionTimeoutMillis: 2000, // How long to wait for a connection
|
|
// Optional: Add SSL configuration if required by your database provider
|
|
// ssl: {
|
|
// rejectUnauthorized: false // Adjust as needed
|
|
// }
|
|
});
|
|
|
|
// Listen for errors on the pool
|
|
pool.on('error', (err) => {
|
|
console.error('Unexpected error on idle client', err);
|
|
process.exit(-1);
|
|
});
|
|
|
|
// Test the database connection on startup
|
|
pool.connect((err, client, release) => {
|
|
if (err) {
|
|
console.error('Error acquiring client for initial DB connection test:', err.stack);
|
|
// Exit the process if the database connection fails on startup
|
|
process.exit(1);
|
|
} else {
|
|
console.log('Successfully connected to PostgreSQL database.');
|
|
// Release the client back to the pool
|
|
release();
|
|
}
|
|
});
|
|
|
|
// Export functions to interact with the database
|
|
module.exports = {
|
|
query: (text, params) => pool.query(text, params),
|
|
// Add transaction helper
|
|
withTransaction: async (callback) => {
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
const result = await callback(client);
|
|
await client.query('COMMIT');
|
|
return result;
|
|
} catch (e) {
|
|
await client.query('ROLLBACK');
|
|
throw e;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
};
|