Multi-tenant apps

Feb 22, 2016 at 1:13 PM
Hi

I have a requirement where I need to base the database connection on the customers url e.g. customer1.app.com. As I cannot access the url inside the application_start in Global.asax is there a way to register the SlimAPI controllers when I do have access to the request or do you have a better approach?

Thanks
Coordinator
Feb 22, 2016 at 7:44 PM
Hmmm... as far as I understand, you have multi-tenant application, so you have cust1, cust2, etc, and each has his own base address (http://cust1.app.com). But you have only one application that serves them, right? It means that the same controller/method should respond to URL like GET http://cust?.app.com/api/users
then in this case, it does not help if you register controller later, you have to arrange so that calls on different sub-domains are redirected to the same common URL that is served by the controller. I think you should look at IIS or web server you have to see how to setup such an arrangement. Or maybe networking routing rules. In the end, all calls would end up in the same place - which is where API is configured at startup.
Inside the controller you can get the full URL by inspecting the web context (controller.Context.WebContext) - you can extract URL and see the subdomain, and then act appropriately
Feb 23, 2016 at 8:33 AM
Hi

Yes it is a multi tenant application however each customer has their own base address and database. The master database will contain the license, package and invoices etc for a customer.

To know which customer database is required I need to get the customer address and check against the master database for the connection string, license, package. I think I will have to find another approach instead of using the slimapi due to not being able to set the connection so early in the process.
Coordinator
Feb 23, 2016 at 5:19 PM
I hope by 'find another approach instead of slimapi' you do not mean giving up slim api completely. Your problem lies outside of particular implementation of controller logic. As far as I see, you have a shared web server/web app, but it needs to serve different domains with different data stores. Whatever solution you use, classic ASP.NET Web API or other, you'll have the same challenge. So you need to solve this routing problem separately from main app logic I think. And then you can happily use SlimApi
Feb 23, 2016 at 5:28 PM
Currently I can see two ways from to resolve this.

1) use web api controllers and initialise the app within each request as I have access to the request
2) use one database to contain all customer data and filter each record with a tennant ID

Will have to have a think as to which way to go and if there are any other approaches to having multi tenant app with different databases.

Sent from Outlook Mobile




Coordinator
Feb 23, 2016 at 5:58 PM
I had multi-tenancy in mind, from the very start, there are some facilities there to support it, although they're not completely tested yet. On multiple databases side, you can connect an entity app to multiple databases / data sources (App.ConnectTo(dbSettings)), each data source has its Name/alias. When there are multiple data sources connected, the fwk looks at session.Context.DataSourceName; the data source name in operation context can be set in controller initialization - when context is assigned, based on the URL of the request, one way to do it. Just override SlimApiController.Init. Then normal data operations will go against the proper database
Feb 23, 2016 at 9:45 PM
I'm evaluating to integrate VITA framework in an existing project and I have the same multi tenancy problem, with different DBMS systems bonus (SQL server and MySql in same application).

In my scenario each user has 3 possible connections to use
  • app_connection that connects to the user database
  • meta_connection that connects to the application configuration
  • data_connections that connects to the user databsae
1st one is fixed for application, the two others connections vary depending on application currently in use and connected user.

Maybe we can share ideas and solutions. At the moment I'm doing it with ServiceStack.OrmLite configuring a connection factory on each request but I don't like it on the ORM side, I like it very much on the web service side.

The server side is stateless, so on each request I have to identify the user by the auth token he sends and just grab the correct connections.
Coordinator
Feb 23, 2016 at 10:28 PM
Edited Feb 23, 2016 at 10:29 PM
Ok guys, sounds like fun, let's figure it out. I'm pretty sure we can make it work.
DarkTeaPot, let me reiterate your list, just to make sure I got it right

Database A, single instance for all users; has it's own entity model (set of entities)
Database B, multiple databases, one entity model
Database C, multiple databases, one entity model, different from B

B and C have different logical models (entities). My guess all databases for B are MySql, while all for C are MS SQL (or vice versa), but it does not matter, you can mix different database types for one app.

So I suggest to define 3 entity apps: A, B, C, and put them into a container:
public class MyServer {
    public EntityAppA AppA;
    public EntityAppB AppB;
    public EntityAppC AppC;
 
   public static MyServer Instance; //singleton, globally available
}
In startup code, initialize the singleton - create entity app; then connect B and C to databases, you can have multiple databases connected with different name (data source name in DbSettings). When initial call comes, you login the user and figure out what's his data source name. You can save DataSourceName in user session (or in Authtoken). When actual data call comes in, you start with OperationContext, then open entity session. You create OperationContext from the entity app you need (A, B or C) and set its DataSourceName from user token. Then do the entity CRUD operations - VITA will correctly select the database.
VITA's WebCallContextHandler and SlimApi controller create operation context for you automatically, but they base it on a single app. You can use AppA for this default context. Then in controller code you can create another Context associated with other App (B or C) and proper DataSourceName
Feb 24, 2016 at 12:08 AM
Edited Feb 24, 2016 at 12:13 AM
Not sure I have explained my scenario fully. Here goes.....
  • 1 web app with multiple bindings (cust1.app.com, cust2.app.com etc)
  • There is 1 master database which will always be connected
  • Each customer has their own schema/database (possibly on the same database server but would definitely have their own schema/database)
From playing around this evening I have tried to initialise the customer entity app (in global) without a database connection to register the slimapi controllers. Override the slimapicontroller.initcontroller within my slimapicontroller e.g. CoursesController. I could then call context.app.connectto() and pass the database server connection details. The only issue with this is that I was unable to get the current web address to determine what database to connect to. I also found that I would also need to change the area for the customer entity app as this contains the schema.

So after all of this I have got no closer to a solution and more confused as to how to approach it ;)

This is the end result that I am trying to achieve.
  • 1 x Web App on a server farm
    • core app (connects to a single database which contains connection strings and schema/database to use for each customer plus other info)
    • customer specific app (another schema / database which would be on its own server but not all customers would be on the same database server)
  • customer 1
    • Domain: customer1.app.com
    • Database: db server 1 | schema: customer1-app
  • customer 2
    • Domain: customer2.app.com
    • Database: db server 1 | schema: customer2-app
  • customer 3
    • Domain: customer3.app.com
    • Database: db server 2 | schema: customer3-app
  • customer 4
    • Domain: customer4.app.com
    • Database: db server 2 | schema: customer4-app
Feb 24, 2016 at 7:14 AM
I came up with something like this to handle the connection to multiple database from a single EntityApp:
  1. first I configure the EntityApp without any connection
  2. when the user requests a connection to a specific database i check if the datasource is defined and if not configure it
  3. return an operation context configured for the user with the correct DataSourceName, if the DataSourceName doesn't result in an usable connection throw exception
What I have found is this
  • if I provide a DataSourceName that is not defined in the EntityApp no exception is thrown by the framework
  • DataAccessService is exposed by the EntityApp but I have to cast it to the base class to be able to check if a DataSourceName has already beed registered
The code that configures the various datasource is just a proof of concept of what will really happen, based on the user and the current application I will have to make some db call to get all the configuration parameters to construct the DbSettings.

Keep in mind I've been looking at vita code and features for the last couple of days so maybe I'm missing something.
 public static class GlobalStoreConfig
    {
        private static EntityAppA AppA { get; set; }

        public static void Configure()
        {
            AppA = new EntityAppA();
            AppA.Init();
        }

        public static OperationContext GetOperationContext(string dataSourceName, UserInfo userInfo = null)
        {
            var das =  (DataAccessService)AppA.DataAccess;

            if (das.DataSources.All(x => x.Name != dataSourceName))
            {
                var dbSettings = GetDbSettings(dataSourceName);
                if (dbSettings == null)
                    throw new ArgumentException($"no settings defined for {dataSourceName} database");

                AppA.ConnectTo(GetDbSettings(dataSourceName));
            }

            var op = new OperationContext(AppA, userInfo);
            op.DataSourceName = dataSourceName;

            return op;
        }

        private static DbSettings GetDbSettings(string dataSourceName)
        {
            var dbOptions = MsSqlDbDriver.DefaultMsSqlDbOptions & ~DbOptions.UseStoredProcs;
            var driver = new MsSqlDbDriver(MsSqlVersion.V2012);

            if (dataSourceName == "DB_A")
                return new DbSettings(driver, dbOptions, "Data Source=H2-DGK;Initial Catalog=h2_vsdev;Integrated Security=True", null, null, DbUpgradeMode.Never, dataSourceName: "DB_A");

            if (dataSourceName == "DB_B")
                return new DbSettings(driver, dbOptions, "Data Source=H2-DGK;Initial Catalog=h2_vsdev_test;Integrated Security=True", null, null, DbUpgradeMode.Never, dataSourceName: "DB_B");

            return null;
        }
    }
Coordinator
Feb 24, 2016 at 7:47 AM
about casting to DataAccessService; the better way is to get IDataSourceManagementService from the app:
var dsMgmt = app.GetService<IDataSourceManagementService>();
you can use then GetDataSource() method - returns null if not found; and RegisterDataSource to add new one.
Using data source name not registered - the exception should be thrown when you try a db operation, using the context and session. That's when the actual lookup will happen.
We actually have another problem here. DataAccessService is not thread-safe, it might crash if you access it from different threads while adding a data source. I need to add some efficient way to handle this. For now, for testing, it's OK, it should be rare event.
Overall looks OK.
Q1- how many data sources (databases) you normally expect? a 100? if so, keeping data sources in a list might not be good, better switch to dictionary. And protect with lock. I will look into that
Q2: Which one is MySql? are MySql databases also 'multiples'? in this case, we might have a problem, cause MySql treats schemas as databases, and this can be a problem, to have multiple 'schemas'. The database is specified in conn string, while schema is deeply hard-coded into Db model, and into SQL statements and stored procedures
Feb 24, 2016 at 8:07 AM
If I do
var dsMgmt = AppA.GetService<IDataSourceManagementService>();
var ds = dsMgmt.GetDataSource(dataSourceName);
to get the datasource from the DataSourceManagementService when no data source has been defined yet I get an exception saying that I can't do that when no data source has been defined.

Aboud the thread safe dataaccessservice, shouldn't be enough to place a lock on an external singleton object while adding the new data source ? This should be an operation that is done once for each database while the application is running or maybe place the data sources in a thread safe collection.

Q1 - I have like 300 databases right now on SQL Server
Q2 - I have also multiple MySql databases (50), they will slowly be converted to SQL Server but it will not be a fast process. I must admint my poor knowledge of MySql but I do not plan to use stored procedures or to have multiple schemas in the same database but maybe I didn't understand your concern.
Coordinator
Feb 24, 2016 at 4:52 PM
Edited Feb 24, 2016 at 7:36 PM
  1. service throwing error when no sources registered - my overlook, will fix that
  2. Thread safety. Adding lock on adding data source is not enough; we have to also lock GET operation, otherwise it might crash if it comes when ds is being added. The problem is that lock is expensive (several hundred cycles), so are thread-safe collections. I will think about a solution, for now for testing you're OK
  3. 300 tables - definitely we need dictionary of sources, not plain list.
  4. MySql is a problem, for now. The problem is that really there's no databases on the server, like MS SQL; only schemas. MySql guys say schemas are treated as databases, but essentially these are schemas, not databases - you can make FK link from one to another. So for MySql you have situation with 1 database and multiple schemas with identiical data model. That would not work in VITA with multi-tenancy, at least in the way we're going with MS SQL. I will look at it now and see if I can provide a solution at driver level.
  5. You need Oracle too?!!
Feb 24, 2016 at 5:18 PM
  1. ok no problem maybe it was the expected behaviour
  2. you are the master here :)
  3. ok
  4. I really hate MySql right now, why we specify a database in the connection string ?
  5. We have some clients with external oracle application we have to interface too, is not mandatory but would be nice to use the same access strategy for all databases.
Thanks
Feb 26, 2016 at 3:19 PM
I made some tests on the MySql side and I understood the problem about database and schema with VITA. I was thinkig to overcome the current limitation configuring different EntityApp for MySql chaning the schema of the modules and resolve the application with my IoC.

I guess the EntityApp.Init is a time consuming step but for testing purposes could be enough, what you think ?
Coordinator
Feb 26, 2016 at 4:30 PM
I think I have a solution for MySql, I will try it over weekend, if it works it will work the same as MS SQL. For now, just continue with MS SQL testing.
Did you see that DAtaAccess service is now multi-threaded? How's filtering working for you?
Feb 26, 2016 at 4:42 PM
I have updated my test solution with the new nuget packages and looked at the commit to see what was changed.
I've seen then multi-thread stuff in dataaccess and tested the filter on the OneToMany attribute and it works.

I have no idea what you have in mind on the soft deleting mplementation, but to me this would already work because once I filter out the first query my objects will load only relevant data.
Coordinator
Feb 29, 2016 at 4:43 PM
hi, about solution for mysql, doing it, give me another day and I'll try to push all
Roman
Feb 29, 2016 at 6:19 PM
Don't worry you are doing great!!
Coordinator
Mar 2, 2016 at 7:12 PM
Hi
new stuff is in. Two improvements for multi-tenant apps
For MS SQL, you can have multiple databases that are linked to the same EntityApp, and share the same DbModel (internal structure, quite heavy, containing all SQLs - it's good to have it shared between 300 databases).
To have DbModel shared, you need to create DbSettings in a special way. There are 2 constructor - one that accepts DbDriver, etc. The other one takes DbModelConfig object (which is a container for DbDriver, DbOptions, etc). So you need to use this constructor. Create a sinlgeton DbModelConfig, save it in static field, and use it for all DbSettings objects when you connect to new database. One important thing: make sure Options has flag ShareDbModel set.

For MySql databases - new feature 'mapped schemas'.. The EntityArea object you use in EntityApp constructor specifies default schema (like 'main'). But it can be mapped to different schema connecting the app to database. The DbSettings.ModelConfig object has a dictionary of mappings from area name (main) to custom schema. When you are connecting to new MySql database, create new DbSettings object with proper data, then call dbSettings.ModelConfig.MapSchema("main", customerSchema) - this will map this customer to MySql schema (which is essentially his database on server). "main" is your main entity area name.
Let me know how it goes, and if it works for you
Roman
Mar 3, 2016 at 8:04 AM
I have made some tests and what I see is that the first database i connect to is upgraded the second one no. I started with 2 empty databases to see what was going on. This happens both for mysql and sql server.
Coordinator
Mar 3, 2016 at 2:44 PM
I will look into this... sorry, do not have server cluster here, obviously insufficient testing. Hope you can live with this for now, a few weeks until next push
Mar 3, 2016 at 2:45 PM
No rush I do not use automatic migrations on multiple databases so not an issue at the moment.