1

I am developing a SAAS application where : A single web application is hosted in IIS which will serve to multiple companies and each company will have its own database. The web application and databases will be running on the same server.

My question is - what is the best practice to change the database connection string based on the URL and should each function in the service layer pass the dbName/company name to identify the db connection string?

Currently, I am passing the dbname in each function in the data layer and it is returning the connection string. The function then connects to db and does the needful operation.

I have checked several multi tenant db queries online but did not find any answers related to my query. Any example with implementation would be a great help.

The way I do it is as follows :

function Send() { _emailService.SendEmail(emailId, dbName); } public class EmailService() { Public SendEmail(string emailId, string dbName) { var connStr = SQLHelper.GetConnectionStr(dbName); ... ... ... connect to db to fetch email details and then send email } } 
5
  • Sharing one application instance for multiple company clients seems risky. Can you not have one instance/db pair per company?
    – MetaFight
    CommentedDec 4, 2017 at 10:29
  • In that case say if we have 20 clients, I will have to deploy the code on 20 different instance which will be too time consuming (+ efforts). With the current architecture we can have a 3 (few) instances managed by a load balancer.CommentedDec 4, 2017 at 10:35
  • 2
    With your current approach you risk state from one company being leaked into another company's requests. Scripted deployments + containerisation significantly reduces the effort of running 20 instances.
    – MetaFight
    CommentedDec 4, 2017 at 11:28
  • I am not sure how state will be leaked? can you please throw more light on it? Also can you help with my original query as well?CommentedDec 4, 2017 at 11:43
  • I'm inexperienced with SAAS development. I could be way off-base. Don't mind me.
    – MetaFight
    CommentedDec 4, 2017 at 13:07

2 Answers 2

1

Running separate instances

It appears you are operating multiple, logically separate instances of a service (one per company). Fundamentally, you question is about how best to ensure that this isolation is maintained.

Currently, the isolation is achieved within your application. As @MetaFight pointed out, this has the risk that a bug in your code may break it. Obvious candidates are things that are kept in memory between requests, e.g. cached data.

Because different (logical) instances of your service do not seem to have a need to interact - i.e. company A's data does not influence company B's data - you might consider moving the isolation out of your application and dealing with it separately. (Again, see @MetaFight's comments).

This reduces the complexity of your application, at the cost of increasing the complexity of your deployments. State can still be leaked in this scenario, e.g. if you make a configuration mistake, connecting company A's service to company B's database. However, you have now separated this problem from your application's logic and can use all the available DevOps tools to handle this (common) problem separately.

Starting from the current state

However, I take it that your application is already implemented, so switching approaches may not (immediately) be an option. In that case, at least the domain logic should be unaware of the fact that there are multiple companies - and it should not keep state between requests. Perhaps you can modularize your application along the following lines:

  • Database Selector

    Turn a URL into a database accessor object. Make all requests through this object. Do the same for everything else that is dependent on the company.

  • Business Functionality

    Calculate data, send e-mails, etc. Uses a database accessor object where necessary. This module does not know there are multiple companies. Better yet, put DB access into a layer of its own and provide only the data to your business logic.

  • Request Handler

    Receives the request data, obtains the correct database accessor and provides it to the business layer.

This architecture can be significantly improved upon, but that's outside the scope of the question. (Take a look here, for example).

Once you have the application in this shape, it's much easier to go the next step: simply have the database selector provide always the same database connection, which can be read from a config file. And then replicate this setup as many times as you need.

Implementation Example

This is a very basic example. Again, there are many ways to improve it, but that's not specifically related to the question. I have kept the DB out of the business logic (sending mails) here. If that's too big a step at first, you can pass the IDatabase instance to the WelcomeMailSender and then go from there.

interface IDatabase { MailData GetMailDataForUser(int userId); } interface IDatabaseSelector { IDatabase GetFromUrl(Url url); } class SendWelcomeMailRequestHandler { private readonly IDatabaseSelector dbSelector; // inject via constructor private readonly WelcomeMailSender sender; // inject via constructor ResponseData HandleRequest(RequestData req) { var db = dbSelector.GetFromUrl(req.RequestUrl); var data = db.GetMailDataForUser(req.UserId); var success = sender.SendWelcomeMail(data); return new ResponseData() { Success = success }; } } class WelcomeMailSender { bool SendWelcomeMail(MailData data) { // do all the mail-sending stuff } } 
1
  • The solution works well if it is a web application, however, in future if I want develop windows service around it and reuse the business and service layer would not it be appropriate to pass the dbname from the controller to service layer down to business layer? In case of windows service, I will have to pass the dbname to the function which in turn invokes the service layer.. let me know your thoughts.CommentedDec 6, 2017 at 7:19
0

Tenancy should be tied in with authentication and authorization. URLs are not secure.

I like that you have a connection string factory (SQLHelper). You can inject that via dependency injection. This avoids duplicating code and making mistakes due to code duplication. To take this further, some DI frameworks allow injection per request (=per user). See the discussion on AddScoped here.

    Start asking to get answers

    Find the answer to your question by asking.

    Ask question

    Explore related questions

    See similar questions with these tags.