Sometimes things just work, eventually

If you read my last post (here), you’ll know I’ve been experimenting a lot with supabase (a cloud-hosted database/api etc). If “a sort of detailed discussion about how I implemented a many-to-many relationship and queried it from the javascript client” doesn’t interest you, feel free to skip this one.

Okay, so if you’ve gotten this far, presumably you care and know a little bit about databases and javascript. Cool, me too!

So, the scenario I’m working on is we have a list of books. These books have a bunch of information stored, like the author, title, description. As well, for organizing, searching, and other future purposes, I want the books to have ‘tags’. Basically these could be anything, maybe the book’s genre. Or maybe a special tag to show that this book was actually a book we read for book club, not just a suggestion.

Described above, we have a many-to-one relationship that may turn out to be false (books have one author, authors have 1 or more books). This is pretty easy to do in our database schema (all this code is just here for explanatory purposes, and doesn’t include a lot of things like you’d probably want in these tables such as created_by field or other such metadata).

CREATE TABLE authors (
  id int PRIMARY KEY,
  name string NOT NULL
);
CREATE TABLE books (
  id int PRIMARY KEY,
  title string NOT NULL,
  author_id int REFERENCES authors(id) NOT NULL
);

This will create our authors and books tables. The tables are linked by that author_id field, and so we can do things like query books and their authors all in one query. In SQL, you’d do that with a join. This is not the place to learn SQL. I use it a lot but I’m far from an expert and there’s some great resources for it out there.

In supabase, if we wanted to get all the books and their authors, we’d do something like this:

type Author = {
  id: number;
  name: string;
}

type Book = {
  id: number;
  title: string;
  author: Author;
}
const {data} = await supabase.from<Book>('books').select(`
  id,
  title,
  author: authors (
    id,
    name
  )
`);

This will return a list of objects of type Book. Super easy so far, and this part is well documented in the supabase client docs. What was less well documented was how to do a more complicated many-to-many join, like in the case with tags.

So the database schema for tags is pretty straighforward. First we create a tags table:

CREATE TABLE tags (
  id int PRIMARY KEY,
  name string NOT NULL
);

But this time, because a book can have many tags, and a tag can have many books, we can’t just join with a simple column on either our tags or books tables. So we do a linking table such as:

CREATE TABLE books_tags (
  book_id int REFERENCES books(id),
  tag_id int REFERENCES tags(id)
);

The records in this table act like a junction between a specific book and a specific tag. Again, in SQL you’d have to do a somewhat sophisticated join to get the list of tags on a book, or the list of books that have a given tag. But in supabase, it’s again fairly straightforward. I thought originally I’d have to do something like this because the documentation makes it seem like you have to step through the relationship explicitly. And then parse through the returned data to put tags as a direct property of a book.

const {data} = await supabase.from<Book>('books').select(`
  id,
  title,
  author: authors (
    id,
    name
  ),
  books_tags (
    book_id,
    tag_id,
    tags (
      id,
      name
    )
  )
`);

But on a whim, after a light google search, it turns out that this works:

const {data} = await supabase.from<Book>('books').select(`
  id,
  title,
  author: authors (
    id,
    name
  ),
  tags (
    id,
    name
  )
`);

Where our new Book type is as follows, without any post-query parsing:

type Author = {
  id: number;
  name: string;
}
type Book = {
  id: number;
  name: string;
}
type Book = {
  id: number;
  title: string;
  author: Author;
  tags?: Tag[]
}

So great when things “just work”! Also, let me know how these code snippets work out for you. If I end up doing more code examples, I think I’ll go with a plugin that does syntax highlighting. And also change the inline code font a bit so it’s more distinct. But open to feedback also.

I think my next post about this will be about how I’m handling authentication during development and my plans for authentication with actual users.