member-console/scripts/sqlc-unused/main.go

// SPDX-License-Identifier: AGPL-3.0-only OR LicenseRef-Commercial
// SPDX-FileCopyrightText: 2025-2026 Christian Galo

// Command sqlc-unused reports the sqlc queries no production code calls.
//
// A sqlc package is a package whose generated header names sqlc; its queries
// are the "-- name:" lines of the queries/*.sql files beside the generated
// code. A query counts as used by a selector that resolves to its method on
// the package's Queries type, or to a method of an interface that *Queries
// implements (the generated Querier, a narrow local interface, a test fake).
// Method values count. A use in a _test.go file is a test use, and so is a
// use in a test-support package, one covered by a ".*" entry of
// scripts/deadcode-testonly.txt, the list both checks read.
//
// The output has one UNUSED line per query with no use at all, one TEST-ONLY
// line per query only tests call, and a TOTAL line. The allowlist
// (scripts/sqlc-unused/allow.txt) holds the unused queries that stay, one
// "<package path>.<Name>  # reason (owner)" per line, the package path being
// relative to the module path. The exit status is 1 when an unused query is
// not listed or a listed query is used, and 2 when a package fails to load.
// A test-only query never fails the check.
//
// Run it from the repository root: go run ./scripts/sqlc-unused. The
// packages under status/ (the local notebook) are dropped before anything
// else, so one that does not compile changes nothing.
package main

import (
	"bufio"
	"errors"
	"fmt"
	"go/ast"
	"go/types"
	"os"
	"path/filepath"
	"regexp"
	"sort"
	"strings"

	"golang.org/x/tools/go/packages"
)

const (
	allowFile   = "scripts/sqlc-unused/allow.txt"
	supportFile = "scripts/deadcode-testonly.txt"
)

var nameRe = regexp.MustCompile(`^--\s*name:\s*(\w+)\s+:\w+`)

// query is one "-- name:" block of a sqlc package, with where its callers are.
type query struct {
	key       string // module-relative package path and name
	pkgPath   string
	name      string
	file      string
	line      int
	prod, tst []string
}

func (q *query) used() bool     { return len(q.prod) > 0 }
func (q *query) testOnly() bool { return len(q.prod) == 0 && len(q.tst) > 0 }
func (q *query) unused() bool   { return len(q.prod) == 0 && len(q.tst) == 0 }

func main() {
	os.Exit(run())
}

func run() int {
	root, err := os.Getwd()
	if err != nil {
		fmt.Fprintln(os.Stderr, "sqlc-unused:", err)
		return 2
	}
	support, err := readSupport(supportFile)
	if err != nil {
		fmt.Fprintln(os.Stderr, "sqlc-unused:", err)
		return 2
	}
	queries, err := scan(root, support)
	if err != nil {
		fmt.Fprintln(os.Stderr, "sqlc-unused:", err)
		return 2
	}
	report(queries)

	allow, err := readAllow(allowFile)
	if err != nil {
		fmt.Fprintln(os.Stderr, "sqlc-unused:", err)
		return 2
	}
	problems := check(queries, allow)
	for _, p := range problems {
		fmt.Fprintln(os.Stderr, p)
	}
	if len(problems) > 0 {
		return 1
	}
	return 0
}

// scan finds the queries of the module rooted at root and where each is
// called. support holds the module-relative paths of the test-support
// packages.
func scan(root string, support map[string]bool) (map[string]*query, error) {
	pkgs, err := load(root)
	if err != nil {
		return nil, err
	}
	sqlc := sqlcPackages(pkgs)
	queries, err := readQueries(root, sqlc)
	if err != nil {
		return nil, err
	}
	s := &scanner{root: root, sqlc: sqlc, queries: queries, support: support, seen: map[string]bool{}}
	for _, p := range pkgs {
		s.scanPackage(p)
	}
	return queries, nil
}

// load reads every package of the module at dir with its tests, drops the
// ones under status/, and fails on a package error in what remains.
func load(dir string) ([]*packages.Package, error) {
	cfg := &packages.Config{
		Dir:   dir,
		Tests: true,
		Mode: packages.NeedName | packages.NeedFiles | packages.NeedSyntax |
			packages.NeedTypes | packages.NeedTypesInfo | packages.NeedImports |
			packages.NeedDeps | packages.NeedModule,
	}
	all, err := packages.Load(cfg, "./...")
	if err != nil {
		return nil, err
	}
	var pkgs []*packages.Package
	for _, p := range all {
		if !strings.Contains(p.PkgPath, "/status/") {
			pkgs = append(pkgs, p)
		}
	}
	if packages.PrintErrors(pkgs) > 0 {
		return nil, errors.New("packages failed to load")
	}
	return pkgs, nil
}

// sqlcPackages returns the non-test variant of every package that holds a
// file generated by sqlc, by import path.
func sqlcPackages(pkgs []*packages.Package) map[string]*packages.Package {
	found := map[string]*packages.Package{}
	for _, p := range pkgs {
		if p.ID != p.PkgPath || strings.HasSuffix(p.PkgPath, ".test") {
			continue
		}
		for _, f := range p.Syntax {
			if generatedBySqlc(f) {
				found[p.PkgPath] = p
				break
			}
		}
	}
	return found
}

// generatedBySqlc reports whether a comment group before the package clause
// is a "Code generated" header that names sqlc. SPDX lines precede it, so
// every group before the clause is read.
func generatedBySqlc(f *ast.File) bool {
	if !ast.IsGenerated(f) {
		return false
	}
	for _, cg := range f.Comments {
		if cg.End() > f.Package {
			break
		}
		if strings.Contains(cg.Text(), "Code generated by sqlc") {
			return true
		}
	}
	return false
}

// modulePath is a package's path relative to its module path.
func modulePath(p *packages.Package) string {
	if p.Module == nil {
		return p.PkgPath
	}
	return strings.TrimPrefix(strings.TrimPrefix(p.PkgPath, p.Module.Path), "/")
}

// readQueries reads the "-- name:" lines of each sqlc package's queries/*.sql.
func readQueries(root string, sqlc map[string]*packages.Package) (map[string]*query, error) {
	queries := map[string]*query{}
	for path, p := range sqlc {
		if len(p.GoFiles) == 0 {
			continue
		}
		files, err := filepath.Glob(filepath.Join(filepath.Dir(p.GoFiles[0]), "queries", "*.sql"))
		if err != nil {
			return nil, err
		}
		for _, fn := range files {
			found, err := namesIn(root, fn)
			if err != nil {
				return nil, err
			}
			for _, q := range found {
				q.pkgPath = path
				q.key = modulePath(p) + "." + q.name
				queries[path+"."+q.name] = q
			}
		}
	}
	return queries, nil
}

// namesIn lists the queries one .sql file declares.
func namesIn(root, fn string) ([]*query, error) {
	fh, err := os.Open(fn)
	if err != nil {
		return nil, err
	}
	defer func() { _ = fh.Close() }() // opened read-only; closing loses no data
	rel, err := filepath.Rel(root, fn)
	if err != nil {
		return nil, err
	}
	var out []*query
	sc := bufio.NewScanner(fh)
	for line := 1; sc.Scan(); line++ {
		if m := nameRe.FindStringSubmatch(sc.Text()); m != nil {
			out = append(out, &query{name: m[1], file: rel, line: line})
		}
	}
	return out, sc.Err()
}

// scanner counts the uses of the queries across every hand-written file.
type scanner struct {
	root    string
	sqlc    map[string]*packages.Package
	queries map[string]*query
	support map[string]bool // module-relative paths of the test-support packages
	seen    map[string]bool // file names already walked; a non-test file is in two variants
}

func (s *scanner) scanPackage(p *packages.Package) {
	for _, f := range p.Syntax {
		fn := p.Fset.Position(f.Pos()).Filename
		rel, err := filepath.Rel(s.root, fn)
		if err != nil || s.seen[fn] || ast.IsGenerated(f) || strings.HasPrefix(rel, "status"+string(filepath.Separator)) {
			continue
		}
		s.seen[fn] = true
		test := strings.HasSuffix(fn, "_test.go") || s.support[modulePath(p)]
		ast.Inspect(f, func(n ast.Node) bool {
			if sel, ok := n.(*ast.SelectorExpr); ok {
				s.selector(p, sel, test)
			}
			return true
		})
	}
}

// selector records a use when sel names a method of a query's package.
func (s *scanner) selector(p *packages.Package, sel *ast.SelectorExpr, test bool) {
	fn, _ := p.TypesInfo.Uses[sel.Sel].(*types.Func)
	if fn == nil {
		return
	}
	recv := fn.Type().(*types.Signature).Recv()
	if recv == nil {
		return
	}
	t := recv.Type()
	if ptr, ok := t.(*types.Pointer); ok {
		t = ptr.Elem()
	}
	pos := p.Fset.Position(sel.Pos())
	rel, err := filepath.Rel(s.root, pos.Filename)
	if err != nil {
		rel = pos.Filename
	}
	where := fmt.Sprintf("%s:%d", rel, pos.Line)

	if named, ok := t.(*types.Named); ok && named.Obj().Name() == "Queries" && named.Obj().Pkg() != nil {
		s.count(named.Obj().Pkg().Path(), fn.Name(), where, test)
		return
	}
	if iface, ok := t.Underlying().(*types.Interface); ok {
		s.viaInterface(p, iface, fn.Name(), where, test)
	}
}

// viaInterface counts a call through an interface for each sqlc package whose
// *Queries implements it and has a query of that name. The package's type is
// the one the calling package imported, because each test variant is
// type-checked separately.
func (s *scanner) viaInterface(p *packages.Package, iface *types.Interface, name, where string, test bool) {
	for path := range s.sqlc {
		if s.queries[path+"."+name] == nil {
			continue
		}
		imported := importedPackage(p, path, map[*packages.Package]bool{})
		if imported == nil {
			continue
		}
		obj := imported.Types.Scope().Lookup("Queries")
		if obj != nil && types.Implements(types.NewPointer(obj.Type()), iface) {
			s.count(path, name, where, test)
		}
	}
}

func (s *scanner) count(pkgPath, name, where string, test bool) {
	q := s.queries[pkgPath+"."+name]
	if q == nil {
		return
	}
	if test {
		q.tst = append(q.tst, where)
	} else {
		q.prod = append(q.prod, where)
	}
}

// importedPackage finds the package with the given path in p's import graph.
func importedPackage(p *packages.Package, path string, seen map[*packages.Package]bool) *packages.Package {
	if p.PkgPath == path && p.Types != nil {
		return p
	}
	if seen[p] {
		return nil
	}
	seen[p] = true
	for _, imp := range p.Imports {
		if found := importedPackage(imp, path, seen); found != nil {
			return found
		}
	}
	return nil
}

// report prints the UNUSED and TEST-ONLY lines, sorted, then the TOTAL line.
func report(queries map[string]*query) {
	var lines []string
	var used, testOnly, unused int
	for _, q := range queries {
		switch {
		case q.used():
			used++
		case q.testOnly():
			testOnly++
			lines = append(lines, fmt.Sprintf("TEST-ONLY %s:%d %s (%d test refs, e.g. %s)", q.file, q.line, q.name, len(q.tst), q.tst[0]))
		default:
			unused++
			lines = append(lines, fmt.Sprintf("UNUSED %s:%d %s", q.file, q.line, q.name))
		}
	}
	sort.Strings(lines)
	for _, l := range lines {
		fmt.Println(l)
	}
	fmt.Printf("TOTAL queries=%d prod=%d test-only=%d unused=%d\n", len(queries), used, testOnly, unused)
}

// readAllow reads the allowlist: one "<package path>.<Name>" per line, with a
// trailing "# reason (owner)"; blank lines and comment lines are ignored.
func readAllow(path string) (map[string]bool, error) {
	data, err := os.ReadFile(path)
	if err != nil {
		return nil, err
	}
	allow := map[string]bool{}
	for _, line := range strings.Split(string(data), "\n") {
		if i := strings.Index(line, "#"); i >= 0 {
			line = line[:i]
		}
		if key := strings.TrimSpace(line); key != "" {
			allow[key] = true
		}
	}
	return allow, nil
}

// readSupport reads the test-support packages from the "<package path>.*"
// entries of the list scripts/deadcode-testonly.txt, which the dead-code
// check reads too. Any other line is not a package and is ignored.
func readSupport(path string) (map[string]bool, error) {
	data, err := os.ReadFile(path)
	if err != nil {
		return nil, err
	}
	support := map[string]bool{}
	for _, line := range strings.Split(string(data), "\n") {
		if i := strings.Index(line, "#"); i >= 0 {
			line = line[:i]
		}
		if pkg, ok := strings.CutSuffix(strings.TrimSpace(line), ".*"); ok && pkg != "" {
			support[pkg] = true
		}
	}
	return support, nil
}

// check returns what fails the allowlist: an unused query that is not listed,
// and a listed query that is used or does not exist. A test-only query never
// fails.
func check(queries map[string]*query, allow map[string]bool) []string {
	var problems []string
	known := map[string]bool{}
	for _, q := range queries {
		known[q.key] = true
		switch {
		case q.unused() && !allow[q.key]:
			problems = append(problems, fmt.Sprintf("sqlc-unused: %s is unused and not in %s", q.key, allowFile))
		case !q.unused() && allow[q.key]:
			problems = append(problems, fmt.Sprintf("sqlc-unused: %s is listed in %s but a caller uses it (stale entry)", q.key, allowFile))
		}
	}
	for key := range allow {
		if !known[key] {
			problems = append(problems, fmt.Sprintf("sqlc-unused: %s is listed in %s but no such query exists (stale entry)", key, allowFile))
		}
	}
	sort.Strings(problems)
	return problems
}