GritGrit
GitHub

Local SQLite

GORM against a local file.

7 minmedium

Local SQLite is the heart of an offline-first app. Your desktop binary writes to a local file; the file survives launches; the sync engine pushes those rows to your server later. This lesson covers the SQLite + GORM setup.

Where the file lives

Windows:  %APPDATA%\field-pos\local.db
macOS:    ~/Library/Application Support/field-pos/local.db
Linux:    ~/.config/field-pos/local.db

Grit uses github.com/adrg/xdg to resolve the right per-OS path. The file persists across launches and survives app updates.

The connection

internal/db/db.go
import (
    "github.com/glebarez/sqlite"
    "gorm.io/gorm"
)


func Open(path string) (*gorm.DB, error) {
    db, err := gorm.Open(sqlite.Open(path+"?_pragma=journal_mode(WAL)"), &gorm.Config{})
    if err != nil {
        return nil, err
    }
    db.AutoMigrate(&User{}, &Sale{}, &Product{})
    return db, nil
}

Note: pure-Go SQLite (glebarez/sqlite), not CGO. Wails builds work on Windows without a C toolchain. WAL mode prevents readers from blocking writers — critical for a responsive UI that polls.

Models

internal/models/sale.go
type Sale struct {
    ID         string    `gorm:"primaryKey"`              // UUID — generated client-side
    LocalSeq   int64     `gorm:"autoIncrement;index"`     // monotonic local ordering
    UserID     string    `gorm:"not null"`
    Total      decimal.Decimal
    Status     string    `gorm:"default:'completed'"`
    ServerID   *string   `gorm:"index"`                   // nil until synced
    CreatedAt  time.Time
}

Two ID columns matter:

  • ID — UUID generated on the device. Stable forever; survives sync. Server adopts this same ID.
  • ServerID — only set after the row syncs successfully. Useful for "synced ✓" vs "pending" UI.
UUIDs not auto-increment. If the same desktop is used by two cashiers offline, both can write rows without ID collisions when they later sync. Auto-increment IDs would collide. UUIDs are the universal sync-safe choice.

Reading + writing from Go (called by React)

app.go
func (a *App) RecordSale(items []SaleItem) (*Sale, error) {
    sale := &Sale{
        ID:     uuid.NewString(),
        UserID: a.currentUserID,
        Total:  computeTotal(items),
        Items:  items,
    }
    if err := a.db.Create(sale).Error; err != nil {
        return nil, fmt.Errorf("save sale: %w", err)
    }
    // Enqueue into outbox (lesson 2.2)
    a.outbox.Enqueue(sale)
    return sale, nil
}


func (a *App) ListSales(limit int) ([]Sale, error) {
    var sales []Sale
    a.db.Order("local_seq DESC").Limit(limit).Find(&sales)
    return sales, nil
}

Both bound to React. React calls RecordSale([...items]), gets the saved row back, and re-renders the list. No network involved — pure local DB.

Backups + corruption recovery

SQLite is incredibly reliable, but for an offline POS that's a cashier's only proof of today's sales, ship a daily backup:

// On app startup, copy local.db to local.db.YYYYMMDD.bak
// Keep last 7 days; rotate older ones out

Belt-and-braces. Disk corruption is rare but not impossible. A 7-day rolling backup costs ~kilobytes and saves a customer's Tuesday when their Wednesday goes wrong.

Quick check

A user takes their desktop app offline for 3 days, records 200 sales, then reconnects. Two cashiers used the same device simultaneously. What collides?

Try it

Wire local SQLite + a write:

  1. Confirm your scaffolded project has internal/db/ with a Connect() function.
  2. Add a Note model + an AddNote(text string) method on App.
  3. Wire a React form that calls AddNote.
  4. Add a few notes. Quit the app. Reopen — your notes should persist.

Paste a screenshot of the notes list after a restart in notes.md.

What's next

Local writes are saved. Next lesson — the outbox pattern for sending them to the server when online.

Spot a typo? Have an idea?

Help us improve this lesson. One click opens a GitHub issue with the lesson URL pre-filled — suggest clearer wording, report a bug, or request more depth. The course keeps improving thanks to learners like you.

Suggest an improvement on GitHub
Previous lessonNext lesson