Support native URI/DSNs

[Jaymon]

https://docs.python.org/3.11/library/sqlite3.html#how-to-work-with-sqlite-uris

[Jaymon]

SQLite

• https://www.sqlite.org/c3ref/open.html#urifilenameexamples
• https://www.sqlite.org/uri.html
• https://docs.python.org/3.11/library/sqlite3.html#how-to-work-with-sqlite-uris

Postgres

• https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
• https://www.psycopg.org/docs/extensions.html#psycopg2.extensions.parse_dsn

Where I'm at

I've updated dsnparse to better parse native postgres and sqlite connection strings. The next thing to do is update the DsnConnection class to handle native strings and configure them appropriately, Sqlite would do something like:

sqlite3.connect("file:<DB-NAME>?mode=ro", uri=True)

And Postgres:

psycopg2.connect(dsn="dbname=test user=postgres password=secret")

SQLite's DSNs should be no problem, if I see file: then I can set everything up appropriately. Postgres's are a little tougher because prom supports postgres also, the connection strings like dbnam=... user=... should be fine, but the uri's also start with postgres or postgresql and so I'm not sure how to distinguish them from ones I should just pass through to psycopg2 and which ones I should parse.

[Jaymon]

It would also be great to support connecting through environment variables. Here are the Postgres environment variables:

It would also be great to connect using the postgres password file

search and links

• psycopg connection through envrionment
• github.com psycopg psycopg2 issues 767 (via)
• postgres environment uri
• pgconnectionuri

The biggest issue here is how to auto-discover that the environment has been used. If I have like an EnvironConfig or something that will check the environment, it will have to semi-know about postgres in order to load the interface.

[Jaymon]

This might actually not be desirable behavior and could lead to unintended consequences, like an important db being cleared simply because prom was run in an environment that had postgres environment variables in it

[Jaymon]

I think I could support this if I allowed something like:

PROM_DSN=environment:postgres

that would tell prom to use the postgres interface and use the POSTGRES environment variables without having to set anything.

You could have shorthand and still have connection names:

PROM_DSN_1=environ:postgres#conn1
PROM_DSN_2=env:sqlite#conn2

I might be better if we reverse it:

PROM_DSN=postgres:environment

It would work the same way but then we don't need a toplevel environment configuration parser, it could just use the standard configuration parsing and that configuration could check for the environment name in the host/path and then handle the configuration.