Thread: Should psql support URI syntax?
Hello, Most database connectors/frameworks nowadays support a URI style connection string. Something like: pgsql://user:pass@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@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@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 usefrequently. However, is there any standard for database URIs? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco
<p>An advantage to this uri form is that it allows applications to be configured uniformly - I do not need to ask "is thisusing libpq, needing one sort of configuration, or Java, needing another?"<p>Rather, I may say, "here is a uri I mayuse 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 usefrequently. > > 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@agliodbs.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 usefrequently. 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@pgadmin.org> wrote: > On Fri, Apr 1, 2011 at 1:10 AM, Joshua Berkus <josh@agliodbs.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 usefrequently. > > 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@pgadmin.org> wrote: >> On Fri, Apr 1, 2011 at 1:10 AM, Joshua Berkus<josh@agliodbs.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'tuse 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@commandprompt.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@commandprompt.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@commandprompt.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 finda "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@gmx.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@gmx.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@commandprompt.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@commandprompt.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