sqlcache

README

sqlcache

sqlcache is a caching middleware for database/sql that enables existing Go programs to add caching in a declarative way. It leverages APIs provided by the handy sqlmw project and is inspired from slonik-interceptor-query-cache.

This liberates your Go program from maintaining imperative code that repeatedly implements the cache-aside pattern. Your program will perceive the database client/driver as a read-through cache.

Tested with PostgreSQL database with pgx as the underlying driver.

Cache backends supported:

• ristretto (in-memory)
• redis

It's easy to add other caching backends by implementing the cache.Cacher interface.

Usage

Create a backend cache instance and install the interceptor:

import (
	"database/sql"

	redis "github.com/go-redis/redis/v7"
	"github.com/jackc/pgx/v4/stdlib"
	"github.com/prashanthpai/sqlcache"
)

func main() {
	...
	rc := redis.NewUniversalClient(&redis.UniversalOptions{
		Addrs: []string{"127.0.0.1:6379"},
	})

	// create a sqlcache.Interceptor instance with the desired backend
	interceptor, err := sqlcache.NewInterceptor(&sqlcache.Config{
		Cache: sqlcache.NewRedis(rc, "sqc"),
	})
	...

	// wrap pgx driver with cache interceptor and register it
	sql.Register("pgx-with-cache", interceptor.Driver(stdlib.GetDefaultDriver()))

	// open the database using the wrapped driver
	db, err := sql.Open("pgx-with-cache", dsn)
	...

Caching is controlled using cache attributes which are SQL comments starting with @cache- prefix. Only queries with cache attributes are cached.

Cache attributes:

Cache attribute	Description	Required?	Default
@cache-ttl	Number (in seconds) to cache the query for.	Yes	N/A
@cache-max-rows	Don't cache if number of rows in query response exceeds this limit.	Yes	N/A

Example query:

rows, err := db.QueryContext(context.TODO(), `
	-- @cache-ttl 30
	-- @cache-max-rows 10
	SELECT name, pages FROM books WHERE pages > $1`, 100)

See example/main.go for a full working example.

References

• A declarative way to cache PostgreSQL queries using Node.js: a blog post by the author of Slonik.
• Declarative Caching with Postgres and Redis: Kyle Davis's talk on Slonik + Redis.

Documentation

Overview

Package sqlcache provides an experimental caching middleware for database/sql users. This liberates your Go program from maintaining imperative code that implements the cache-aside pattern. Your program will perceive the database client/driver as a read-through cache.

Usage:

import (
	"database/sql"

	redis "github.com/go-redis/redis/v7"
	"github.com/hellodword/sqlcache"
	"github.com/jackc/pgx/v4/stdlib"
)

func main() {
	...
	rc := redis.NewUniversalClient(&redis.UniversalOptions{
		Addrs: []string{"127.0.0.1:6379"},
	})

	// create a sqlcache.Interceptor instance with the desired backend
	interceptor, err := sqlcache.NewInterceptor(&sqlcache.Config{
		Cache: sqlcache.NewRedis(rc, "sqc"),
	})
	...

	// wrap pgx driver with the interceptor and register it
	sql.Register("pgx-with-cache", interceptor.Driver(stdlib.GetDefaultDriver()))

	// open the database using the wrapped driver
	db, err := sql.Open("pgx-with-cache", dsn)
	...
}

Caching is controlled using cache attributes which are SQL comments starting with `@cache-` prefix. Only queries with cache attributes are cached.

Example query:

rows, err := db.QueryContext(context.TODO(), `
	-- @cache-ttl 30
	-- @cache-max-rows 10
	SELECT name, pages FROM books WHERE pages > $1`, 100)

Index

• func NoopHash(query string, args []driver.NamedValue) (string, error)
• type Config
• type Interceptor
  • func NewInterceptor(config *Config) (*Interceptor, error)
  • func (i *Interceptor) ConnQueryContext(ctx context.Context, conn driver.QueryerContext, query string, ...) (driver.Rows, error)
  • func (i *Interceptor) Disable()
  • func (i *Interceptor) Driver(d driver.Driver) driver.Driver
  • func (i *Interceptor) Enable()
  • func (i *Interceptor) Stats() *Stats
  • func (i *Interceptor) StmtQueryContext(ctx context.Context, conn driver.StmtQueryContext, query string, ...) (driver.Rows, error)
• type Stats

Functions

func NoopHash

func NoopHash(query string, args []driver.NamedValue) (string, error)

NoopHash returns a string representation of the query and args. Whitespaces in the query string is stripped off.

Types

type Config

type Config struct {
	// Cache must be set to a type that implements the cache.Cacher interface
	// which abstracts the backend cache implementation. This is a required
	// field and cannot be nil.
	Cache cache.Cacher
	// OnError is called whenever methods of cache.Cacher interface or HashFunc
	// returns error. Since sqlcache package does not log any failures, you can
	// use this hook to log errors or even choose to disable/bypass sqlcache.
	OnError func(error)
	// HashFunc can be optionally set to provide a custom hashing function. By
	// default sqlcache uses mitchellh/hashstructure which internally uses FNV.
	// If hash collision is a concern to you, consider using NoopHash.
	HashFunc func(query string, args []driver.NamedValue) (string, error)
}

Config is the configuration passed to NewInterceptor for creating new Interceptor instances.

type Interceptor

type Interceptor struct {
	sqlmw.NullInterceptor
	// contains filtered or unexported fields
}

Interceptor is a ngrok/sqlmw interceptor that caches SQL queries and their responses.

func NewInterceptor

func NewInterceptor(config *Config) (*Interceptor, error)

NewInterceptor returns a new instance of sqlcache interceptor initialised with the provided config.

func (*Interceptor) ConnQueryContext

func (i *Interceptor) ConnQueryContext(ctx context.Context, conn driver.QueryerContext, query string, args []driver.NamedValue) (driver.Rows, error)

ConnQueryContext intecepts database/sql's DB.QueryContext Conn.QueryContext calls.

func (*Interceptor) Disable

func (i *Interceptor) Disable()

Disable disables the interceptor resulting in cache bypass. All queries would go directly to the SQL backend.

func (*Interceptor) Driver

func (i *Interceptor) Driver(d driver.Driver) driver.Driver

Driver returns the supplied driver.Driver with a new object that has all of its calls intercepted by the sqlcache.Interceptor. Any DB call without a context passed will not be intercepted.

func (*Interceptor) Enable

func (i *Interceptor) Enable()

Enable enables the interceptor. Interceptor instance is enabled by default on creation.

func (*Interceptor) Stats

func (i *Interceptor) Stats() *Stats

Stats returns sqlcache stats.

func (*Interceptor) StmtQueryContext

func (i *Interceptor) StmtQueryContext(ctx context.Context, conn driver.StmtQueryContext, query string, args []driver.NamedValue) (driver.Rows, error)

StmtQueryContext intecepts database/sql's stmt.QueryContext calls from a prepared statement.

type Stats

type Stats struct {
	Hits   uint64
	Misses uint64
	Errors uint64
}

Stats contains sqlcache statistics.