Get Single User by ID

Now let’s build an endpoint to get a single user by their ID. This uses the SQL WHERE clause you learned in Lesson 1, but with safe parameterised queries to prevent SQL injection.

Single User Endpoint

// Get single user by ID
app.get("/users/:id", async (req, res) => {
  try {
    const userId = Number(req.params.id);

    // Validate the ID
    if (isNaN(userId)) {
      return res.status(400).json({
        error: "Invalid user ID",
      });
    }

    // This is like: SELECT * FROM users WHERE id = 1
    const [rows] = await pool.execute(
      "SELECT id, username, email FROM users WHERE id = ?",
      [userId]
    );
    const users = rows as User[]; // Type the database results

    if (users.length === 0) {
      return res.status(404).json({
        error: "User not found",
      });
    }

    // Extract the single user (TypeScript knows this is User type)
    const user = users[0];
    res.json(user); // Return single User object
  } catch (error) {
    console.error("Database error:", error);
    res.status(500).json({
      error: "Failed to fetch user",
    });
  }
});

Understanding the Single User Code

URL Parameters

• Uses the same URL parameter pattern from Module 1: /users/:id.
• Number() converts the string ID to a number.

SQL with WHERE

• SELECT * FROM users WHERE id = ? is the same WHERE query from Lesson 1.
• The ? is a placeholder that gets replaced safely with the actual ID.
• This prevents SQL injection attacks.

TypeScript Pattern for Single Results

• The database always returns an array, even for single results (hence users.length === 0 check).
• We type the results: const users = rows as User[] to get type safety.
• Extract single user: const user = users[0] — TypeScript automatically knows this is a User.
• No need to type user explicitly since TypeScript infers it from the typed array.

Different Responses

• 200: Success — returns user object, e.g. { id: 2, username: "bob", email: "bob@example.com" }.
• 400: Bad request — returns { error: "Invalid user ID" }.
• 404: Not found — returns { error: "User not found" }.
• 500: Server error — returns { error: "Failed to fetch user" }.

Repo link