That doesn't use connection pooling which I would consider "best practice" for production code.

I'm mostly interested in seeing examples of exception handling and abstraction levels. I've done some plain searches on github and the approaches vary a great deal.

Yeah, I would expect DB access patterns to vary wildly from app to app. Different people have very different approaches to abstraction layers over DB access and also very different approaches to exception handling.

If you have specific questions about specific situations, folks might be able to at least tell you what they might do in those cases -- but I suspect you'll get "it depends" a lot of the time.

@U04V70XH6 do you know of any repos that show off what you personally consider best practice? I've been looking at the tests in next.jdbc itself, but it doesn't seem to do much with regards to the two aspects I mentioned. In the case of exception handling, I should mention I'm using Postgres. We briefly discussed before that the underlying class doesn't offer much data about the exception. I'm leaning towards just doing some basic pattern matching against the exception message (which is also an approach I've seen others use).

I think the main issue you'll have is that very few apps are on GH and that's what you'd need to look at to see the sorts of patterns you're asking about.

As for the latter, all I'll say is that I've never really needed to pattern match against exception messages. What are you finding that you need to do that for, beyond catching the primary exception types in SQL?

I'd like for my functions to be able to log specific errors and for their call sites to be able to understand exactly why a failure occurred beyond just PSQLException. The attached error message does include full detail, but I think it would help with maintainability if I described the errors in terms of the problem domain at this part of the application.

https://github.com/jepsen-io/jepsen/blob/main/cockroachdb/src/jepsen/cockroach/client.clj#L199-L202 something like this kind of fits with what I was thinking.

I think, in general, it's better to stick to the standard SQL exception hierarchy https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/package-tree.html and not us DB-specific exception types. That hierarchy pretty clearly divides transient from non-transient exceptions, for example, which is what should drive strategy here (e.g., can you retry later and expect success or is this a failure).

My experience with exceptions is generally that either you can handle the exception "locally" (within a very small call tree) or you can't handle it at all (so it's going to get caught by something more generic near the top of the call tree and the whole request abandoned).

It's rare that code further up the call chain is realistically going to be able to do a bunch of specific things based on increased detail in an exception. That's been my experience with JVM-based languages for decades. Log an exception will full details, sure, but either handle it close to where it occurs or "not at all".

How do you get from a PSQLException to a part of the standard SQL exception hierarchy? e.g. from something like this:

[{:type org.postgresql.util.PSQLException                                                                              
  :message "ERROR: duplicate key value violates unique constraint \"x_pkey\"\n  Detail: Key (x)=(...) already exists." 
  :at [org.postgresql.core.v3.QueryExecutorImpl receiveErrorResponse "QueryExecutorImpl.java" 2676]}]                  

Does PostgreSQL simply ignore the standard Java SQL stuff? Sheesh, that's stupid. I just looked at the code and it seems like they use PSQLException everywhere and it just extends java.sql.SQLException and so you're completely hamstrung... does (ex-cause e) give you anything more useful when you catch a PSQLException?

(the more I learn about PG, the more I'm glad I use MySQL instead!)

Apparently this is a known limitation of the PG JDBC for at least eight years: https://stackoverflow.com/questions/26383624/postgres-exceptions-and-java

(it seems PG hasn't taken advantage of the JDBC 4 exception hierarchy like other vendors have)

Seems like PG expect you to work with the SQL state for the most part https://www.postgresql.org/docs/current/errcodes-appendix.html (with the caveats from that SO answer that e.getSQLState() returns null for some operations and you have to go down the chain of exceptions with e.getNextException() to get something more useful.

So... back to your OP Q about "best practices" with next.jdbc: the answer will depend on which DB you use 🙂 but it def. sounds like you should be working from the SQL state error codes instead of the server messages to avoid your code breaking because the PG team decide to reword an error message 🙂

@U04V70XH6 Thanks for looking into it! It looks like there was some effort to address this issue in PgJDBC, but I'm not sure if it has been abandoned: https://github.com/pgjdbc/pgjdbc/issues/963 https://github.com/pgjdbc/pgjdbc/pull/1904 I will try the e.getSQLState() approach you mention above. There are some examples of people doing that on GitHub.

Do you think this merits a blurb in the next.jdbc docs? I searched for something along the lines of exception handling before and there isn't really anything. I suppose that this vendor-specific caveat may trip some people up at first and a brief explanation of things might help.

I think there might be a light at the end of the tunnel. It seems the PgJDBC maintainers are interested in deprecating the PSQLException (in stages to smooth the deprecation) and exposing the Java SQL exception hierarchy as the primary way to get error information. Please see https://github.com/pgjdbc/pgjdbc/pull/1904.

I don't use PG so I'm not really interested in how they manage their project -- they are a pretty opinionated bunch, as are the core DB maintainers. The lack of sensible exceptions in PG is probably worth a mention in the Tips & Tricks section for PG. Please create me a GH issue so I don't forget. Thanks.

Here's the issue for reference: https://github.com/seancorfield/next-jdbc/issues/226