Generic Interceptor/Middleware for caching, retry, circuit breaker, etc.

[StevenACoffman]

What do you want to change?

See prior #1442 #930 #1076 #2207

ngrok/sqlmw provides database/sql interface interceptors (middleware), but does not work with the pgx / pgxpool interface. In addition, sqlc likes to remove comments that are middleware directives without providing a configuration option to change this behavior (comment passthrough).

My specific use case is that I would like a sqlc plugin like prashanthpai/sqlcache which provides a nice declarative mechanism for caching individual queries to Redis to improve performance. Other use cases like circuit breakers, retry mechanisms, etc. are also facilitated by having generic interceptor middleware in sqlc.

Describe the solution you'd like
A plugin or package like ngrok/sqlmw specific to sqlc but usable for pgx / pgxpool to facilitate a generic caching layer like prashanthpai/sqlcache without resorting to the database/sql interface.

BTW, prashanthpai/sqlcache relies on query "annotations", like so:

-- name: GetUsers :many
-- @cache-ttl 30
-- @cache-max-rows 10
SELECT * FROM users;

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[bldrdash]

@StevenACoffman I came here looking for the same thing.

You can get these annotations by modifying the sqlc-gen-go template in internal/templates/./queryCode.tmpl.

That said, you didn't want to use the database/sql interface and I was unable to get prashanthpai/sqlcache to work using pgx/pgxpool directly. But that looks like a limitation of prashanthpai/sqlcache

I was able to get this to work by modifying the template in internal/templates/stdlib/queryCode.tmpl like so:

...
const {{.ConstantName}} = {{$.Q}}-- name: {{.MethodName}} {{.Cmd}}
{{range .Comments}}--{{.}}
{{end -}}
{{escape .SQL}}
{{$.Q}}
...

You can then write your query as:

-- name: ProductByZip :many
-- @cache-ttl 30
-- @cache-max-rows 100
select distinct p.name as product, v.name as vendor from product p
join vendors v on p.vendor = v.id
join vendorsa va on v.id = va.vendor
where va.zip = $1;

Hope this helps.

[StevenACoffman]

@bldrdash Nice!

I filed an issue with jackc/pgx#1935 along the same lines, and the maintainer raised a good point.

The declarative nature of prashanthpai/sqlcache is nice, but the way sqlcache (and sqlmw) is implemented in the SQL driver, it requires a re-parse before deciding to just use the cached version. This makes the performance worse than it needs to be.

If the sqlc generated template Go code decided to attempt to use the cached version, it would not require re-parsing the SQL, so it would perform faster.

[StevenACoffman]

For folks who find there way here, if you are using pgx AND sqlc, you might be interested in looking at https://github.com/amirsalarsafaei/sqlc-pgx-monitoring which is aware of the relevant OTEL attribute.Keys for both https://github.com/amirsalarsafaei/sqlc-pgx-monitoring/blob/d96e6d0990332c1edea7dbb1f18e5efb68d39054/dbtracer/consts.go#L6