I'm able to get this to work

(.getConnection (next.jdbc/get-datasource {:jdbcUrl "jdbc:"
                                            :user "docker"
                                            :password "docker"}))
=> #object[org.postgresql.jdbc.PgConnection 0xbe0eb3d "org.postgresql.jdbc.PgConnection@be0eb3d"]

This also works

(next.jdbc/get-connection "jdbc:")
=> #object[org.postgresql.jdbc.PgConnection 0x4624fa68 "org.postgresql.jdbc.PgConnection@4624fa68"]

so it looks like next.jdbc doesn't support the user:password@host format?

Also, mildly interesting that the "jdbc:" is required at the beginning of the string now, previously it wasn't required. I'm not sure if that was a feature of the old version of migratus, or clojure.java.jdbc

It was an old "bug" in java.jdbc. I would have expected the plain JDBC string to work, at least with recent versions of next.jdbc. Let me check.

Yeah, the jdbc: string should work just fine in next.jdbc so maybe that's a bug in Migratus?

It’s not a user vs username thing? I seem to remember having an issue with that when I migrated a long time ago.

The "jdbc:" format works, "jdbc: doesn't edit: works when given to next.jdbc/get-connection

Results in an UnknownHostException

not sure if this is the best document to look at, but the format is listed here https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

@U021UJJ3CQ6 HikariCP has the user/username thing, not JDBC itself, just FYI.

@UCQGNA673 Does that second format work when given to next.jdbc directly? (with user:password@host) If so, then it sounds like something Migratus is doing wrong. Otherwise, if that second format does not work in next.jdbc on its own, please open a GH issue.

(next.jdbc/get-connection "jdbc:")
=> #object[org.postgresql.jdbc.PgConnection 0x543f6258 "org.postgresql.jdbc.PgConnection@543f6258"]

(next.jdbc/get-connection "jdbc:")
Execution error (UnknownHostException) at sun.nio.ch.NioSocketImpl/connect (NioSocketImpl.java:567).
docker:[email protected]

OK, I'll consider that a bug in next.jdbc. I'm on vacation today so if you can pop that into a GH issue with that code/output, I'll get it fixed this weekend.

Thank you, I'll open an issue!

FWIW, the next.jdbc code around this simply delegates to DriverManager/getConnection so it appears that it is JDBC itself that is rejecting the URL with the embedded user:password format (for whichever DB driver you are using).

I just looked around at JDBC documentation for several databases and I can't actually find any examples of user:password@host -- except for Oracle Thin JDBC which supports user/password@host -- all of the other JDBC docs I've found show ?user=..&password=.. in the URL, not embedded, and talk about setting them as properties or providing them to getConnection() rather than embedded before the hostname in the URL.

https://jdbc.postgresql.org/documentation/use/ doesn't seem to indicate that user:password@host is accepted.

Interesting. It's accepted by clojure.java.jdbc

I'll open the ticket nevertheless and include this info on the ticket. I'm not sure if it's better to support the format or not. 🤷

I'll have to check but I think java.jdbc parses the string and converts it to a legal JDBC string -- which I think was a mistake.

Yeah, it specifically treats the JDBC connection string as a URI (which it really isn't) and pulls apart the "user info" portion of the URI and turns it into separate properties that get passed to DriverManager/getConnection:

(if-let [user-info (.getUserInfo uri)]
       {:user (first (str/split user-info #":"))
        :password (second (str/split user-info #":"))})

java.net.URI is a very forgiving format but it really has nothing to do with what JDBC drivers accept as a connection string.

https://github.com/seancorfield/honeysql/issues/436

Sorry for taking so long with it, it was pretty late yesterday when we had the discussion

I understand if you don't want to support it by the way. It will make our migration more difficult, but I don't think you want to support all the weird decisions that were made with clojure.java.jdbc

I mean, it is a valid design choice to try to make the migration as smooth as possible, but there's a balance

I think it's an interesting possible enhancement. My thinking is that I would wrap the DriverManager/getConnection call in a try / catch and if the URL passed in looks like it might include credentials, I could unpack it and retry with user / password in the properties instead of the JDBC URL. And I would print a warning to stdout about it so folks know there's a migration needed.

I suspect that wouldn't help if you were using a connection pool tho' (e.g., HikariCP) -- which I would expect everyone to be using in production?

Looks like you opened the issue on the wrong repo -- it should be next.jdbc, not HoneySQL -- @UCQGNA673

Haha oh man

I'll move it

You have too many great libraries ;)

Thanks 🙂

https://github.com/seancorfield/next-jdbc/issues/229

Are you using connection pooling? (if not, why not?)

We are, although not for migrations. The get-connection is there just for testing

So... for production use, where you use HikariCP (or whatever), are you using a JDBC URL with embedded user:password@host?

We have a lot of services, some of them use a map for the db-spec, and a minority use JDBC URL. I know there's at least a few of these that parse the URL and turn it into a map. I'm not that familiar with all of them, I can take a closer look if it's relevant?

The services used to be spread out over multiple repos, we've now moved to a polylith monorepo and are trying to harmonise the way we do things, but that of course takes time 🙂

It kind of is. I'm trying to get a sense of how broad your migration problem is and how much effort it's worth expending on adding this support vs fixing your code 🙂

ahh right, makes sense

After all, you could probably do a global regex find'n'replace to turn URL strings from jdbc::port/etc to jdbc::port/etc?user=user&password=password 🙂

Or change how you build JDBC URLs from component parts. Depending on how/where you store user name and password.

IF next.jdbc does not support the "embedded" format for the JDBC URLs, we'll need to migrate a handful of services. We'd probably make them use a map for the db-spec, which is better anyway IMO compared to a string. This will probably be a day or three worth of work.

Like I say, this is about JDBC drivers themselves not supporting it -- next.jdbc currently just passes the URL to the driver.

Right, important distinction 👍

To "support" this, next.jdbc would have to add logic around that JDBC call to catch errors, figure out if the cause might be embedded credentials, if so pick apart the URL to extract user/password, and then rebuild the URL as a valid JDBC URL and retry.

I tried changing the URLs in our config btw, but we have a few places where we construct a java.net.URI out of the string, and call .getUserInfo on it. That doesn't work when you have the jdbc: prefix, or have the username and password as query parameters.

but that is more our problem

Which I'd then be committed to documenting and maintaining "forever". Which is why I'd prefer not to do this.

clojure.java.jdbc should never have supported the URI format, TBH.

(predated me taking it over I believe)

commented on the ticket too, but I went through a few of the services and it looks like all of them parse the string to a map that is then given to HikariCP

(defn- parse-db-uri
  "Returns the database configuration as a map."
  [db-uri]
  (let [uri (URI. db-uri)
        user-info (str/split (.getUserInfo uri) #":")]
    {:adapter (.getScheme uri)
     :server-name (.getHost uri)
     :port-number (.getPort uri)
     :database-name (subs (.getPath uri) 1)
     :username (first user-info)
     :password (second user-info)}))

OK. Er... ouch! 🙂

I'm sure there was a reason years ago why they decided to do it like that 😅