Table-based multitenancy (tenant_id column in each table)

[frederikhors]

I'm trying to figure out how to implement a table-based multitenancy (tenant_id column in each table) system in my go-pg project.

I can use a where statement (as I have seen in many issues, searching before open this one) but having also tried Rails (and the gem apartment, https://github.com/influitive/apartment) I wonder if it is possible to avoid using a where for each query and use a "global" switch tenant_id.

Maybe using hooks (https://github.com/go-pg/pg/wiki/Model-Hooks)?

[frederikhors]

@vmihailenco any hint?

I have been waiting for nine months... :(

[vmihailenco]

I don't have any hints and at the moment I don't have time to design something as complex as hooks. It is easy to solve some particular problem with specific hook, but there is always slightly different problem that requires slightly different hook. Overall it seems like a big time sink in order to solve few edge cases that already can be solved in a more explicit manner (just write a where!).

[frederikhors]

Thank you for your answer.

I prefer not to manually write the wheres in the code because error-prone being non-self-generated code.

A beforeSelect hook would solve all the problems in this case.

What I need is to avoid from this:

var players []*models.Player
db.Model(&players).Order("id desc").Select()
return players, nil

to this:

var players []*models.Player
user := ctx.Value(auth.CTXKeyUser).(*models.User)
db.Model(&players).Order("id desc").Where("tenant_id = ?", user.TenantID).Select()
return players, nil

[frederikhors]

I can use the Apply() as in this example: #380 (comment) but I don't know how to get context in filter func.

How to?

UPDATE

Using ModelContext() I can see the ctx in q.ctx in my filter func but I get obviously Unexported field ctx usage if I try to use it.

[vmihailenco]

You can use q.Context() to obtain query context.

[frederikhors]

This is the signature. What should I put in parentheses?

I'm using this right now but I think I can do better:

func FilterByTenant(ctx context.Context) func(q *orm.Query) (*orm.Query, error) {
	user := ctx.Value(auth.CTXKeyUser).(*models.User)
	return func(q *orm.Query) (*orm.Query, error) {
		q = q.Where("tenant_id = ?", user.TenantID)
		return q, nil
	}
}