Detect sql.ErrNoRows and return nil instead

[xeoncross]

I would rather the database implementation didn't leak out into my domain or service layer. I currently have to check for sql.ErrNoRows when .Scan()ing a single record and would rather the generated query.sql.go model just return a nil pointer instead.

Is there a way we could add an option to capture sql.ErrNoRows and return nil instead?

Currently generated code:

func (q *Queries) FetchKeywordBySlug(ctx context.Context, slug string) (*Keyword, error) {
	row := q.db.QueryRowContext(ctx, fetchKeywordBySlug, slug)
	var i Keyword
	err := row.Scan(
		&i.ID,
		&i.Keyword,
		&i.Slug,
	)
	return &i, err
}

New code:

func (q *Queries) FetchKeywordBySlug(ctx context.Context, slug string) (*Keyword, error) {
	row := q.db.QueryRowContext(ctx, fetchKeywordBySlug, slug)
	var i Keyword
	err := row.Scan(
		&i.ID,
		&i.Keyword,
		&i.Slug,
	)
	if err != nil {
		if !errors.Is(err, sql.ErrNoRows) {
			return nil, err
		}
		return nil, nil // Does not require SQL knowledge by the caller
	}
	return &i, nil
}

[xeoncross]

Is there a way that I can override the template sqlc uses to generate this code?

[hariprasadrajaram]

The hard way is you have to fork and take your own build of sqlc by editing the go template files

an easier way is, write another program that can read the generated code

1. Change the file permission of generated code
2. Read the file, replace the string, save the file
3. Again, change the file permission.

[preslavrachev]

Is this in the plans for the foreseeable future? I will be extremely happy if I won't need to do these extra SQL-specific checks in my business code. However, I also understand that having this by default will break the compatibility with everyone's code at the moment. So, we shall best have it turned off by default, and leave it to people to gradually adapt to it.

On a more general topic, it would be great if sqlc could read templates from known locations, so end users could copy the original templates, and modify them according to their needs.

[mvitaly]

Does it make sense to create a toggle to enable this functionality while keeping it disabled by default (until the next major version maybe)?

[kyleconroy]

With generics landing in Go 1.18, we can write a single wrapper function to provide this functionality.

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
)

type Keyword struct {
	Foo string
}

func FetchKeywordBySlug(ctx context.Context, slug string) (*Keyword, error) {
	switch slug {
	case "error":
		return nil, fmt.Errorf("unknown error")
	case "norows":
		return nil, sql.ErrNoRows
	default:
		return &Keyword{}, nil
	}

}

func Wrap[T any](row *T, err error) (*T, error) {
	if err != nil {
		if !errors.Is(err, sql.ErrNoRows) {
			return nil, err
		}
		return nil, nil
	}
	return row, nil
}

func main() {
	for _, test := range []string{"regular", "norows", "error"} {
		row, err := Wrap(FetchKeywordBySlug(context.Background(), test))
		fmt.Println(test, row, err)
	}
}

I'd like to explore using something like this before adding the configuration option.

[preslavrachev]

@kyleconroy is this feature still planned? it will be extremely helpful to have it. The code you wrote above seems reasonable. Moreover, we are approaching Go 1.20 in February and generics are not going away or getting any major changes. I think it makes sense to explore adding this option.

[kyleconroy]

@preslavrachev The code snippet I posted above does not require any changes to sqlc. You can use it starting in Go 1.18.

As for adding this feature, I'm open to it, but it's low on the list of priorities.