Connecting a Database

mysql2

npm install mysql2\n\n// config/db.js\nconst mysql = require("mysql2/promise");\n\nconst pool = mysql.createPool({\n  host: process.env.DB_HOST,\n  user: process.env.DB_USER,\n  password: process.env.DB_PASS,\n  database: process.env.DB_NAME,\n});\n\nmodule.exports = pool;

Queries

const [rows] = await pool.query("SELECT * FROM courses");\nconst [rows] = await pool.query("SELECT * FROM courses WHERE id = ?", [id]);\nconst [result] = await pool.query("INSERT INTO courses SET ?", { title, price });\nconsole.log(result.insertId);

Environment variables

// .env  (never commit!)\nDB_HOST=localhost\nDB_USER=root\nDB_PASS=\nDB_NAME=kaa_lms\n\n// load it\nrequire("dotenv").config();

Async/await everywhere

app.get("/api/courses", async (req, res) => {\n  const [rows] = await pool.query("SELECT * FROM courses");\n  res.json(rows);\n});

A repository layer

// services/courseService.js\nexports.getAll = async () => {\n  const [rows] = await pool.query("SELECT * FROM courses");\n  return rows;\n};\n\n// controller calls the service, not SQL directly

Tip: Wrap database code in services/repositories. When the schema changes, you change one file — not fifty.