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
    }

}

2 Answers2

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
    }
}
doubleYou
  • 2,867
  • 1
  • 13
  • 26
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.

Martin K
  • 2,947