I have a program built in node js. The purpose of this program is to get information from the pos system of a cafe through an api and filter it according to the necessary parameters and write it to the db. And then the web page should print pending orders and completed orders. When I run this program from my local computer and connect to the postgresql database on my computer, the program works perfectly, but when I upload it to the railway server, sometimes it displays the prepared and ready orders to the web page very late or sometimes it doesn't even show. It's my first time to create such a program in nodejs, please review the code and help me correct my code if there are any wrong parts. I think the code is correct in general, but there are some small problems in the code that adds, deletes, updates new data to the postgresql database and sorts the orders on the web page, please help me to fix it.
My code:
const express = require('express');
const http = require('http');
const axios = require('axios');
const { Pool } = require('pg');
const { DateTime } = require('luxon');
const { env } = require('process');
const cors = require('cors');
const app = express();
const port = 3000;
app.use(express.static('public'));
// Add cors middleware here
app.use(cors());
// const pool = new Pool({
// user: 'postgres',
// host: 'localhost', // Typically 'localhost' or '127.0.0.1' for local development
// database: 'db',
// password: 'password',
// port: 5432,
// });
const pool = new Pool({
user: 'user here',
host: 'host here',
database: 'database here',
password: 'password here',
// port: 26243,
});
const apiEndpoint = 'https://joinposter.com/api/dash.getTransactions';
const accessToken = "token_here";
const waitingMinutes = 3;
async function createTableWithRetry() {
const maxRetries = 3; // Set the maximum number of retries
let retryCount = 0;
while (retryCount < maxRetries) {
const client = await pool.connect();
try {
await client.query(`
CREATE TABLE IF NOT EXISTS orders (
id BIGSERIAL PRIMARY KEY,
transaction_id VARCHAR(355) UNIQUE,
product_id_count INT,
finishedcooking_count INT,
is_printed BOOLEAN DEFAULT FALSE,
finished_at TIMESTAMP DEFAULT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
`);
// If the operation is successful, break out of the loop
break;
} catch (error) {
// Handle the error gracefully
if (error.code === 'ENOTFOUND' || error.code === 'EAI_AGAIN') {
console.error(`Error: Could not resolve hostname. Check your PostgreSQL host configuration.`);
} else {
console.error(`Error creating table. Attempt ${retryCount + 1}/${maxRetries}:`, error.message);
// Retry the operation after a delay
await new Promise(resolve => setTimeout(resolve, 1000)); // Add a delay (1 second in this example)
}
// Increment the retry count
retryCount++;
} finally {
client.release();
}
}
if (retryCount === maxRetries) {
console.error(`Maximum number of retries reached. Operation failed.`);
}
}
async function fetchOrders() {
const client = await pool.connect();
try {
const result = await client.query(`
SELECT * FROM orders
WHERE NOW() - created_at <= INTERVAL '80 minutes'
AND NOW() - created_at >= INTERVAL '0 minutes'
ORDER BY transaction_id DESC;
`);
return result.rows;
} finally {
client.release();
}
}
async function fetchData() {
try {
const today = DateTime.now().toFormat('yyyyMMdd');
const url = `${apiEndpoint}?token=${accessToken}&dateFrom=${today}&dateTo=${today}&include_history=true`;
const response = await axios.get(url, { timeout: 20000 });
const data = response.data;
return data;
} catch (error) {
console.error('Error fetching data:', error);
return null;
}
}
async function insertOrUpdateData(data) {
const client = await pool.connect();
try {
await client.query('BEGIN');
for (const transaction of data.response) {
const transactionId = transaction.transaction_id;
const productIdCount = transaction.history
.filter((history) => history.type_history === 'sendtokitchen')
.flatMap((history) => history.value_text)
.filter((entry) => 'product_id' in entry)
.length;
const finishedcookingCount = transaction.history
.filter((history) => history.type_history === 'finishedcooking')
.length;
await client.query(
`
INSERT INTO orders (transaction_id, product_id_count, finishedcooking_count)
VALUES ($1, $2, $3)
ON CONFLICT (transaction_id) DO UPDATE
SET product_id_count = $2, finishedcooking_count = $3;
`,
[transactionId, productIdCount, finishedcookingCount]
);
}
await client.query('COMMIT');
} catch (error) {
await client.query('ROLLBACK');
console.error('Error inserting/updating data:', error);
} finally {
client.release();
}
}
async function markOrdersPrinted() {
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query(`
UPDATE orders
SET is_printed = TRUE,
finished_at = CASE
WHEN finished_at IS NULL THEN NOW()
ELSE finished_at
END
WHERE product_id_count = finishedcooking_count
AND product_id_count != 0;
`);
await client.query('COMMIT');
} catch (error) {
await client.query('ROLLBACK');
console.error('Error marking orders as printed:', error);
} finally {
client.release();
}
}
async function deleteOldFinishedOrders() {
const client = await pool.connect();
try {
const currentTime = DateTime.now();
if (currentTime.hour === 4 && currentTime.minute === 10) {
await client.query('DROP TABLE IF EXISTS orders;');
console.log("Table 'orders' dropped at 4:10 AM");
await new Promise(resolve => setTimeout(resolve, 5000));
await client.query(`
CREATE TABLE IF NOT EXISTS orders (
id SERIAL PRIMARY KEY,
transaction_id VARCHAR(255) UNIQUE,
product_id_count INT,
finishedcooking_count INT,
is_printed BOOLEAN DEFAULT FALSE,
finished_at TIMESTAMP DEFAULT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
`);
console.log("Table 'orders' recreated.");
}
await new Promise((resolve) => setTimeout(resolve, 1000));
} catch (error) {
console.error('Error deleting old finished orders:', error);
} finally {
client.release();
}
}
async function main() {
while (true) {
const apiData = await fetchData();
if (apiData) {
await insertOrUpdateData(apiData);
await new Promise((resolve) => setTimeout(resolve, 1000));
await markOrdersPrinted();
await new Promise((resolve) => setTimeout(resolve, 1000));
await deleteOldFinishedOrders();
await new Promise((resolve) => setTimeout(resolve, 1000));
}
await new Promise((resolve) => setTimeout(resolve, 5000));
}
}
app.get('/', async (req, res) => {
const orders = await fetchOrders();
const preparingOrders = orders.filter(
(order) => order.product_id_count > 0 && order.finishedcooking_count < order.product_id_count
);
const readyOrders = orders.filter(
(order) =>
order.product_id_count === order.finishedcooking_count &&
order.product_id_count !== 0 &&
DateTime.now().diff(DateTime.fromJSDate(order.finished_at), 'minutes').minutes < parseInt(waitingMinutes)
);
app.get('/fetch_data', async (req, res) => {
const orders = await fetchOrders(); // Implement fetchOrders function
res.json({
preparingOrders: orders.filter(order => order.product_id_count > 0 && order.finishedcooking_count < order.product_id_count),
readyOrders: orders.filter(order => order.product_id_count === order.finishedcooking_count &&
order.product_id_count !== 0 &&
DateTime.now().diff(DateTime.fromJSDate(order.finished_at), 'minutes').minutes < parseInt(waitingMinutes))
});
});
const htmlResponse = `
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Order Status</title>
<link rel="icon" href="public/awlogo.png" type="image/png">
<style>
body {
margin: 0;
font-family: Arial, sans-serif;
}
.container {
display: flex;
margin: 0 auto; /* markazga joylashgan */
padding: 0px; /* tepa, chap, past va o'ng yonida 20 px ichki padding */
box-sizing: border-box; /* padding va borderni ichki qismini qo'shadi */
}
.yellow-side {
flex: 1;
background-color: yellow;
padding: 0px;
height: 1000vh;
}
.green-side {
flex: 1;
background-color: green;
padding: 0px;
height: 1000vh;
}
ul {
list-style-type: none;
padding: 0;
display: flex;
flex-wrap: wrap;
justify-content: flex-start;
margin-left: 5px;
margin-right: -44px;
}
li {
background-color: rgba(128, 128, 128, 0.5);
margin: 7px;
padding: 0px;
padding-right: 1px;
padding-left: 1px;
border-radius: 99px;
flex: 1 0 200px;
max-width: calc(12%);
text-align: center;
font-size: 45px;
font-weight: bold;
line-height: 0px;
}
.bolim{
text-align: center;
font-size: 28px;
}
.tana {
width: 1920;
height: 1080;
}
</style>
</head>
<body class="tana">
<div class="container">
<div class="yellow-side">
<h1 class="bolim">ГОТОВИТСЯ</h1>
<ul id="preparingOrdersList">
${preparingOrders.map((order) => `<li><p>${order.transaction_id.slice(-2)}</p></li>`).join('')}
</ul>
</div>
<div class="green-side">
<h1 class="bolim">ГОТОВЫЙ</h1>
<ul id="readyOrdersList">
${readyOrders.map((order) => `<li><p>${order.transaction_id.slice(-2)}</p></li>`).join('')}
</ul>
</div>
</div>
<script>
async function fetchDataAndUpdate() {
try {
const response = await fetch('/fetch_data');
if (response.ok) {
const data = await response.json();
updateOrdersList(data.preparingOrders, 'preparingOrdersList');
updateOrdersList(data.readyOrders, 'readyOrdersList');
}
} catch (error) {
console.error('Error fetching data:', error);
}
}
function updateOrdersList(orders, listId) {
const list = document.getElementById(listId);
list.innerHTML = orders.map(order => \`<li><h3>\${order.transaction_id.slice(-2)}</h3></li>\`).join('');
}
setInterval(fetchDataAndUpdate, 8000);
</script>
</body>
</html>
`;
res.send(htmlResponse);
});
app.listen(port, () => {
console.log(`Server is running on port ${port}`);
});
(async () => {
await createTableWithRetry();
await main();
})();
Here are some errors that came up during testing after uploading the app to the railway server:
2024-02-19 21:20:50.698 UTC [8] LOG: listening on IPv4 address "0.0.0.0", port 5432
2024-02-19 21:20:50.698 UTC [8] LOG: listening on IPv6 address "::", port 5432
2024-02-19 21:20:50.712 UTC [8] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-02-19 21:20:50.718 UTC [78] LOG: database system was shut down at 2024-02-19 21:20:50 UTC
2024-02-19 21:20:50.724 UTC [8] LOG: database system is ready to accept connections
2024-02-19 21:25:50.815 UTC [76] LOG: checkpoint starting: time
2024-02-19 21:25:55.047 UTC [76] LOG: checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.221 s, sync=0.005 s, total=4.233 s; sync files=12, longest=0.003 s, average=0.001 s; distance=261 kB, estimate=261 kB; lsn=0/1954508, redo lsn=0/19544D0
2024-02-19 21:29:29.138 UTC [105] ERROR: deadlock detected
2024-02-19 21:29:29.138 UTC [105] DETAIL: Process 105 waits for ShareLock on transaction 757; blocked by process 119.
Process 119 waits for ShareLock on transaction 755; blocked by process 105.
Process 105:
INSERT INTO orders (transaction_id, product_id_count, finishedcooking_count)
VALUES ($1, $2, $3)
ON CONFLICT (transaction_id) DO UPDATE
This is an error in the postgresql db log in Railway:
2024-02-19 21:35:50.983 UTC [76] LOG: checkpoint starting: time
2024-02-19 21:35:52.503 UTC [76] LOG: checkpoint complete: wrote 16 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.506 s, sync=0.003 s, total=1.521 s; sync files=10, longest=0.003 s, average=0.001 s; distance=508 kB, estimate=508 kB; lsn=0/1A334A8, redo lsn=0/1A33470
2024-02-19 21:40:50.603 UTC [76] LOG: checkpoint starting: time
2024-02-19 21:40:51.717 UTC [76] LOG: checkpoint complete: wrote 12 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.105 s, sync=0.004 s, total=1.115 s; sync files=7, longest=0.003 s, average=0.001 s; distance=244 kB, estimate=482 kB; lsn=0/1A70838, redo lsn=0/1A707F8
2024-02-19 21:45:50.817 UTC [76] LOG: checkpoint starting: time
2024-02-19 21:45:51.731 UTC [76] LOG: checkpoint complete: wrote 10 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.904 s, sync=0.003 s, total=0.914 s; sync files=6, longest=0.003 s, average=0.001 s; distance=25 kB, estimate=436 kB; lsn=0/1A76DE0, redo lsn=0/1A76D98
2024-02-19 21:55:16.358 UTC [244] LOG: unexpected EOF on client connection with an open transaction
2024-02-19 21:55:16.358 UTC [119] LOG: unexpected EOF on client connection with an open transaction
2024-02-19 21:55:16.368 UTC [302] LOG: unexpected EOF on client connection with an open transaction
When the program is working properly, it should display like this, but sometimes it doesn't display at all. When the program is working properly, it should display like this , but sometimes it doesn't display at all.
I thought that the cause of the program's malfunction was the time of the functions that work with the postgresql database, and I tried to change their times in every way, but it didn't work.
I think the problem is in this part of the code:
async function main() {
while (true) {
const apiData = await fetchData();
if (apiData) {
await insertOrUpdateData(apiData);
await new Promise((resolve) => setTimeout(resolve, 1000));
await markOrdersPrinted();
await new Promise((resolve) => setTimeout(resolve, 1000));
await deleteOldFinishedOrders();
await new Promise((resolve) => setTimeout(resolve, 1000));
}
await new Promise((resolve) => setTimeout(resolve, 5000));
}
}