Should psql support URI syntax?

Hello,

Most database connectors/frameworks nowadays support a URI style
connection string. Something like:

pgsql://user:pass(at)host/database

Do we think psql should support this style of connection string?

Sincerely,

Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On 03/31/2011 07:25 PM, Joshua D. Drake wrote:
> Hello,
>
> Most database connectors/frameworks nowadays support a URI style
> connection string. Something like:
>
> pgsql://user:pass(at)host/database
>
> Do we think psql should support this style of connection string?
>
>

Syntactic sugar aside, what is the advantage of that over a conninfo string?

cheers

andrew

On Thu, 2011-03-31 at 19:32 -0400, Andrew Dunstan wrote:
>
> On 03/31/2011 07:25 PM, Joshua D. Drake wrote:
> > Hello,
> >
> > Most database connectors/frameworks nowadays support a URI style
> > connection string. Something like:
> >
> > pgsql://user:pass(at)host/database
> >
> > Do we think psql should support this style of connection string?
> >
> >
>
> Syntactic sugar aside, what is the advantage of that over a conninfo string?

I would think it would be purely syntatic sugar really, which does
incorporate a familiar interface for those who are working in different
worlds (.Net/Drupal/JAVA) etc...

Perhaps programability would also be useful from a shell/system script
perspective.

Sincerely,

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

> I would think it would be purely syntatic sugar really, which does
> incorporate a familiar interface for those who are working in
> different
> worlds (.Net/Drupal/JAVA) etc...

I wouldn't mind having something more standard supported; I'm always looking up the conninfo for the options I don't use frequently.

However, is there any standard for database URIs?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco

An advantage to this uri form is that it allows applications to be
configured uniformly - I do not need to ask "is this using libpq, needing
one sort of configuration, or Java, needing another?"

Rather, I may say, "here is a uri I may use with any of my applications"

On Thu, 2011-03-31 at 19:10 -0500, Joshua Berkus wrote:
> > I would think it would be purely syntatic sugar really, which does
> > incorporate a familiar interface for those who are working in
> > different
> > worlds (.Net/Drupal/JAVA) etc...
>
> I wouldn't mind having something more standard supported; I'm always looking up the conninfo for the options I don't use frequently.
>
> However, is there any standard for database URIs?

There is an IETF RFC for generic URI:

http://www.ietf.org/rfc/rfc2396.txt
http://www.ietf.org/rfc/rfc3986.txt

Sincerely,

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Heyho!

On Friday 01 April 2011 02.39:25 Christopher Browne wrote:
> An advantage to this uri form is that it allows applications to be
> configured uniformly - I do not need to ask "is this using libpq, needing
> one sort of configuration, or Java, needing another?"
>
> Rather, I may say, "here is a uri I may use with any of my applications"

Since URI stings are popular, it might really make sense if pg could
recommend a preferred form of postgres URI strings (and obviously implement
it in libpq). For the non-libpq APIs (there's at least
http://python.projects.postgresql.org/, don't know about others), it would
still be just a recommendation that they could follow or not follow, so the
situation wouldn't change too much from today, I fear.

cheers
-- vbi

--
East Indians sometimes see Heaven as a giant bureaucracy, and frequently
report being sent back because of clerical errors.
<http://scienceblogs.com/cortex/2007/01/culture_and_neardeath_experien.php>

On Fri, Apr 1, 2011 at 1:10 AM, Joshua Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> I would think it would be purely syntatic sugar really, which does
>> incorporate a familiar interface for those who are working in
>> different
>> worlds (.Net/Drupal/JAVA) etc...
>
> I wouldn't mind having something more standard supported; I'm always looking up the conninfo for the options I don't use frequently.

I have a sneaking suspicion that the options you have to look up won't
be any more obvious (or standardized) in a URI connection string.

That said, I do support adding this in the future, if only to keep up
with the Jones'.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Apr 1, 2011 at 10:24, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Fri, Apr 1, 2011 at 1:10 AM, Joshua Berkus <josh(at)agliodbs(dot)com> wrote:
>>
>>> I would think it would be purely syntatic sugar really, which does
>>> incorporate a familiar interface for those who are working in
>>> different
>>> worlds (.Net/Drupal/JAVA) etc...
>>
>> I wouldn't mind having something more standard supported; I'm always looking up the conninfo for the options I don't use frequently.
>
> I have a sneaking suspicion that the options you have to look up won't
> be any more obvious (or standardized) in a URI connection string.
>
> That said, I do support adding this in the future, if only to keep up
> with the Jones'.

So are the ones out there *already* even compatible, before we start
adding our own? For example, for JDBC I beleive it has to be
jdbc:postgresql://blahblah... Even if you can say the jdbc part is
protocol specific, the example quoted by JD had pgsql://. How many
other combinations can we find already out in the wild, and how do we
pick which one to use in this case?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 04/01/2011 04:34 AM, Magnus Hagander wrote:
> On Fri, Apr 1, 2011 at 10:24, Dave Page<dpage(at)pgadmin(dot)org> wrote:
>> On Fri, Apr 1, 2011 at 1:10 AM, Joshua Berkus<josh(at)agliodbs(dot)com> wrote:
>>>> I would think it would be purely syntatic sugar really, which does
>>>> incorporate a familiar interface for those who are working in
>>>> different
>>>> worlds (.Net/Drupal/JAVA) etc...
>>> I wouldn't mind having something more standard supported; I'm always looking up the conninfo for the options I don't use frequently.
>> I have a sneaking suspicion that the options you have to look up won't
>> be any more obvious (or standardized) in a URI connection string.
>>
>> That said, I do support adding this in the future, if only to keep up
>> with the Jones'.
> So are the ones out there *already* even compatible, before we start
> adding our own? For example, for JDBC I beleive it has to be
> jdbc:postgresql://blahblah... Even if you can say the jdbc part is
> protocol specific, the example quoted by JD had pgsql://. How many
> other combinations can we find already out in the wild, and how do we
> pick which one to use in this case?
>

Of course they aren't compatible. So we solve that by just adding to the
soup!

cheers

andrew

On Fri, 2011-04-01 at 08:13 -0400, Andrew Dunstan wrote:

> >> That said, I do support adding this in the future, if only to keep up
> >> with the Jones'.
> > So are the ones out there *already* even compatible, before we start
> > adding our own? For example, for JDBC I beleive it has to be
> > jdbc:postgresql://blahblah... Even if you can say the jdbc part is
> > protocol specific, the example quoted by JD had pgsql://. How many
> > other combinations can we find already out in the wild, and how do we
> > pick which one to use in this case?
> >
>
>
> Of course they aren't compatible. So we solve that by just adding to the
> soup!

Well I would argue that if compatibility (as opposed to familiarity) is
our goal, we need to focus on one and only one syntax, JDBC. It doesn't
matter our particular bent, JDBC is the one that is in the most use.

If we can agree on syntax we want to support, I would put efforts into
working a patch.

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

"Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:

> Well I would argue that if compatibility (as opposed to
> familiarity) is our goal, we need to focus on one and only one
> syntax, JDBC. It doesn't matter our particular bent, JDBC is the
> one that is in the most use.

The start of a URI defines the protocol so that the correct protocol
handler can be used, like http: versus ftp:. jdbc:postgresql: URIs
define one protocol on the wire. Are we talking about a separate
protocol or the same one, in terms of what happens on the wire? If
the same one, I would tend to agree with JD that we can just use the
existing URI format. It seems to me that claiming a second protocol
prefix for the same protocol would only be a good idea if there was
a "marketing" benefit in doing so.

If we do decide it's worth getting some non-jdbc-based protocol
identifier, I would suggest pq: if it's not taken, as we call the
library for using it libpq.

-Kevin

On Fri, 2011-04-01 at 12:04 -0500, Kevin Grittner wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
>
> > Well I would argue that if compatibility (as opposed to
> > familiarity) is our goal, we need to focus on one and only one
> > syntax, JDBC. It doesn't matter our particular bent, JDBC is the
> > one that is in the most use.
>
> The start of a URI defines the protocol so that the correct protocol
> handler can be used, like http: versus ftp:. jdbc:postgresql: URIs
> define one protocol on the wire. Are we talking about a separate
> protocol or the same one, in terms of what happens on the wire? If
> the same one, I would tend to agree with JD that we can just use the
> existing URI format. It seems to me that claiming a second protocol
> prefix for the same protocol would only be a good idea if there was
> a "marketing" benefit in doing so.

Good point then it would be something like:

postgresql:ssl/
or
pq:ssl/

?

Sincerely,

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Fri, Apr 1, 2011 at 12:43 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> On Fri, 2011-04-01 at 08:13 -0400, Andrew Dunstan wrote:
>
>> >> That said, I do support adding this in the future, if only to keep up
>> >> with the Jones'.
>> > So are the ones out there *already* even compatible, before we start
>> > adding our own? For example, for JDBC I beleive it has to be
>> > jdbc:postgresql://blahblah... Even if you can say the jdbc part is
>> > protocol specific, the example quoted by JD had pgsql://. How many
>> > other combinations can we find already out in the wild, and how do we
>> > pick which one to use in this case?
>> >
>> Of course they aren't compatible. So we solve that by just adding to the
>> soup!
>
> Well I would argue that if compatibility (as opposed to familiarity) is
> our goal, we need to focus on one and only one syntax, JDBC. It doesn't
> matter our particular bent, JDBC is the one that is in the most use.
>
> If we can agree on syntax we want to support, I would put efforts into
> working a patch.

+1 on that. JDBC does happen to get used a lot, so that sure seems
like it would be useful.

Adding a different sort of URI (e.g. - as Kevin Grittner suggests,
downthread, a "pq:") doesn't strike me as being very useful, as it
would only get potentially used for "new" things, and it would be
worse in the sense that it "adds to the soup" of URI formats that are
a distinct minority in their degree of usage.

As I already commented, there's value in being able to use the already
somewhat popular JDBC URIs for our other accesses to Postgres
databases.

Wearing my "Afilias hat," that's certainly true.

- We *know* we'll need to use JDBC URIs in many cases, as we've got a
goodly number of applications written in Java that will be using that.
(And, not to beat a dead horse, these cases would find a "pq:" URI to
be useless.)

- I think there'd be a "win" in being able to use the very same URIs
for other things, as well. Personally, I like using
PGHOST/PGPORT/PGDATABASE/... quite well, but I'm pretty sure life
would be made easier for our operations folks if they only had one
form of URI (or 'connection string') they needed to set up.
--
http://linuxfinances.info/info/linuxdistributions.html

On fre, 2011-04-01 at 09:12 +0200, Adrian von Bidder wrote:
> Since URI stings are popular, it might really make sense if pg could
> recommend a preferred form of postgres URI strings (and obviously
> implement it in libpq). For the non-libpq APIs (there's at least
> http://python.projects.postgresql.org/, don't know about others), it
> would still be just a recommendation that they could follow or not
> follow, so the situation wouldn't change too much from today, I fear.

Well, there isn't any requirement that URIs be

prot://hostname:port/something

They just have to be

prot:something

So you could just turn the existing conninfo syntax into a URI by doing
something like

postgresql:dbname=foo%20hostname=bar

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Well, there isn't any requirement that URIs be

> prot://hostname:port/something

> They just have to be

> prot:something

> So you could just turn the existing conninfo syntax into a URI by doing
> something like

> postgresql:dbname=foo%20hostname=bar

True, but the need for those %20's is annoying. I tend to agree with
the suggestion that adopting the JDBC syntax would be the way to go,
assuming that we can use it 100%-as-is (any incompatibility defeats
the purpose).

regards, tom lane

On sön, 2011-04-03 at 12:41 -0400, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > Well, there isn't any requirement that URIs be
>
> > prot://hostname:port/something
>
> > They just have to be
>
> > prot:something
>
> > So you could just turn the existing conninfo syntax into a URI by doing
> > something like
>
> > postgresql:dbname=foo%20hostname=bar
>
> True, but the need for those %20's is annoying. I tend to agree with
> the suggestion that adopting the JDBC syntax would be the way to go,
> assuming that we can use it 100%-as-is (any incompatibility defeats
> the purpose).

Btw., there is also

$dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});

using a kind-of URI notation.

Hello,

O.k., the basic JDBC syntax is:

jdbc:<driver>://<host>[:<port>]/<database_name>

Where driver is the actual database such as postgresql or db2.

I am thinking something like:

postgres:ssl://localhost:5432/template

Many drivers support an extended syntax like:

postgres:ssl://localhost:5432/template1/?username=jd&password=foobar&ssl=true

But I don't know if we want to go there.

Sincerely,

Joshua D. Drake
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

"Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:

> Many drivers support an extended syntax like:
>
>
postgres:ssl://localhost:5432/template1/?username=jd&password=foobar&ssl=true
>
> But I don't know if we want to go there.

We've been there for years:

http://jdbc.postgresql.org/documentation/head/connect.html

-Kevin

On Wed, 2011-04-06 at 13:35 -0500, Kevin Grittner wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
>
> > Many drivers support an extended syntax like:
> >
> >
> postgres:ssl://localhost:5432/template1/?username=jd&password=foobar&ssl=true
> >
> > But I don't know if we want to go there.
>
> We've been there for years:
>
> http://jdbc.postgresql.org/documentation/head/connect.html
>

Not for psql we haven't.

JD

> -Kevin
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Hi,

On Wednesday 06 April 2011 20.31:38 Joshua D. Drake wrote:
> postgres:ssl://localhost:5432/template1/?username=jd&password=foobar&ssl=
> true
>
> But I don't know if we want to go there.

I would expect that *if* an URI syntax becomes implemented, it should
support all possible options.

Especially things like ssl but also connecting via Unix socket instead of
TCP should be covered. Else applications would need to offer one way to
specify the "usual" connection and another way to offer configuration with
"special requirements" such as connection to unix socket or via ssl. (...
which would probably not be supported by many applications, since "everybody
connects to localhost:5432 anyway..."

-- vbi

--
FORTUNE REMEMBERS THE GREAT MOTHERS: #6

"Johnny, if you fall and break your leg, don't come running to me!"
-- Mrs. Emily Barstow, June 16, 1954