Multiple App Databases

ServiceStack Apps have great support for multiple App Databases, for all it's supported RDBMS starting with the built-in Database Admin UI which lets you browse and query an App's configured databases:

You can easily try this out from a new database-enabled blazor-vue project template, created with the x dotnet tool:

This will let you create any ServiceStack Project Template with your preferred Project Name from the command-line, e.g:

Which creates a new .NET App that you can open with your preferred .NET IDE or text editor, e.g:

By default the App is configured to use a local SQLite database, we can extend it to connect to different RDBMS's by adding the necessary RDBMS and AdminDatabaseFeature NuGet packages in DatabaseTest.csproj:

<PackageReference Include="ServiceStack.OrmLite.MySql" Version="8.*" />
<PackageReference Include="ServiceStack.OrmLite.PostgreSQL" Version="8.*" />
<PackageReference Include="ServiceStack.OrmLite.SqlServer.Data" Version="8.*" />
<PackageReference Include="ServiceStack.Server" Version="8.*" />

We can then register named connections for each of our databases by replacing the existing Configure.Db.cs with:

public class ConfigureDb : IHostingStartup
{
    public void Configure(IWebHostBuilder builder) => builder
        .ConfigureServices((context,services) => {
            var dbFactory = new OrmLiteConnectionFactory(
                context.Configuration.GetConnectionString("DefaultConnection") ?? "App_Data/db.sqlite",
                SqliteDialect.Provider);

            dbFactory.RegisterConnection("postgres", 
                "Server=localhost;User Id=postgres;Password=p@55wOrd;Database=test;Pooling=true;MinPoolSize=0;MaxPoolSize=200",
                PostgreSqlDialect.Provider);

            dbFactory.RegisterConnection("mysql", 
                "Server=localhost;User Id=root;Password=p@55wOrd;Database=test;Pooling=true;MinPoolSize=0;MaxPoolSize=200",
                MySqlDialect.Provider);

            dbFactory.RegisterConnection("mssql", 
                "Server=localhost;User Id=sa;Password=p@55wOrd;Database=test;MultipleActiveResultSets=True;Encrypt=False;",
                SqlServer2012Dialect.Provider);

            services.AddSingleton<IDbConnectionFactory>(dbFactory);
        })
        .ConfigureAppHost(appHost => {
            // Enable built-in Database Admin UI at /admin-ui/database
            appHost.Plugins.Add(new AdminDatabaseFeature());
        });
}

This will now let us access the registered databases in our APIs, but first lets populate the databases with some data.

When a new project is created it populates its default configured SQLite database with some test data, we can do the same for the other registered database by duplicating the App's initial DB migration to a new DB Migration1001.cs with:

Then annotating it with a [NamedConnection] attribute for each of your registered database, e.g:

[NamedConnection("mssql")]
[NamedConnection("mysql")]
[NamedConnection("postgres")]
public class Migration1001 : MigrationBase
{
    //...
}

That can then be executed with:

Where it will execute all new DB Migrations, in this case apply the same Migration to each configured database.

Now that our App's databases are all populated and ready to go, we can run it with:

Then view the built-in Admin Database UI at:

and signing in with the Admin user created in Configure.AuthRepository.cs:

• admin@email.com
• p@55wOrd

Where it displays all the App's configured database tables on its home page:

Whose contents can be viewed by drilling down and clicking on each table:

Which displays its rows using the AutoQuery Grid Vue Component that can be sorted and filtered as needed:

Named database connections​

Named connections can be opened by its name from the registered IDbConnectionFactory:

using var db = dbFactory.Open("postgres");

Inside a Service this can be resolved using OpenDbConnection:

public class MyServices : Service
{
    public object Any(GetPostgresBookings request)
    {
        using var db = OpenDbConnection("postgres");
        return db.Select<Booking>();
    } 
}

The [NamedConnection] attribute can be used to configure Services base.Db connection with the named connection RDBMS, e.g:

[NamedConnection("mysql")]
public class QueryMySqlBookings {}

public class BookingServices : Service
{
    public object Any(QueryMySqlBookings request) => Db.Select<Reports>();
}

Or if using AutoQuery it can be used to associate Data Models with the named connection:

[NamedConnection("mssql")]
public class QuerySqlServerBookings : QueryDb<Booking> {}

Otherwise for other Services the [ConnectionInfo] attribute can be used to change the base.Db to use the registered named connection for all APIs in a Service class, e.g:

[ConnectionInfo(NamedConnection = "postgres")]
public class PostgresServices : Service
{
    public object Any(GetPostgresBookings request)
    {
        return db.Select<Booking>();
    }
}

Vue .mjs project template features​

Whilst you have the App running, check out its other high-productivity features:

Create a multi-user Booking system with AutoQuery​

The App's Bookings APIs are built using AutoQuery CRUD, allowing for rapid development of typed CRUD Services using only declarative POCO DTOs:

In addition, all AutoQuery APIs benefit from the built-in Locode's Auto Management UI:

As well as end-to-end typed integrations with the most popular programming languages accessible from the code tab of the built-in API Explorer:

I hope this has been an informative post and highlighted some cool products and features, any questions or feedback is welcome by commenting below.