auto-marshals

main.go

package main

import (
	"database/sql"
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

// JSONB satisfies sql.Scanner (read) + driver.Valuer (write) for one column.
type JSONB map[string]any

func (j *JSONB) Scan(src any) error { // called per column
	switch v := src.(type) {
	case []byte:
		return json.Unmarshal(v, j)
	case string:
		return json.Unmarshal([]byte(v), j)
	default:
		return fmt.Errorf("unsupported type %T", src)
	}
}

func (j JSONB) Value() (driver.Value, error) { // used on INSERT/UPDATE
	return json.Marshal(j)
}

func main() {
	db, err := sql.Open("postgres", "postgres://user:pw@localhost/db?sslmode=disable")
	if err != nil { log.Fatal(err) }
	defer db.Close()

	// Example query returning id (int) and metadata (jsonb)
	rows, err := db.Query(`SELECT id, metadata FROM items WHERE id = $1`, 42)
	if err != nil { log.Fatal(err) }
	defer rows.Close()

	for rows.Next() {
		var (
			id   int
			meta JSONB
		)
		if err := rows.Scan(&id, &meta); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("id=%d meta=%v\n", id, meta)
	}

	// Insert/update: db.Exec(`INSERT INTO items (id, metadata) VALUES ($1,$2)`, 99, JSONB{"k":"v"})
}