README
¶
SQLite migrations
SQLite schema versions are authoritative on normal read-write startup. A current database must take a marker-only fast path; physical schema inspection is reserved for migration slow paths and explicit diagnostics.
Adding a migration
- Update the canonical fresh schema.
- Increment the store's target version.
- Append exactly one ordered migration with a positive version, a non-empty description, and a non-nil callback.
- Add or update a faithful fixture from a released schema. Label synthetic missing-object fixtures as synthetic.
- Compare fresh and migrated schemas with
sqliteutil.SchemaSignature. - Test representative data preservation, callback failure rollback, and retry from the previous marker.
- Write the marker only in the same
BEGIN IMMEDIATEtransaction as the change it publishes. - Never add startup reconciliation in place of a versioned migration.
- Consider whether the immediately previous binary can still read the migrated file, and document any mixed-binary boundary.
Migration lists must be checked with sqliteutil.ValidateMigrations in package tests. Slow paths acquire sqliteutil.WithImmediateMigrationTx, then re-read their marker and schema state on that locked connection before writing. A future marker is rejected before writes. Released migration numbers and meanings are immutable; production repairs always use a new version.
Common changes
Add a column
Put the column in the canonical CREATE TABLE. In the migration, call sqliteutil.ColumnExists and issue an explicit ALTER TABLE ... ADD COLUMN only when absent. Validate known legacy variants; do not swallow arbitrary “already exists” errors.
Add an index
Put the index in the canonical schema and create it in the migration that owns its first published version. A current startup must not run CREATE INDEX IF NOT EXISTS as reconciliation.
Change a trigger
Keep released trigger SQL frozen. A new migration drops/recreates the trigger and performs any required data backfill transactionally. The canonical fresh schema contains the latest definition.
Backfill data
Perform the backfill in the same migration transaction as its schema change and marker update. Test meaningful old rows, defaults, row counts, and rollback—not only object existence.
Rebuild a table
Use version-named, immutable table SQL. Create the replacement table, copy an explicit column list, verify behavior in tests, restore version-owned indexes/triggers, and drop the old table inside one transaction. Include rollback and content-preservation coverage. Never point a historical rebuild at a mutable canonical table definition.
Mixed-binary rollout notes
- Session v49 adds only the singleton marker key and reconciles already-supported tables, indexes, and triggers. The immediately previous binary's
SELECT version/UPDATE schema_version SET version = ?statements remain valid; that binary may still accept v49 because it predates future-version rejection. - Memory v12 canonicalizes the existing tables and owns the already-supported vector index. Its key/value marker remains readable by the previous binary.
- File-history v3 adds a singleton marker key while preserving
SELECT versionand whole-tableUPDATEcompatibility. - Jobs v1 adopts the existing jobs-v2 shape and adds a marker unknown to older binaries; the reconciled columns and indexes remain compatible with the immediately previous jobs code.
A process running an older binary concurrently with a new migrator is not granted write coordination beyond SQLite locking. Deploy one write-capable version at a time during these first marker transitions.
Errors and operations
Migration errors should identify the store, observed and supported versions, migration version and description, and failed operation. Callback failures leave the previously committed marker safe. Lock acquisition uses the configured SQLite busy_timeout; expiry returns a retryable contextual error rather than spinning indefinitely.
Unknown unversioned schemas fail closed with a structural description and recovery guidance, without user data. Full corruption checking belongs to term-llm doctor (see the user-facing documentation and internal/doctor/README.md for contributor notes), which also compares each database against the schema its migrations produce on a fresh install, and must not be added to normal startup.
Documentation
¶
Index ¶
- func ColumnExists(exec Executor, table, column string) (bool, error)
- func FileURI(path string) string
- func IndexExists(exec Executor, index string) (bool, error)
- func ResolveDBPathOverride(pathOverride string, defaultPath func() (string, error)) (string, error)
- func SchemaSignature(ctx context.Context, db *sql.DB) ([]string, error)
- func TableExists(exec Executor, table string) (bool, error)
- func TriggerExists(exec Executor, trigger string) (bool, error)
- func UserTableCount(exec Executor) (int, error)
- func ValidateMigrations(migrations []Migration, firstVersion, targetVersion int, contiguous bool) error
- func WithImmediateMigrationTx(ctx context.Context, db *sql.DB, fn func(Executor) error) (err error)
- type Executor
- type Migration
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func ColumnExists ¶ added in v0.0.406
ColumnExists reports whether table contains column.
func FileURI ¶ added in v0.9.48
FileURI converts a filesystem path into a SQLite file: URI. Windows drive and UNC paths are normalized so the URI stays valid on every platform, and the conversion is deliberately independent of the running GOOS so it stays testable on any builder.
func IndexExists ¶ added in v0.0.406
IndexExists reports whether a named index exists.
func ResolveDBPathOverride ¶
ResolveDBPathOverride expands and absolutizes an optional SQLite database path. An empty override delegates to defaultPath; :memory: is returned unchanged.
func SchemaSignature ¶ added in v0.0.406
SchemaSignature captures a normalized structural signature for tests and diagnostics. It includes sqlite_master SQL, complete table_info metadata, and foreign-key metadata while excluding SQLite's auto-generated object names. It is intentionally not used by normal migration startup paths.
func TableExists ¶ added in v0.0.406
TableExists reports whether a table or virtual table exists.
func TriggerExists ¶ added in v0.0.406
TriggerExists reports whether a named trigger exists.
func UserTableCount ¶ added in v0.0.406
UserTableCount returns the number of non-SQLite-owned tables. It is useful only while classifying an unversioned database on a locked slow path.
func ValidateMigrations ¶ added in v0.0.406
func ValidateMigrations(migrations []Migration, firstVersion, targetVersion int, contiguous bool) error
ValidateMigrations validates a migration list. When contiguous is true, the first version must equal firstVersion and every following version must be the next integer. A non-empty list must end at targetVersion.
func WithImmediateMigrationTx ¶
WithImmediateMigrationTx runs fn on a dedicated connection in a SQLite BEGIN IMMEDIATE transaction. SQLite's configured busy_timeout governs lock acquisition; this function does not add an unbounded retry loop.
Cleanup uses a short context independent of ctx, so cancellation cannot skip rollback. If rollback cannot be confirmed, database/sql's Raw hook returns driver.ErrBadConn, which tells the pool to discard rather than reuse the connection.
Types ¶
type Executor ¶
type Executor interface {
Exec(query string, args ...any) (sql.Result, error)
Query(query string, args ...any) (*sql.Rows, error)
QueryRow(query string, args ...any) *sql.Row
}
Executor is the context-bound query surface exposed to a migration transaction. Implementations must keep every operation on the same SQLite connection.
Source Files