sqlite

package
v0.0.0-...-4c5b182 Latest Latest
Warning

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

 Go to latest Published: Jan 17, 2026 License: BSD-3-Clause Imports: 29 Imported by: 0

Documentation

Overview

Package sqlite provides a Go wrapper around SQLite with transaction management, automatic backups, and type-safe query helpers.

Features

  • WAL Mode: Uses Write-Ahead Logging for better concurrent read performance
  • Single-Writer/Multi-Reader: Serialized writes with concurrent reads via separate connection pools
  • Automatic Backups: Periodic background backups with smart retention policies
  • Transaction Tracking: Context-based tracking to prevent nested transactions
  • Type-Safe Queries: Generic helpers for scanning rows into typed values
  • JSON Object Storage: Store and retrieve Go structs as JSON blobs
  • Schema Generation: Code generators for SQL schemas from Go structs

Creating a Database

Use New to create a database with background workers (periodic backups), or NewNoWorkers for tests and situations where background work is not desired: 

db, err := sqlite.New("/path/to/db.sqlite", log.Printf)
if err != nil {
    return err
}
defer db.Close()

Transactions

All database operations require a context with a transaction tracker. Use NewContext to create one: 

ctx := sqlite.NewContext()

// Read-only transaction
err := db.Read(ctx, func(tx *sqlite.Tx) error {
    count, err := sqlite.QuerySingle[int](tx, "SELECT COUNT(*) FROM users")
    return err
})

// Read/Write transaction
err := db.Write(ctx, "create-user", func(tx *sqlite.Tx) error {
    _, err := tx.Exec("INSERT INTO users (name) VALUES (?)", "alice")
    return err
})

Type-Safe Queries

Generic helpers scan rows into typed values: 

// Single value
count, err := sqlite.QuerySingle[int](tx, "SELECT COUNT(*) FROM users")

// Single row as JSON
user, err := sqlite.QueryJSONRow[User](tx, "SELECT JSONObj FROM users WHERE id = ?", id)

// Multiple rows
ids, err := sqlite.QueryTypedRows[int64](tx, "SELECT id FROM users")

JSON Object Storage

Store Go structs implementing ObjectWithMetadata using Put: 

type User struct {
    ID        int64     `json:"id"`
    Name      string    `json:"name"`
    UpdatedAt time.Time `json:"updated_at"`
}

func (u User) GetID() int64              { return u.ID }
func (u *User) SetUpdatedAt(t time.Time) { u.UpdatedAt = t }
func (u User) TableName() string         { return "users" }

err := sqlite.Put(tx, &user)

Schema Generation

The schema subpackage provides tools for generating SQL schemas from Go structs and managing schema migrations. Use go:generate directives: 

//go:generate go run pkg.maisem.dev/sqlite/schema/sqlgen -type User -output schema.sql
//go:generate go run pkg.maisem.dev/sqlite/schema/embed -f schema.sql

The sqlgen tool generates:

  • schema.sql: CREATE TABLE statements with JSONObj blob storage
  • <pkg>_tables.go: TableName() methods for each type

Tables use a JSONObj BLOB column to store the struct as JSON, with generated columns for indexed fields. Use struct tags to control column generation: 

type User struct {
    ID        int64  `json:"id"`
    Email     string `json:"email" sql:"stored,unique"`
    TenantID  int64  `json:"tenant_id" sql:"stored,index,fk:Tenant.ID"`
    DeletedAt *time.Time `json:"deleted_at,omitempty" sql:"stored,omitempty"`
}

Supported sql tag options:

  • stored: Create a STORED generated column
  • virtual: Create a VIRTUAL generated column
  • unique: Add a unique index on the column
  • index: Add a non-unique index on the column
  • omitempty: Allow NULL values (for optional fields)
  • fk:<Type>.<Field>: Add a foreign key constraint
  • inline: Embed metadata fields (for Metadata[ID] pattern)

For custom SQL statements (composite indexes, etc.), use //sqlgen: comments inside the struct definition: 

type UserProject struct {
    UserID    UserID    `sql:"stored"`
    ProjectID ProjectID `sql:"stored"`
    //sqlgen: CREATE UNIQUE INDEX user_project_unique ON user_projects (UserID, ProjectID);
}

The embed tool compresses schema.sql into schemas/v<N>.sql.gz for versioned migrations managed by [schema.Manager].

Backup Retention

Automatic backups follow this retention policy:

  • Keep all backups from the last hour
  • Keep one backup per hour for the last 24 hours
  • Keep one backup per day for the last 30 days
  • Delete older backups (while respecting a minimum count)

Error Handling

Use IsConstraintError to check for constraint violations (UNIQUE, etc.) and IsTableNotFoundError to check for missing tables.

Index

Constants

This section is empty.

Variables

View Source 
var ErrNeedWriteTx = errors.New("need write tx")
View Source 
var ErrSkipCommit = errors.New("skip commit")
View Source 
var UTCNowKey = ctxkey.New[func() time.Time]("sqlite.UTCNow", nil)

Functions

func AttachTracker 

func AttachTracker(ctx context.Context) context.Context

AttachTracker attaches a TxTracker to the context.

func CreateWithSafeID 

func CreateWithSafeID[T any, ID ~int64](create func(id ID) (T, error)) (T, error)

CreateWithSafeID attempts to create an object with a unique ID by retrying with different IDs when constraint errors occur. It calls the create function with generated IDs until successful or max attempts reached.

func InsertSafeID 

func InsertSafeID[T ~int64](tx *Tx, id T) error

InsertSafeID stores a SafeID in the database with its type using the provided transaction.

func InsertWithAutoID 

func InsertWithAutoID[T any](tx *Tx, table tableName, obj T) (int64, error)

func IsConstraintError 

func IsConstraintError(err error) bool

IsConstraintError reports whether err represents a SQLITE_CONSTRAINT error.

func IsTableNotFoundError 

func IsTableNotFoundError(err error) bool

func NewContext 

func NewContext() context.Context

NewContext returns a new context with a TxTracker attached. It should only be used for root contexts, to attach a TxTracker to a child context use AttachTracker.

func Put 

func Put[O ObjectWithMetadata[ID], ID Key](tx *Tx, obj O) error

Put inserts or replaces an object in the table with the given ID.

func QueryJSONRow 

func QueryJSONRow[T any](rx *Tx, query queryString, args ...any) (T, error)

func QueryJSONRows 

func QueryJSONRows[T any](rx *Tx, query queryString, args ...any) ([]T, error)

func QuerySingle 

func QuerySingle[T any](rx *Tx, query queryString, args ...any) (T, error)

func QueryToCSV 

func QueryToCSV(tx *Tx, query queryString, args ...any) (string, error)

QueryToCSV executes a SQL query and returns the results formatted as CSV. The first row contains column names, and subsequent rows contain the data. NULL values are represented as "NULL", and strings containing commas, quotes, newlines, tabs or spaces are properly quoted.

func QueryTypedRow 

func QueryTypedRow[T any](rx *Tx, query queryString, args ...any) (T, error)

QueryTypedRow executes a query and returns the result as a single value of the given type. The query must return a single column.

func QueryTypedRows 

func QueryTypedRows[T any](rx *Tx, query queryString, args ...any) ([]T, error)

QueryTypedRows executes a query and returns the results as a slice of the given type. The query must return a single column.

func Read 

func Read[T any](ctx context.Context, db *DB, fn func(*Tx) (T, error)) (T, error)

func ReserveSafeIDTx 

func ReserveSafeIDTx[T ~int64](tx *Tx) (T, error)

ReserveSafeIDTx generates a new SafeID of the specified type and stores it in the database using the provided transaction. It uses collision-resistant generation with retry logic.

func ScanSingle 

func ScanSingle[T any](row Scanner) (T, error)

ScanSingle scans a single value from a row.

func ScanSingleJSON 

func ScanSingleJSON[T any](row Scanner) (T, error)

ScanSingleJSON scans a single JSON value from a row.

func UnsafeQueryString 

func UnsafeQueryString(s string) queryString

UnsafeQueryString creates a [queryString] from a string. This is unsafe because it allows the caller to pass constructed queries to the database which may lead to SQL injection if not used carefully.

Types

type DB 

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

DB is a Read/Write wrapper around a sqlite database. It is safe to use DB from multiple goroutines.

func New 

func New(path string, logf logger.Logf) (*DB, error)

New creates a new DB instance and starts all background workers.

func NewNoWorkers 

func NewNoWorkers(path string, logf logger.Logf) (*DB, error)

NewNoWorkers creates a new DB instance without starting the background backup worker.

func (*DB) Backup 

func (db *DB) Backup(ctx context.Context, slug string) (string, error)

Backup creates a new backup of the database. It returns the path to the backup file.

If the database has not changed since the last backup, it skips the backup and returns an empty string.

func (*DB) Close 

func (db *DB) Close() error

Close closes the DB.

func (*DB) CondWrite 

func (db *DB) CondWrite(ctx context.Context, why string, fn func(*Tx) error) error

func (*DB) Conn 

func (db *DB) Conn(ctx context.Context) (*sql.Conn, error)

func (*DB) InitSchema 

func (db *DB) InitSchema(schema string) error

InitSchema initializes the database schema by executing the provided SQL script. This is typically called during database initialization to create necessary tables.

func (*DB) Path 

func (db *DB) Path() string

func (*DB) Read 

func (db *DB) Read(ctx context.Context, fn func(*Tx) error) error

func (*DB) ReadTx 

func (db *DB) ReadTx(ctx context.Context) (_ *Tx, err error)

ReadTx creates a new read-only transaction.

func (*DB) ReadTxWithWhy 

func (db *DB) ReadTxWithWhy(ctx context.Context, why string) (_ *Tx, err error)

func (*DB) ReadWithWhy 

func (db *DB) ReadWithWhy(ctx context.Context, why string, fn func(*Tx) error) error

ReadWithWhy executes a function in a read-only transaction with the specified reason. The reason is used for monitoring and debugging purposes.

func (*DB) RxManager 

func (db *DB) RxManager(ctx context.Context) *RxManager

func (*DB) ScrubBackups 

func (db *DB) ScrubBackups(ctx context.Context, keepAtLeast int) error

ScrubBackups removes old backups according to retention policies: - Keep all backups from the last hour - Keep one backup per hour for the last 24 hours - Keep one backup per day for the last 30 days - Delete everything else

func (*DB) Tx 

func (db *DB) Tx(ctx context.Context, why string) (_ *Tx, err error)

Tx creates a new read/write transaction.

func (*DB) Write 

func (db *DB) Write(ctx context.Context, why string, fn func(*Tx) error) error

Write executes a function in a new transaction and commits the transaction. If the function returns ErrSkipCommit, the transaction is rolled back without returning an error to the caller.

type Key 

type Key interface {
	~int64 | ~string
}

type ObjectWithMetadata 

type ObjectWithMetadata[ID Key] interface {
	GetID() ID
	SetUpdatedAt(time.Time)
	TableName() string
}

type Result 

type Result struct {
	RowsAffected int64
	LastInsertID int64
}

Result is a wrapper around sql.Result.

type RxManager 

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

func (*RxManager) Close 

func (h *RxManager) Close()

func (*RxManager) Rollback 

func (h *RxManager) Rollback()

func (*RxManager) Rx 

func (h *RxManager) Rx() (*Tx, error)

type Scanner 

type Scanner interface {
	Scan(dest ...any) error
}

type Stmt 

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

Stmt is a wrapper around sql.Stmt.

func (*Stmt) Close 

func (s *Stmt) Close() error

Close closes the statement.

func (*Stmt) Exec 

func (s *Stmt) Exec(args ...any) (sql.Result, error)

Exec executes ExecContext on the statement using the statement's context.

func (*Stmt) Query 

func (s *Stmt) Query(args ...any) (*sql.Rows, error)

Query executes QueryContext on the statement using the statement's context.

func (*Stmt) QueryRow 

func (s *Stmt) QueryRow(args ...any) *sql.Row

QueryRow executes QueryRowContext on the statement using the statement's context.

type Tx 

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

Tx is a wrapper around sql.Tx.

func (*Tx) Commit 

func (tx *Tx) Commit() error

Commit commits the transaction.

func (*Tx) Exec 

func (tx *Tx) Exec(query queryString, args ...any) (sql.Result, error)

Exec executes ExecContext on the transaction using the transaction's context.

func (*Tx) ExecAndReturnLastInsertID 

func (tx *Tx) ExecAndReturnLastInsertID(query queryString, args ...any) (int64, error)

ExecAndReturnLastInsertID executes a query and returns the last insert ID.

func (*Tx) ExecAndReturnRowsAffected 

func (tx *Tx) ExecAndReturnRowsAffected(query queryString, args ...any) (int64, error)

ExecAndReturnRowsAffected executes a query and returns the number of rows affected.

func (*Tx) OnCommit 

func (tx *Tx) OnCommit(fn func())

OnCommit registers a callback to be called when the transaction is committed, but before the application-level write lock is released.

func (*Tx) OnRollback 

func (tx *Tx) OnRollback(fn func())

OnRollback registers a callback to be called when the transaction is rolled back. Exactly one of OnCommit and OnRollback will be called, but not both.

func (*Tx) Prepare 

func (tx *Tx) Prepare(query queryString) (*Stmt, error)

Prepare prepares a statement for execution.

func (*Tx) Query 

func (tx *Tx) Query(query queryString, args ...any) (*sql.Rows, error)

Query executes QueryContext on the transaction using the transaction's context.

func (*Tx) QueryRow 

func (tx *Tx) QueryRow(query queryString, args ...any) *sql.Row

QueryRow executes QueryRowContext on the transaction using the transaction's context.

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Rollback rolls back the transaction.

func (*Tx) UTCNow 

func (tx *Tx) UTCNow() time.Time

UTCNow returns the time at which the Tx was created.

func (*Tx) Writable 

func (tx *Tx) Writable() bool

type TxTracker 

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

TxTracker tracks active SQL transactions in a context. It ensures that only one transaction is active at a time for a given context.

func (*TxTracker) Track 

func (t *TxTracker) Track(why string) (untrackOnce func())

Track returns a function that untracks the tx from the tracker. It is safe to call this function multiple times.

Source Files

View all Source files

Directories

Path Synopsis
embed command
equality
sqlgen command
sqlgen is a tool to generate SQL schema from Go struct types.
 sqlgen is a tool to generate SQL schema from Go struct types.

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.