How do you handle database errors in Go without getting coupled to the SQL driver?

A common way to interact with a SQL database in Go is to use the built in database/sql interface. Many different third-party packages implement this interface in a way that is specific to some particular database without exposing that work to you as a consumer, e.g. Postgres driver, MySQL driver, etc.

However, database/sql doesn't provide any specific error types, leaving it up to the driver instead. This presents a problem: any error handling you do for these specific errors beyond nil checks now works off of the assumption of a particular driver. If you decide to change drivers later, all of the error handling code must be modified. If you want to support multiple drivers, you need to write additional checks for that driver too.

This seemingly undermines the primary benefit of using interfaces: portability with an agreed-upon contract.

Here's an example to illustrate this problem using the jackc/pgx/v4/stdlib driver and suite of helper packages:

import (
	"database/sql"
	"errors"
	"github.com/jackc/pgconn"
	"github.com/jackc/pgerrcode"
)

// Omitted code for the sake of simplification, err comes from database/sql

if err != nil {
	var pgerr *pgconn.PgError
	if errors.As(err, &pgerr) {
		if pgerrcode.IsIntegrityConstraintViolation(pgerr.SQLState()) {
			return nil, errors.New("related entity does not exist")
		}
	}
	// If we wanted to support another database driver, we'd have to include that here

	return nil, errors.New("failed to insert the thing")
}

If I already have to put driver-specific code into my package, why bother accepting the database/sql interface at all? I could instead require the specific driver, which is arguably safer since it prevents the consumer from trying to use some other unsupported driver that we don't have error handling for.

Is there better way to handle specific database/sql errors?

You don't need driver specific code to get SQLState. Example:

func getSQLState(err error) {
        type checker interface {
                SQLState() string
        }
        pe := err.(checker)
        log.Println("SQLState:", pe.SQLState())
}

But SQLState is a database specific anyway. If you switch to another database/driver in the future then you need to change all error codes manually. Compiler would not help to detect it.

> Package sql provides a generic interface around SQL (or SQL-like) databases.

There is a compromise between providing the minimal common set of features, and providing features that would not be available for all implementations. The sql package has prioritized the former, while maybe you prefer more of the latter.

You could argue that every possible implementation should be able to provide a specific error for your example. Maybe that's the case. Maybe not. I don't know.

Either way it is possible for you to wrap pgerrcode.IsIntegrityConstraintViolation inside a function that does this check for every driver that you support. Then it is up to you to decide how to deal with drivers that lacks support.