Rene Nochebuena
237cba9bad
Pure-Go CGO-free SQLite client with launcher lifecycle, write-mutex serialisation, health check, unit-of-work via context injection, and structured error mapping. What's included: - Executor / Tx / Client / Component interfaces using database/sql native types - Tx.Commit() / Tx.Rollback() without ctx, matching the honest database/sql contract - New(logger, cfg) constructor; database opened in OnInit - Config struct with env-tag support; default Pragmas: WAL + 5s busy timeout + FK enforcement - PRAGMA foreign_keys = ON enforced explicitly in OnInit - writeMu sync.Mutex acquired by UnitOfWork.Do to serialise writes and prevent SQLITE_BUSY - UnitOfWork via context injection; GetExecutor(ctx) returns active Tx or *sql.DB - HandleError mapping SQLite extended error codes to xerrors codes (unique/primary-key → AlreadyExists, foreign-key → InvalidInput, ErrNoRows → NotFound) - health.Checkable at LevelCritical; pure-Go modernc.org/sqlite driver (CGO_ENABLED=0 compatible) Tested-via: todo-api POC integration Reviewed-against: docs/adr/
55 lines
2.2 KiB
Markdown
55 lines
2.2 KiB
Markdown
# ADR-003: Foreign Key Enforcement via PRAGMA and DSN
|
|
|
|
**Status:** Accepted
|
|
**Date:** 2026-03-18
|
|
|
|
## Context
|
|
|
|
SQLite disables foreign key constraint enforcement by default for backwards compatibility.
|
|
Applications that define `REFERENCES` clauses in their schema will silently insert orphaned
|
|
rows unless they explicitly enable enforcement. This is a common source of data integrity bugs.
|
|
|
|
## Decision
|
|
|
|
Foreign key enforcement is enabled at two points:
|
|
|
|
1. **DSN parameter** — The default `Pragmas` config value includes `_fk=true`:
|
|
```
|
|
?_journal=WAL&_timeout=5000&_fk=true
|
|
```
|
|
This sets `PRAGMA foreign_keys = ON` for every connection opened via the DSN.
|
|
|
|
2. **OnInit explicit PRAGMA** — After opening the database pool, `OnInit` executes an
|
|
additional `PRAGMA foreign_keys = ON` call:
|
|
```go
|
|
if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
|
|
_ = db.Close()
|
|
return fmt.Errorf("sqlite: enable foreign keys: %w", err)
|
|
}
|
|
```
|
|
If this call fails, `OnInit` returns an error and the pool is closed, preventing startup
|
|
with an unsafe configuration.
|
|
|
|
The redundancy is deliberate: the DSN parameter may be overridden by callers who supply a
|
|
custom `Pragmas` value, but the `OnInit` PRAGMA call always runs and fails loudly if it cannot
|
|
enforce foreign keys.
|
|
|
|
`HandleError` maps `SQLITE_CONSTRAINT_FOREIGNKEY` (error code 787) to
|
|
`xerrors.ErrInvalidInput` so that foreign key violations surface as validation errors to
|
|
callers rather than opaque internal errors.
|
|
|
|
## Consequences
|
|
|
|
**Positive:**
|
|
- Foreign key constraints are always active when using the default configuration.
|
|
- Failure to enable them is a startup error, not a silent misconfiguration.
|
|
- Violations produce a structured `xerrors.ErrInvalidInput` error.
|
|
|
|
**Negative:**
|
|
- Callers who deliberately omit `_fk=true` from a custom `Pragmas` string still get the
|
|
enforcement applied by the `OnInit` PRAGMA. There is no opt-out without modifying the source.
|
|
- `PRAGMA foreign_keys = ON` must be set per-connection; `database/sql` connection pooling
|
|
means this implicit approach (via DSN) can behave differently under pool pressure. The
|
|
explicit `OnInit` PRAGMA mitigates this for the initial connection but cannot guarantee it
|
|
for all pooled connections when `MaxOpenConns > 1`.
|