Feature request: option for preconfigured host/port/user/database "profile"

[clbarnes]

Most people are connecting to only a small number of databases, with an even smaller number of users etc. It's a pain to write them out every time, though. This could be scripted, but pgcli could also handle it. The configurations could be added to the pgclirc, and potentially (singly) inherit from each other.

For example, rather than typing pgcli -h some.host.com -p 5433 -U someuser -d otherdb all the time, we could have in the config file

[profile]
[[work]]
host = some.host.com
port = 5433
user = someuser
database = otherdb

and type pgcli --profile work.

Then we could also do (inside the above)

[[[root]]]
user = postgres

pgcli --profile work.root would keep the config from profile.work, except where overwritten (i.e. the user).

Options given in the command line could also override the profile.

Doesn't have to be "profile" (something we could use a non-clashing abbreviation for would be great).

Using the full postgresql://[user[:password]@][netloc][:port][/dbname][?extra=value[&other=other-value]] string would be incompatible with using a profile.

EDIT: changed syntax to match ConfigObj. Anything directly in the profile section could be applied if --profile was given without an argument, or a user-wide default, or just act as a parent for "realised" profiles; whatever you think is best.

[laixintao]

Currently, there is a feature called alias_dsn which may suit your need. Both mycli and pglic are supported.

https://www.mycli.net/loginpath

[clbarnes]

Sorry, I missed that in the sample config file! However, the -d option in pgcli seems to be in use for database name, and I couldn't see how to access the aliases on the pgcli docs.

This certainly resolves most of the use cases for this, besides the readability and inheritance proposed here. These are pretty minor considerations though, so this can be closed if you think the use case is sufficiently covered.

[laixintao]

pgcli --help tells that you can use -D dsn_name for that:

 pgcli --help
Usage: pgcli [OPTIONS] [DBNAME] [USERNAME]

Options:
...
  -D, --dsn TEXT          Use DSN configured into the [alias_dsn] section of
                          pgclirc file.
  --list-dsn              list of DSN configured into the [alias_dsn] section
                          of pgclirc file.

But it is a little bit obscure indeed, may be we can list this feature in readme. :)

[clbarnes]

Happy for this to be closed when the docs PRs are merged: https://github.com/dbcli/pgcli.com/pull/39 and #1164 .

[nikkegg]

Currently, there is a feature called alias_dsn which may suit your need. Both mycli and pglic are supported.

https://www.mycli.net/loginpath

@laixintao Thank you for sharing this! It is kind of nice, but I could not for the life of me figure out how to pass a wildcard to DSN? I have multiple databases per environment, so it would be great if I could use a wildcard in DSN and just pass the database name as CLI arg, something like:

pgcli -D staging -d users

which is something pg_service.conf supports out of the box (via export PGSERVICE=x but nonetheless)

If anyone know how this can be achieved with pgcli I would be super keen and grateful to hear 🙏🏻

[laixintao]

I think https://github.com/dbcli/pgcli/pull/1352 will resolve this issue, basically it allows you use --dbname or --port to overwrite the value in dsn.

Unfortunately not being merged yet.