Now we’ll create an API endpoint to get all users from the users table you created in Lesson 1. This connects the SQL SELECT * FROM users command to a real API endpoint.

User Interface Definition

First, define the TypeScript interface for our User data structure (matches the users table from Lesson 1):

interface User {
  id: number;
  username: string;
  email: string;
}

This interface provides type safety for API responses and clarifies the data structure our endpoints will return. Add this to your index file below.

Get All Users Endpoint

Create or update src/index.ts:

import express from "express";
import { pool } from "./database";

const app = express();
const PORT = process.env.PORT || 3000;

interface User {
  id: number;
  username: string;
  email: string;
}

// Get all users from the database
app.get("/users", async (req, res) => {
  try {
    // This is the same SQL query you used in Lesson 1
    const [rows] = await pool.execute("SELECT * FROM users");
    const users = rows as User[]; // Type the result for clarity

    // Return the users array to the frontend
    res.json(users);
  } catch (error) {
    console.error("Database query error:", error);
    res.status(500).json({
      error: "Failed to fetch users",
    });
  }
});

app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

Understanding the Code

The SQL Query

  • SELECT * FROM users is exactly the same query used in Lesson 1.
  • Now it runs inside your API instead of via Workbench.

pool.execute()

  • Executes your SQL query using the database connection.
  • Returns an array where [rows] contains the query results.
  • await waits for the database to respond.

TypeScript Type Assertion

  • We use as User[] to help TypeScript understand the shape of database data. This improves autocomplete and catches mistakes during development.
  • Think of it as: “This data from the database should have id, username, and email fields.”
  • Before: rows has limited autocomplete because TypeScript doesn’t know the structure.
  • After: users has full autocomplete and will show TypeScript errors if you use wrong property names.
  • Important: as does not change or convert the data — it only tells TypeScript how to understand it.

Error Handling

  • try/catch catches any database errors (connection failed, etc.).
  • Returns a 500 status code for server errors.
  • Logs errors to help debugging.

Response Format

  • Returns the users array directly to the frontend.
  • Each user object contains id, username, and email fields (matching the User interface).
  • Example frontend receives: [{ id: 2, username: 'bob', email: 'bob@example.com' }, ...].

Repo link

Tags: