multi-tenancy Join or multiple Select queries postgres

Question

I am implementing multi tenancy using single database and separating data for each tenant using a tenant_id. This id is passed in the jwt token as well. I have two tables right now genre and books. genre table has columns like tenant_id, genre_id, ..... and books table has columns genre_id, book_id, book_name, ....

So 1 genre can have multiple books associated with it and 1 tenant can have multiple genres associated with it.

Now every time a book is fetched or updated I want to make sure the right person is making these calls.

I know of two ways to do it.

First way: Make two queries. First fetch the book, get the associated genre_id in the object. Then fetch that genre and compare the jwt tenant_id with the tenant_id inside this genre object. Something like this

const book= await ReadBook(req.query.book_id);    // fetches my book from db
const genre = await ReadBook(book.genre_id);      // fetches the genre from db
if (genre.tenant_id === jwtToken.tenant_id) {..}  // compare if same or not

Second way: Do this query in db

select b.*,  g.tenant_id as tenant_id 
from book_data b, genre_data g 
where b.book_id = '0eokdpz0l' and g.tenant_id = 'M1MzgzMDM' and b.genre_id = g.genre_id

Which method is more efficient? If theres a more efficient method then these then please let me know too

Answer 1

It's good practice to stick to ORM abstraction if possible, while minimising how much and how often data is transferred to/from db. Sequelize is able to construct an equivalent to that query for you, with the necessary joins and filters on the ids. Something among the lines of:

Books.findAll({
  where: {book_id: '0eokdpz0l'},
  include: [{
    model: Genre,
    where: {tenant_id : jwtToken.tenant_id}
   }]
}).then(books => {
  /* ... */
});

Running multiple queries in sequence not only adds latency due to additional round trips to/from db (and possibly connection setup if you're not pooling or holding them open) but it's also moving more bytes of data around, needlessly. tenant_id mismatch on db would send back a shorter message with an empty result. Checking it on client side requires downloading data even when you'll have to discard it.