SQLite Setup

BatAudit supports SQLite as a zero-dependency alternative to PostgreSQL. No database server required — everything is stored in a single file.

---

When to use SQLite

Scenario	Use
Local development	✅ SQLite
Simple self-host, low traffic	✅ SQLite
Single server, single team	✅ SQLite
High write throughput (>1k events/min)	⚡ PostgreSQL is better
Multiple Writer instances	⚡ PostgreSQL is required

info

SQLite uses WAL (Write-Ahead Logging) mode and a 5-second busy timeout, which allows the Reader and Worker to run concurrently alongside the Writer without conflicts. For most self-hosted use cases this is sufficient.

---

Environment variables

DB_DRIVER=sqlite
SQLITE_PATH=/data/bataudit.db   # path inside the container (or absolute path on host)

SQLITE_PATH defaults to bataudit.db in the working directory if not set.

---

Docker Compose

Create a minimal docker-compose.sqlite.yml:

version: "3.9"

services:
  redis:
    image: redis:7-alpine
    volumes:
      - bat_redis:/data
    restart: unless-stopped

  writer:
    image: ghcr.io/joaovrmoraes/bataudit/writer:latest
    environment:
      - DB_DRIVER=sqlite
      - SQLITE_PATH=/data/bataudit.db
      - REDIS_ADDRESS=redis:6379
      - GIN_MODE=release
    volumes:
      - bat_sqlite:/data
    ports:
      - "8081:8081"
    depends_on: [redis]
    restart: unless-stopped

  reader:
    image: ghcr.io/joaovrmoraes/bataudit/reader:latest
    environment:
      - DB_DRIVER=sqlite
      - SQLITE_PATH=/data/bataudit.db
      - JWT_SECRET=${JWT_SECRET}
      - INITIAL_OWNER_EMAIL=${INITIAL_OWNER_EMAIL}
      - INITIAL_OWNER_PASSWORD=${INITIAL_OWNER_PASSWORD}
      - INITIAL_OWNER_NAME=${INITIAL_OWNER_NAME}
      - GIN_MODE=release
    volumes:
      - bat_sqlite:/data
    ports:
      - "8082:8082"
    depends_on: [redis]
    restart: unless-stopped

  worker:
    image: ghcr.io/joaovrmoraes/bataudit/worker:latest
    environment:
      - DB_DRIVER=sqlite
      - SQLITE_PATH=/data/bataudit.db
      - REDIS_ADDRESS=redis:6379
    volumes:
      - bat_sqlite:/data
    depends_on: [redis]
    restart: unless-stopped

volumes:
  bat_sqlite:
  bat_redis:

All three services mount the same bat_sqlite volume so they share the same .db file.

Start:

docker compose -f docker-compose.sqlite.yml up -d

---

Running without Docker

git clone https://github.com/joaovrmoraes/bataudit.git
cd bataudit

export DB_DRIVER=sqlite
export SQLITE_PATH=./bataudit.db
export JWT_SECRET=<secret>
export INITIAL_OWNER_EMAIL=you@example.com
export INITIAL_OWNER_PASSWORD=changeme
export REDIS_ADDRESS=localhost:6379

go run ./cmd/api/reader    # terminal 1
go run ./cmd/api/writer    # terminal 2
go run ./cmd/api/worker    # terminal 3

---

Migrations

SQLite migrations live in internal/db/migrations/sqlite/. They run automatically on startup — no manual steps needed.

Types mapped from PostgreSQL:

PostgreSQL	SQLite
JSONB	TEXT
TIMESTAMPTZ	DATETIME
UUID PRIMARY KEY DEFAULT gen_random_uuid()	TEXT PRIMARY KEY (UUID generated by app)

---

Backups

Backing up SQLite is as simple as copying the file:

# One-time backup
cp /var/lib/docker/volumes/bat_sqlite/_data/bataudit.db backup-$(date +%Y%m%d).db

# Or from inside the container
docker run --rm -v bat_sqlite:/data -v $(pwd):/backup alpine \
  cp /data/bataudit.db /backup/bataudit-$(date +%Y%m%d).db

tip

Because WAL mode is enabled, copy the .db file while the services are running — SQLite guarantees a consistent snapshot.

---

Limitations

• Single-file concurrency — WAL mode allows concurrent reads alongside one writer. If you need multiple independent Writer processes, use PostgreSQL instead.
• No native JSONB indexing — JSON fields are stored as TEXT; filtering inside JSON payloads is done in application code rather than a DB index.
• File must be shared — all services (Writer, Reader, Worker) must mount the same volume containing the .db file.