Last active
October 2, 2025 06:07
-
-
Save wjkoh/98ec8354bcc901d2413a4e26a7da102b to your computer and use it in GitHub Desktop.
Go: SQLite-based, On-disk LRU Cache
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package fred | |
| import ( | |
| "context" | |
| "database/sql" | |
| "errors" | |
| "log/slog" | |
| "time" | |
| ) | |
| func NewSQLiteDBs(ctx context.Context, fname string) (Executor, Queryer, error) { | |
| u := url.URL{Scheme: "file", Opaque: fname} | |
| q := make(url.Values) | |
| q.Set("mode", "rwc") | |
| q.Set("_foreign_keys", "true") | |
| q.Set("_journal_mode", "WAL") | |
| q.Set("_synchronous", "NORMAL") | |
| q.Set("_txlock", "immediate") | |
| u.RawQuery = q.Encode() | |
| rwc, err := sql.Open("sqlite3", u.String()) | |
| if err != nil { | |
| return nil, nil, err | |
| } | |
| rwc.SetMaxOpenConns(1) | |
| // Ping to force creation of the SQLite file. | |
| if err := rwc.PingContext(ctx); err != nil { | |
| return nil, nil, err | |
| } | |
| q = make(url.Values) | |
| q.Set("mode", "ro") | |
| u.RawQuery = q.Encode() | |
| ro, err := sql.Open("sqlite3", u.String()) | |
| if err != nil { | |
| return nil, nil, err | |
| } | |
| if err := ro.PingContext(ctx); err != nil { | |
| return nil, nil, err | |
| } | |
| // SQLite 3.45.0+: JSONB | |
| // SQLite 3.44.0+: %F and %T for strftime | |
| // SQLite 3.38.0+: JSON | |
| if err := checkSQLiteVersion(ctx, ro, "3.45.0", "SQLITE_ENABLE_FTS5"); err != nil { | |
| return nil, nil, fmt.Errorf("checkSQLiteVersion: %w", err) | |
| } | |
| return rwc, ro, nil | |
| } | |
| type sqliteLRUCache struct { | |
| rw Executor | |
| maxEntries int | |
| } | |
| func NewSQLiteLRUCache(ctx context.Context, rw Executor, maxEntries int) (*sqliteLRUCache, error) { | |
| if maxEntries <= 0 { | |
| return nil, errors.New("maxEntries must be positive") | |
| } | |
| _, err := rw.ExecContext( | |
| ctx, | |
| `CREATE TABLE IF NOT EXISTS cache_entries(cache_entry_id TEXT PRIMARY KEY, value BLOB, last_accessed INTEGER NOT NULL); | |
| CREATE INDEX IF NOT EXISTS idx_cache_entries_last_accessed ON cache_entries(last_accessed);`, | |
| ) | |
| if err != nil { | |
| return nil, err | |
| } | |
| return &sqliteLRUCache{rw: rw, maxEntries: maxEntries}, nil | |
| } | |
| func (c *sqliteLRUCache) Put(ctx context.Context, k string, v []byte) error { | |
| tx, err := c.rw.BeginTx(ctx, nil) | |
| if err != nil { | |
| return err | |
| } | |
| defer func() { | |
| if err := tx.Rollback(); err != nil && !errors.Is(err, sql.ErrTxDone) { | |
| slog.Error("Rollback failed", "err", err) | |
| } | |
| }() | |
| _, err = tx.ExecContext( | |
| ctx, | |
| `INSERT INTO cache_entries(cache_entry_id, value, last_accessed) VALUES(?, ?, ?) | |
| ON CONFLICT(cache_entry_id) DO UPDATE SET value=excluded.value, last_accessed=excluded.last_accessed`, | |
| k, | |
| v, | |
| time.Now().UnixNano(), | |
| ) | |
| if err != nil { | |
| return err | |
| } | |
| _, err = tx.ExecContext( | |
| ctx, | |
| `DELETE FROM cache_entries WHERE cache_entry_id NOT IN ( | |
| SELECT cache_entry_id FROM cache_entries ORDER BY last_accessed DESC LIMIT ? | |
| )`, | |
| c.maxEntries, | |
| ) | |
| if err != nil { | |
| return err | |
| } | |
| return tx.Commit() | |
| } | |
| func (c *sqliteLRUCache) Get(ctx context.Context, key string) ([]byte, error) { | |
| tx, err := c.rw.BeginTx(ctx, nil) | |
| if err != nil { | |
| return nil, err | |
| } | |
| defer func() { | |
| if err := tx.Rollback(); err != nil && !errors.Is(err, sql.ErrTxDone) { | |
| slog.Error("Rollback failed", "err", err) | |
| } | |
| }() | |
| var value []byte | |
| // NOTE: Starting a transaction to access QueryRowContext, as the method is not on the Executor interface. | |
| if err := tx.QueryRowContext( | |
| ctx, | |
| `UPDATE cache_entries SET last_accessed = ? WHERE cache_entry_id = ? RETURNING value`, | |
| time.Now().UnixNano(), | |
| key, | |
| ).Scan(&value); err != nil { | |
| return nil, err | |
| } | |
| if err := tx.Commit(); err != nil { | |
| return nil, err | |
| } | |
| return value, nil | |
| } | |
| func (c *sqliteLRUCache) Delete(ctx context.Context, key string) error { | |
| _, err := c.rw.ExecContext(ctx, `DELETE FROM cache_entries WHERE cache_entry_id = ?`, key) | |
| return err | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment