README
¶
SQLite with Buffered Batch Writes
A high-performance SQLite driver wrapper providing transparent read/write separation and buffered batch writes.
Features
- Read/Write Separation: Dedicated reader and writer connections for optimal concurrency
- Buffered Batch Writes: Aggregates multiple writes and flushes based on size threshold or time interval
- Automatic Memory Tuning: Dynamically configures SQLite cache sizes based on available system memory
- WAL Mode: Enabled by default for file databases, enabling concurrent reads during writes
- Savepoint Support: Nested transaction support via SQLite savepoints
- Thread-Safe Writer: Serialized writes through a single goroutine via channel-based coordination
Installation
go get github.com/cnlangzi/sqlite
Quick Start
import "github.com/cnlangzi/sqlite"
db, err := sqlite.Open(context.Background(), "mydb.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Write operations (buffered)
_, err = db.Exec("INSERT INTO users (name) VALUES (?)", "alice")
if err != nil {
log.Fatal(err)
}
// Read operations (concurrent)
row := db.QueryRow("SELECT name FROM users WHERE id = ?", 1)
var name string
row.Scan(&name)
Configuration
Buffer Configuration
db, err := sqlite.Open(ctx, "mydb.db")
if err != nil {
log.Fatal(err)
}
// Customize buffer behavior
db.Writer.(*sqlite.Writer).Configure(sqlite.Buffer{
BufferSize: 200, // Number of statements before flush (default: 100)
FlushInterval: 200 * time.Millisecond, // Maximum wait before flush (default: 100ms)
})
In-Memory Database
// Use ":memory:" for an in-memory database
db, err := sqlite.Open(ctx, ":memory:")
Architecture
Read/Write Separation
The DB wrapper maintains two separate *sql.DB connections:
| Connection | Purpose | Configuration |
|---|---|---|
Writer |
All write operations | Single connection, serialized writes |
Reader |
All read operations | Connection pool scaled to 2 * runtime.NumCPU() |
Buffered Batch Writes
The Writer transparently batches writes through a dedicated goroutine:
- Writes are sent via a channel to a single flush goroutine
- The goroutine collects writes into an in-memory transaction
- Flush occurs when:
- Buffer reaches
BufferSizethreshold FlushIntervalelapses since last commitWriter.Commit()is calledWriter.Close()is called
- Buffer reaches
Automatic Memory Tuning
For file databases, cache sizes are automatically configured based on physical memory:
| Parameter | Target | Min | Max | Fallback |
|---|---|---|---|---|
| Writer cache_size | 5% RAM | 32 MB | 512 MB | 100 MB |
| Reader cache_size | 12.5% RAM | 64 MB | 2 GB | 256 MB |
| Reader mmap_size | 50% RAM | 256 MB | 128 GB | 1 GB |
API Reference
Open
func Open(ctx context.Context, dsn string) (*DB, error)
Opens a SQLite database. DSN can be a file path or :memory: for in-memory mode.
DB
type DB struct {
Writer *Writer // Buffered write operations
Reader *sql.DB // Direct read operations (connection pool)
}
Writer
// Execute a write statement
func (w *Writer) Exec(query string, args ...any) (sql.Result, error)
// Execute with context
func (w *Writer) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
// Flush pending writes immediately
func (w *Writer) Commit() error
// Begin a buffered transaction
func (w *Writer) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)
// Close the writer (flushes and waits for completion)
func (w *Writer) Close() error
Tx (Buffered Transaction)
// Buffer a statement (does not execute immediately)
func (btx *Tx) Exec(query string, args ...any) (sql.Result, error)
// Execute all buffered statements atomically
func (btx *Tx) Commit() error
// Discard all buffered statements
func (btx *Tx) Rollback() error
Savepoint
For fine-grained transaction control within a batch:
sp, err := sqlite.NewSavepoint(tx)
if err != nil {
log.Fatal(err)
}
// ... do work ...
sp.Release() // or sp.Rollback() to undo
Performance Characteristics
- Writes: Serialized through a single goroutine; batching reduces transaction overhead
- Reads: Concurrent via connection pool; WAL mode allows reads during writes
- Memory: In-memory mode shares a single connection; file mode uses separate reader/writer connections
- Durability: Writes are committed to WAL on flush interval; call
Commit()for immediate persistence
Documentation
¶
Index ¶
- Variables
- type BufferConfig
- type BufferWriter
- func (w *BufferWriter) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)
- func (w *BufferWriter) Close() error
- func (w *BufferWriter) Exec(query string, args ...any) (sql.Result, error)
- func (w *BufferWriter) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
- func (w *BufferWriter) Flush() error
- type DB
- func (db *DB) Close() error
- func (db *DB) Exec(query string, args ...interface{}) (sql.Result, error)
- func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
- func (db *DB) Flush() error
- func (db *DB) Query(query string, args ...interface{}) (*sql.Rows, error)
- func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
- func (db *DB) QueryRow(query string, args ...interface{}) *sql.Row
- func (db *DB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
- type Savepoint
- type Task
- type TaskArgs
- type TaskFunc
- type TaskResult
- type Tx
Constants ¶
This section is empty.
Variables ¶
var ErrClosed = errors.New("sqlite: writer is closed")
var ErrTxNotready = errors.New("sqlite: transaction not ready")
Functions ¶
This section is empty.
Types ¶
type BufferConfig ¶
type BufferConfig struct {
// Size is the number of statements to buffer before triggering an
// automatic flush. A transaction is committed when this threshold
// is reached. Default: 100.
Size int
// FlushInterval is the maximum duration between commits. The writer
// flushes pending statements if this interval elapses since the last
// commit, even if Size has not been reached. Default: 100ms.
FlushInterval time.Duration
}
BufferConfig holds batch writer configuration. The writer buffers incoming statements and flushes them in batches to reduce transaction overhead and improve throughput.
func (*BufferConfig) Validate ¶
func (c *BufferConfig) Validate()
Validate sets default values for any zero fields and ensures the configuration is usable. It is called automatically by NewWriter; you only need to invoke it if you wish to check a config before use.
type BufferWriter ¶ added in v0.0.2
BufferWriter provides transparent batch writing with channel-based coordination. All writes are serialized through a single goroutine via cmdCh.
func NewWriter ¶
func NewWriter(db *sql.DB, cfg BufferConfig) *BufferWriter
NewWriter creates a Writer wrapping the provided *sql.DB. The returned Writer starts a background flush goroutine immediately. Close must be called to shut it down cleanly.
func (*BufferWriter) BeginTx ¶ added in v0.0.2
BeginTx starts a new buffered transaction. The returned Tx does not acquire a database lock immediately; statements are buffered locally and only committed when Tx.Commit is called. The opts parameter is accepted for compatibility with database/sql but is not used (savepoints handle the transactional semantics internally).
func (*BufferWriter) Close ¶ added in v0.0.2
func (w *BufferWriter) Close() error
Close signals the flush goroutine to stop, waits for it to drain and commit any remaining buffered work, then marks the writer as closed. It is safe to call Close multiple times concurrently; subsequent calls return nil immediately.
func (*BufferWriter) Exec ¶ added in v0.0.2
Exec executes a write statement (INSERT, UPDATE, DELETE, etc.) asynchronously. The statement is buffered and flushed according to the BufferConfig settings. This is the non-context variant; use ExecContext if you need cancellation or deadline control.
func (*BufferWriter) ExecContext ¶ added in v0.0.2
func (w *BufferWriter) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
ExecContext executes a write statement (INSERT, UPDATE, DELETE, etc.) asynchronously. The statement is buffered and flushed according to the BufferConfig settings. If the context is cancelled before the statement is processed, ctx.Err() is returned.
func (*BufferWriter) Flush ¶ added in v0.0.2
func (w *BufferWriter) Flush() error
type DB ¶
type DB struct {
Writer *BufferWriter
Reader *sql.DB
// contains filtered or unexported fields
}
DB wraps a SQLite database with logical read/write separation. All writes go through the buffered Writer; all reads go directly to the Reader. This separation allows concurrent reads while writes are being buffered and committed by the background flush goroutine.
func New ¶
New opens a SQLite database from the given DSN. If DSN is empty, it defaults to ":memory:" (an in-memory database). For file paths, the file is created if it does not exist. This is an alias for Open; prefer Open for clarity in application code.
func Open ¶
Open opens a SQLite database from the given DSN. If DSN is empty, it defaults to ":memory:" (an in-memory database). For file paths, the file is created if it does not exist. This is the preferred constructor.
func (*DB) Close ¶
Close shuts down the writer flush goroutine (committing any pending writes) and closes both the Writer and Reader database connections. Always call Close when you are done with the DB to avoid leaking goroutines or connections.
func (*DB) Exec ¶
Exec delegates to Writer.Exec, executing a write statement (INSERT, UPDATE, DELETE, CREATE TABLE, etc.) asynchronously with buffering. See Writer.Exec for details on buffering behavior.
func (*DB) ExecContext ¶
func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
ExecContext delegates to Writer.ExecContext, executing a write statement with context cancellation support. See Writer.ExecContext for details.
func (*DB) Query ¶
Query delegates to Reader.Query, executing a read-only query on the dedicated reader connection pool. Returns *sql.Rows that must be closed. Reads are served concurrently with writes and do not block the Writer.
func (*DB) QueryContext ¶
func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
QueryContext delegates to Reader.QueryContext, executing a read-only query with context cancellation support. Returns *sql.Rows that must be closed.
type Savepoint ¶
type Savepoint struct {
// contains filtered or unexported fields
}
Savepoint represents a named SQLite savepoint within a transaction. Savepoints allow partial rollback of a transaction without aborting it entirely. The savepoint is created with a cryptographically random name to avoid collisions when multiple savepoints are used concurrently.
func NewSavepoint ¶
NewSavepoint creates and names a new SQLite savepoint within the given transaction. The savepoint is immediately active. Use Release() to commit the savepoint or Rollback() to undo all statements since it was created.
func (*Savepoint) Name ¶
Name returns the SQLite savepoint name. Useful for debugging and for executing raw ROLLBACK TO or RELEASE statements if needed.
type Task ¶
type Task struct {
// contains filtered or unexported fields
}
Task is a concrete implementation of TaskFunc that wraps a query and its execution function. It is created by the package-level helper functions Query, QueryRow, Exec, and Commit.
func (*Task) Exec ¶
func (t *Task) Exec(tx *sql.Tx) TaskResult
Exec implements TaskFunc by running the stored execution function.
func (*Task) Notify ¶
func (t *Task) Notify() chan TaskResult
Notify implements TaskFunc by returning the result channel.
type TaskArgs ¶
type TaskArgs struct {
// contains filtered or unexported fields
}
TaskArgs bundles a query string with its parameters for deferred execution.
type TaskFunc ¶
type TaskFunc interface {
// Exec executes the task using the provided transaction. It returns
// the result of the execution (e.g., sql.Result for writes) or an error.
Exec(w *sql.Tx) TaskResult
// Notify returns a channel that receives the TaskResult once Exec completes.
// The caller uses this to wait for the task to finish.
Notify() chan TaskResult
// Flush returns true if this task should trigger an immediate commit.
// Used by Commit task to force a flush after buffering completes.
Flush() bool
}
TaskFunc is the interface implemented by tasks submitted to the Writer. It represents a unit of work to be executed within a SQLite transaction.
func Commit ¶
Commit creates a TaskFunc that commits the current transaction. It also sets Flush() to true, ensuring any pending buffered writes are flushed before this task is processed.
func Exec ¶
Exec creates a TaskFunc that executes a write statement (INSERT, UPDATE, DELETE, etc.) with the given arguments. The statement is not executed until the Writer's flush goroutine processes the task.
type TaskResult ¶
type TaskResult struct {
Result any // the execution result, typically a sql.Result or *sql.Row
Error error // any error that occurred during execution
}
TaskResult holds the outcome of a task execution.
type Tx ¶
type Tx struct {
// contains filtered or unexported fields
}
Tx is a buffered transaction that batches multiple write statements and executes them atomically on Commit. Unlike a traditional database transaction, Tx does not hold a lock on the database; statements are buffered locally and submitted to the Writer's shared transaction on Commit.
func (*Tx) Commit ¶
Commit executes all buffered statements atomically in the Writer's global transaction. All statements are committed together or rolled back together on error. After Commit is called, the Tx can no longer be used.
func (*Tx) Exec ¶
Exec buffers a write statement for later execution. The statement is NOT executed immediately; it is held in memory until Commit is called, at which point all buffered statements are executed atomically within a single SQLite transaction. Returns (nil, nil) because execution is deferred.
Source Files