multi-tenancy Join or multiple Select queries postgres-CodePudding

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

CodePudding user response：

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.