Aggregate functions and join tables with Drizzle

As is tradition, I have created this blog as a way to try out some new tech that has interested me in the past months.

Coming from a design background, I have always been a "front-of-the-front-end" developer. I have done plenty of fullstack work, but in my 17 years of professional experience, I've never been the one architecting a database.

I want to sharpen my skills in that area, so I decided to build every component of this blog from scratch. I'm currently using neon (postgres) for my database, and Drizzle for my ORM. I just barely know what I am doing, and I'm posting here some of my learnings along the way.

The blog application I am building has an admin panel for managing posts, and posts can have tags. This is a many-to-many relationship, and whilst modeling this with Drizzle was verbose, it was relatively straight-forward. Where I ran in to issues was when querying for a list of posts with all of their tags.

I discovered that there are a couple of ways to do this. One requires more data transformation on the application side, and the other results in a more complex query but a simpler data transformation afterwards.

Using Select and joins

I can use inner joins to get all posts and all tags like this:

  const result = await db
    .select()
    .from(posts)
    .leftJoin(tagsToPosts, eq(tagsToPosts.postId, posts.id))
    .leftJoin(tags, eq(tagsToPosts.tagId, tags.id));

The result of this is an array containing all posts and their tags, but the same post will appear once for every tag it has.

For instance, if I have 2 posts titled Erdäpfel and Oida, and Erdapfel has the tags [{ tagName: "potato" }, { tagName: "food" }], I would get something like the following:

const result = {
    posts: {
      id: 1,
      title: "Erdäpfel",
    },
    tags_to_posts: {
      tagId: "713fbb64-9134-4624-a241-fd75dfc9cb76",
      postId: 1
    },
    tags: {
      id: "713fbb64-9134-4624-a241-fd75dfc9cb76",
      name: "potato"
    }
  }, {
    posts: {
      id: 1,
      title: "Erdäpfel",
    },
    tags_to_posts: {
      tagId: "74de6e89-6ad8-4ad0-af87-7eea4e712471",
      postId: 1
    },
    tags: {
      id: "74de6e89-6ad8-4ad0-af87-7eea4e712471",
      name: "food"
    }
  }, {
    posts: {
      id: 1,
      title: "Oida",
    },
    tags_to_posts: null,
    tags: null
  }

Notice that the hilarious post about Erdäpfeln appears twice. This could normally be solved by using a array_agg Aggregate function, which could be used to collapse the list down so that each post would appear once with a single tags array for each.

However, Drizzle doesn't appear to support using aggregate functions, so instead you can use JavaScript to massage the data in to the format you desire.

If you are a 10x developer you might reach for reduce here, but I went for a good old for…of

function aggregateTags(
  result: {
    posts: Post;
    tags_to_posts: TagToPosts;
    tags: Tag;
  }[],
) {
  let postsWithTags: PostWithTags[] = [];

  for (const row of result) {
    let existingPost = postsWithTags.find((post) => post.id === row.posts.id);

    if (!existingPost) {
      postsWithTags.push({
        ...row.posts,
        ...{ tags: row.tags ? [row.tags.name] : [] },
      });
    } else {
      row.tags && existingPost.tags.push(row.tags.name);
    }
  }

  return postsWithTags;
}

The trade-off here is that the SQL query is simpler and we have more control over it, but the resulting data needs to be further processed by the application.

Using the query builder

Another option to retrieve posts with their tags is to use the query builder (rqb) to make a relational query, and trust Drizzle to figure out the joins.

  return await db.query.posts
    .findMany({
      with: {
        tagsToPosts: {
          with: {
            tag: {
              columns: {
                name: true,
              },
            },
          },
        },
      },
      orderBy: [desc(posts.createdAt)],
    })
    .then((result) => {
      return result.map(flattenTags);
    });

function flattenTags(
  post: Post & { tagsToPosts: (TagToPosts & { tag: Tag })[] },
) {
  const { tagsToPosts, ...rest } = post;

  return {
    ...rest,
    tags: post.tagsToPosts.map((ttp) => ttp.tag.name),
  };
}

As you can see, the query builder returns a simpler result — each post only appears once in the resulting array. In this case, each post has a tagsToPosts array, which is our join table, and it contains the tag names. We can map over this join table in our application to retrieve the tag names, which is arguably a simpler transformation.