db

package
v0.0.6 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Feb 14, 2026 License: MIT Imports: 19 Imported by: 0

README

Database Package

The db package provides an opinionated SQLite layer built on top of the modernc.org/sqlite driver (CGO free). It gives the application safe defaults for concurrency, durability, and observability without forcing extra dependencies.

Highlights

  • Separate connection pools for writers and readers to reduce contention.
  • Write-ahead logging (WAL) with synchronous=NORMAL for balanced durability.
  • Busy timeout, short per-operation deadlines, and aggressive WAL checkpointing.
  • Simple API for executing statements and managing explicit transactions.

Installation

The package is internal to this repository. Import it from other Go packages with:

import "github.com/crgimenes/devengine/db"

All examples assume Go 1.26 or later.

Opening the database

Use db.New() when you want to respect the configured database path (from config.Cfg.DBFile). If no path is configured it falls back to devengine.db in the working directory.

store, err := db.New()
if err != nil {
    log.Fatalf("open database: %v", err)
}
defer store.Close()

In tests or tools you can bypass the global configuration and create a new instance pointing to a temporary file:

tmpDir := t.TempDir()
store, err := db.NewWithPath(filepath.Join(tmpDir, "test.db"))
if err != nil {
    t.Fatalf("open database: %v", err)
}
defer store.Close()

Both helpers open two pools:

  • A single-writer pool configured with WAL, busy timeout, foreign_keys enabled, and an IMMEDIATE transaction lock for predictable latency under contention.
  • A read-only pool sized to GOMAXPROCS (minimum 4 connections) for parallel SELECTs.

Executing statements

Use the Exec, Query, and QueryRow helpers for ad-hoc operations. All methods run with short timeouts to avoid runaway queries.

if err := store.Exec(`CREATE TABLE IF NOT EXISTS items(id INTEGER PRIMARY KEY, name TEXT NOT NULL)`); err != nil {
    return fmt.Errorf("create table: %w", err)
}
if err := store.Exec(`INSERT INTO items(name) VALUES(?)`, "example"); err != nil {
    return fmt.Errorf("insert: %w", err)
}
var count int
if err := store.QueryRow(`SELECT COUNT(*) FROM items`).Scan(&count); err != nil {
    return fmt.Errorf("count: %w", err)
}

Always call Scan (or Err) on the returned row to release the underlying timeout context.

Transactions

Call BeginTransaction for multi-statement writes. The returned transaction provides matching Exec, Query, and QueryRow methods. Commit rolls back automatically on failure.

tx, err := store.BeginTransaction()
if err != nil {
    return fmt.Errorf("begin: %w", err)
}
if err := tx.Exec(`INSERT INTO kv(k, v) VALUES(?, ?)`, "key", "value"); err != nil {
    _ = tx.Rollback()
    return fmt.Errorf("insert: %w", err)
}
if err := tx.Commit(); err != nil {
    return fmt.Errorf("commit: %w", err)
}

Transactions run on the single-writer pool to guarantee serialized writes. They do not accept a context because per-operation timeouts are applied inside each helper.

Graceful shutdown

Always call Close during application shutdown. The method performs a best-effort WAL checkpoint (wal_checkpoint(TRUNCATE)) before closing both pools. The main application defers closing the database until after the HTTP server and background work finish so that all in-flight requests can drain.

func shutdown(ctx context.Context, store *db.SQLite) {
    // stop HTTP server first, wait for workers, then:
    store.Close()
}

Troubleshooting

  • Database is locked: Busy timeouts handle short spikes, but long-running readers can still block writers. Keep transactions small and avoid starting them far in advance of the write.
  • Slow queries: The read pool enforces per-operation timeouts. Tune SQL or add indexes if queries exceed the deadline.
  • Unexpected temporary files: WAL mode keeps a *-wal file while the process runs. Close triggers wal_checkpoint(TRUNCATE) to shrink it; ensure the process exits cleanly to reap the file.

Testing helpers

Unit tests can create isolated databases with NewWithPath and leverage the small helpers defined in db_test.go for scanning pragma results. Run the package tests (including examples) with:

go test ./db

Examples

See example_test.go for runnable examples demonstrating initialization, read/write helpers, and transactions. Running go test ./db executes them along with the rest of the test suite.

Documentation

Overview

Package db provides SQLite access using modernc.org/sqlite (no CGO). Goals: performance, concurrency and predictability with minimal dependencies. - Separate pools: one writer (RW) and many readers (RO). - WAL + synchronous=NORMAL + busy_timeout. - Short transactions; no per-operation context timeouts in this layer. - WAL checkpoint on Close() for hygiene.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// ErrNotFound is returned when an entity, attribute, record, or value is not found.
	ErrNotFound = errors.New("eav: not found")

	// ErrConflict is returned when an optimistic lock fails (rev mismatch).
	ErrConflict = errors.New("eav: conflict")

	// ErrInvalidValue is returned when a value violates type or column rules.
	ErrInvalidValue = errors.New("eav: invalid value")
)

Sentinel errors for EAV operations.

Functions

func IsValidUsername 

func IsValidUsername(username string) error

IsValidUsername validates a username to prevent abuse.

func RunMigration 

func RunMigration() error

RunMigration applies all pending migrations in order:

  1. Engine migrations (0001-0999) from devengine/db
  2. Application migrations (1000+) from the configured AppFS

Migrations are sorted lexicographically by ID, ensuring engine migrations run before application migrations due to the numbering convention. RunMigration applies all pending migrations using the global Storage.

func RunMigrationOn 

func RunMigrationOn(s *SQLite) error

RunMigrationOn applies all pending migrations on the provided SQLite instance.

func SetAppMigrationsFS 

func SetAppMigrationsFS(fsys fs.FS)

SetAppMigrationsFS configures an optional fs.FS containing application migrations. Files must follow the pattern: NNNN_name.up.sql where NNNN is a 4-digit prefix (e.g., 1000_characters.up.sql).

Migration ordering:

  • Engine migrations (devengine): 0001-0999
  • Application migrations: 1000-9999

Call this function before RunMigration() in your application's main.go: 

db.SetAppMigrationsFS(migrations.FS)
err := db.RunMigration()

Types

type DescendantItem added in v0.0.6 

type DescendantItem struct {
	MenuItem
	Depth int
}

DescendantItem represents a menu item with its depth in the hierarchy.

func CollectMenuDescendants added in v0.0.6 

func CollectMenuDescendants(items []MenuItem, parentID int64) []DescendantItem

CollectMenuDescendants recursively collects all descendants of a given parent ID. It includes cycle detection: if an item ID is visited more than once, its children are not processed to prevent infinite recursion.

type EAVAttribute 

type EAVAttribute struct {
	ID            int64  `json:"id"`
	ReferenceID   string `json:"reference_id"`
	EntityTypeID  int64  `json:"entity_type_id"`
	MachineName   string `json:"machine_name"`
	Label         string `json:"label"`
	HelpText      string `json:"help_text"`
	PrimitiveKind string `json:"primitive_kind"` // BOOL, INT, REAL, TEXT, DATETIME
	IsRequired    bool   `json:"is_required"`
	IsUnique      bool   `json:"is_unique"`
	IsIndexed     bool   `json:"is_indexed"`
	MaxLength     *int   `json:"max_length,omitempty"` // For TEXT fields, NULL for other types
	IsComputed    bool   `json:"is_computed"`
	ComputedExpr  string `json:"computed_expr"` // Filo expression
	// Default values for new records (user-defined)
	DefaultVBool     *bool     `json:"default_v_bool,omitempty"`
	DefaultVInt      *int64    `json:"default_v_int,omitempty"`
	DefaultVReal     *float64  `json:"default_v_real,omitempty"`
	DefaultVText     *string   `json:"default_v_text,omitempty"`
	DefaultVDatetime *string   `json:"default_v_datetime,omitempty"`
	CreatedAt        time.Time `json:"created_at"`
	UpdatedAt        time.Time `json:"updated_at"`
	DeletedAt        time.Time `json:"deleted_at,omitempty"` // zero value means not deleted
}

EAVAttribute represents a typed field (column) belonging to an entity type.

type EAVEntityType 

type EAVEntityType struct {
	ID          int64     `json:"id"`
	ReferenceID string    `json:"reference_id"`
	MachineName string    `json:"machine_name"`
	Name        string    `json:"name"`
	Description string    `json:"description"`
	PreSave     string    `json:"pre_save"` // Filo script executed before saving records
	PosLoad     string    `json:"pos_load"` // Filo script executed after loading records
	CreatedAt   time.Time `json:"created_at"`
	UpdatedAt   time.Time `json:"updated_at"`
	DeletedAt   time.Time `json:"deleted_at,omitempty"` // zero value means not deleted
}

EAVEntityType represents a logical schema (similar to a table in relational databases). This is the new EAV core implementation without workspace or forms concepts.

type EAVRecord 

type EAVRecord struct {
	ID           int64     `json:"id"`
	ReferenceID  string    `json:"reference_id"`
	EntityTypeID int64     `json:"entity_type_id"`
	Status       string    `json:"status"` // 'draft' or 'active'
	Rev          int       `json:"rev"`    // optimistic lock counter, starts at 1
	CreatedAt    time.Time `json:"created_at"`
	UpdatedAt    time.Time `json:"updated_at"`
	DeletedAt    time.Time `json:"deleted_at,omitempty"` // zero value means not deleted
}

EAVRecord represents an instance (row) of an entity type.

type EAVRecordValues added in v0.0.2 

type EAVRecordValues map[string]interface{}

EAVRecordValues holds typed values keyed by attribute machine_name. Values are typed as interface{} and must be one of: bool, int64, float64, string, nil.

func ExecutePosLoadScript added in v0.0.3 

func ExecutePosLoadScript(
	entityType *EAVEntityType,
	values EAVRecordValues,
) (modifiedValues EAVRecordValues, userError string, err error)

ExecutePosLoadScript runs the entity type's pos_load Filo script. This is called after loading record data and applying defaults, but before display. It is the last transformation step before the user sees the data.

Parameters:

  • entityType: The entity type containing the pos_load script
  • values: Current field values keyed by attribute machine_name (after defaults applied)

Returns:

  • modifiedValues: Values after script execution (may be modified by script)
  • userError: User-facing error message if script set "error" variable
  • err: System error if script execution failed

func ExecutePreSaveScript added in v0.0.3 

func ExecutePreSaveScript(
	entityType *EAVEntityType,
	values EAVRecordValues,
) (modifiedValues EAVRecordValues, userError string, err error)

ExecutePreSaveScript runs the entity type's pre_save Filo script. This is a core EAV function that must be called before any record is saved, regardless of the caller (sysop tools, forms, APIs, etc.).

Parameters:

  • entityType: The entity type containing the pre_save script
  • values: Current field values keyed by attribute machine_name

Returns:

  • modifiedValues: Values after script execution (may be modified by script)
  • userError: User-facing error message if script set "error" variable
  • err: System error if script execution failed

func ExecutePreSaveScriptWithSetup added in v0.0.3 

func ExecutePreSaveScriptWithSetup(
	entityType *EAVEntityType,
	values EAVRecordValues,
	setup ScriptEngineSetupFunc,
) (modifiedValues EAVRecordValues, userError string, err error)

ExecutePreSaveScriptWithSetup runs the pre_save script with a custom engine setup. This allows the caller to inject custom builtins (like DB access with a transaction).

Parameters:

  • entityType: The entity type containing the pre_save script
  • values: Current field values keyed by attribute machine_name
  • setup: Custom function to configure the Filo engine (register builtins)

Returns:

  • modifiedValues: Values after script execution (may be modified by script)
  • userError: User-facing error message if script set "error" variable
  • err: System error if script execution failed

type EAVValue 

type EAVValue struct {
	RecordID    int64     `json:"record_id"`
	AttributeID int64     `json:"attribute_id"`
	VBool       *bool     `json:"v_bool,omitempty"`
	VInt        *int64    `json:"v_int,omitempty"`
	VReal       *float64  `json:"v_real,omitempty"`
	VText       *string   `json:"v_text,omitempty"`
	VDatetime   *string   `json:"v_datetime,omitempty"` // ISO-8601 UTC
	UpdatedAt   time.Time `json:"updated_at"`
}

EAVValue represents a typed cell value for a (record, attribute) pair. Exactly one of the v_* fields should be set based on the attribute's primitive_kind.

type File 

type File struct {
	ID               int64
	UserID           int64
	OriginalFilename string
	Filename         string
	Filesize         int64
	Filetype         string
	Filehash         string
	Filetag          string
	Filedescription  string
	Processed        bool
	CreatedAt        string
	UpdatedAt        string
}

type Form added in v0.0.4 

type Form struct {
	ID               int64
	ReferenceID      string
	MachineName      string
	Label            string
	Description      string
	EAVEntityTypeID  *int64 // NULL if not bound to EAV
	HideSubmitButton bool   // When true, form does not display submit button
	HideCancelButton bool   // When true, form does not display cancel button
	HideTitle        bool   // When true, form does not display title header
	ShowSystemInfo   bool   // When true, displays record ID and status in runtime
	MenuID           *int64 // Associated menu for navbar display when form is active
	CreatedAt        time.Time
	UpdatedAt        time.Time
	DeletedAt        *time.Time
}

Form represents a form definition.

type FormElement added in v0.0.4 

type FormElement struct {
	ID             int64
	ReferenceID    string
	FormID         int64
	ParentID       *int64 // NULL for root-level elements
	MachineName    string
	ElementKind    string // 'group', 'field', 'divider', 'button', etc.
	Label          string
	HelpText       string
	ZOrder         int
	ColSpan        int    // Bootstrap column span (1-12, default 12)
	Alignment      string // Horizontal alignment: 'left', 'center', 'right'
	UIKind         string // Plugin ID
	UIMetaJSON     string
	EAVAttributeID *int64 // NULL for UI-only elements
	IsUIOnly       bool
	IsReadonly     bool
	HideLabel      bool
	HideHelpText   bool
	ValidateExpr   string
	ComputedExpr   string
	// Button-specific properties (only used when ElementKind = 'button')
	ButtonFiloCode   string // Server-side Filo script (never sent to client)
	ButtonRunSave    bool   // Execute save action in same transaction
	ButtonJSCode     string // Client-side JavaScript
	ButtonStyle      string // Bootstrap button style (primary, secondary, etc.)
	ButtonConfirmMsg string // Confirmation dialog text
	CreatedAt        time.Time
	UpdatedAt        time.Time
	DeletedAt        *time.Time
}

FormElement represents a UI element within a form (field, group, divider, etc.).

func SortElementsHierarchically added in v0.0.4 

func SortElementsHierarchically(elements []FormElement) []FormElement

SortElementsHierarchically sorts elements so that children appear immediately after their parent, respecting z_order within each level. This creates a flat list suitable for display in a table while preserving hierarchy. 

type Menu struct {
	ID          int64
	ReferenceID string
	MachineName string
	Label       string
	Description string
	CreatedAt   time.Time
	UpdatedAt   time.Time
	DeletedAt   *time.Time
}

Menu represents a menu definition. 

type MenuItem struct {
	ID          int64
	ReferenceID string
	MenuID      int64
	ParentID    *int64 // NULL for root-level items
	MachineName string
	Label       string
	Icon        string // Optional Bootstrap icon class
	ItemType    string // 'link', 'separator', 'submenu'
	URL         string // Optional, for links
	JSCode      string // JavaScript code to execute client-side
	FiloCode    string // Filo code to execute server-side
	ZOrder      int
	CreatedAt   time.Time
	UpdatedAt   time.Time
	DeletedAt   *time.Time
}

MenuItem represents a menu item.

func GetDirectChildren added in v0.0.6 

func GetDirectChildren(items []MenuItem, parentID int64) []MenuItem

GetDirectChildren returns only the direct children of a parent ID from the items slice.

func SortMenuItemsHierarchically added in v0.0.6 

func SortMenuItemsHierarchically(items []MenuItem) []MenuItem

SortMenuItemsHierarchically sorts items so that children appear immediately after their parent, respecting z_order within each level. 

type MenuItemNode struct {
	MenuItem
	Children        []MenuItemNode
	MenuMachineName string // Menu machine name for action URLs
}

MenuItemNode represents a menu item with its children as a tree structure.

func BuildMenuItemTree added in v0.0.6 

func BuildMenuItemTree(items []MenuItem) []MenuItemNode

BuildMenuItemTree builds a tree structure for menu items. It returns only the root-level items (items with no parent), each with their children populated recursively.

func BuildMenuItemTreeWithName added in v0.0.6 

func BuildMenuItemTreeWithName(items []MenuItem, menuMachineName string) []MenuItemNode

BuildMenuItemTreeWithName builds a tree structure with the menu machine name propagated to all nodes.

type Row 

type Row struct {
	// contains filtered or unexported fields
}

Row wraps sql.Row to keep a uniform return type and allow future extension. No context cancellation is used at this layer.

func (*Row) Err 

func (r *Row) Err() error

Err mirrors (*sql.Row).Err.

func (*Row) Scan 

func (r *Row) Scan(dest ...any) error

Scan delegates to the underlying sql.Row.

type SQLite 

type SQLite struct {
	// contains filtered or unexported fields
}

SQLite holds separate read/write pools. 

var (
	// Storage keeps a global handle for convenience (preserves your original pattern).
	Storage *SQLite

	ErrNoRows = sql.ErrNoRows
)

func New 

func New() (*SQLite, error)

New initializes RW/RO pools. Uses config.Cfg.DBFile as the SQLite path/URI;

func NewWithPath 

func NewWithPath(path string) (*SQLite, error)

NewWithPath creates SQLite pools for a specific file/URI path.

Example

ExampleNewWithPath shows how to open a temporary database, create a table, and read data using the helper methods that apply timeouts automatically. 

package main

import (
	"fmt"
	"os"
	"path/filepath"

	"github.com/crgimenes/devengine/db"
)

func main() {
	tmpDir, err := os.MkdirTemp("", "db-example-")
	if err != nil {
		panic(err)
	}
	defer os.RemoveAll(tmpDir)

	store, err := db.NewWithPath(filepath.Join(tmpDir, "example.db"))
	if err != nil {
		panic(err)
	}
	defer store.Close()

	if err := store.Exec(`CREATE TABLE items(name TEXT NOT NULL)`); err != nil {
		panic(err)
	}
	if err := store.Exec(`INSERT INTO items(name) VALUES(?)`, "widget"); err != nil {
		panic(err)
	}

	var name string
	if err := store.QueryRow(`SELECT name FROM items`).Scan(&name); err != nil {
		panic(err)
	}

	fmt.Println("name:", name)
}

Output:

name: widget

func (*SQLite) BeginTransaction 

func (s *SQLite) BeginTransaction() (*Transaction, error)

BeginTransaction starts a write transaction.

IMPORTANT: We intentionally do not propagate context timeouts in this package. Callers should avoid long-lived transactions; SQLite busy_timeout handles transient contention, and application code should keep critical sections short.

func (*SQLite) CheckEAVValueUnique 

func (s *SQLite) CheckEAVValueUnique(
	attributeID int64,
	primitiveKind string,
	value interface{},
	excludeRecordID int64,
) (bool, error)

CheckEAVValueUnique verifies if a value is unique for a given attribute. Returns true if the value is unique (or NULL), false if a duplicate exists.

Parameters:

  • attributeID: The attribute to check
  • primitiveKind: Type of the attribute (BOOL, INT, REAL, TEXT, DATETIME)
  • value: The value to check (must match primitiveKind type)
  • excludeRecordID: Record ID to exclude from check (use 0 for new records)

Rules:

  • NULL values are always considered unique (multiple NULLs allowed)
  • Only checks against active (non-deleted) records
  • Uses read-only pool for optimal concurrency

func (*SQLite) CheckpointWAL 

func (s *SQLite) CheckpointWAL() error

CheckpointWAL triggers a WAL checkpoint with TRUNCATE.

func (*SQLite) Close 

func (s *SQLite) Close()

Close closes pools; performs a best-effort WAL checkpoint first.

func (*SQLite) ConsumeMagicLinkToken 

func (s *SQLite) ConsumeMagicLinkToken(token string) (string, error)

ConsumeMagicLinkToken validates and consumes a magic link token, returning the associated email.

func (*SQLite) CountFilesByUserID 

func (s *SQLite) CountFilesByUserID(userID int64) (int, error)

CountFilesByUserID returns the total count of non-deleted files for a user.

func (*SQLite) CountUsersWithUsernamePrefix 

func (s *SQLite) CountUsersWithUsernamePrefix(prefix string) (int, error)

CountUsersWithUsernamePrefix counts how many users have a username starting with the given prefix (case-insensitive). Used for generating unique usernames when conflicts occur.

func (*SQLite) CreateEAVAttribute 

func (s *SQLite) CreateEAVAttribute(
	entityTypeID int64,
	machineName, label, helpText, primitiveKind string,
	isRequired, isUnique, isIndexed bool,
	maxLength *int,
	isComputed bool,
	computedExpr string,
	defaultVBool *bool,
	defaultVInt *int64,
	defaultVReal *float64,
	defaultVText, defaultVDatetime *string,
) (*EAVAttribute, error)

CreateEAVAttribute creates a new attribute with validation and default values.

func (*SQLite) CreateEAVEntityType 

func (s *SQLite) CreateEAVEntityType(name, machineName, description, preSave, posLoad string) (*EAVEntityType, error)

CreateEAVEntityType creates a new entity type with an opaque reference_id.

func (*SQLite) CreateEAVRecord 

func (s *SQLite) CreateEAVRecord(entityTypeID int64) (*EAVRecord, error)

CreateEAVRecord creates a new record with rev=1 and opaque reference_id.

func (*SQLite) CreateForm added in v0.0.4 

func (s *SQLite) CreateForm(machineName, label, description string, eavEntityTypeID *int64) (*Form, error)

CreateForm creates a new form.

func (*SQLite) CreateFormElement added in v0.0.4 

func (s *SQLite) CreateFormElement(
	formID int64,
	parentID *int64,
	machineName, elementKind, label, helpText string,
	zOrder, colSpan int,
	uiKind, uiMetaJSON string,
	eavAttributeID *int64,
	isUIOnly, isReadonly bool,
) (*FormElement, error)

CreateFormElement creates a new form element.

func (*SQLite) CreateMenu added in v0.0.6 

func (s *SQLite) CreateMenu(machineName, label, description string) (*Menu, error)

CreateMenu creates a new menu.

func (*SQLite) CreateMenuItem added in v0.0.6 

func (s *SQLite) CreateMenuItem(
	menuID int64,
	parentID *int64,
	machineName, label, icon, itemType, url, jsCode, filoCode string,
	zOrder int,
) (*MenuItem, error)

CreateMenuItem creates a new menu item.

func (*SQLite) CreateMinimalUserForOAuthFallback 

func (s *SQLite) CreateMinimalUserForOAuthFallback(
	email string,
	avatarURL string,
) (*User, error)

CreateMinimalUserForOAuthFallback creates a minimal user account with email only when OAuth signup fails. This allows the user to complete their profile at /me. Returns error if email is empty or if database operation fails.

func (*SQLite) DeleteEAVValue 

func (s *SQLite) DeleteEAVValue(recordID, attributeID int64) error

DeleteEAVValue removes a value for a (record, attribute) pair.

func (*SQLite) DeleteFormElement added in v0.0.4 

func (s *SQLite) DeleteFormElement(id int64) error

DeleteFormElement permanently deletes a form element.

func (*SQLite) DeleteMenuItem added in v0.0.6 

func (s *SQLite) DeleteMenuItem(id int64) error

DeleteMenuItem permanently deletes a menu item.

func (*SQLite) Exec 

func (s *SQLite) Exec(query string, args ...any) error

Exec executes a write statement on the RW pool (outside explicit transactions).

func (*SQLite) GenerateUniqueUsername 

func (s *SQLite) GenerateUniqueUsername(baseUsername string) (string, error)

GenerateUniqueUsername generates a unique username by appending a number if the base username is taken. If baseUsername is available, returns it unchanged. Otherwise, appends 1, 2, 3, etc. until a unique username is found.

func (*SQLite) GetEAVAttributeByID 

func (s *SQLite) GetEAVAttributeByID(id int64) (*EAVAttribute, error)

GetEAVAttributeByID retrieves an attribute by internal ID.

func (*SQLite) GetEAVAttributeByRefID 

func (s *SQLite) GetEAVAttributeByRefID(refID string) (*EAVAttribute, error)

GetEAVAttributeByRefID retrieves an attribute by opaque reference_id.

func (*SQLite) GetEAVEntityTypeByID 

func (s *SQLite) GetEAVEntityTypeByID(id int64) (*EAVEntityType, error)

GetEAVEntityTypeByID retrieves an entity type by internal ID (for internal use).

func (*SQLite) GetEAVEntityTypeByMachineName 

func (s *SQLite) GetEAVEntityTypeByMachineName(machineName string) (*EAVEntityType, error)

GetEAVEntityTypeByMachineName retrieves an entity type by machine_name (for code/routing).

func (*SQLite) GetEAVEntityTypeByRefID 

func (s *SQLite) GetEAVEntityTypeByRefID(refID string) (*EAVEntityType, error)

GetEAVEntityTypeByRefID retrieves an entity type by opaque reference_id (for external APIs).

func (*SQLite) GetEAVRecordByID 

func (s *SQLite) GetEAVRecordByID(id int64) (*EAVRecord, error)

GetEAVRecordByID retrieves a record by internal ID.

func (*SQLite) GetEAVRecordByRefID 

func (s *SQLite) GetEAVRecordByRefID(refID string) (*EAVRecord, error)

GetEAVRecordByRefID retrieves a record by opaque reference_id.

func (*SQLite) GetEAVValuesByRecordID 

func (s *SQLite) GetEAVValuesByRecordID(recordID int64) ([]EAVValue, error)

GetEAVValuesByRecordID retrieves all values for a record in a single query, excluding values for soft-deleted attributes.

func (*SQLite) GetFileByUserIDAndFilename 

func (s *SQLite) GetFileByUserIDAndFilename(
	userID int64,
	filename string,
) (*File, error)

GetFileByUserIDAndFilename retrieves a file by user ID and filename.

func (*SQLite) GetFileByUserReferenceIDAndFilename 

func (s *SQLite) GetFileByUserReferenceIDAndFilename(
	userRefID string,
	filename string,
) (*File, error)

GetFileByUserReferenceIDAndFilename retrieves a file by user reference ID and filename.

func (*SQLite) GetFormByMachineName added in v0.0.4 

func (s *SQLite) GetFormByMachineName(machineName string) (*Form, error)

GetFormByMachineName retrieves a form by machine_name. Returns nil, nil if not found (soft not-found to allow fallback logic).

func (*SQLite) GetFormByRefID added in v0.0.4 

func (s *SQLite) GetFormByRefID(refID string) (*Form, error)

GetFormByRefID retrieves a form by reference_id.

func (*SQLite) GetFormElementByRefID added in v0.0.4 

func (s *SQLite) GetFormElementByRefID(refID string) (*FormElement, error)

GetFormElementByRefID retrieves a form element by reference_id.

func (*SQLite) GetMenuByID added in v0.0.6 

func (s *SQLite) GetMenuByID(id int64) (*Menu, error)

GetMenuByID retrieves a menu by its ID. Returns nil, nil if not found.

func (*SQLite) GetMenuByMachineName added in v0.0.6 

func (s *SQLite) GetMenuByMachineName(machineName string) (*Menu, error)

GetMenuByMachineName retrieves a menu by machine_name. Returns nil, nil if not found (soft not-found to allow fallback logic).

func (*SQLite) GetMenuByRefID added in v0.0.6 

func (s *SQLite) GetMenuByRefID(refID string) (*Menu, error)

GetMenuByRefID retrieves a menu by reference_id.

func (*SQLite) GetMenuItemByRefID added in v0.0.6 

func (s *SQLite) GetMenuItemByRefID(refID string) (*MenuItem, error)

GetMenuItemByRefID retrieves a menu item by reference_id.

func (*SQLite) GetUserByEmail 

func (s *SQLite) GetUserByEmail(email string) (*User, error)

GetUserByEmail retrieves a user by their email address. Returns error if user not found.

func (*SQLite) GetUserByID 

func (s *SQLite) GetUserByID(userID int64) (*User, error)

GetUserByID retrieves a user by their ID.

func (*SQLite) GetUserByOAuthProviderID 

func (s *SQLite) GetUserByOAuthProviderID(provider string, providerID string) (int64, error)

GetUserByOAuthProviderID retrieves a user ID by OAuth provider and provider ID.

func (*SQLite) GetUserOrCreateByEmail 

func (s *SQLite) GetUserOrCreateByEmail(email string) (*User, error)

GetUserOrCreateByEmail creates or retrieves a user by email.

func (*SQLite) GetUserOrCreateByOAuth 

func (s *SQLite) GetUserOrCreateByOAuth(
	provider string,
	providerID string,
	email string,
	username string,
	avatarURL string,
) (*User, error)

GetUserOrCreateByOAuth creates or retrieves user via OAuth provider.

func (*SQLite) ListEAVAttributesByEntityTypeID 

func (s *SQLite) ListEAVAttributesByEntityTypeID(entityTypeID int64) ([]EAVAttribute, error)

ListEAVAttributesByEntityTypeID returns all active attributes for an entity type, ordered by machine_name for predictable iteration.

func (*SQLite) ListEAVEntityTypes 

func (s *SQLite) ListEAVEntityTypes() ([]EAVEntityType, error)

ListEAVEntityTypes returns all active entity types ordered by machine_name.

func (*SQLite) ListEAVRecordsByEntityTypeID 

func (s *SQLite) ListEAVRecordsByEntityTypeID(entityTypeID int64, limit, offset int) ([]EAVRecord, int, error)

ListEAVRecordsByEntityTypeID returns paginated records for an entity type, ordered by updated_at DESC, id DESC. Returns records and total count.

func (*SQLite) ListFilesByUserID 

func (s *SQLite) ListFilesByUserID(
	userID int64,
	offset int,
	limit int,
) ([]*File, error)

ListFilesByUserID returns files for a user with pagination.

func (*SQLite) ListFilesByUserIDSorted 

func (s *SQLite) ListFilesByUserIDSorted(
	userID int64,
	sort string,
	offset int,
	limit int,
) ([]*File, error)

ListFilesByUserIDSorted returns files for a user with explicit sort option. sort supports: "date_desc" (default) and "name_asc".

func (*SQLite) ListFormElements added in v0.0.4 

func (s *SQLite) ListFormElements(formID int64) ([]FormElement, error)

ListFormElements returns all non-deleted elements for a form, ordered by z_order.

func (*SQLite) ListForms added in v0.0.4 

func (s *SQLite) ListForms() ([]Form, error)

ListForms returns all non-deleted forms.

func (*SQLite) ListGroupElements added in v0.0.4 

func (s *SQLite) ListGroupElements(formID int64) ([]FormElement, error)

ListGroupElements returns elements that can be parents (groups, accordions, cards, tabs).

func (*SQLite) ListMenuItems added in v0.0.6 

func (s *SQLite) ListMenuItems(menuID int64) ([]MenuItem, error)

ListMenuItems returns all non-deleted items for a menu, ordered by z_order.

func (*SQLite) ListMenus added in v0.0.6 

func (s *SQLite) ListMenus() ([]Menu, error)

ListMenus returns all non-deleted menus.

func (*SQLite) ListRelationalTables 

func (s *SQLite) ListRelationalTables() ([]TableInfo, error)

ListRelationalTables returns all user-created relational tables from the SQLite schema. Excludes system tables, migrations, FTS tables, and EAV/Forms core tables.

func (*SQLite) ListSubmenuItems added in v0.0.6 

func (s *SQLite) ListSubmenuItems(menuID int64) ([]MenuItem, error)

ListSubmenuItems returns items that can be parents (type 'submenu').

func (*SQLite) MergeOAuthProfileData 

func (s *SQLite) MergeOAuthProfileData(
	userID int64,
	oauthUsername string,
	oauthAvatarURL string,
) (*User, error)

MergeOAuthProfileData merges OAuth provider data into existing user, updating only blank fields. It updates username (with conflict resolution) if Username is empty and oauthUsername is provided. It updates avatar_url if user.AvatarURL is empty and oauthAvatarURL is provided. Returns updated user with enabled=true if both username and email are now present, or error.

func (*SQLite) MoveElementDown added in v0.0.4 

func (s *SQLite) MoveElementDown(elementID int64) error

MoveElementDown swaps this element's z_order with the next element (higher z_order, same parent).

func (*SQLite) MoveElementUp added in v0.0.4 

func (s *SQLite) MoveElementUp(elementID int64) error

MoveElementUp swaps this element's z_order with the previous element (lower z_order, same parent).

func (*SQLite) MoveMenuItemDown added in v0.0.6 

func (s *SQLite) MoveMenuItemDown(itemID int64) error

MoveMenuItemDown swaps this item's z_order with the next item (higher z_order, same parent).

func (*SQLite) MoveMenuItemUp added in v0.0.6 

func (s *SQLite) MoveMenuItemUp(itemID int64) error

MoveMenuItemUp swaps this item's z_order with the previous item (lower z_order, same parent).

func (*SQLite) PurgeExpiredMagicLinkTokens 

func (s *SQLite) PurgeExpiredMagicLinkTokens() error

PurgeExpiredMagicLinkTokens removes expired magic link tokens from the database.

func (*SQLite) Query 

func (s *SQLite) Query(query string, args ...any) (*sql.Rows, error)

Query executes a SELECT on the RO pool (parallel reads).

func (*SQLite) QueryRW 

func (s *SQLite) QueryRW(query string, args ...any) (*sql.Rows, error)

QueryRW allows SELECT using the RW pool (rarely needed).

func (*SQLite) QueryRow 

func (s *SQLite) QueryRow(query string, args ...any) *Row

QueryRow executes a single-row SELECT on the RO pool.

func (*SQLite) QueryRowRW 

func (s *SQLite) QueryRowRW(query string, args ...any) *Row

func (*SQLite) RO added in v0.0.3 

func (s *SQLite) RO() *sql.DB

RO returns the read-only database connection. This is useful for external integrations that need direct access to sql.DB.

func (*SQLite) RW added in v0.0.3 

func (s *SQLite) RW() *sql.DB

RW returns the read-write database connection. This is useful for external integrations that need direct access to sql.DB.

func (*SQLite) SaveFileMetadata 

func (s *SQLite) SaveFileMetadata(f *File) (*File, error)

SaveFileMetadata saves file metadata to the database.

func (*SQLite) SearchFilesByUserIDFTS 

func (s *SQLite) SearchFilesByUserIDFTS(
	userID int64,
	query string,
	sort string,
	offset int,
	limit int,
) ([]*File, error)

SearchFilesByUserIDFTS performs a full-text search across original_filename, filename, filetag, and filedescription using the FTS virtual table. Returns paginated results for a given user. Sort supports: "date_desc" (default) and "name_asc".

func (*SQLite) SoftDeleteEAVAttribute 

func (s *SQLite) SoftDeleteEAVAttribute(id int64) error

SoftDeleteEAVAttribute marks an attribute as deleted.

func (*SQLite) SoftDeleteEAVEntityType 

func (s *SQLite) SoftDeleteEAVEntityType(id int64) error

SoftDeleteEAVEntityType marks an entity type as deleted.

func (*SQLite) SoftDeleteEAVRecord 

func (s *SQLite) SoftDeleteEAVRecord(id int64) error

SoftDeleteEAVRecord marks a record as deleted.

func (*SQLite) SoftDeleteFileByUserAndFilename 

func (s *SQLite) SoftDeleteFileByUserAndFilename(
	userID int64,
	filename string,
) error

SoftDeleteFileByUserAndFilename marks a file as deleted (soft delete) for a specific user and filename. It does not remove file contents from disk; a background worker may perform physical deletion later.

func (*SQLite) SoftDeleteForm added in v0.0.4 

func (s *SQLite) SoftDeleteForm(id int64) error

SoftDeleteForm soft-deletes a form.

func (*SQLite) SoftDeleteMenu added in v0.0.6 

func (s *SQLite) SoftDeleteMenu(id int64) error

SoftDeleteMenu soft-deletes a menu.

func (*SQLite) StoreMagicLinkToken 

func (s *SQLite) StoreMagicLinkToken(
	token string,
	email string,
	expiresAt time.Time) error

StoreMagicLinkToken stores a magic link token for email-based authentication.

func (*SQLite) UpdateEAVAttribute 

func (s *SQLite) UpdateEAVAttribute(
	id int64,
	machineName, label, helpText, primitiveKind string,
	isRequired, isUnique, isIndexed bool,
	maxLength *int,
	isComputed bool,
	computedExpr string,
	defaultVBool *bool,
	defaultVInt *int64,
	defaultVReal *float64,
	defaultVText, defaultVDatetime *string,
) (*EAVAttribute, error)

UpdateEAVAttribute updates an existing attribute including default values.

func (*SQLite) UpdateEAVEntityType added in v0.0.2 

func (s *SQLite) UpdateEAVEntityType(id int64, name, description, preSave, posLoad string) (*EAVEntityType, error)

UpdateEAVEntityType updates an existing entity type's metadata.

func (*SQLite) UpdateEAVRecordRev 

func (s *SQLite) UpdateEAVRecordRev(id int64, currentRev int) (int, error)

UpdateEAVRecordRev implements optimistic locking by incrementing rev only if currentRev matches. The validation is enforced by a SQLite trigger (trg_eav_records_update_rev). Returns the new rev on success, or ErrConflict if the rev doesn't match or trigger fails.

func (*SQLite) UpdateEAVRecordStatus 

func (s *SQLite) UpdateEAVRecordStatus(id int64, currentRev int, status string) error

UpdateEAVRecordStatus updates the status of a record (e.g., from 'draft' to 'active') and increments rev for optimistic locking

func (*SQLite) UpdateFileMetadataByUserAndFilename 

func (s *SQLite) UpdateFileMetadataByUserAndFilename(
	userID int64,
	filename string,
	description string,
	tag string,
) error

UpdateFileMetadataByUserAndFilename updates the file description and tag for a file owned by the given user, only if it is not deleted.

func (*SQLite) UpdateForm added in v0.0.4 

func (s *SQLite) UpdateForm(id int64, machineName, label, description string, eavEntityTypeID *int64, hideSubmitButton, hideCancelButton, hideTitle, showSystemInfo bool, menuID *int64) error

UpdateForm updates a form's basic info.

func (*SQLite) UpdateFormElement added in v0.0.4 

func (s *SQLite) UpdateFormElement(
	id int64,
	parentID *int64,
	machineName, elementKind, label, helpText string,
	zOrder, colSpan int,
	alignment string,
	uiKind, uiMetaJSON string,
	eavAttributeID *int64,
	isUIOnly, isReadonly, hideLabel, hideHelpText bool,
	buttonFiloCode string, buttonRunSave bool, buttonJSCode, buttonStyle, buttonConfirmMsg string,
) error

UpdateFormElement updates an existing form element.

func (*SQLite) UpdateMenu added in v0.0.6 

func (s *SQLite) UpdateMenu(id int64, machineName, label, description string) error

UpdateMenu updates a menu's basic info.

func (*SQLite) UpdateMenuItem added in v0.0.6 

func (s *SQLite) UpdateMenuItem(
	id int64,
	parentID *int64,
	machineName, label, icon, itemType, url, jsCode, filoCode string,
	zOrder int,
) error

UpdateMenuItem updates an existing menu item.

func (*SQLite) UpdateUserProfile 

func (s *SQLite) UpdateUserProfile(
	userID int64,
	username string,
	avatarURL string,
) (*User, error)

UpdateUserProfile updates username, avatar_url and enables user if email is already validated (email must be non-empty). Validates that username and email are not duplicated (case-insensitive). Returns the updated user or error if validation fails or SQL error occurs.

func (*SQLite) UpsertEAVValue 

func (s *SQLite) UpsertEAVValue(
	recordID, attributeID int64,
	vBool *bool, vInt *int64, vReal *float64, vText, vDatetime *string,
) error

UpsertEAVValue inserts or updates a value for a (record, attribute) pair. Validates that exactly one value is non-nil and matches the attribute's primitive_kind.

func (*SQLite) UpsertEAVValueWithRev 

func (s *SQLite) UpsertEAVValueWithRev(
	recordID, attributeID int64,
	currentRev int,
	vBool *bool, vInt *int64, vReal *float64, vText, vDatetime *string,
) (int, error)

UpsertEAVValueWithRev inserts or updates a value for a (record, attribute) pair and increments the record's rev for optimistic locking. The rev increment validation is enforced by a SQLite trigger (trg_eav_records_update_rev). Returns the new rev on success, or ErrConflict if currentRev doesn't match.

type ScriptEngineSetupFunc added in v0.0.3 

type ScriptEngineSetupFunc func(*filo.Engine)

ScriptEngineSetupFunc is a function that configures a Filo engine before execution. This allows callers to register additional builtins (like DB access) without creating circular dependencies. 

var CurrentScriptSetup ScriptEngineSetupFunc = DefaultScriptSetup

CurrentScriptSetup is the active setup function. Set this at application startup to register additional builtins like DB access. 

var DefaultScriptSetup ScriptEngineSetupFunc = func(eng *filo.Engine) {
	filostrings.RegisterBuiltins(eng)
}

DefaultScriptSetup is the default setup that only registers string builtins.

type TableInfo 

type TableInfo struct {
	Name string // table name
	SQL  string // CREATE TABLE statement
}

TableInfo represents basic metadata about a database table.

type Transaction 

type Transaction struct {
	// contains filtered or unexported fields
}

Transaction wraps a write transaction.

Example

ExampleTransaction demonstrates how to use explicit transactions for grouped writes. 

package main

import (
	"fmt"
	"os"
	"path/filepath"

	"github.com/crgimenes/devengine/db"
)

func main() {
	tmpDir, err := os.MkdirTemp("", "db-example-tx-")
	if err != nil {
		panic(err)
	}
	defer os.RemoveAll(tmpDir)

	store, err := db.NewWithPath(filepath.Join(tmpDir, "example-tx.db"))
	if err != nil {
		panic(err)
	}
	defer store.Close()

	if err := store.Exec(`CREATE TABLE kv(k TEXT PRIMARY KEY, v TEXT NOT NULL)`); err != nil {
		panic(err)
	}

	tx, err := store.BeginTransaction()
	if err != nil {
		panic(err)
	}
	if err := tx.Exec(`INSERT INTO kv(k, v) VALUES(?, ?)`, "a", "committed"); err != nil {
		_ = tx.Rollback()
		panic(err)
	}
	if err := tx.Commit(); err != nil {
		panic(err)
	}

	tx2, err := store.BeginTransaction()
	if err != nil {
		panic(err)
	}
	if err := tx2.Exec(`INSERT INTO kv(k, v) VALUES(?, ?)`, "b", "rolled-back"); err != nil {
		_ = tx2.Rollback()
		panic(err)
	}
	if err := tx2.Rollback(); err != nil {
		panic(err)
	}

	var value string
	if err := store.QueryRow(`SELECT v FROM kv WHERE k = ?`, "a").Scan(&value); err != nil {
		panic(err)
	}

	fmt.Println("stored value:", value)
}

Output:

stored value: committed

func (*Transaction) Commit 

func (t *Transaction) Commit() error

Commit finalizes a transaction; on error, attempts a rollback.

func (*Transaction) Exec 

func (t *Transaction) Exec(query string, args ...any) error

Exec executes a write statement inside the transaction.

func (*Transaction) Query 

func (t *Transaction) Query(query string, args ...any) (*sql.Rows, error)

Query runs a SELECT inside the transaction (consistent view).

func (*Transaction) QueryRow 

func (t *Transaction) QueryRow(query string, args ...any) *Row

QueryRow returns a single row inside the transaction.

func (*Transaction) Rollback 

func (t *Transaction) Rollback() error

Rollback aborts the transaction.

type User 

type User struct {
	ID          int64     `json:"id"`
	ReferenceID string    `json:"reference_id"`
	Username    string    `json:"username"`
	Email       string    `json:"email"`
	Enabled     bool      `json:"enabled"`
	Sysop       bool      `json:"sysop"`
	AvatarURL   string    `json:"avatar_url,omitempty"`
	CreatedAt   time.Time `json:"created_at,omitzero"`
	UpdatedAt   time.Time `json:"updated_at,omitzero"`
}

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.