README
¶
sqlite
Another take at persistence for Go Programs
Documentation
¶
Overview ¶
Package sqlite implements a driver over the SQLite database engine.
To get started, the Open and Conn.Exec methods should be sufficient, but do note that OpenPool is used throughout the server code.
The package API is designed to expose as possible of the underlying engine as possible, including the option to expose Gofunctions via RegisterFunc.
Example ¶
A quick tour of the most important functions in the library
ctn, err := Open(MemoryPath)
if err != nil {
log.Fatal("cannot open a temporary database", err)
}
ctx := context.Background()
sts := []string{
"create table tbl1 (a primary key, b)",
"insert into tbl1 (a, b) values ('hello','world'), ('bonjour','monde'), ('hola','mundo')",
}
for _, st := range sts {
// Exec can be used for direct statements
if err := ctn.Exec(ctx, st).Err(); err != nil {
log.Fatal("cannot create table", err)
}
}
// Scan to iterate over all results
rows := ctn.Exec(ctx, "select a, b from tbl1")
for rows.Next() {
var a, b string
rows.Scan(&a, &b)
fmt.Println(a, b)
}
if rows.Err() != nil {
log.Fatal("cannot query tbl1", rows.Err())
}
// ScanOne is a handy shortcut
var b string
if err := ctn.Exec(ctx, "select b from tbl1 where a = ?", "bonjour").ScanOne(&b); err != nil {
log.Fatal("cannot find value matching \"bonjour\"")
}
fmt.Println(b)
Output: hello world bonjour monde hola mundo monde
Index ¶
- Constants
- Variables
- func Backup(ctn *Conn, dest string) error
- func BackupDB(pool *Connections, dest string) error
- func GetBuffer() *bytes.Buffer
- func IsComplete(st string) bool
- func LoadExtensions(db SQLITE3)
- func Register(f ...func(SQLITE3))
- func RegisterFunc(name string, t any) func(SQLITE3)
- func ReturnBuffer(b *bytes.Buffer)
- type ColStrings
- type Conn
- func (ctn *Conn) AsPointer(v any) PointerValue
- func (c *Conn) Close() error
- func (cn *Conn) CreateBLOB(database, table, column string, size int) (*DirectBLOB, error)
- func (ctn *Conn) EndTx(ctx context.Context) error
- func (c *Conn) Exec(ctx context.Context, cmd string, args ...any) *Rows
- func (cn *Conn) OpenBLOB(database, table, column string, rownum int) (*DirectBLOB, error)
- func (ctn *Conn) RollbackTx(ctx context.Context) error
- type Connections
- type DirectBLOB
- type MultiString
- type NullString
- type PointerValue
- type Rows
- func (r *Rows) Bytecode() string
- func (rows *Rows) ColumnName(i int) string
- func (r *Rows) Err() error
- func (r *Rows) ExplainQueryPlan() string
- func (r *Rows) IsExplain() bool
- func (rows *Rows) Next() bool
- func (rows *Rows) NumColumn() int
- func (rows *Rows) Scan(dst ...any)
- func (r *Rows) ScanOne(dst ...any) error
- type SQLITE3
Examples ¶
Constants ¶
const MemoryPath = "file::memory:?mode=memory"
Variables ¶
var BusyTransaction = errText[C.SQLITE_BUSY]
var DBName = "main"
DBName is the name of the database used in the application (main by default).
It can be changed before a back-up is started to reflect the application specificities.
var DuplicateRecords = errors.New("duplicate records in scanone")
var ErrNull = errors.New("null SQL value")
ErrNull is a sentinel value that can be returned by functions that want to return (or otherwise handle) null values
var NumThreads int
var ZombieTimeout = 30 * time.Second
ZombieTimeout is the time after which a transaction is considered leaked
Functions ¶
func Backup ¶ added in v0.5.14
Backup performs an [online backup] to dest
[online backup] https://www.sqlite.org/backup.html
func BackupDB ¶
func BackupDB(pool *Connections, dest string) error
BackupDB performs an [online backup] to dest
[online backup] https://www.sqlite.org/backup.html
func GetBuffer ¶
GetBuffer returns a bytes buffer from a system-wide pool. It is useful to avoid too much allocation when serializing. The buffer must be release with ReturnBuffer.
func IsComplete ¶
IsComplete returns true iff the statement is complete
func LoadExtensions ¶
func LoadExtensions(db SQLITE3)
LoadExtensions loads all registered extensions against the database db. This function is automatically called when Open is, and is made available only for modules compiled as a shared library.
func Register ¶
func Register(f ...func(SQLITE3))
Register adds a new statically compiled extension to register when a new SQLite connection is opened. It should be called in a sub-package init function.
Most of the time, f is going to be returned from RegisterFunc.
If not, due to the way CGO handles names bug-13467, callers need to wrap this in an unsafe pointer:
(*C.sqlite3)(unsafe.Pointer(db))
func RegisterFunc ¶
RegisterFunc binds a Go function as an application-defined SQL function. Functions can only be exposed if:
- Their inputs arguments are integer, text, bytes or a pointer to an arbitrary type
- Their name is not a registered SQL keyword
- They return either (1) no argument, (2) a single argument of type integer, text, bytes, error or PointerValue or (3) 2 arguments of which the first argument must be of type integer, text, bytes or PointerValue the second argument must be of type erorr.
func ReturnBuffer ¶
ReturnBuffer returns the buffer to the pool. The buffer must not be used afterwards (this also mean the bytes returned from [bytes.buffer.Bytes]).
Types ¶
type ColStrings ¶
ColStrings augments MultiString with the name of the columns. the pointer type is required to comply with Go’s addressability constraints on maps.
type Conn ¶
type Conn struct {
// contains filtered or unexported fields
}
Conn is a connection to a given database. Conn is safe for concurrent use.
Internally, Conn maps to an sqlite3 object, and present the same characteristics when not documented otherwise.
func (*Conn) AsPointer ¶ added in v0.7.0
func (ctn *Conn) AsPointer(v any) PointerValue
AsPointer is used to pass the value using the SQLite pointer passing interface. This interface is only useful for reading the value in virtual functions. The “pointer type” parameter will be derived from the underlying data type name.
func (*Conn) CreateBLOB ¶
func (cn *Conn) CreateBLOB(database, table, column string, size int) (*DirectBLOB, error)
CreateBLOB creates a new incremental I/O buffer. The database, table, and column are only quoted, and therefore should not be from untrusted inputs.
func (*Conn) EndTx ¶ added in v0.5.18
End terminates (commits) the current transaction. If the transaction is terminated (whether by commit or rollback), it is a no-op
func (*Conn) Exec ¶
Exec executes cmd, optionally binding args to parameters in the query. Rows are a a cursor over the results, and the first row is already executed: commands needs not call Rows.Next afterwards. Arguments are matched by position.
This function will panic if the command is invalid SQL. This is intented for static SQL statements (written in your code), not for untrusted SQL.
type Connections ¶
type Connections struct {
// contains filtered or unexported fields
}
Connections is a pool of connections to a single SQLite database.
func OpenPool ¶
func OpenPool(name string, exts ...func(SQLITE3)) (*Connections, error)
OpenPool ceates a new connection pool
func (*Connections) BeginTx ¶ added in v0.5.18
BeginTx starts a new deferred [transaction]. All queries in the connection before calling EndTx / RollbackTx are within the same transaction. The connection must not be used after the cleanup function was called.
ctx, tx, clean := p.BeginTx(ctx) defer clean() tx.Exec(ctx, …) tx.EndTx(ctx)
[transaction] https://www.sqlite.org/lang_transaction.html
func (*Connections) Close ¶
func (p *Connections) Close() error
Close closes all connections in the pool. It can be safely called concurrently [Connections.Savepoint], Connections.Exec and [Connections.Release] but note that calls to [Connections.Savepoint] or Connections.Exec that happen after Close might block forever. The mechanism to terminate other connections has to be done out of band.
func (*Connections) FreeCount ¶ added in v0.5.6
func (c *Connections) FreeCount() int
FreeCount returns the number of free connections in the pool
type DirectBLOB ¶
type DirectBLOB struct {
RowNumber int
// contains filtered or unexported fields
}
ReadWriterAt is an incremental I/O buffer. It is mostly useful to work directly with binary formats in SQLite – so akin to mmap.
func (*DirectBLOB) Close ¶
func (r *DirectBLOB) Close() error
func (*DirectBLOB) Size ¶
func (r *DirectBLOB) Size() int
type MultiString ¶
type MultiString []string
MultiString is used to read all values of a statement as string. This is useful if you don’t know ahead of time the values returned.
type NullString ¶
type NullString string
NullString as a marker type: strings of length 0 will be set as null instead
type PointerValue ¶
Marker type for values that should be passed as pointer. Most users will prefer [AsPointer].
type Rows ¶
type Rows struct {
// contains filtered or unexported fields
}
Rows are iterator structure over the underlying database statement results.
Rows are lightweigth object, and should not be reused after Rows.Err or Rows.ScanOne calls.
func NewErroredRows ¶ added in v0.5.12
NewErroredRows create a collection of rows that will error on the first call. This is useful to pipeline call and only handle error at the end, e.g.:
ctn, err := sqlite.Open("/doesnotexist")
if err != nil {
return NewErroredRows(c, err)
}
return ctn.Exec(ctx, "select * from table")
func (*Rows) Bytecode ¶
Bytecodes outputs the detailed query plan through the SQLite bytecode operands. This is a fairly low-level operation, and is only really useful for troubleshooting. Even then, Rows.ExplainQueryPlan is usually a much better option.
func (*Rows) ColumnName ¶
ColumnName return the ith (zero-based) column name. This is mostly convenient in a loop:
for i := 0; i < st.NumColumn(); i++ {
buf.WriteString(st.ColumnName(i) + "\t")
}
func (*Rows) Err ¶
Err finalizes the statement, and return an error if any. Rows should not be used after this.
func (*Rows) ExplainQueryPlan ¶
QueryPlan returns the query plan of the existing statement under a graphical form.
func (*Rows) IsExplain ¶
IsExplain returns true iff the statement is an explain query plan command. This is the most important interface for debugging.
func (*Rows) Next ¶
Next advances the cursor to the next result in the set. It returns false if there are no more results, or if an error, or a timeout occur. Use Rows.Err to disambiguate between those cases.
func (*Rows) NumColumn ¶
NumColumn returns the count of columns returned by the current statement. Use Rows.ColumnName if the name is useful.
func (*Rows) Scan ¶
Scan unmarshals the underlying SQLite value into a Go value. Values in dst are matched by position against the columns in the query, e.g.
rows := ctn.Exec(ctx, "select a, b from t")
for rows.Next() {
var c, d string
rows.Scan(&c, &d)
// c -> column a
// d -> column b
}
Scan defers errors to the Rows.Err method (but note that Rows.Next will stop at the first error).
Conversion is done depending on the SQLite type affinity and the type in Go. Richer Go types (e.g. bytes.Buffer, or time.Time) are automatically read too. Read the code for the full map – but if you’re relying on this, you are probably already doing something too smart.
Source Files
¶
Directories
¶
| Path | Synopsis |
|---|---|
|
cmd
|
|
|
constrainer
command
Constrainer is a tool to generate acces methods from virtual tables types definitions.
|
Constrainer is a tool to generate acces methods from virtual tables types definitions. |
|
stability
command
|
|
|
Package stability provides an API to validate the stability of persisted data structures.
|
Package stability provides an API to validate the stability of persisted data structures. |