Take a look at github.com/wroge/esquel.

Scan

This package offers a convenient and flexible way to scan SQL rows into any type, leveraging the power of generics.

Features

• Efficient and Reusable: Avoid repetitive code and define the column-mapping in one place.
• Auto Closing: No need to worry about resource leaks.
• No Reflection: Faster than reflection based mappers.
• Robust Error Handling: Best practices for managing errors.

Usage

import "github.com/wroge/scan"

type Author struct {
	ID   int64
	Name string
}

type Post struct {
	ID      int64
	Title   string
	Authors []Author
}

// Define mapping of database columns to struct fields.
var columns = scan.Columns[Post]{
	// Map the 'id' column to the 'ID' field in the 'Post' struct.
	// Uses the 'scan.Any' function for direct assignment without additional processing.
	"id": scan.Any(func(p *Post, id int64) { p.ID = id }),

	// Map the 'title' column to the 'Title' field in the 'Post' struct.
	// The 'scan.Null' function allows handling of nullable database columns.
	// If the 'title' column is null, 'default title' is used as the value.
	"title": scan.Null("default title", func(p *Post, title string) { p.Title = title }),

	// Map the 'authors' column, expected to be in JSON format, to the 'Authors' field in the 'Post' struct.
	// The 'scan.JSON' function automatically handles unmarshalling of the JSON data into the 'Author' struct slice.
	"authors": scan.JSON(func(p *Post, authors []Author) { p.Authors = authors }),

	// Or you could create a custom scanner with this function.
	// "column": scan.Func[Post, V](func(p *Post, value V) error {
	// 	return nil
	// }),
}

rows, err := db.Query("SELECT ...")
// handle error

Scanning all rows

posts, err := scan.All(rows, columns)
// handle error

Scanning the first row

post, err := scan.First(rows, columns)
if err != nil {
	if errors.Is(err, scan.ErrNoRows) {
		// handle no rows
	}

	// handle other error
}

Scanning exactly one row

post, err := scan.One(rows, columns)
if err != nil {
	if errors.Is(err, scan.ErrTooManyRows) {
		// handle too many rows
		// post is valid
	}

	if errors.Is(err, scan.ErrNoRows) {
		// handle no rows
	}

	// handle other error
}

Scanning a limited number of rows

posts, err := scan.Limit(10, rows, columns)
if err != nil {
	if errors.Is(err, scan.ErrTooManyRows) {
		// ignore if result set has more than 10 rows
		// len(posts) == 10
	}

	// handle other error
}

Using the Iterator directly

iter, err := scan.Iter(rows, columns)
// handle error

defer iter.Close()

for iter.Next() {
	var post Post

	err = iter.Scan(&post)
	// handle error

	// Or use the Value method:
	post, err := iter.Value()
	// handle error
}