One-to-Many Relationships (1:M)

Recall that when working in a relational database, a One-to-Many relationship means that a single record can be associated with many records in another table.

For example, if building a new blogging platform, each user can be associated with many posts that they've authored.

Our database schema might look like the following:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users,
    msg TEXT NOT NULL
);

API Response for One-to-Many

When we call our API to get the messages belonging to a given user, we'd expect to receive something like the following with regard to our JSON response:

{
  name: "Alissa",
  type: "admin",
  posts: [
    {id: 1, msg: 'post #1'},
    {id: 2, msg: 'post #2'}
  ]
}

But, if we look at our schema, we know this data is stored in two difference places: name and type from our users table, and the post information from our posts table.

How can we return the JSON format that we'd expect?

Crafting Our One-to-Many API Response

Sticking to our example above, to create the JSON format for the requests made to our API, we'll just add an additional property on our user object and populate it with that user's messages:

router.get("/:id", async function (req, res, next) {
  const userRes = await db.query(
    `SELECT name, type FROM users WHERE id=$1`,
    [req.params.id]
  );

  const postsRes = await db.query(
    `SELECT id, msg FROM posts 
     WHERE user_id = $1`,
    [req.params.id]
  );

  const user = userRes.rows[0];
  user.posts = postsRes.rows;
  return res.json(user);
});

Many-to-Many Relationships (M:M)

Besides One-to-Many (1:M) relationships, we've also encountered Many-to-Many (M:M) relationships.

Take, for example, the blogging platform we were discussing in the last example.

Every user can have many posts associated with them. In the way that our application has been built, each of these posts can also be associated with many different tags (or categories), which serve as labels that help readers identify the topic the post is about.

Our schema might look like the following, establishing a M:M between posts and tags:

CREATE TABLE tags (
    code TEXT PRIMARY KEY,
    tag TEXT UNIQUE
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users,
    msg TEXT NOT NULL
);

CREATE TABLE posts_tags (
    post_id INTEGER NOT NULL REFERENCES posts,
    tag_code TEXT NOT NULL REFERENCES tags,
    PRIMARY KEY(message_id, tag_code)
);

API Response for Many-to-Many

Let's first consider the response we might expect if we were requesting all information about a specific blog post.

We'd probably want to return an id, the msg of the post, and also the tags associated with the post. In that case, our JSON would look like the following:

{
  id: 1,
  msg: "post #1",
  tags: ["Node", "JavaScript"]
}

Crafting Our Many-to-Many API Response

Like our customized response for our 1:M JSON, we'll do the same for our M:M relationship.

To retrieve this information with a SQL query, let's recall our work with associative tables. Our posts_tags table contains all of the information we need to JOIN together the information from posts and tags that we'd like to return in our JSON response above.

Let's formulate that query:

SELECT p.id, p.msg, t.tag
FROM posts AS p
LEFT JOIN posts_tags AS pt 
  ON p.id = pt.post_id
LEFT JOIN tags AS t ON pt.tag_code = t.code
WHERE p.id = $1;`

Without any further adjustment and put into our db.query() method, the above query would return the following:

[
  {id: 1, msg: "post #1", tag: "Node"},
  {id: 1, msg: "post #1", tag: "JavaScript"},
]

We're getting closer! At this point we need to eliminate the duplicate information shared across all returned rows, namely the id and msg, by combining the records:

router.get("/:id", async function (req, res, next) {
  const result = await db.query(
        `SELECT p.id, p.msg, t.tag
            FROM posts AS p
              LEFT JOIN posts_tags AS pt 
                ON p.id = pt.post_id
              LEFT JOIN tags AS t ON pt.tag_code = t.code
            WHERE p.id = $1;`,
      [req.params.id]);

  let { id, msg } = result.rows[0];
  let tags = result.rows.map(r => r.tag);

  return res.json({ id, msg, tags });
});

There we go!

On Lines 11-12, we are creating a brand new object, using the shared properties from our response (id and msg). We're then creating a new Array that contains all tags that have been returned.

Finally, we are returning a new object that contains the finished properties our user expects: id, msg and tags.

Handling Missing Resources

What if our user attempts to access a record that doesn't exist?

Take a look at the following example:

router.put("/:id", async function (req, res, next) {
  try {
    const result = await db.query(
          `UPDATE posts SET msg=$1 WHERE id = $2
           RETURNING id, user_id, msg`,
        [req.body.msg, req.params.id]);

    return res.json(result.rows[0]);
  }

  catch (err) {
    return next(err);
  }
});

In the case above, if there are no rows returned as a response to our query, our response will be undefined.

Not what our user expects!

To make sure that this doesn't happen, we need to throw a 404 if a resource cannot be located:

router.put("/v3/:id", async function (req, res, next) {
  try {
    const result = await db.query(
          `UPDATE posts SET msg=$1 WHERE id = $2
           RETURNING id, user_id, msg`,
        [req.body.msg, req.params.id]);

    if (result.rows.length === 0) {
      throw new ExpressError("No such post!", 404);
    }

    return res.json(result.rows[0]);
  }

  catch (err) {
    return next(err);
  }
});