Feature request: configurable templates

[kofalt]

What do you want to change?

Hi - big fan of sqlc, thank you for your hard work!

There are a few reasonable use cases where you might want to extend each generated query function. I'll demonstrate two here:

1. Instrumentation

This example adds SQL transaction count, latency, and errors to Prometheus.
First, setup in non-generated file in same package:

var (
	Tx = promauto.NewSummary(prometheus.SummaryOpts{
		Namespace: metricsNamespace,
		Name:      "tx",
		Help:      "SQL Tx count & duration.",
	})
	TxErrors = promauto.NewCounter(prometheus.CounterOpts{
		Namespace: metricsNamespace,
		Name:      "txErrors",
		Help:      "SQL Tx errors.",
	})
)

Add to generated code, for each query func:

start := time.Now()

// query...

Tx.Observe(float64(time.Since(start).Milliseconds()))
if err != nil {
	TxErrors.Inc()
}

2. Vendor-specific libraries

CockroachDB expects users to implement client-side Tx retries.
They provide a library to accomplish this, which looks like the following for pgx/v5:

// Notice their preferred function signature
// It would be ergonomic to have general-purpose handling of pgx.Tx
func insertRows(ctx context.Context, tx pgx.Tx, accts [4]uuid.UUID) error {
	// query...
}

err = crdbpgx.ExecuteTx(context.Background(), conn, pgx.TxOptions{}, func(tx pgx.Tx) error {
	return insertRows(context.Background(), tx, accounts)
})

You could handle this from each caller using sqlc's built-in WithTx, but it might be nicer to modify the signatures and call crdbpgx.ExecuteTx or a generic helper function directly.

Request

Now, some of this could be handled by a utility like GoWrap, but adding a second layer of code generation seems a lot for small things like this. Easier to just modify the code templates. However, the gen code in internal/codegen/golang/gen.go does not support custom template paths, nor does the config file.

Today, you need to fork sqlc to make this happen. I was impressed with the process & structs it sets up, it was very easy. Here's a partial example of the first use case, that adds startTx & endTx functions you can fill out in your own project:

--- a/internal/codegen/golang/templates/pgx/queryCode.tmpl
+++ b/internal/codegen/golang/templates/pgx/queryCode.tmpl
@@ -28,9 +28,11 @@ type {{.Ret.Type}} struct { {{- range .Ret.Struct.Fields}}
 {{end -}}
 {{- if $.EmitMethodsWithDBArgument -}}
 func (q *Queries) {{.MethodName}}(ctx context.Context, db DBTX, {{.Arg.Pair}}) ({{.Ret.DefineType}}, error) {
+       start := q.startTx()
        row := db.QueryRow(ctx, {{.ConstantName}}, {{.Arg.Params}})
 {{- else -}}
 func (q *Queries) {{.MethodName}}(ctx context.Context, {{.Arg.Pair}}) ({{.Ret.DefineType}}, error) {
+       start := q.startTx()
        row := q.db.QueryRow(ctx, {{.ConstantName}}, {{.Arg.Params}})
 {{- end}}
        {{- if or (ne .Arg.Pair .Ret.Pair) (ne .Arg.DefineType .Ret.DefineType) }}
@@ -42,6 +44,7 @@ func (q *Queries) {{.MethodName}}(ctx context.Context, {{.Arg.Pair}}) ({{.Ret.De
                err = fmt.Errorf("query {{.MethodName}}: %w", err)
        }
        {{- end}}
+       q.endTx(start, err)
        return {{.Ret.ReturnName}}, err
 }
 {{end}}

It would be great if the tool supported this without forking. Suggested config addition:

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        sql_package: "pgx/v5"
        package: "tutorial"
        out: "tutorial"

        # New config key: support per-lang custom templates folder.
        # Uses any of the following that are present:
        #    batchCode.tmpl
        #    copyfromCopy.tmpl
        #    dbCode.tmpl
        #    interfaceCode.tmpl
        #    queryCode.tmpl
        templates: "go-templates"

Thanks!

What database engines need to be changed?

None - I do not believe this requires changes to the database engines.

What programming language backends need to be changed?

All, minor - thread a config value into the template execute calls.

[kofalt]

This could be considered a superset of #1612, #1364, as it would let users self-service these sort of requests.