Possible to change database connection at runtime in a Sharp App?

ServiceStack
1

In particular, using the rockwind-aws project as an example, I’d like to enable multitenancy by being able to change the database at run time such as you can do with a global request filter in a “normal” web app. Wasn’t sure if or how you can pull that off in a Sharp App.

2

If you want to add Multitenancy support to a Sharp App you’d need to use your own Custom AppHost as done in the Chat App demo where you can override GetDbConnection() to specify which IDbConnection should be used for each request.

3

Thanks for the reference. I’ll check it out and report back.

4

Having a difficult time getting this to work. I’m not quite understanding how to create the Plugin correctly. I’d also like to be able to debug during development. Is there anyway you could provide a sample project that for Visual Studio that has a Sharp App with a custom App Host in the same solution?

I tried following the Chat example to create a single solution with a Sharp App and App Host, but unable to get it to work.

What I tried doing was creating a regular Selfhost app, and then taking that compiled DLL and dropped it into a the plugins folder of a sharp app, but didn’t seem to do anything. But even if it did, I don’t like that workflow. I’d like to be able to have it in a single solution that could make use of the App Host so I can do things like change the DB connection at run time for the sharp page/api.

5

Just create a class library with your AppHost and drop it in the /plugins folder. The docs provides a link to the /example-plugins/Chat project used to create Chat.dll.

It doesn’t provide an IDE integrated debugging experience since it’s not a regular compiled & built dotnet app, I’d recommend configuring logging and analyzing your App through its log output, e.g:

LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);

If needed you can attach to the running Sharp App process (i.e. x.exe or app.exe) and debug it that way.

The only other thing you can try is NetCoreTemplates/parcel-webapp which has scripts for running the client bundling & compiling the server plugin. Otherwise Sharp Apps isn’t going to be a good fit.

6

I guess debugging within the IDE isn’t a big deal since we’re really only using it to change the connection string at run time.

So I took another look at the example chat app and was indeed able to recompile it and use the resulting DLL. However, I’m still not able to get the overridden GetDbConnection to trigger when calling a Sharp API that is using database scripting we added to the chat sharp app. So I guess I’ll have to tinker around with it a bit more.

Thanks

7

What Sharp API are you calling? Is this not a ServiceStack Service registered via Plugin?

8

So using the existing chat app, we have extended it to provide the following proof of concept, just for purposes of figuring how to eventually do this in a clean new app:

  1. Added a /hello/_name/index.html based on https://github.com/NetCoreWebApps/Blog/blob/master/hello/_name/index.html

  2. Added a /data/test/index.html with the following code to try and query the database

API /data/test

{{
 `SELECT * FROM test_model`
   |> dbSelect
   |> return
}}

#1 works, #2 doesn’t.

The plugin dll has the following in the app host:

public override IDbConnection GetDbConnection(IRequest req = null)
{
    var connectionString = $"Server=localhost;User Id=postgres;Password=admin;Database=platform;Pooling=true;MinPoolSize=0;MaxPoolSize=200";
    req.Items[Keywords.DbInfo] = new ConnectionInfo { 
        ConnectionString = connectionString
    };
    return base.GetDbConnection(req);
}

But because /data/test is not returning the data, the above is apparently not being triggered.

9

So in the first example you’re going through a Sharp API which is excuted through a ServiceStack Service so it executes the normal Service Request Filters to execute your script and treat its response like an API Response.

But if you’re just executing script page directly, it’s not going through a Service so your DB Scripts are just calling the DbScriptsAsync.cs directly, which are just wrappers around OrmLite APIs which doesn’t know about ServiceStack or its Services/Filters/etc.

Thinking through some things you can try, first thing you can try is at the start of your page calling:

{{ {connectionString:'...'} |> useDb }}

Which calls the useDb script to initialize DbInfo for that script execution.

Something else that should work (depending on how the IRequest is passed down to #Script), is that since all ServiceStack Requests execute the PreRequestFilters, you can instead configure the DB Connection to use in a PreRequestFilters which you can register in a plugin (so you may not need a custom AppHost), e.g:

appHost.PreRequestFilters.Add((req, res) => {
    req.Items[Keywords.DbInfo] = new ConnectionInfo {
        ConnectionString = "..."
    };
});

Which should work for both Services & non Service requests like direct #Script Pages.

10

By the way since you’re experimenting with #Script data APIs you might find this generic data /sharpdata/_table.html script useful:

```code|quiet
var ignore = 'db,format,orderby,skip,take'.split(',')
var sql = `SELECT * FROM ${sqlQuote(table)}`

var filters = []
#each qs.Keys where !ignore.contains(it.sqlVerifyFragment().lower())
    `${sqlQuote(it)} = @${it}` |> addTo => filters
/each
#if !filters.isEmpty()
    sql = `${sql} WHERE ${filters.join(' AND ')}`
/if

#if qs.orderBy
    sql = `${sql} ORDER BY ${sqlOrderByFields(qs.orderBy)}`
/if
#if qs.skip || qs.take
    sql = `${sql} ${sqlLimit(qs.skip,qs.take)}`
/if

sql |> dbSelect(queryDictionary.withoutKeys(ignore)) |> return
```
{{sql}}
{{htmlError}}

Which lets you query different tables in your RDBMS, e.g. it’s enabled on http://rockwind-sqlite.web-app.io so you can use it to do common queries:

11

This didn’t work:

{{ {connectionString:'Server=localhost;User Id=postgres;Password=admin;Database=platform;Pooling=true;MinPoolSize=0;MaxPoolSize=200'} |> useDb }}
  {{
    `SELECT * FROM test_model`
      |> dbSelect
      |> return
   }}

And I’m a bit confused by the appHost code. Where does that go if not implementing a custom AppHost?

12

In a plugin.

13

Created a Plugin as:

namespace MyScriptPlugin
{
    public class Class1 : IPlugin
    {
        public void Register(IAppHost appHost)
        {
            appHost.PreRequestFilters.Add((req, res) => {
                req.Items[Keywords.DbInfo] = new ConnectionInfo
                {
                    ConnectionString = "Server=localhost;User Id=postgres;Password=admin;Database=platform;Pooling=true;MinPoolSize=0;MaxPoolSize=200"
                };
            });
        }
    }
}

Getting error on run:

C:\Users-\Documents\GitHub\chat-master>web
Using ‘C:\Users-\Documents\GitHub\chat-master\app.settings’
Attempting to load plugin ‘plugins/MyScriptPlugin.dll’, size: 5120 bytes
Unable to load one or more of the requested types.
Could not load file or assembly 'System.Runtime, Version=4.2.2.0,

14

Is this a .NET Standard 2.0 library? It’s a known issue for netcoreapp .dll’s, don’t know why it doesn’t support dynamically loading netcoreapp Assemblies when all the System.* .dll’s are pre-loaded in the framework install.

15

No, it’s a .NET Core 3.1 library.

16

Change it to:

<TargetFramework>netstandard2.0</TargetFramework>
17

Ok, that fixed the error. But still unable to get the connection to change at run time no matter what I try.

So I just realized something. I don’t think I’m loading the OrmLite PostgreSQL library anywhere. Where would that be done in this case? Maybe that is why it’s bombing?

18

Your initial db connection needs to be defined in your app.settings, e.g:

db postgres
db.connection Server=localhost;User Id=postgres;Password=admin;Database=platform;Pooling=true;MinPoolSize=0;MaxPoolSize=200
19

Ok, so that did the trick for the initial load. /data/test now returns expected results. But it seems the connection string is still not getting set for each request from within that custom plugin because when I change the app.settings connection string to something bogus, it bombs out again on the request to /data/test. I would expect at that point for the PreRequestFilters code to supply the correct connection.

20

All the PreRequestFilters does is set a variable as visible in its implementation, the registered DbScripts will look to see if it’s set, but you can’t just initialize the original db connection with an invalid connecitonString and expect it to work.

I’ll test the pre-request filter to see if it works for me.