🟢 Node.js Lesson 06 — MySQL + .env Secrets

1. Setup mysql2 + dotenv

npm install mysql2 dotenv

# Project structure
├── .env              ← secrets (never commit!)
├── .gitignore        ← must include .env
├── db.js             ← database connection
├── server.js         ← express app
└── routes/
    └── users.js

# .env
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASS=yourpassword
DB_NAME=myapp
PORT=3000
JWT_SECRET=very-long-random-secret-key-here

# .gitignore
node_modules/
.env
.env.local
*.log

2. Database Connection (db.js)

// db.js
require('dotenv').config();  // load .env variables
const mysql = require('mysql2/promise');

// Connection Pool (multiple connections — better than single)
const pool = mysql.createPool({
  host:     process.env.DB_HOST,
  port:     process.env.DB_PORT || 3306,
  user:     process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,  // max 10 concurrent connections
  queueLimit: 0
});

// Test connection
async function testConnection() {
  try {
    const conn = await pool.getConnection();
    console.log('✅ MySQL connected!');
    conn.release();
  } catch (err) {
    console.error('❌ MySQL connection failed:', err.message);
    process.exit(1);
  }
}
testConnection();

module.exports = pool;

3. CRUD Queries with Prepared Statements

// routes/users.js
const express = require('express');
const db = require('../db');
const router = express.Router();

// GET all users
router.get('/', async (req, res) => {
  try {
    const [rows] = await db.execute('SELECT id, name, email, role FROM users');
    res.json({ data: rows, total: rows.length });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET one user
router.get('/:id', async (req, res) => {
  try {
    const [rows] = await db.execute(
      'SELECT id, name, email, role FROM users WHERE id = ?',
      [req.params.id]   // ? = prepared statement — SQL injection ကင်း!
    );
    if (!rows.length) return res.status(404).json({ error: 'Not found' });
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST — create user
router.post('/', async (req, res) => {
  const { name, email, password } = req.body;
  if (!name || !email || !password)
    return res.status(400).json({ error: 'All fields required' });

  try {
    const [result] = await db.execute(
      'INSERT INTO users (name, email, password) VALUES (?, ?, ?)',
      [name, email, password]  // hash password in real app!
    );
    res.status(201).json({ id: result.insertId, name, email });
  } catch (err) {
    if (err.code === 'ER_DUP_ENTRY')
      return res.status(409).json({ error: 'Email already exists' });
    res.status(500).json({ error: err.message });
  }
});

// PUT — update
router.put('/:id', async (req, res) => {
  const { name, email } = req.body;
  try {
    const [result] = await db.execute(
      'UPDATE users SET name = ?, email = ? WHERE id = ?',
      [name, email, req.params.id]
    );
    if (!result.affectedRows) return res.status(404).json({ error: 'Not found' });
    res.json({ id: req.params.id, name, email });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE
router.delete('/:id', async (req, res) => {
  const [result] = await db.execute('DELETE FROM users WHERE id = ?', [req.params.id]);
  if (!result.affectedRows) return res.status(404).json({ error: 'Not found' });
  res.status(204).end();
});

module.exports = router;

4. server.js — Complete

// server.js
require('dotenv').config();
const express = require('express');
const app = express();

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

// Routes
app.use('/api/users', require('./routes/users'));
app.use('/api/posts', require('./routes/posts'));

// Health check
app.get('/health', (req, res) => res.json({ status: 'OK' }));

// 404
app.use((req, res) => res.status(404).json({ error: 'Route not found' }));

// Global error handler
app.use((err, req, res, next) => {
  console.error(err);
  res.status(500).json({ error: 'Internal server error' });
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => console.log(`Server: http://localhost:${PORT}`));