Skip to content


Demis Bellot edited this page Oct 25, 2016 · 17 revisions

This page has moved to

ServiceStack provides a number of ways of changing the database connection used at runtime based on an incoming Request. You can use a Request Filter, use the [ConnectionInfo] Request Filter Attribute, use the [NamedConnection] attribute on Auto Query Services, access named connections in Custom Service implementations or override GetDbConnection(IRequest) in your AppHost.

Change Database Connection at Runtime

The default implementation of IAppHost.GetDbConnection(IRequest) includes an easy way to change the DB Connection that can be done by populating the ConnectionInfo POCO in any Request Filter in the Request Pipeline:

req.Items[Keywords.DbInfo] = new ConnectionInfo {
    NamedConnection  = ... //Use a registered NamedConnection for this Request
    ConnectionString = ... //Use a different DB connection for this Request
    ProviderName     = ... //Use a different Dialect Provider for this Request

To illustrate how this works we'll go through a simple example showing how to create an AutoQuery Service that lets the user change which DB the Query is run on. We'll control which of the Services we want to allow the user to change the DB it's run on by having them implement the interface below:

public interface IChangeDb
    string NamedConnection { get; set; }
    string ConnectionString { get; set; }
    string ProviderName { get; set; }

We'll create one such AutoQuery Service, implementing the above interface:

public class QueryRockstars : QueryBase<Rockstar>, IChangeDb
    public string NamedConnection { get; set; }
    public string ConnectionString { get; set; }
    public string ProviderName { get; set; }

For this example we'll configure our Database to use a default SQL Server 2012 database, register an optional named connection looking at a "Reporting" PostgreSQL database and register an alternative Sqlite RDBMS Dialect that we also want the user to be able to use:

ChangeDB AppHost Registration

container.Register<IDbConnectionFactory>(c => 
    new OrmLiteConnectionFactory(defaultDbConn, SqlServer2012Dialect.Provider));

var dbFactory = container.Resolve<IDbConnectionFactory>();

//Register NamedConnection
dbFactory.RegisterConnection("Reporting", ReportConnString, PostgreSqlDialect.Provider);

//Register DialectProvider
dbFactory.RegisterDialectProvider("Sqlite", SqliteDialect.Provider);

ChangeDB Request Filter

To enable this feature we just need to add a Request Filter that populates the ConnectionInfo with properties from the Request DTO:

GlobalRequestFilters.Add((req, res, dto) => {
   var changeDb = dto as IChangeDb;
   if (changeDb == null) return;

   req.Items[Keywords.DbInfo] = new ConnectionInfo {
       NamedConnection = changeDb.NamedConnection,
       ConnectionString = changeDb.ConnectionString,
       ProviderName = changeDb.ProviderName,

Since our IChangeDb interface shares the same property names as ConnectionInfo, the above code can be further condensed using a Typed Request Filter and ServiceStack's built-in AutoMapping down to just:

RegisterTypedRequestFilter<IChangeDb>((req, res, dto) =>
    req.Items[Keywords.DbInfo] = dto.ConvertTo<ConnectionInfo>());

Change Databases via QueryString

With the above configuration the user can now change which database they want to execute the query on, e.g:

var response = client.Get(new QueryRockstars()); //SQL Server

var response = client.Get(new QueryRockstars {   //Reporting PostgreSQL DB
    NamedConnection = "Reporting"

var response = client.Get(new QueryRockstars {   //Alternative SQL Server Database
    ConnectionString = "Server=alt-host;Database=Rockstars;User Id=test;Password=test;"

var response = client.Get(new QueryRockstars {   //Alternative SQLite Database
    ConnectionString = "C:\backups\2016-01-01.sqlite",
    ProviderName = "Sqlite"

ConnectionInfo Attribute

To make it even easier to use we've also wrapped this feature in a simple ConnectionInfoAttribute.cs which allows you to declaratively specify which database a Service should be configured to use, e.g we can configure the Db connection in the Service below to use the PostgreSQL Reporting database with:

[ConnectionInfo(NamedConnection = "Reporting")]
public class ReportingServices : Service
    public object Any(Sales request)
        return new SalesResponse { Results = Db.Select<Sales>() };

Auto Query Named Connection

Auto Query can also easily be configured to query any number of different databases registered in your AppHost.

In the example below we configure our main RDBMS to use SQL Server and register a Named Connection to point to a Reporting PostgreSQL RDBMS:

var dbFactory = new OrmLiteConnectionFactory(connString, SqlServer2012Dialect.Provider);

dbFactory.RegisterConnection("Reporting", pgConnString, PostgreSqlDialect.Provider);

Any normal AutoQuery Services like QueryOrders will use the default SQL Server connection whilst QuerySales will execute its query on the PostgreSQL Reporting Database instead:

public class QueryOrders : QueryDb<Order> {}

[ConnectionInfo(NamedConnection = "Reporting")]
public class QuerySales : QueryDb<Sales> {}

An alternative to specifying the [ConnectionInfo] Request Filter Attribute on the AutoQuery Request DTO, is to specify the named connection on the POCO Table instead, e.g:

public class Sales { ... }

public class QuerySales : QueryDb<Sales> {}

Resolving Named Connections in Services

Whilst inside a Service you can change which DB connection to use by passing in the NamedConnection when opening a DB Connection. E.g. The example below allows the user to change which database to retrieve all sales records for otherwise fallbacks to "Reporting" database by default:

public class SalesServices : Service
   public IDbConnectionFactory ConnectionFactory { get; set; } 

   public object Any(GetAllSales request)
       var namedConnection = request.NamedConnection ?? "Reporting";
       using (var db = ConnectionFactory.Open(namedConnection)) 
           return db.Select<Sales>();

Override Connection used per request at Runtime

All built-in dependencies available from Service base class, AutoQuery, Razor View pages, etc are resolved from a central overridable location in your AppHost. This lets you control which pre-configured dependency gets used based on the incoming Request for each Service by overriding any of the AppHost methods below:

public virtual IDbConnection Db
    get { return db ?? (db = HostContext.AppHost.GetDbConnection(Request)); }

public virtual ICacheClient Cache
    get { return cache ?? (cache = HostContext.AppHost.GetCacheClient(Request)); }

public virtual MemoryCacheClient LocalCache
    get { return localCache ?? 
              (localCache = HostContext.AppHost.GetMemoryCacheClient(Request)); }

public virtual IRedisClient Redis
    get { return redis ?? (redis = HostContext.AppHost.GetRedisClient(Request)); }

public virtual IMessageProducer MessageProducer
    get { return messageProducer ?? 
              (messageProducer = HostContext.AppHost.GetMessageProducer(Request)); }

E.g. to change the DB Connection your Service uses you can override GetDbConnection(IRequest) in your AppHost.

Multitenancy RDBMS AuthProvider

ServiceStack resolves its IAuthProvider from the overridable GetAuthRepository(IRequest) AppHost factory method just like the other "Multitenancy-aware" dependencies above letting you dynamically change which AuthProvider should be used based on the incoming request.

This can be used with the new OrmLiteAuthRepositoryMultitenancy provider to maintain isolated User Accounts per tenant in all major supported RDBMS

Since each tenant database uses their own isolated UserAuth tables we need to provide the list of db connection strings that the OrmLite AuthRepository uses to check and create any missing User Auth tables:

var connectionStrings = 100.Times(i => GetConnectionStringForTenant(i));
container.Register<IAuthRepository>(c =>
    new OrmLiteAuthRepositoryMultitenancy(c.TryResolve<IDbConnectionFactory>(),

container.Resolve<IAuthRepository>().InitSchema(); // Create any missing UserAuth tables

However if you've already created all UserAuth table schema's for each tenant or are manually creating them out-of-band you can register it without the list of connection strings:

container.Register<IAuthRepository>(c =>
    new OrmLiteAuthRepositoryMultitenancy(c.TryResolve<IDbConnectionFactory>()));

Then to specify which AuthRepository should be used for each request we can override GetAuthRepository() in your AppHost and return the OrmLiteAuthRepositoryMultitenancy configured to use the same Multitenant DB connection used in that request, e.g:

public override IAuthRepository GetAuthRepository(IRequest req = null)
    return req != null
        ? new OrmLiteAuthRepositoryMultitenancy(GetDbConnection(req)) //At Runtime
        : TryResolve<IAuthRepository>();                              //On Startup

Now when GetAuthRepository() is called within the context of a request it uses the same Multitenancy DB as your other services, otherwise when called outside (e.g. on Startup) it uses the default IOC Registration configured with the connectionStrings for each Multitenant DB that it can use to create any missing UserAuth table schemas not found in any of the Multitenant databases.

Multi Tenancy Example

To show how easy it is to implement a Multi Tenancy Service with this feature we've added a stand-alone Multi Tenancy AppHost Example showing 2 different ways we can configure a Service to use different databases based on an incoming request.

In this example we've configured our AppHost to use the master.sqlite database as default and registered 3 different named connections referencing 3 different databases. Each database is then initialized with a different row in the TenantConfig table to identify the database that it's in.

public class MultiTenantChangeDbAppHost : AppSelfHostBase
    public MultiTenantChangeDbAppHost()
        : base("Multi Tennant Test", typeof (MultiTenantChangeDbAppHost).Assembly) {}

    public override void Configure(Container container)
        container.Register<IDbConnectionFactory>(new OrmLiteConnectionFactory(
            "~/App_Data/master.sqlite".MapAbsolutePath(), SqliteDialect.Provider));

        var dbFactory = container.Resolve<IDbConnectionFactory>();

        const int noOfTennants = 3;

        using (var db = dbFactory.OpenDbConnection())
            InitDb(db, "MASTER", "Masters inc.");

        noOfTennants.Times(i =>
            var tenantId = "T0" + (i + 1);
            using (var db = dbFactory.OpenDbConnectionString(GetTenantConnString(tenantId)))
                InitDb(db, tenantId, "ACME {0} inc.".Fmt(tenantId));

        RegisterTypedRequestFilter<IForTenant>((req,res,dto) => 
            req.Items[Keywords.DbInfo] = new ConnectionInfo { ConnectionString = GetTenantConnString(dto.TenantId)});

    public void InitDb(IDbConnection db, string tenantId, string company)
        db.Insert(new TenantConfig { Id = tenantId, Company = company });

    public string GetTenantConnString(string tenantId)
        return tenantId != null 
            ? "~/App_Data/tenant-{0}.sqlite".Fmt(tenantId).MapAbsolutePath()
            : null;

This example uses only contains a single Service which returns the first result in the TenantConfig table:

public interface IForTenant
    string TenantId { get; }

public class TenantConfig
    public string Id { get; set; }
    public string Company { get; set; }

public class GetTenant : IForTenant, IReturn<GetTenantResponse>
    public string TenantId { get; set; }

public class GetTenantResponse
    public TenantConfig Config { get; set; }

public class MultiTenantService : Service
    public object Any(GetTenant request)
        return new GetTenantResponse
            Config = Db.Select<TenantConfig>().FirstOrDefault(),

Calling this Service with a different TenantId value changes which database the Service is configured with:

var client = new JsonServiceClient(Config.AbsoluteBaseUri);

var response = client.Get(new GetTenant()); //= Company: Masters inc. 

var response = client.Get(new GetTenant { TenantId = "T01" }); //= Company: ACME T01 inc.

var response = client.Get(new GetTenant { TenantId = "T02" }); //= Company: ACME T02 inc.

var response = client.Get(new GetTenant { TenantId = "T03" }); //= Compnay: ACME T03 inc.

client.Get(new GetTenant { TenantId = "T04" }); // throws WebServiceException

An alternative way to support Multitenancy using a Custom DB Factory is available in MultiTennantAppHostTests.cs.

  1. Getting Started

    1. Creating your first project
    2. Create Service from scratch
    3. Your first webservice explained
    4. Example Projects Overview
    5. Learning Resources
  2. Designing APIs

    1. ServiceStack API Design
    2. Designing a REST-ful service with ServiceStack
    3. Simple Customer REST Example
    4. How to design a Message-Based API
    5. Software complexity and role of DTOs
  3. Reference

    1. Order of Operations
    2. The IoC container
    3. Configuration and AppSettings
    4. Metadata page
    5. Rest, SOAP & default endpoints
    6. SOAP support
    7. Routing
    8. Service return types
    9. Customize HTTP Responses
    10. Customize JSON Responses
    11. Plugins
    12. Validation
    13. Error Handling
    14. Security
    15. Debugging
    16. JavaScript Client Library (ss-utils.js)
  4. Clients

    1. Overview
    2. C#/.NET client
      1. .NET Core Clients
    3. Add ServiceStack Reference
      1. C# Add Reference
      2. F# Add Reference
      3. VB.NET Add Reference
      4. Swift Add Reference
      5. Java Add Reference
    4. Silverlight client
    5. JavaScript client
      1. Add TypeScript Reference
    6. Dart Client
    7. MQ Clients
  5. Formats

    1. Overview
    2. JSON/JSV and XML
    3. HTML5 Report Format
    4. CSV Format
    5. MessagePack Format
    6. ProtoBuf Format
  6. View Engines 4. Razor & Markdown Razor

    1. Markdown Razor
  7. Hosts

    1. IIS
    2. Self-hosting
    3. Messaging
    4. Mono
  8. Security

    1. Authentication
    2. Sessions
    3. Restricting Services
    4. Encrypted Messaging
  9. Advanced

    1. Configuration options
    2. Access HTTP specific features in services
    3. Logging
    4. Serialization/deserialization
    5. Request/response filters
    6. Filter attributes
    7. Concurrency Model
    8. Built-in profiling
    9. Form Hijacking Prevention
    10. Auto-Mapping
    11. HTTP Utils
    12. Dump Utils
    13. Virtual File System
    14. Config API
    15. Physical Project Structure
    16. Modularizing Services
    17. MVC Integration
    18. ServiceStack Integration
    19. Embedded Native Desktop Apps
    20. Auto Batched Requests
    21. Versioning
    22. Multitenancy
  10. Caching

  11. Caching Providers

  12. HTTP Caching 1. CacheResponse Attribute 2. Cache Aware Clients

  13. Auto Query

  14. Overview

  15. Why Not OData

  16. AutoQuery RDBMS

  17. AutoQuery Data 1. AutoQuery Memory 2. AutoQuery Service 3. AutoQuery DynamoDB

  18. Server Events

    1. Overview
    2. JavaScript Client
    3. C# Server Events Client
    4. Redis Server Events
  19. Service Gateway

    1. Overview
    2. Service Discovery
  20. Encrypted Messaging

    1. Overview
    2. Encrypted Client
  21. Plugins

    1. Auto Query
    2. Server Sent Events
    3. Swagger API
    4. Postman
    5. Request logger
    6. Sitemaps
    7. Cancellable Requests
    8. CorsFeature
  22. Tests

    1. Testing
    2. HowTo write unit/integration tests
  23. ServiceStackVS

    1. Install ServiceStackVS
    2. Add ServiceStack Reference
    3. TypeScript React Template
    4. React, Redux Chat App
    5. AngularJS App Template
    6. React Desktop Apps
  24. Other Languages

    1. FSharp
      1. Add ServiceStack Reference
    2. VB.NET
      1. Add ServiceStack Reference
    3. Swift
    4. Swift Add Reference
    5. Java
      1. Add ServiceStack Reference
      2. Android Studio & IntelliJ
      3. Eclipse
  25. Amazon Web Services

  26. ServiceStack.Aws

  27. PocoDynamo

  28. AWS Live Demos

  29. Getting Started with AWS

  30. Deployment

    1. Deploy Multiple Sites to single AWS Instance
      1. Simple Deployments to AWS with WebDeploy
    2. Advanced Deployments with OctopusDeploy
  31. Install 3rd Party Products

    1. Redis on Windows
    2. RabbitMQ on Windows
  32. Use Cases

    1. Single Page Apps
    2. HTML, CSS and JS Minifiers
    3. Azure
    4. Connecting to Azure Redis via SSL
    5. Logging
    6. Bundling and Minification
    7. NHibernate
  33. Performance

    1. Real world performance
  34. Other Products

    1. ServiceStack.Redis
    2. ServiceStack.OrmLite
    3. ServiceStack.Text
  35. Future

    1. Roadmap
Clone this wiki locally
You can’t perform that action at this time.