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

Question

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?

Are you planning to use pgx batches or bulk insert using CopyFrom? — serge-v
– serge-v, Commented Jan 26, 2022 at 12:45
Handling database specific errors requires different strategies depending on the database. How do you think you could somehow "combine" them? — Volker
– Volker, Commented Jan 26, 2022 at 13:35
@Volker My original intent was not to support every SQL standard database but to leave the door open to switching to another driver for the same database tech later, e.g. Postgres. In that case, the errors from Postgres are still the same but the driver's method of exposing those errors may differ. Maybe that is naive though, since pgx is effectively the standard for Postgres. — Jared Petersen
– Jared Petersen, Commented Jan 26, 2022 at 17:49
@serge-v No, not at this time. If I needed driver-specific features, I think the answer would be to just use the driver directly instead of database/sql. — Jared Petersen
– Jared Petersen, Commented Jan 26, 2022 at 17:52
Some advice: pick a DB and implement to that DB. Go's database/sql does an excellent job of not playing favorites, leaving you with the choice. But once you make a choice - you will find it's impossible to make things "generic" to maybe give yourself the option to change DB's later. And even if you do, the ROI on that investment is hard to justify. So make a DB choice and stick with it. Then you can make conscious choices to optimize your queries based on that DB's strengths. — colm.anseo
– colm.anseo, Commented Feb 2, 2022 at 19:19

serge-v · Accepted Answer · 2022-01-26 15:31:39Z

1

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.

edited Jan 26, 2022 at 15:31

answered Jan 26, 2022 at 13:09

serge-v

7884 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lukasz032 Over a year ago

This won't work in the MySQL driver, as its error type doesn't define SQLState() method, instead opting for a SQLState field of type [5]byte (the definition)

Lars Christian Jensen · Accepted Answer · 2022-01-26 08:28:34Z

0

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.

answered Jan 26, 2022 at 8:28

Lars Christian Jensen

1,6821 gold badge16 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related