10.5 Node.js (Express) with MySQL

# Amazon Linux 2023 / CentOS Stream 9
curl -fsSL https://rpm.nodesource.com/setup_22.x | sudo bash -
sudo yum install -y nodejs git

# Ubuntu
curl -fsSL https://deb.nodesource.com/setup_22.x | sudo -E bash -
sudo apt install -y nodejs git

node -v && npm -v

sudo mkdir -p /opt/nodeapp && sudo chown $USER:$USER /opt/nodeapp
cd /opt/nodeapp
npm init -y
npm install express mysql2 dotenv

cat > .env <<'EOF'
DB_HOST=127.0.0.1
DB_USER=appuser
DB_PASS=App@Pass123
DB_NAME=appdb
PORT=3000
EOF
chmod 600 .env

cat > app.js <<'EOF'
require("dotenv").config();
const express = require("express");
const mysql = require("mysql2/promise");

const app = express();
app.use(express.urlencoded({ extended: true }));
app.use(express.json());

const pool = mysql.createPool({
  host: process.env.DB_HOST,          // use 127.0.0.1, not "localhost" (avoids IPv6 ::1 issue)
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  connectionLimit: 10,
});

const esc = (s) => String(s).replace(/[&<>"']/g, (c) => `&#${c.charCodeAt(0)};`);

app.get("/", async (req, res) => {
  try {
    const [rows] = await pool.query("SELECT id, name, city FROM students ORDER BY id");
    const trs = rows.map(r => `<tr><td>${r.id}</td><td>${esc(r.name)}</td><td>${esc(r.city)}</td></tr>`).join("");
    res.send(`<!DOCTYPE html><html><body style="font-family:Arial;max-width:700px;margin:30px auto">
      <h1>Students (Node.js + Express + MySQL)</h1>
      <table border="1" cellpadding="6"><tr><th>ID</th><th>Name</th><th>City</th></tr>${trs}</table>
      <h3>Add student</h3>
      <form method="post" action="/add"><input name="name" required placeholder="Name">
      <input name="city" required placeholder="City"><button>Add</button></form></body></html>`);
  } catch (err) {
    res.status(500).send("Database error: " + esc(err.message));
  }
});

app.post("/add", async (req, res) => {
  await pool.execute("INSERT INTO students (name, city) VALUES (?, ?)", [req.body.name, req.body.city]);
  res.redirect("/");
});

app.get("/api/students", async (req, res) => {
  const [rows] = await pool.query("SELECT id, name, city FROM students");
  res.json(rows);
});

app.get("/health", (req, res) => res.json({ status: "ok" }));

const port = process.env.PORT || 3000;
app.listen(port, "127.0.0.1", () => console.log(`API listening on 127.0.0.1:${port}`));
EOF

node app.js &          # quick test
sleep 2 && curl -s http://127.0.0.1:3000/api/students
kill %1

sudo npm install -g pm2
cd /opt/nodeapp
pm2 start app.js --name nodeapp
pm2 status
pm2 logs nodeapp --lines 20        # Ctrl+C to exit logs
pm2 startup systemd                # prints a 'sudo env PATH=...' command - copy & run it
pm2 save                           # remember the current process list for reboot

sudo tee /etc/systemd/system/nodeapp.service > /dev/null <<EOF
[Unit]
Description=Node.js Express app
After=network.target mariadb.service mysql.service

[Service]
User=$USER
WorkingDirectory=/opt/nodeapp
ExecStart=/usr/bin/node /opt/nodeapp/app.js
Restart=always
Environment=NODE_ENV=production

[Install]
WantedBy=multi-user.target
EOF
sudo systemctl daemon-reload
sudo service nodeapp start
sudo systemctl enable nodeapp