SQLite Store

The SQLite store (herald/store/sqlite) provides a lightweight backend for Herald using Grove ORM with sqlitedriver. It creates the same 7 tables as the PostgreSQL store but uses SQLite-native types: TEXT for JSON data, INTEGER for booleans, and datetime('now') for timestamp defaults.

Installation01

go get github.com/xraph/herald
go get github.com/xraph/grove
go get github.com/xraph/grove/drivers/sqlitedriver

Creating a Store02

import (
    "github.com/xraph/grove"
    "github.com/xraph/grove/drivers/sqlitedriver"
    "github.com/xraph/herald/store/sqlite"
)

// Open a SQLite connection via Grove.
db := grove.Open(sqlitedriver.New(
    sqlitedriver.WithDSN("file:herald.db?_journal=WAL"),
))

// Create the Herald store.
store := sqlite.New(db)

// Run migrations.
if err := store.Migrate(ctx); err != nil {
    log.Fatal("migration failed:", err)
}

Constructor03

func New(db *grove.DB) *Store

New accepts a *grove.DB backed by sqlitedriver and returns a *Store that satisfies the store.Store interface. Internally it calls sqlitedriver.Unwrap(db) to access the SQLite-specific query builder.

Tables04

The store creates the same 7 tables as PostgreSQL, with SQLite-appropriate type adaptations.

Type Differences from PostgreSQL

PostgreSQL	SQLite	Notes
`JSONB NOT NULL DEFAULT '{}'`	`TEXT NOT NULL DEFAULT '{}'`	JSON stored as text strings
`BOOLEAN NOT NULL DEFAULT TRUE`	`INTEGER NOT NULL DEFAULT 1`	`1`/`0` for booleans
`TIMESTAMPTZ NOT NULL DEFAULT NOW()`	`TEXT NOT NULL DEFAULT (datetime('now'))`	ISO 8601 text timestamps
`REFERENCES ... ON DELETE CASCADE`	Not used	FK support is optional in SQLite

Tables Created

Table	Entity	Primary Key Prefix
`herald_providers`	Provider configuration	`hpvd_`
`herald_templates`	Template definitions	`htpl_`
`herald_template_versions`	Locale-specific content	`htpv_`
`herald_messages`	Delivery log	`hmsg_`
`herald_inbox`	In-app notifications	`hinb_`
`herald_preferences`	User preferences	`hprf_`
`herald_scoped_configs`	Scoped provider overrides	`hscf_`

Indexes and Constraints

All tables share the same index and unique constraint definitions as PostgreSQL:

herald_providers: index on (app_id, channel)
herald_templates: unique on (app_id, slug, channel)
herald_template_versions: unique on (template_id, locale)
herald_messages: indexes on (app_id, status) and (created_at DESC)
herald_inbox: indexes on (user_id, read, created_at DESC) and (app_id, user_id)
herald_preferences: unique on (app_id, user_id)
herald_scoped_configs: unique on (app_id, scope, scope_id)

JSON Handling05

Since SQLite lacks a native JSONB type, the SQLite store models use string fields instead of map[string]string and marshal/unmarshal JSON manually:

// PostgreSQL model:
Credentials map[string]string `grove:"credentials,type:jsonb"`

// SQLite model:
Credentials string `grove:"credentials"` // JSON text

The toXxxModel and fromXxxModel converters handle the JSON serialization transparently.

UPSERT Patterns06

SQLite supports the same ON CONFLICT upsert syntax as PostgreSQL:

_, err := s.sdb.NewInsert(m).
    OnConflict("(app_id, user_id) DO UPDATE").
    Set("overrides = EXCLUDED.overrides").
    Set("updated_at = EXCLUDED.updated_at").
    Exec(ctx)

Template Deletion07

Because SQLite foreign key support is optional and may not be enabled, the SQLite store explicitly deletes template versions before deleting a template:

func (s *Store) DeleteTemplate(ctx context.Context, templateID id.TemplateID) error {
    // Delete versions first (SQLite FK support is optional)
    _, _ = s.sdb.NewDelete((*templateVersionModel)(nil)).
        Where("template_id = ?", templateID.String()).
        Exec(ctx)
    // Then delete the template
    // ...
}

Lifecycle Methods08

Method	Behaviour
`Migrate(ctx)`	Creates all 7 tables and indexes via Grove's migration orchestrator
`Ping(ctx)`	Calls `db.Ping(ctx)` to verify database connectivity
`Close()`	Calls `db.Close()` to release the database connection

Connection Setup Example09

package main

import (
    "context"
    "log"

    "github.com/xraph/grove"
    "github.com/xraph/grove/drivers/sqlitedriver"
    "github.com/xraph/herald"
    "github.com/xraph/herald/store/sqlite"
)

func main() {
    ctx := context.Background()

    db := grove.Open(sqlitedriver.New(
        sqlitedriver.WithDSN("file:herald.db?_journal=WAL&_busy_timeout=5000"),
    ))

    store := sqlite.New(db)
    if err := store.Migrate(ctx); err != nil {
        log.Fatal("migrate:", err)
    }

    h, err := herald.New(
        herald.WithStore(store),
    )
    if err != nil {
        log.Fatal("herald:", err)
    }
    defer store.Close()

    _ = h
}

When to Use10

Development and testing -- zero-dependency, file-based database with no server required.
Single-node deployments -- embedded applications, CLI tools, or self-contained services.
CI/CD pipelines -- fast, disposable database for integration tests.
Prototyping -- get started immediately without database infrastructure.

Limitations11

Single-writer concurrency -- SQLite uses file-level locking. WAL mode helps but is not suitable for high-concurrency write workloads.
No native JSONB -- JSON stored as text, so JSON path queries are not supported at the database level.
No cascading deletes -- foreign key enforcement is optional; the store handles cascades manually.

← Previous

PostgreSQL Store

MongoDB Store