Multitenancy on same database

ServiceStack Sessions
1

Hi, I’m working on an invoicing SaaS system where each user has its data stored in the same table.
An “Invoice” table is storing invoices of all database users.
To filter their invoices, I use a UserId field which is requested each time a SqlExpression is called in the code (so I call it manually).
Since there is not only invoices but other tables with UserId field, I would like to know if there is a better way to do this.
I mean I could use an interface like IHasUserId for each class with a UserId field, and I was thinking that maybe I can add automatically a SqlExpression.Where(p => p.UserId == UserSession.Id) each time a SqlExpression of a IHasUserId class is called.

Thank you.

2

Hi @labilbe,

Using an interface in your data access layer to consistently filter your shared tables by the current user sounds like an approach that would work (from what I can gather) yes. If you need to add team functionality (multiple users that can see the same set of invoices), best you only have to make that change in one spot (from userId to another identifier). Another common SaaS functionality for admins is to “View as someone” which might get hard to extend if you are explicitly leaning on UserSession.Id.

Saying all that, having separate schemas (via connection string) can be a more explicit way of making this separation, have a look at the ServiceStack docs on Multi-tenancy for some inspiration of possible solutions if you haven’t already.

Always good to add existing solution code if you can to give others as much context as possible as people might see a pattern they have already tried and have more helpful suggestions.

Hope that helps!

1 Like
3

One thing I’ll add is that it’s a good idea for any tenant filters to use the Ensure APIs to make sure the condition is always applied irrespective of the other conditions added to the SqlExpression.

I wouldn’t use the static filter which relies on singleton request context which is disabled by default in ASP.NET Core, I’d prefer to use an extension method instead which is more readable & explicit at the call-site when the condition is being applied, e.g:

public static class TenantUtils
{
  public static SqlExpression<T> FromTenant<T>(this IDbConnection db, int userId)
      where T : IHasUserId
      => db.From<T>().Ensure(x => x.UserId == userId);
}

Which your Services would call to return a pre-filtered SqlExpression query with:

var q = db.FromTenant<Invoice>(dto.UserId);
1 Like
4

Thank you very much to you two.
This is a smart solution which is convenient for me. I didn’t know about this Ensure() method. Great!

1 Like
5

Does that mean I cannot use AutoQuery anymore?

6

Our AutoQuery CRUD shows examples of using Ensure filters on AutoQuery APIs, e.g:

[ValidateIsAuthenticated]
[AutoFilter(QueryTerm.Ensure, nameof(IAudit.SoftDeletedDate), Template = SqlTemplate.IsNull)]
[AutoFilter(QueryTerm.Ensure, nameof(IAuditTenant.TenantId),  Eval = "Request.Items.TenantId")]
public abstract class QueryDbTenant<From, Into> : QueryDb<From, Into> {}

E.g. above example assumes the Tenant Id for the request is populated in IRequest.Items["TenantId"], e.g in a Request Filter. The Eval evaluates a #Script expression so is flexible.

7

I’m going to read the AutoQuery help because it’s a bit difficult for me at the moment.
Do you have an open source project where I can find all (or at least most) of the features used by ServiceStack? Thx

8

There are lots of different example ServiceStack projects showcasing different features linked to on our Explore page:
https://docs.servicestack.net/explore-servicestack

TechStacks is a large real world example that makes use of AutoQuery, but in general kitchen sink examples with everything enabled would be overwhelmingly complicated and distractive to what each feature does & needs.

1 Like