Building API Endpoints for Connected Data | Noroff Front-end Development

Now let’s build API endpoints that use this relationship. We’ll create two essential endpoints that demonstrate how to work with connected data in your Express API.

Setting Up TypeScript Interfaces

First, let’s define interfaces for our new data structures. You should already have the User interface, so we’ll add these new interfaces to the index.ts file:

interface Post {
  id: number;
  title: string;
  content: string;
  user_id: number;
  created_at: string;
}

interface PostWithUser extends Post {
  username: string;
  email: string;
}

Post interface: Represents our new posts table

PostWithUser interface:

Used for JOIN queries that combine post data with user information.
Uses extends Post to automatically include all Post fields (id, title, content, user_id, created_at).
Adds the additional user fields we need (username, email).
Much cleaner than copying all the Post fields again.

Note: In SQL, JOIN without any qualifier defaults to INNER JOIN. This means JOIN and INNER JOIN are equivalent - you’ll see both syntaxes used interchangeably in examples and documentation.

Endpoint 1: GET /posts - All Posts with Author Information

This endpoint returns all posts along with the author’s information using a JOIN query:

app.get("/posts", async (req, res) => {
  try {
    const [rows] = await pool.execute(`
      SELECT 
        posts.id,
        posts.title,
        posts.content,
        posts.user_id,
        posts.created_at,
        users.username,
        users.email
      FROM posts 
      INNER JOIN users ON posts.user_id = users.id
      ORDER BY posts.created_at DESC
    `);

    const posts = rows as PostWithUser[];
    res.json(posts);
  } catch (error) {
    console.error("Error fetching posts:", error);
    res.status(500).json({ error: "Failed to fetch posts" });
  }
});

Key points

The SQL query uses an INNER JOIN like we practiced earlier, but without a WHERE clause so it returns all posts with their authors.
To use a WHERE clause, we’d need a route parameter in the URL - we’ll see this in the next example.
We select specific columns to match our PostWithUser interface.
const posts = rows as PostWithUser[] tells TypeScript what type the data is.
ORDER BY posts.created_at DESC shows newest posts first.
Error handling follows the same pattern as previous lessons.

Endpoint 2: GET /users/:id/posts - Get All Posts by a Specific User

This endpoint shows all posts written by a particular user:

app.get("/users/:id/posts", async (req, res) => {
  try {
    const userId = Number(req.params.id);

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

    const [rows] = await pool.execute(
      `
      SELECT 
        posts.id,
        posts.title,
        posts.content,
        posts.user_id,
        posts.created_at
      FROM posts 
      WHERE posts.user_id = ?
      ORDER BY posts.created_at DESC
    `,
      [userId]
    );

    const posts = rows as Post[];
    res.json(posts);
  } catch (error) {
    console.error("Error fetching user posts:", error);
    res.status(500).json({ error: "Failed to fetch user posts" });
  }
});

Key points

Uses a WHERE clause to filter posts by user_id (which comes from the :id route parameter).
No JOIN needed since we are returning an array of Posts and only select from the posts table.
const posts = rows as Post[] tells TypeScript this returns Post data (no user info).
Uses parameterised queries to prevent SQL injection.

Endpoint 3: GET /users/:id/posts-with-user - Get Posts by User with Author Info

This combines the route parameter from endpoint 2 with the JOIN query from endpoint 1 (error handling omitted for brevity):

app.get("/users/:id/posts-with-user", async (req, res) => {
  const userId = Number(req.params.id);

  const [rows] = await pool.execute(
    `
    SELECT posts.id, posts.title, posts.content, posts.user_id, posts.created_at,
           users.username, users.email
    FROM posts 
    INNER JOIN users ON posts.user_id = users.id
    WHERE users.id = ?
  `,
    [userId]
  );

  const posts = rows as PostWithUser[];
  res.json(posts);
});

Key difference

Uses both a JOIN (like endpoint 1) and WHERE clause (like endpoint 2) to return PostWithUser[] data for a specific user.

Testing Your Endpoints

Test these endpoints with your API client. Replace the user IDs with actual IDs from your users table:

Get all posts with authors info:

GET http://localhost:3000/posts

Get posts by a specific user

GET http://localhost:3000/users/4/posts

Get posts by a specific user with author info

GET http://localhost:3000/users/4/posts-with-user

You should see the different data structures returned by each endpoint:

Endpoint 1: Returns PostWithUser[] for all posts.
Endpoint 2: Returns Post[] for one user’s posts.
Endpoint 3: Returns PostWithUser[] for one user’s posts.

Repo link

Tags: