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

ServiceStack
21

I instead changed the connection string in app.settings to another database, to simulate multitenancy. Same problem. The PreRequestFilters codes doesn’t seem to be setting the new connection string.

22

So useDb is working as expected.

Created an app.settings that’s initialized with first db:

debug true
name Multitenancy test

# Configure below. Supported dialects: sqlite, mysql, postgresql, sqlserver
db sqlserver
db.connection $MSSQL_CONNECTION

Then added the _table.html script above, which lets me query the initial table as expected at /tablename.

Ran with:

$ x

I then use useDb to change it to use a different Connection String + RDBMS provider:

{{ {connectionString:'TECHSTACKS_DB'.envVariable(),providerName:'postgres'} |> useDb }}

and _table.html works as expected and queries the specified db /table-in-techstacks

23

That did 'er! I’m not sure why that useDb statement didn’t work when I tried it earlier. Perhaps it was competing with the custom plugin??? In any event, I have removed that plugin and all is well! Thanks so much for helping me through this.

Lovin’ #Script!!!

I will check out those uberdata scripts too.

1 Like
24

ok the request filter is working as well, I’ve got a custom Plugin created like:

public class ChangeDbFeature : IPlugin
{
    public void Register(IAppHost appHost)
    {
        var dbTechStacks = Environment.GetEnvironmentVariable("TECHSTACKS_DB");
        appHost.PreRequestFilters.Add((req, res) => {
            var db = req.QueryString["db"];
            if (db == null) return;
            req.Items[Keywords.DbInfo] = db == "northwind"
                ? new ConnectionInfo { 
                      ConnectionString = "northwind.sqlite", 
                      ProviderName = "sqlite" 
                }
                : db == "techstacks"
                    ? new ConnectionInfo {
                        ConnectionString = dbTechStacks,
                        ProviderName = "postgres"
                    }
                    : null;
        });
    }
}

Then in app.settings need to register the plugins you want loaded, e.g:

features ChangeDbFeature

Likely the missing piece, the AppHost is automatically used but you must explicitly list the plugins you want loaded with the features setting. When you don’t care about ordering you can use plugin/* to load all remaining plugins it finds.

I just needed to add the db parameter to the list of ignored fields:

var ignore = 'db,format,orderby,limit,offset'.split(',')

Then changing the db with ?db=northwind or ?db=techstacks works as expected.

I’d recommend this as the cleaner option since a PreRequestFilter will be able to set the IRequest.Items[Keywords.DbInfo] for all ServiceStack handled requests which the dbScripts is able to see and use.

25

Great! Will try this out tomorrow. This would be the missing piece we need because we need more control over each request, such as determining the host name, etc for which I’m assuming can be done here.

26

That worked! Thanks!

1 Like
27

Great, glad to hear it.

FYI I’ve updated the DbScripts to make them context aware of the dialect provider being used (i.e. instead of using the default provider).

This is available from the latest x dotnet tool v0.0.21:

$ dotnet tool update -g x

This now lets us write a more generic sharpdata/_table.html script which works in all RDBMS providers and supports Multitenancy where it will use the configured req.Items[Keywords.DbInfo] or useDb connection (if configured):

```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}}

A handy way to debug the generated SQL is to coment out the return statement, e.g:

* sql |> dbSelect(queryDictionary.withoutKeys(ignore)) |> return *

Then the script will return the sql string output instead of the executed resultset.

This change is also available on the latest MyGet if you’re using this script in a normal dotnet app. I’ll publish an updated app in a couple of days time. Currently working on adding some features to it.

28

This is Great! Definitely useful for us. Can’t wait for the updated app.

29

VERY helpful, thanks! We can make great use of this script.

30

I’m getting an error when trying to pass in &id=2 for your sharpdata/_table.html example. The full query string is:

https://localhost:5001/data/table_name?db=platform&id=2

PostgresException: 42883: operator does not exist: integer = text

StackTrace:
   at Expression (binding): sql
   at Page: data/_table.html

But when I put in some other parameter instead of id, it works, such as:

https://localhost:5001/data/table_name?db=platform&field1=somevalue

Any idea what I’m missing?

31

That PostgreSQL doesn’t let you query an integer column with a string parameter, you’d need an explicit cast for types where it doesn’t exist or convert the value in the string queryDictionary to be an int.

For postgres it’s easier just to add the explicit cast to the column, e.g:

#each qs.Keys where !ignore.contains(it.sqlVerifyFragment().lower())
    `${sqlQuote(it)}::text = @${it}` |> addTo => filters
/each

I’ve also added a sqlCast DbScript which will allow you to perform an RDBMS agnostic cast that should work in all supported RDBMS’s, e.g:

#each qs.Keys where !ignore.contains(it.sqlVerifyFragment().lower())
    `${sqlQuote(it).sqlCast('varchar')} = @${it}` |> addTo => filters
/each

This is available in the latest x v0.0.27 dotnet tool just published.

32

Thank you. Will try it out tomorrow.

33

Has the Nuget package been updated yet? When I do this from the context of a .NET Core app it says it can’t find sqlCast.

34

Yeah it is now, I thought you were using the x tool for running sharpdata scripts.

Anyway in the latest v0.0.28 of the x tool it now also supports registering multiple RDBMS connections in app.settings, e.g:

Here’s an example of configuring a main SQL Server connection + a SQLite & PostgreSQL named connection:

debug false
name Multi RDBMS test

# Configure below. Supported dialects: sqlite, mysql, postgres, sqlserver
db sqlserver
db.connection $MSSQL_CONNECTION
db.connections[northwind]  { db:sqlite, connection:'northwind.sqlite' }
db.connections[techstacks] { db:postgres, connection:$TECHSTACKS_DB }

Which you can use without needing a C# plugin by including a useDb configuration in your script, which if you put in a directory + file structure like:

_db\_table.html

{{ {namedConnection:db} |> useDb  }}

```code|quiet
var ignore = ['db','format','orderby','skip','take']
var sql = `SELECT * FROM ${sqlQuote(table)}`
var filters = []
#each qs.Keys where !ignore.contains(it.sqlVerifyFragment().lower())
    `${sqlQuote(it).sqlCast('varchar')} = @${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(qs.toObjectDictionary().withoutKeys(ignore)) |> return
```
{{ ifError |> show(sql) }}
{{htmlError}}

You’ll be able to call your named RDBMS’s with the optimal paths like:

/northwind/customer
/techstacks/tecnhology

These scripts can be quickly downloaded in a new folder with mix:

$ md test && cd test
$ x mix sharpdata
35

Awesome, works great!!!

I thought you were using the x tool for running sharpdata scripts

I was but it proved to be a little disorienting for me. I prefer the full solution development experience in Visual Studio 2019 and being able to debug the plugins, etc. So I’m going back towards running these sharp scripts within that context.

Thanks!

36

Looks like a Guid type value isn’t working. No error, but not returning anything.

https://localhost:5001/data/test_model/?db=platform&id=547b41344f444a70832fbda848070168

Does the sqlCast script need to be updated to handle this?

37

The script is only going to support string comparisons or fields that can be coerced into strings.

First find out if you can use normal SQL (e.g. in a SQL Editor like DataGrip, pgAdmin or even psql) to query that field with a string value, if you can, use that exact string in the comparison.

38

This works in pgAdmin with id being a uuid field

SELECT id, name
FROM public.test_model
WHERE id = '6f542c312bff4e7b8d08c70615fc64bf'
39

Does this?

SELECT id, name
FROM public.test_model
WHERE id::varchar = '6f542c312bff4e7b8d08c70615fc64bf'
40

Nope. No errors, just no results.