How to Use the SQLite Cache Backend in Gorig: A Complete Guide

Initialize a durable, type-safe SQLite cache in Gorig by calling cache.NewSQLiteCache[T]("type"), which creates a WAL-mode database in the .cache directory and exposes generic methods like Set, Get, Incr, RPush, and BRPop for persistent storage.

The jom-io/gorig framework provides a generic caching abstraction that supports multiple backends, including an embedded SQLite implementation. The SQLite cache backend in Gorig offers durable, file-based persistence with the same type-safe API as Redis or in-memory stores, making it ideal for development, testing, or single-node deployments where external dependencies are undesirable.

How the Gorig SQLite Cache Backend Works

Core Architecture

The implementation lives in cache/cache.sqlite.go and centers on the SQLiteCache[T] struct. This generic type holds a *sql.DB connection and a sync.RWMutex to coordinate concurrent access. Values of type T are automatically marshalled to JSON before storage and unmarshalled on retrieval, allowing you to cache complex structs, maps, or primitives while maintaining type safety.

Database Schema and WAL Mode

When you instantiate a cache, the constructor performs several setup steps:

1. Creates a .cache directory relative to the working directory (if missing).
2. Opens (or creates) a SQLite file named .<cacheType>.db where cacheType is the string passed to the constructor.
3. Enables WAL mode (Write-Ahead Logging) for better concurrency and durability.
4. Ensures two tables exist:
   • cache – stores key, value (JSON text), and expiration (Unix timestamp; 0 means never expires).
   • queue – implements a simple FIFO list used by RPush and BRPop.

The constructor caches instances in a sync.Map (cacheSqliteIns), ensuring that multiple calls with the same cacheType reuse the same database connection.

Initializing the SQLite Cache in Gorig

To create a SQLite-backed cache, invoke NewSQLiteCache[T] with a domain-specific identifier. The generic parameter T defines the type of values you will store.

package main

import (
    "log"
    "time"
    
    "github.com/jom-io/gorig/cache"
)

func main() {
    // Create a cache for session data where each entry is a map[string]any
    sessCache, err := cache.NewSQLiteCache[map[string]any]("session")
    if err != nil {
        log.Fatalf("failed to create sqlite cache: %v", err)
    }
    
    // The cache is now ready for operations
    _ = sessCache
}

The "session" argument determines the filename (.session.db) and ensures that any other component using "session" shares the same underlying database.

Performing Cache Operations

The SQLiteCache implements the same interface as other Gorig cache backends, providing consistent methods for CRUD operations, counters, and queue management.

Storing and Retrieving Data

Use Set to store values with an optional time-to-live (TTL). Pass 0 as the duration for permanent storage. Retrieve values with Get, which returns the zero value of T and an error if the key is missing or expired.

// Store a user object with a 5-minute expiration
user := map[string]any{
    "id":   12345,
    "name": "alice",
}
err := sessCache.Set("user:12345", user, 5*time.Minute)

// Retrieve it later
var retrieved map[string]any
retrieved, err = sessCache.Get("user:12345")
if err != nil {
    log.Printf("cache miss: %v", err)
} else {
    fmt.Printf("cached user: %+v\n", retrieved)
}

Working with Counters

The Incr method provides atomic increment operations on numeric keys. If the key does not exist, it initializes to 0 before incrementing. This is useful for tracking page views, API quotas, or sequence generation.

// Increment a page view counter
cnt, err := sessCache.Incr("page:home:views")
if err != nil {
    log.Printf("increment failed: %v", err)
}
fmt.Printf("home page views: %d\n", cnt)

Using the FIFO Queue

The SQLite backend supports list operations via RPush (right push) and BRPop (blocking right pop), enabling simple job queues. BRPop blocks until an item is available or the specified timeout expires, returning ErrCacheMiss on timeout.

// Push a job onto the queue
if err := sessCache.RPush("jobs", "job-1"); err != nil {
    log.Printf("rpush error: %v", err)
}

// Block up to 10 seconds waiting for a job
job, err := sessCache.BRPop(10*time.Second, "jobs")
if err != nil {
    log.Printf("brpop missed: %v", err) // ErrCacheMiss after timeout
} else {
    fmt.Printf("got job: %s\n", job)
}

Managing Expiration and Cleanup

Expiration is handled automatically. Every mutating operation (Set, Del, Incr, Expire, RPush) triggers an asynchronous cleanup() goroutine that deletes rows where the Unix timestamp expiration is in the past. You can also manually set or update expiration with the Expire method.

// Update TTL for an existing key
err := sessCache.Expire("user:12345", 30*time.Minute)

Configuration and Performance Tuning

While the SQLite backend works out of the box, understanding its configuration helps optimize for your workload:

• WAL Mode: The constructor enables Write-Ahead Logging via PRAGMA journal_mode=WAL, allowing concurrent reads while writing and preventing writer starvation.
• Timeouts: The implementation uses sqliteTimeOut to prevent deadlocks when acquiring the internal sync.RWMutex. Operations block only briefly if the cache is under heavy contention.
• Directory Permissions: The .cache directory is created with 0755 permissions. Ensure your deployment environment has write access to the working directory or modify the path in NewSQLiteCache before compilation.
• Connection Reuse: The sync.Map (cacheSqliteIns) ensures that multiple initializations with the same cacheType string reuse the same *sql.DB connection, preventing file lock conflicts and reducing memory overhead.

Summary

• The SQLite cache backend in Gorig provides durable, file-based persistence with a generic, type-safe API defined in cache/cache.sqlite.go.
• Initialize it with NewSQLiteCache[T]("type"), which creates a .cache directory, enables WAL mode, and reuses connections for identical cache types.
• Store and retrieve JSON-serialized values using Set, Get, and Expire, with automatic background cleanup of expired entries.
• Implement counters with Incr and simple job queues with RPush and BRPop.
• Swap the SQLite backend for Redis or in-memory caches without changing business logic, as all implementations share the same interface defined in cache/cache.go.

Frequently Asked Questions

What file does the SQLite cache create on disk?

The constructor creates a hidden directory named .cache in your working directory, then opens (or creates) a SQLite database file named .<cacheType>.db inside it. For example, NewSQLiteCache[...]("session") produces .cache/.session.db. The file uses WAL mode, so you may also see .session.db-wal and .session.db-shm files during operation.

Can I use the SQLite cache for production workloads?

Yes, but with caveats. The SQLite backend is suitable for single-node deployments, development environments, or low-to-moderate traffic services where external Redis infrastructure is undesirable. It supports concurrent reads via WAL mode and handles moderate write loads, but high-throughput scenarios or multi-node deployments should use the Redis backend to avoid file-lock contention and ensure data consistency across instances.

How do I switch from SQLite to Redis in Gorig?

Because all cache backends implement the generic Cache[T] interface defined in cache/cache.go, migration requires only changing the constructor call. Replace cache.NewSQLiteCache[YourType]("domain") with cache.NewRedisCache[YourType](redisClient, "domain") (or the appropriate Redis constructor). All subsequent method calls—Set, Get, Incr, RPush, etc.—remain identical and require no modification.

What types can I store in the SQLite cache?

The generic type parameter T can be any Go type that can be marshalled to JSON using encoding/json. This includes primitive types (string, int, float), structs, maps (e.g., map[string]any), and slices. The cache automatically handles JSON serialization before writing to the value column and deserialization when retrieving. Ensure your structs have exported fields and appropriate JSON tags if you need custom field names.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}