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

> Learn to use the SQLite cache backend in Gorig with this guide. Initialize a type-safe SQLite cache and leverage methods for persistent storage. Get started with Gorig caching today.

- Repository: [Jom/gorig](https://github.com/jom-io/gorig)
- Tags: how-to-guide
- Published: 2026-03-05

---

**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`](https://github.com/jom-io/gorig/blob/main/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.

```go
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.

```go
// 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.

```go
// 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.

```go
// 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.

```go
// 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`](https://github.com/jom-io/gorig/blob/main/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`](https://github.com/jom-io/gorig/blob/main/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`](https://github.com/jom-io/gorig/blob/main/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.