saved connection details

[Avinash-Bhat]

There should be a way to save/load the connections to a file. This helps in quickly connecting to a previous connection.

This is somewhat similar to the ssh config and will open up possibilities to have the different auto-commit, transaction-isolation, readOnly for each connections in future.

The current properties format may prove inadequate for this, and a new format like TOML or YAML may need to be agreed upon (I'm leaning towards the former).

[julianhyde]

I like the analogy with ssh config. I think it should be read-only (i.e. sqlline reads from the file if present but never writes to it). Writing might accidentally store passwords that users do not wish to store, in files that are not secure enough.

Like ssh_config, connections would have a name.

[Avinash-Bhat]

@julianhyde an alternative for the password could be to use a separate config for password. the password could be encrypted using something like jasypt.

[julianhyde]

True. I'm wary of using (two way) encryption (because you have to store the decrypt key somewhere). Better let people store their passwords in files that only they can see (and only they edit, but we read).

[aryeh-looker]

Certainly — yes — having something like a YAML or TOML file could be helpful. I got something like this to work by creating a wrapper for sqlline, written in java, which reaches into sqlline APIs to startup sqlline with an arbitrary classpath [classloader, too, but that may beyond the scope of this issue (could enable URI for classloader in YAML, but URI classloaders are going away in JDK11, so may be inappropriate)]/driver/credentials. Certainly saved credentials could be useful.

[snuyanzin]

I also have something in my sandbox e.g. there is a branch https://github.com/snuyanzin/sqlline/tree/saved_connections_in_file it can read (no writes from sqlline) similar to yaml files (not true yaml) however it's enough to specify properties for the connection and connection string looks like

!connect -c pg-pp

where pg-pp is the name of connection from my file. also it's possible to override or add some more properties in !connect, i.e. usage becomes

!connect [-p property value]* (-c <connectionName> | <url>) [username] [password] [driver]

example of such file

pg-local:
  url: jdbc:postgresql://localhost:5432/postgres
  user: postgres
  password: postgres
  another_property: value
  another_property2: value
  another_property3: value
pg-dev:
# comment
  url: jdbc:postgresql://mydev:5432/dev
  user: dev
  password: dev_pass
  another_property: value
pg-pp:
# comment
  url: jdbc:postgresql://my_pp:5432/pp
  user: pp
  password: pp_pass
  another_property: value
pg-live:
# comment
  url: jdbc:postgresql://my_live:5432/live
  user: live
  password: live_pass
  another_property: value