Wails in Practice #2 SQLite Local Database — Pure Go, No CGO

In #1 we split the notes app into three layers — binding, service, repository. This post implements the repository layer with SQLite so notes survive a close. A single-file SQLite is ideal storage for a local desktop app, but in Wails you have to clear one trap first: CGO.

This is the second of ten posts (two parts).

• #1 Designing a real project — what to build and how to split it
• #2 SQLite local database — pure Go, no CGO ← this post
• #3 Full-text search and data flow — FTS5 and event-driven updates
• #4 Tray residence and a global shortcut — capture fast from the background
• #5 Signing and notarization — how a shipped app earns trust
• #6 CI/CD automated releases — three-platform delivery with GitHub Actions

The trap first: CGO blocks cross-compilation #

The best-known SQLite driver in Go is mattn/go-sqlite3. But it links a C SQLite, so CGO must be on. Once CGO is on, a C compiler enters the build, and at that moment Go’s headline strength — easy cross-compilation — collapses. Producing a Windows binary from macOS suddenly needs a Windows C toolchain. This problem hits head-on when #6’s CI build produces binaries for three platforms.

The fix is the pure-Go SQLite driver modernc.org/sqlite. It is SQLite transpiled to Go, so there is no C dependency and it compiles without CGO. Performance is slightly below the CGO version, but at local-notes-app scale the difference is imperceptible, and the benefit — cross-compilation just works — is far larger.

import (
	"database/sql"

	_ "modernc.org/sqlite" // driver name is "sqlite"
)

The underscore (_) on the import means initialize the package without otherwise using it. That registers a driver named "sqlite" with database/sql. Note it differs from the mattn driver’s name "sqlite3", so do not confuse them.

The repository layer: open and schema #

The repository begins by opening the DB file and preparing the schema. Put the file under os.UserConfigDir, covered in intro #5, to avoid install-path permission issues.

type NoteRepository struct {
	db *sql.DB
}

func OpenRepository(appName string) (*NoteRepository, error) {
	base, err := os.UserConfigDir()
	if err != nil {
		return nil, err
	}
	dir := filepath.Join(base, appName)
	if err := os.MkdirAll(dir, 0o755); err != nil {
		return nil, err
	}

	db, err := sql.Open("sqlite", filepath.Join(dir, "notes.db"))
	if err != nil {
		return nil, err
	}
	repo := &NoteRepository{db: db}
	if err := repo.migrate(); err != nil {
		return nil, err
	}
	return repo, nil
}

Migrations: manage the schema by version #

Building the schema with CREATE TABLE IF NOT EXISTS alone works for the first version but gets awkward when you later add a column. In practice you record the schema version in the DB itself and step it up by version. SQLite’s PRAGMA user_version fits exactly.

func (r *NoteRepository) migrate() error {
	var version int
	if err := r.db.QueryRow(`PRAGMA user_version`).Scan(&version); err != nil {
		return err
	}

	if version < 1 {
		_, err := r.db.Exec(`
			CREATE TABLE notes (
				id         INTEGER PRIMARY KEY AUTOINCREMENT,
				title      TEXT NOT NULL,
				body       TEXT NOT NULL,
				created_at DATETIME NOT NULL,
				updated_at DATETIME NOT NULL
			);
			PRAGMA user_version = 1;
		`)
		if err != nil {
			return err
		}
	}
	// later versions continue here as if version < 2 { ... }
	return nil
}

Thanks to this structure, when #3 adds a full-text search table, existing users’ DBs also step up to the next version automatically. New install or existing, the same code reaches the correct schema.

CRUD: the repository owns the SQL #

Keep the repository to pure DB access. Rules like validation belong to the service layer.

func (r *NoteRepository) Insert(n Note) (Note, error) {
	res, err := r.db.Exec(
		`INSERT INTO notes (title, body, created_at, updated_at) VALUES (?, ?, ?, ?)`,
		n.Title, n.Body, n.CreatedAt, n.UpdatedAt,
	)
	if err != nil {
		return Note{}, err
	}
	n.ID, _ = res.LastInsertId()
	return n, nil
}

func (r *NoteRepository) List() ([]Note, error) {
	rows, err := r.db.Query(
		`SELECT id, title, body, created_at, updated_at FROM notes ORDER BY updated_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	notes := []Note{} // a non-nil empty slice — arrives as [] on the frontend
	for rows.Next() {
		var n Note
		if err := rows.Scan(&n.ID, &n.Title, &n.Body, &n.CreatedAt, &n.UpdatedAt); err != nil {
			return nil, err
		}
		notes = append(notes, n)
	}
	return notes, rows.Err()
}

Always pass values into SQL through ? placeholders. Concatenating strings directly opens the door to SQL injection, and even in a local app you do not know what characters a note body will contain, so keep the habit. Returning an empty list as []Note{} rather than nil matters too: a nil slice becomes null in JSON and breaks array iteration on the frontend.

The service layer: validation here #

The service wraps the repository and applies domain rules. It also fills in the time values.

func (s *NoteService) Create(title, body string) (Note, error) {
	title = strings.TrimSpace(title)
	if title == "" {
		return Note{}, errors.New("please enter a title")
	}
	now := time.Now()
	return s.repo.Insert(Note{
		Title:     title,
		Body:      body,
		CreatedAt: now,
		UpdatedAt: now,
	})
}

The error returned here becomes a rejected Promise on the frontend, as covered in intro #5. An empty title puts “please enter a title” straight on the screen.

Summary #

• When using SQLite in Wails, mattn/go-sqlite3 requires CGO and blocks cross-compilation. The pure-Go driver modernc.org/sqlite (driver name "sqlite") removes the problem.
• Put the DB file in an app directory under os.UserConfigDir to avoid install-path permission issues.
• Version the schema with PRAGMA user_version and migrate in steps. New and existing installs reach the latest schema with the same code.
• Keep the repository to pure DB access and rules like validation in the service layer. Always pass values via ? placeholders, and return an empty list as []Note{}.
• Next post layers FTS5 full-text search on this repository and notifies the frontend of data changes via events.