PostgreSQL Error Codes

[addisonwebb]

Over the past few days I have been trying to improve error handling in my server application and ran into a pain point. QueryError provides some helpful data for debugging purposes and gives you enough information to take general action like returning a 500 status code when there is a database error, but it doesn't provide detailed information needed to take specific actions to help the client/end user fix what went wrong.

For example:
Let's say part of my schema looks something similar to this:

CREATE TABLE place (
    id uuid NOT NULL PRIMARY KEY,
    name text UNIQUE,
    description text
);

At some point I might allow users of my app to create new places. What happens when a user enters in a name that already exists? Currently, I would get back a QueryError.databaseError with a string describing the unique constraint violation. This data isn't very useful outside of debugging your app. I would like to be able to access the PostgreSQL error code and the other error fields in something like a dictionary that is provided with the error. (or some other solution)

Then, I would have more information about what the .databaseError was and could present UI to better help the end user understand what went wrong.

Maybe an alert like this:

A place with the name XXXX is already in the database. Please use a different name.

If anyone has any thoughts or ideas I would love to hear them. I am open to discussing potential solutions and maybe even pairing up to tackle this problem. (Pair programming anyone? 😜)