Build an Express.js API server that queries the analytics database and returns JSON. Includes session-based authentication with bcrypt password hashing and role-based access control.
Initialize the project and install dependencies:
mkdir reporting-api && cd reporting-api npm init -y npm install express mysql2 express-session bcrypt
The project uses four packages:
| Package | Purpose |
|---|---|
express |
HTTP framework for routing and middleware |
mysql2 |
MySQL driver with Promise support |
express-session |
Server-side session management |
bcrypt |
Password hashing and comparison |
Directory structure after setup:
reporting-api/ api.js # Main server — routes + startup auth.js # Session config + auth middleware package.json node_modules/
mysql2 package provides a Promise-based API via mysql2/promise, which lets us use async/await for database queries instead of nested callbacks. It is wire-compatible with the original mysql package but adds prepared statement support and better performance.
The auth.js file handles session configuration, login/logout routes, and two middleware functions for protecting routes. Let's walk through each piece.
const session = require('express-session');
const bcrypt = require('bcrypt');
const sessionMiddleware = session({
secret: 'change-this-to-a-real-secret',
resave: false,
saveUninitialized: false,
cookie: {
httpOnly: true,
secure: false, // Set true in production with HTTPS
maxAge: 24 * 60 * 60 * 1000, // 24 hours
},
});
Key settings:
secret — signs the session cookie to prevent tampering. In production, use a long random string from an environment variable.resave: false — don't save the session back to the store if it wasn't modified during the request.saveUninitialized: false — don't create a session until something is stored in it. This avoids creating empty sessions for every unauthenticated request.httpOnly: true — prevents client-side JavaScript from reading the session cookie, mitigating XSS attacks.secure: false — allows cookies over HTTP for local development. Set to true in production so cookies are only sent over HTTPS.function loginRoute(pool) {
return async (req, res) => {
const { email, password } = req.body;
if (!email || !password) {
return res.status(400).json({
success: false,
error: 'Email and password required'
});
}
try {
const [rows] = await pool.execute(
'SELECT id, email, password_hash, display_name, role FROM users WHERE email = ?',
[email]
);
if (rows.length === 0) {
return res.status(401).json({
success: false,
error: 'Invalid credentials'
});
}
const user = rows[0];
const match = await bcrypt.compare(password, user.password_hash);
if (!match) {
return res.status(401).json({
success: false,
error: 'Invalid credentials'
});
}
req.session.user = {
id: user.id,
email: user.email,
displayName: user.display_name,
role: user.role
};
res.json({
success: true,
data: {
email: user.email,
displayName: user.display_name,
role: user.role
}
});
} catch (err) {
console.error('Login error:', err.message);
res.status(500).json({
success: false,
error: 'Internal server error'
});
}
};
}
The login flow:
users table for a matching email using a parameterized query (prevents SQL injection).bcrypt.compare() to check the plaintext password against the stored hash. This is a constant-time comparison, so it doesn't leak timing information about how much of the password matched.function logoutRoute(req, res) {
req.session.destroy(() => {
res.json({ success: true });
});
}
Calling req.session.destroy() removes the session from the server-side store. The session cookie will still exist on the client, but it will point to a nonexistent session, so subsequent requests are unauthenticated.
function requireAuth(req, res, next) {
if (!req.session.user) {
return res.status(401).json({
success: false,
error: 'Authentication required'
});
}
next();
}
This middleware checks if a user is stored in the session. If not, it returns a 401 response. If the user is authenticated, it calls next() to pass control to the route handler. Apply it to any route that requires a logged-in user.
function requireRole(...roles) {
return (req, res, next) => {
if (!req.session.user || !roles.includes(req.session.user.role)) {
return res.status(403).json({
success: false,
error: 'Insufficient permissions'
});
}
next();
};
}
This is a higher-order function: it takes a list of allowed roles and returns a middleware function. Use it like requireRole('admin', 'analyst') to restrict a route to specific roles. It returns 403 (Forbidden) rather than 401 (Unauthorized) because the user is authenticated but lacks permission.
The api.js file defines the Express server and five reporting endpoints. Each route follows the same pattern: parse query parameters, build a parameterized SQL query, execute it, and return JSON. Let's walk through each one.
const express = require('express');
const mysql = require('mysql2/promise');
const { sessionMiddleware, requireAuth } = require('./auth');
const app = express();
const PORT = 3007;
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
database: 'analytics',
waitForConnections: true,
connectionLimit: 10,
});
app.use(express.json());
app.use(sessionMiddleware);
The connection pool maintains up to 10 persistent MySQL connections. When a route executes a query, it borrows a connection from the pool and returns it when done. This avoids the overhead of opening a new connection for every request.
app.use((req, res, next) => {
res.header('Access-Control-Allow-Origin', 'http://localhost:8080');
res.header('Access-Control-Allow-Credentials', 'true');
res.header('Access-Control-Allow-Headers', 'Content-Type');
res.header('Access-Control-Allow-Methods', 'GET, POST, OPTIONS');
if (req.method === 'OPTIONS') return res.sendStatus(204);
next();
});
The dashboard frontend runs on a different origin (localhost:8080) than the API (localhost:3007), so the browser will block cross-origin requests unless the API explicitly allows them. Setting Access-Control-Allow-Credentials: true allows the browser to send the session cookie with cross-origin requests.
const { loginRoute, logoutRoute } = require('./auth');
app.post('/api/login', loginRoute(pool));
app.post('/api/logout', logoutRoute);
The login and logout handlers are defined in auth.js but mounted here. The login route needs access to the database pool to look up users, so loginRoute(pool) is a factory that returns the handler.
Returns four summary metrics for a date range in a single query:
app.get('/api/dashboard', requireAuth, async (req, res) => {
try {
const [start, end] = getDateRange(req.query);
const [[summary]] = await pool.execute(`
SELECT
(SELECT COUNT(*) FROM pageviews
WHERE server_timestamp BETWEEN ? AND ?
AND type = 'pageview') AS total_pageviews,
(SELECT COUNT(DISTINCT session_id) FROM pageviews
WHERE server_timestamp BETWEEN ? AND ?) AS total_sessions,
(SELECT ROUND(AVG(load_time)) FROM performance
WHERE server_timestamp BETWEEN ? AND ?) AS avg_load_time_ms,
(SELECT COUNT(*) FROM errors
WHERE server_timestamp BETWEEN ? AND ?) AS total_errors
`, [start, end, start, end, start, end, start, end]);
res.json({ success: true, data: summary });
} catch (err) {
console.error('Dashboard error:', err.message);
res.status(500).json({ success: false, error: 'Internal server error' });
}
});
The double destructure [[summary]] extracts the first row from the result set. The pool.execute() call returns [rows, fields], and since this is a scalar subquery returning one row, rows[0] is the summary object.
Example response:
{
"success": true,
"data": {
"total_pageviews": 12847,
"total_sessions": 3421,
"avg_load_time_ms": 1250,
"total_errors": 47
}
}
Returns two datasets: pageviews per day (for charting) and top pages by view count:
app.get('/api/pageviews', requireAuth, async (req, res) => {
try {
const [start, end] = getDateRange(req.query);
const [byDay] = await pool.execute(
`SELECT DATE(server_timestamp) AS day, COUNT(*) AS views
FROM pageviews
WHERE server_timestamp BETWEEN ? AND ? AND type = 'pageview'
GROUP BY day ORDER BY day`,
[start, end]
);
const [topPages] = await pool.execute(
`SELECT url, COUNT(*) AS views
FROM pageviews
WHERE server_timestamp BETWEEN ? AND ? AND type = 'pageview'
GROUP BY url ORDER BY views DESC LIMIT 20`,
[start, end]
);
res.json({ success: true, data: { byDay, topPages } });
} catch (err) {
console.error('Pageviews error:', err.message);
res.status(500).json({ success: false, error: 'Internal server error' });
}
});
The byDay array provides time-series data for a line chart. The topPages array provides ranked data for a table or bar chart. Both queries filter on type = 'pageview' to exclude other event types (like unload events).
Returns aggregated Web Vitals and load time metrics per page:
app.get('/api/performance', requireAuth, async (req, res) => {
try {
const [start, end] = getDateRange(req.query);
const [byPage] = await pool.execute(
`SELECT url,
ROUND(AVG(load_time)) AS avg_load_ms,
ROUND(AVG(ttfb)) AS avg_ttfb_ms,
ROUND(AVG(lcp), 2) AS avg_lcp,
ROUND(AVG(cls), 4) AS avg_cls,
COUNT(*) AS samples
FROM performance
WHERE server_timestamp BETWEEN ? AND ?
GROUP BY url ORDER BY avg_load_ms DESC LIMIT 20`,
[start, end]
);
res.json({ success: true, data: { byPage } });
} catch (err) {
console.error('Performance error:', err.message);
res.status(500).json({ success: false, error: 'Internal server error' });
}
});
Metrics returned per page:
avg_load_ms — Average total page load time in millisecondsavg_ttfb_ms — Average Time to First Byteavg_lcp — Average Largest Contentful Paint (seconds)avg_cls — Average Cumulative Layout Shift (unitless, typically 0-1)samples — Number of performance records, useful for judging statistical significanceReturns the most frequent errors and error counts by day:
app.get('/api/errors', requireAuth, async (req, res) => {
try {
const [start, end] = getDateRange(req.query);
const [byMessage] = await pool.execute(
`SELECT error_message, COUNT(*) AS occurrences,
MAX(server_timestamp) AS last_seen
FROM errors
WHERE server_timestamp BETWEEN ? AND ?
GROUP BY error_message ORDER BY occurrences DESC LIMIT 20`,
[start, end]
);
const [trend] = await pool.execute(
`SELECT DATE(server_timestamp) AS day, COUNT(*) AS error_count
FROM errors
WHERE server_timestamp BETWEEN ? AND ?
GROUP BY day ORDER BY day`,
[start, end]
);
res.json({ success: true, data: { byMessage, trend } });
} catch (err) {
console.error('Errors error:', err.message);
res.status(500).json({ success: false, error: 'Internal server error' });
}
});
The byMessage array groups errors by message text, showing how many times each error occurred and when it was last seen. The trend array provides daily error counts for charting error rates over time.
Returns session counts by day and aggregate session quality metrics:
app.get('/api/sessions', requireAuth, async (req, res) => {
try {
const [start, end] = getDateRange(req.query);
const [byDay] = await pool.execute(
`SELECT DATE(start_time) AS day, COUNT(*) AS session_count
FROM sessions
WHERE start_time BETWEEN ? AND ?
GROUP BY day ORDER BY day`,
[start, end]
);
const [[stats]] = await pool.execute(
`SELECT ROUND(AVG(duration_seconds)) AS avg_duration_sec,
ROUND(AVG(page_count), 1) AS avg_pages,
ROUND(SUM(CASE WHEN page_count = 1 THEN 1 ELSE 0 END)
/ COUNT(*) * 100, 1) AS bounce_rate_pct
FROM sessions
WHERE start_time BETWEEN ? AND ?`,
[start, end]
);
res.json({ success: true, data: { byDay, stats } });
} catch (err) {
console.error('Sessions error:', err.message);
res.status(500).json({ success: false, error: 'Internal server error' });
}
});
The stats object includes:
avg_duration_sec — Average session length in secondsavg_pages — Average number of pages viewed per sessionbounce_rate_pct — Percentage of sessions that viewed only one page (a "bounce")Every route accepts optional start and end query parameters for filtering by date. The getDateRange helper parses these and provides sensible defaults:
function getDateRange(query) {
const end = query.end || new Date().toISOString().slice(0, 10);
const start = query.start || new Date(Date.now() - 30 * 86400000).toISOString().slice(0, 10);
return [start + ' 00:00:00', end + ' 23:59:59'];
}
How it works:
end is omitted, it defaults to today's date.start is omitted, it defaults to 30 days before today.YYYY-MM-DD format from the query string.00:00:00 and 23:59:59) to ensure the full day is included at both ends.Example usage in API requests:
# Last 30 days (default) GET /api/dashboard # Specific range GET /api/dashboard?start=2026-01-01&end=2026-01-31 # From a specific date to today GET /api/dashboard?start=2026-02-01
start is not after end. The parameterized queries prevent SQL injection, but invalid dates would result in empty result sets rather than errors.
Every route handler wraps its database logic in a try/catch block:
app.get('/api/some-route', requireAuth, async (req, res) => {
try {
// ... query database ...
res.json({ success: true, data: results });
} catch (err) {
console.error('Route error:', err.message);
res.status(500).json({ success: false, error: 'Internal server error' });
}
});
Three principles guide the error handling strategy:
console.error() call records the actual error message (including SQL errors, connection failures, etc.) where only the server operator can see it.success: false. This makes the API predictable for the frontend: it can always parse the response and check data.success.Unknown column 'pasword' in 'where clause' tells an attacker about your schema. Always map database errors to a generic message in the response.
The consistent response format makes error handling straightforward on the frontend:
const response = await fetch('/api/dashboard', { credentials: 'include' });
const data = await response.json();
if (!data.success) {
showError(data.error); // Always a safe, generic string
return;
}
// Use data.data safely
renderDashboard(data.data);
Start the server:
node api.js # Output: Reporting API running on port 3007
First, log in to get a session cookie. The -c flag saves cookies to a file:
curl -X POST http://localhost:3007/api/login \
-H "Content-Type: application/json" \
-d '{"email":"admin@example.com","password":"secret123"}' \
-c cookies.txt
Expected response:
Now use the saved cookie for authenticated requests. The -b flag sends cookies from the file:
# Dashboard summary (default: last 30 days) curl -b cookies.txt http://localhost:3007/api/dashboard # Pageviews for January 2026 curl -b cookies.txt "http://localhost:3007/api/pageviews?start=2026-01-01&end=2026-01-31" # Performance metrics curl -b cookies.txt http://localhost:3007/api/performance # Error frequency curl -b cookies.txt http://localhost:3007/api/errors # Session statistics curl -b cookies.txt http://localhost:3007/api/sessions
Test without authentication to verify the middleware blocks the request:
curl http://localhost:3007/api/dashboard
Log out and confirm the session is destroyed:
curl -X POST -b cookies.txt http://localhost:3007/api/logout
# Try accessing after logout
curl -b cookies.txt http://localhost:3007/api/dashboard
# Returns: {"success":false,"error":"Authentication required"}
| python3 -m json.tool to any curl command to pretty-print the JSON response. For example: curl -b cookies.txt http://localhost:3007/api/dashboard | python3 -m json.tool