Building a Simple Weather App using the MySQL, Express, React, and Node.js

mkdir weather-app && cd weather-app
npm init -y && npm pkg set type=module
npm install express mysql2

WEATHER_API_KEY=your-key-here
DB_HOST=localhost
DB_USER=weather_user
DB_PASSWORD=a-strong-password
DB_NAME=weather_db

CREATE TABLE weather_cache (
  city       VARCHAR(80) NOT NULL PRIMARY KEY,
  data       TEXT        NOT NULL,
  fetched_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

// server.js
import express from 'express';
import mysql from 'mysql2/promise';
import path from 'node:path';

const app = express();
const db = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 5,
});

const CITY = /^[\p{L} .'-]{2,60}$/u;

app.get('/api/weather', async (req, res) => {
  const city = String(req.query.city ?? '').trim();
  if (!CITY.test(city)) return res.status(400).json({ error: 'Enter a valid city name' });
  const key = city.toLowerCase();

  const [rows] = await db.execute(
    'SELECT data FROM weather_cache WHERE city = ? AND fetched_at > NOW() - INTERVAL 10 MINUTE',
    [key],
  );
  if (rows.length) return res.json(JSON.parse(rows[0].data));

  const url = new URL('https://api.openweathermap.org/data/2.5/weather');
  url.search = new URLSearchParams({ q: city, units: 'metric', appid: process.env.WEATHER_API_KEY });
  const r = await fetch(url, { signal: AbortSignal.timeout(8000) });
  if (r.status === 404) return res.status(404).json({ error: 'City not found' });
  if (!r.ok) return res.status(502).json({ error: 'Weather service unavailable' });

  const w = await r.json();
  const result = {
    city: w.name,
    country: w.sys?.country,
    tempC: Math.round(w.main.temp),
    feelsLikeC: Math.round(w.main.feels_like),
    humidity: w.main.humidity,
    description: w.weather?.[0]?.description,
  };

  await db.execute(
    `INSERT INTO weather_cache (city, data) VALUES (?, ?)
     ON DUPLICATE KEY UPDATE data = VALUES(data), fetched_at = CURRENT_TIMESTAMP`,
    [key, JSON.stringify(result)],
  );
  res.json(result);
});

// Serve the built React app in production
const dist = path.resolve('client/dist');
app.use(express.static(dist));
app.get('/{*splat}', (req, res) => res.sendFile(path.join(dist, 'index.html')));

app.listen(process.env.PORT || 3001);

npm create vite@latest client -- --template react
cd client && npm install

// client/src/App.jsx
import { useState } from 'react';

export default function App() {
  const [city, setCity] = useState('');
  const [weather, setWeather] = useState(null);
  const [error, setError] = useState('');
  const [loading, setLoading] = useState(false);

  async function search(e) {
    e.preventDefault();
    setLoading(true); setError(''); setWeather(null);
    try {
      const r = await fetch(`/api/weather?city=${encodeURIComponent(city)}`);
      const body = await r.json();
      if (!r.ok) throw new Error(body.error || 'Something went wrong');
      setWeather(body);
    } catch (err) {
      setError(err.message);
    } finally {
      setLoading(false);
    }
  }

  return (
    <main>
      <h1>Weather</h1>
      <form onSubmit={search}>
        <label htmlFor="city">City</label>
        <input id="city" value={city} onChange={(e) => setCity(e.target.value)} required />
        <button disabled={loading}>{loading ? 'Loading…' : 'Search'}</button>
      </form>
      {error && <p role="alert">{error}</p>}
      {weather && (
        <section aria-live="polite">
          <h2>{weather.city}, {weather.country}</h2>
          <p>{weather.tempC}°C, feels like {weather.feelsLikeC}°C</p>
          <p>{weather.description} · Humidity {weather.humidity}%</p>
        </section>
      )}
    </main>
  );
}