search

drogonframework / drogon

Drogon: A C++14/17/20 based HTTP web application framework running on Linux/macOS/Unix/Windows
MIT License
11.52k stars 1.1k forks source link

Ability to choose the default schema (search_path) #1792

Open papel opened 1 year ago

papel commented 1 year ago

I'm unable to choose the schema to use when connecting to the database. Currently I have to prepend the schema name before the table name in every query.

I also tried the following command and it didn't work. clientPtr->execSqlSync("SET search_path = $1", "my_schema");

When creating a Postgres connection in other libraries, we usually use arguments like options='-csearch_path=my_schema' to specify the schema.

It would be good if there were an option in config.json to choose the default schema. The parameter could be either "schema": "my_schema" or "rdbms_options": "-csearch_path=my_schema"

an-tao commented 1 year ago

@papel thanks for your feedback. Actually, there's a option for pg's schema, as following: image Did you try it?

papel commented 1 year ago

@an-tao, I tried adding it, but it isn't working. It says the relation doesn't exist and it is still using 'public'.

If I create a new project with drogon_ctl create project, the file config.json in the main directory will not have schema, but the file models/config.json will.

I also tried to find "search_path" and "schema" with grep -r -H in the source and I didn't find the place where it is handled.

rafaelelter commented 1 year ago

You can currently specify the connection string using the newPgClient method.

auto clientPtr = drogon::orm::DbClient::newPgClient("host=XXX port=XXX dbname=XXX user=XXX password=XXX options='-csearch_path=XXX'", 1);

I agree an implementation with the config file would be handy.

hwc0919 commented 5 months ago

This PR #1972 adds supports for pg connection options, but is currently hanging because I cannot make a good abstraction for three db types.