Why does my Node.js Express MySQL CRUD API sometimes fail to return data or handle requests correctly? [closed]

Question

I have a Node.js REST API built with Express and MySQL (using mysql2). It provides basic CRUD operations (GET all items, GET by ID, POST, PUT, DELETE). The server starts correctly and the database connection is successful, but I’m unsure if the structure is correct and I sometimes get unexpected behavior like missing data or failed requests.

Here is my code:

const express = require('express'); const mysql = require('mysql2'); const cors = require('cors'); const app = express(); app.use(cors()); app.use(express.json()); const connection = mysql.createConnection({ host: 'localhost', user: 'root', password: '', database: 'database_name' }); connection.connect((err) => { if (err) { console.log(err); return; } console.log('Database connected'); }); // GET ALL DATA app.get('/api/items', (req, res) => { const sql = 'SELECT * FROM table_name'; connection.query(sql, (err, results) => { if (err) { res.status(500).json({ error: 'Database query failed' }); return; } res.json(results); }); }); // GET DATA BY ID app.get('/api/items/:id', (req, res) => { const id = req.params.id; const sql = 'SELECT * FROM table_name WHERE id = ?'; connection.query(sql, [id], (err, results) => { if (err) { res.status(500).json({ error: 'Database query failed' }); return; } if (results.length === 0) { res.status(404).json({ error: 'Item not found' }); return; } res.json(results[0]); }); }); // INSERT NEW DATA app.post('/api/items', (req, res) => { const { field1, field2, field3 } = req.body; const sql = ` INSERT INTO table_name (field1, field2, field3) VALUES (?, ?, ?) `; connection.query(sql, [field1, field2, field3], (err, results) => { if (err) { res.status(500).json({ error: 'Insert failed' }); return; } res.status(201).json({ message: 'Data inserted successfully', insertedId: results.insertId }); }); }); // UPDATE DATA app.put('/api/items/:id', (req, res) => { const id = req.params.id; const { field1, field2, field3 } = req.body; const sql = ` UPDATE table_name SET field1 = ?, field2 = ?, field3 = ? WHERE id = ? `; connection.query(sql, [field1, field2, field3, id], (err, results) => { if (err) { res.status(500).json({ error: 'Update failed' }); return; } res.json({ message: 'Data updated successfully' }); }); }); // DELETE DATA app.delete('/api/items/:id', (req, res) => { const id = req.params.id; const sql = 'DELETE FROM table_name WHERE id = ?'; connection.query(sql, [id], (err, results) => { if (err) { res.status(500).json({ error: 'Delete failed' }); return; } res.json({ message: 'Data deleted successfully' }); }); }); const PORT = 3000; app.listen(PORT, () => { console.log(`Server running on port ${PORT}`); });

What I’ve checked MySQL server is running Database exists Connection logs “Database connected” No syntax errors in Node.js console Problem

Sometimes requests don’t return expected results or I’m unsure if my error handling and query structure is correct. I’m also not sure if this is the proper way to structure a CRUD API with Express + MySQL.

Question

Is there anything wrong or risky in this implementation (connection handling, queries, or Express routing), and how should it be improved for reliability and best practice?