What is the correct/idiomatic way of handling possible null values from a database query?

Question

I'm handling a many-to-many relationship in Go. For this, I'm using the pgx PostgreSQL driver.

For the sake of keeping this question as simple as possible, let's assume a simple blog post that can have some tags:

CREATE TABLE IF NOT EXISTS tag (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tagName varchar(255) UNIQUE NOT NULL,
);

CREATE TABLE IF NOT EXISTS post (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title varchar(255) NOT NULL,
    description varchar(255),
);

CREATE TABLE IF NOT EXISTS post_tag (
    post_id bigint REFERENCES post (id) ON UPDATE CASCADE ON DELETE CASCADE,
    tag_id bigint REFERENCES tag (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT post_tag_pkey PRIMARY KEY (post_id, tag_id)
);

To retrieve posts and their tags, I use something like the following query (in a view for easy querying in Go):

SELECT p.id as post_id, p.title, p.description, t.id as tag_id, t.tagName as tag_name
    FROM post p 
    LEFT JOIN post_tag pt
    ON p.id = pt.post_id
    LEFT JOIN tag t 
    ON t.id = pt.tag_id;

This query will likely return some rows where tag_id and tag_name are null. The way I'm currently handling this, is the following (error handling removed for sake of simplicity):

func ReadPosts() ([]*model.Post, error) {
    var posts []*model.Post
    var postWithTags = make(map[uint64]*model.Post)

    statement := `SELECT *
                    FROM post_with_tag` // pgsql view with joins to get tags

    rows, _ := db.Query(
        context.Background(),
        statement,
    )

    for rows.Next() {
        var (
            post     model.Post
            tag      model.Tag
            tagID    pgtype.Numeric
            tagName  pgtype.Varchar
            tagValid bool
        )

        _ = rows.Scan(
            &post.ID,
            &post.Title,
            &post.Description,
            &tagID,
            &tagName,
        )

        if tagID.Status == pgtype.Present {
            tag.ID = tagID.Int.Uint64()
            tag.Name = tagName.String
            tagValid = true
        } else {
            tagValid = false
        }

        if _, ok := postWithTags[post.ID]; ok {
            if tagValid {
                postWithTags[post.ID].Tags = append(postWithTags[post.ID].Tags, &tag)
            }
        } else {
            post.Tags = []*model.Tag{}

            if tagValid {
                post.Tags = []*model.Tag{
                    &tag,
                }
            }

            postWithTags[post.ID] = &post
        }
    }

    for _, v := range postWithTags {
        posts = append(posts, v)
    }

    return posts, nil
}

As you can see, I'm using pgtype to handle the potential null values. I should mention that this solution works. However, I have two issues:

1. This solution seems quite clunky and messy; it's complex to read (at least to me). Is there a better, more idiomatic Go way of doing this?
2. When calling tagID.Int.Uint64() I always get returned 0 as the tag ID, which is not correct. Is there something I'm doing wrong here? (I'm using pgtype.Numeric because the tag ID in the database is a pgsql bigint).

Answer 1

If you're using a table view and you don't need to filter by NULL (e.g. WHERE col IS [NOT] NULL) then you may want to just use COALESCE in the view, that way you can save yourself some headache at the Go end. If you're dealing directly with tables, you can still use COALESCE in your SQL statement string that you've constructed in Go, however if you choose to do that you will not be able to use it together with SELECT *, instead you will have to list the columns explicitly.

If you don't like the COALESCE approach you can implement your own sql.Scanner that, instead of having a value field, would have a pointer field which then allows you to set your model fields by indirection on the same line as the one on which you're scanning the columns.

_ = rows.Scan(
    &post.ID,
    &post.Title,
    &post.Description,
    MyNullNumeric{&tag.ID},
    MyNullString{&tag.Name},
)

And the MyNullXxx could look like this:

type MyNullString struct {
    Ptr *string
}

func (ns MyNullString) Scan(src interface{}) error {
    switch v := src.(type) {
    case string:
        *ns.Ptr = v
    case []byte:
        *ns.Ptr = string(v)
    case nil:
        // nothing
    default:
        // maybe nothing or error, up to you
    }
    return nil
}