Documentation ¶
Overview ¶
Package sqinn provides interface to SQLite databases in Go without cgo.
It uses Sqinn (http://github.com/cvilsmeier/sqinn) for accessing SQLite databases. It is not a database/sql driver.
Basic Usage ¶
The following sample code opens a database, inserts some data, queries it, and closes the database. Error handling is left out for brevity.
import "github.com/cvilsmeier/sqinn-go/sqinn" func main() { // Launch sqinn. sq, _ := sqinn.Launch(sqinn.Options{}) defer sq.Terminate() // Open database. sq.Open("./users.db") defer sq.Close() // Create a table. sq.ExecOne("CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR)") // Insert users. sq.ExecOne("INSERT INTO users (id, name) VALUES (1, 'Alice')") sq.ExecOne("INSERT INTO users (id, name) VALUES (2, 'Bob')") // Query users. rows, _ := sq.Query("SELECT id, name FROM users ORDER BY id", nil, []byte{sqinn.ValInt, sqinn.ValText}) for _, row := range rows { fmt.Printf("id=%d, name=%s\n", row.Values[0].AsInt(), row.Values[1].AsString()) } // Output: // id=1, name=Alice // id=2, name=Bob }
Parameter Binding ¶
For Exec:
nUsers := 3 nParamsPerUser := 2 sq.Exec("INSERT INTO users (id, name) VALUES (?, ?)", nUsers, nParamsPerUser, []any{ 1, "Alice", 2, "Bob", 3, nil, })
For Query:
// Query users where id < 42. rows, err := sq.Query( "SELECT id, name FROM users WHERE id < ? ORDER BY name", []any{42}, // WHERE id < 42 []byte{sqinn.ValInt, sqinn.ValText}, // two columns: int id and string name )
Options ¶
Sqinn searches the sqinn binary in the $PATH environment. You can customize that behavior by specifying the path to sqinn explicitly when launching sqinn.
sq, _ := sqinn.Launch(sqinn.Options{ SqinnPath: "C:/projects/wombat/bin/sqinn.exe", }) // or, even better sq, _ := sqinn.Launch(sqinn.Options{ SqinnPath: os.Getenv("SQINN_PATH"), })
The sqinn subprocess prints debug and error messages on its stderr. You can consume it by setting a sqinn.Logger.
sq, _ := sqinn.Launch(sqinn.Options{ Logger: sqinn.StdLogger{}, })
See the sqinn.Logger docs for more details.
Low-level Functions ¶
Sqinn implements many of SQLite's C API low-level functions prepare(), finalize(), step(), etc. Although made available, we recommend not using them. Instead, use Exec and Query. Most (if not all) database tasks can be accomplished with Exec and Query.
Example (Basic) ¶
package main import ( "fmt" "log" "os" "github.com/cvilsmeier/sqinn-go/sqinn" ) func main() { // Find sqinn sqinnPath := os.Getenv("SQINN_PATH") if sqinnPath == "" { log.Printf("SQINN_PATH not set, please install sqinn and set SQINN_PATH") return } // Launch sqinn. Terminate at program exit. sq := sqinn.MustLaunch(sqinn.Options{ SqinnPath: sqinnPath, }) defer sq.Terminate() // Open database. Close when we're done. sq.MustOpen(":memory:") defer sq.Close() // Create a table. sq.MustExecOne("CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR)") // Insert users. sq.MustExecOne("INSERT INTO users (id, name) VALUES (1, 'Alice')") sq.MustExecOne("INSERT INTO users (id, name) VALUES (2, 'Bob')") // Query users. rows := sq.MustQuery("SELECT id, name FROM users ORDER BY id", nil, []byte{sqinn.ValInt, sqinn.ValText}) for _, row := range rows { fmt.Printf("%d %q\n", row.Values[0].AsInt(), row.Values[1].AsString()) } }
Output: 1 "Alice" 2 "Bob"
Example (HandlingNullValues) ¶
package main import ( "fmt" "os" "github.com/cvilsmeier/sqinn-go/sqinn" ) func main() { // Launch sqinn. Env SQINN_PATH must point to sqinn binary. sq := sqinn.MustLaunch(sqinn.Options{ SqinnPath: os.Getenv("SQINN_PATH"), }) defer sq.Terminate() // Open database. sq.MustOpen(":memory:") defer sq.Close() // Create table sq.MustExecOne("CREATE TABLE names (val TEXT)") // Insert 2 rows, the first is non-NULL, the second is NULL sq.MustExecOne("BEGIN") sq.MustExec( "INSERT INTO names (val) VALUES (?)", 2, // insert 2 rows 1, // each row has 1 column []any{ "wombat", // first row is 'wombat' nil, // second row is NULL }, ) sq.MustExecOne("COMMIT") // Query rows rows := sq.MustQuery( "SELECT val FROM names ORDER BY val", nil, // no query parameters []byte{sqinn.ValText}, // one column of type TEXT ) for _, row := range rows { stringValue := row.Values[0].String if stringValue.IsNull() { fmt.Printf("NULL\n") } else { fmt.Printf("%q\n", stringValue.Value) } } }
Output: NULL "wombat"
Example (ParameterBinding) ¶
package main import ( "fmt" "log" "os" "github.com/cvilsmeier/sqinn-go/sqinn" ) func main() { // Find sqinn sqinnPath := os.Getenv("SQINN_PATH") if sqinnPath == "" { log.Printf("SQINN_PATH not set, please install sqinn and set SQINN_PATH") return } // Launch sqinn. sq := sqinn.MustLaunch(sqinn.Options{ SqinnPath: sqinnPath, }) defer sq.Terminate() // Open database. sq.MustOpen(":memory:") defer sq.Close() // Create table sq.MustExecOne("CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR)") // Insert 3 rows at once sq.MustExecOne("BEGIN") sq.MustExec( "INSERT INTO users (id, name) VALUES (?,?)", 3, // insert 3 rows 2, // each row has 2 columns []any{ 1, "Alice", // bind first row 2, "Bob", // bind second row 3, nil, // third row has no name }, ) sq.MustExecOne("COMMIT") // Query rows rows := sq.MustQuery( "SELECT id, name FROM users WHERE id < ? ORDER BY id ASC", []any{42}, // WHERE id < 42 []byte{sqinn.ValInt, sqinn.ValText}, // two columns: int id, string name ) for _, row := range rows { fmt.Printf("%d %q\n", row.Values[0].AsInt(), row.Values[1].AsString()) } }
Output: 1 "Alice" 2 "Bob" 3 ""
Example (SqliteSpecialties) ¶
package main import ( "os" "path/filepath" "github.com/cvilsmeier/sqinn-go/sqinn" ) func main() { // Launch sqinn. Env SQINN_PATH must point to sqinn binary. sq := sqinn.MustLaunch(sqinn.Options{ SqinnPath: os.Getenv("SQINN_PATH"), }) defer sq.Terminate() // Open database. sq.MustOpen(":memory:") defer sq.Close() // Enable foreign keys, see https://sqlite.org/pragma.html#pragma_foreign_keys sq.MustExecOne("PRAGMA foreign_keys = 1") // Set busy_timeout, see https://sqlite.org/pragma.html#pragma_busy_timeout sq.MustExecOne("PRAGMA busy_timeout = 10000") // Enable WAL mode, see https://sqlite.org/pragma.html#pragma_journal_mode sq.MustExecOne("PRAGMA journal_mode = WAL") // Enable NORMAL sync, see https://sqlite.org/pragma.html#pragma_synchronous sq.MustExecOne("PRAGMA synchronous = NORMAL") // Make a backup into a temp file filename := filepath.Join(os.TempDir(), "db_backup.sqlite") os.Remove(filename) // remove in case it exists, sqlite does not want to overwrite sq.MustExec("VACUUM INTO ?", 1, 1, []any{ filename, }) }
Output:
Index ¶
- Constants
- type AnyValue
- type BlobValue
- type DoubleValue
- type Int64Value
- type IntValue
- type Logger
- type NoLogger
- type Options
- type Row
- type Sqinn
- func (sq *Sqinn) Bind(iparam int, value any) error
- func (sq *Sqinn) Changes() (int, error)
- func (sq *Sqinn) Close() error
- func (sq *Sqinn) Column(icol int, colType byte) (AnyValue, error)
- func (sq *Sqinn) Exec(sql string, niterations, nparams int, values []any) ([]int, error)
- func (sq *Sqinn) ExecOne(sql string) (int, error)
- func (sq *Sqinn) Finalize() error
- func (sq *Sqinn) IoVersion() (byte, error)
- func (sq *Sqinn) MustExec(sql string, niterations, nparams int, values []any) []int
- func (sq *Sqinn) MustExecOne(sql string) int
- func (sq *Sqinn) MustOpen(filename string)
- func (sq *Sqinn) MustQuery(sql string, values []any, colTypes []byte) []Row
- func (sq *Sqinn) Open(filename string) error
- func (sq *Sqinn) Prepare(sql string) error
- func (sq *Sqinn) Query(sql string, params []any, colTypes []byte) ([]Row, error)
- func (sq *Sqinn) Reset() error
- func (sq *Sqinn) SqinnVersion() (string, error)
- func (sq *Sqinn) SqliteVersion() (string, error)
- func (sq *Sqinn) Step() (bool, error)
- func (sq *Sqinn) Terminate() error
- type StdLogger
- type StringValue
Examples ¶
Constants ¶
const ( // ValNull represents the NULL value (Go nil) ValNull byte = 0 // ValInt represents a Go int ValInt byte = 1 // ValInt64 represents a Go int64 ValInt64 byte = 2 // ValDouble represents a Go float64 ValDouble byte = 6 // the IEEE variant // ValText represents a Go string ValText byte = 4 // ValBlob represents a Go []byte ValBlob byte = 5 )
Value types for binding query parameters and retrieving column values.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type AnyValue ¶
type AnyValue struct { Int IntValue // a nullable Go int Int64 Int64Value // a nullable Go int64 Double DoubleValue // a nullable Go float64 String StringValue // a nullable Go string Blob BlobValue // a nullable Go []byte }
An AnyValue can hold any value type.
func (AnyValue) AsBlob ¶
AsBlob returns a []byte value or nil if it is NULL or the value is not a blob.
func (AnyValue) AsDouble ¶
AsDouble returns a double value or 0.0 if it is NULL or the value is not a double.
func (AnyValue) AsInt ¶
AsInt returns an int value, or 0 if it is not set (NULL), or the value is not an int.
type BlobValue ¶
type BlobValue struct { // Set is false if the value is NULL, otherwise true. Set bool // Value is the []byte value. Value []byte }
A BlobValue holds a nullable []byte value. The zero value is not set (a.k.a. NULL).
type DoubleValue ¶
type DoubleValue struct { // Set is false if the value is NULL, otherwise true. Set bool // Value is the float64 value. Value float64 }
A DoubleValue holds a nullable float64 value. The zero value is not set (a.k.a. NULL).
func (DoubleValue) IsNull ¶ added in v1.1.1
func (v DoubleValue) IsNull() bool
IsNull returns true if the value is NULL, otherwise true.
type Int64Value ¶
type Int64Value struct { // Set is false if the value is NULL, otherwise true. Set bool // Value is the int64 value. Value int64 }
An Int64Value holds a nullable int64 value. The zero value is not set (a.k.a. NULL).
func (Int64Value) IsNull ¶ added in v1.1.1
func (v Int64Value) IsNull() bool
IsNull returns true if the value is NULL, otherwise true.
type IntValue ¶
type IntValue struct { // Set is false if the value is NULL, otherwise true. Set bool // Value is the int value. Value int }
An IntValue holds a nullable int value. The zero value is not set (a.k.a. NULL).
type Logger ¶
type Logger interface {
Log(s string)
}
A Logger logs error and debug messages coming from the stderr output of the sqinn child process.
type Options ¶
type Options struct { // Path to Sqinn executable. Can be an absolute or relative path. // Empty is the same as "sqinn". Default is empty. SqinnPath string // Logger logs the debug and error messages that the sinn subprocess will output // on its stderr. Default is nil, which does not log anything. Logger Logger // Log the binary io protocol. Only for debugging. Should normally be false. Default is false. LogBinary bool }
Options for launching a Sqinn instance.
type Row ¶
type Row struct {
Values []AnyValue
}
A Row represents a query result row and holds a slice of values, one value per requested column.
type Sqinn ¶
type Sqinn struct {
// contains filtered or unexported fields
}
Sqinn is a running sqinn instance.
func Launch ¶
Launch launches a new Sqinn subprocess. The options specify the path to the sqinn executable, among others. See docs for Options for details. If an error occurs, it returns (nil, err).
func MustLaunch ¶ added in v1.1.1
MustLaunch is like Launch except it panics on error.
func (*Sqinn) Bind ¶
Bind binds the iparam'th parameter with the specified value. The value can be an int, int64, float64, string, []byte or nil. Not that iparam starts at 1 (not 0):
This is a low-level function. Use Exec/Query instead.
For further details, see https://www.sqlite.org/c3ref/bind_blob.html.
func (*Sqinn) Changes ¶
Changes counts the number of rows modified by the last SQL operation.
This is a low-level function. Use Exec/Query instead.
For further details, see https://www.sqlite.org/c3ref/changes.html.
func (*Sqinn) Close ¶
Close closes the database connection that has been opened with Open. After Close has been called, this Sqinn instance can be terminated, or another database can be opened with Open.
For further details, see https://www.sqlite.org/c3ref/close.html.
func (*Sqinn) Column ¶
Column retrieves the value of the icol'th column. The colType specifies the expected type of the column value. Note that icol starts at 0 (not 1).
This is a low-level function. Use Exec/Query instead.
For further details, see https://www.sqlite.org/c3ref/column_blob.html.
func (*Sqinn) Exec ¶
Exec executes a SQL statement multiple times and returns the number of modified rows for each iteration. It supports bind parmeters. Exec is used to execute SQL statements that do not return results (see Query for those).
The niterations tells Exec how often to run the sql. It must be >= 0 and should be >= 1. If niterations is zero, the statement is not run at all, and the method call is a waste of CPU cycles.
Binding sql parameters is possible with the nparams and values arguments. The nparams argument tells Exec how many parameters to bind per iteration. nparams must be >= 0.
The values argument holds the parameter values. Parameter values can be of the following type: int, int64, float64, string, blob or nil. The length of values must always be niterations * nparams.
Internally, Exec preapres a statement, binds nparams parameters, steps the statement, resets the statement, binds the next nparams parameters, and so on, until niterations is reached.
Exec returns, for each iteration, the count of modified rows. The resulting int slice will always be of length niterations.
If an error occurs, it will return (nil, err).
func (*Sqinn) ExecOne ¶
ExecOne executes a SQL statement and returns the number of modified rows. It is used primarily for short, simple statements that have no parameters and do not query rows. A good use case is for beginning and committing a transaction:
_, err = sq.ExecOne("BEGIN"); // do stuff in tx _, err = sq.ExecOne("COMMIT");
Another use case is for DDL statements:
_, err = sq.ExecOne("DROP TABLE users"); _, err = sq.ExecOne("CREATE TABLE foo (name VARCHAR)");
ExecOne(sql) has the same effect as Exec(sql, 1, 0, nil).
If a error occurs, ExecOne will return (0, err).
func (*Sqinn) Finalize ¶
Finalize finalizes a statement that has been prepared with Prepare. To avoid memory leaks, each statement has to be finalized. Moreover, since Sqinn allows only one statement at a time, each statement must be finalized before a new statement can be prepared.
This is a low-level function. Use Exec/Query instead.
For further details, see https://www.sqlite.org/c3ref/finalize.html.
func (*Sqinn) IoVersion ¶
IoVersion returns the protocol version for this Sqinn instance. The version is >= 1. If an error occurs, it returns (0, err).
func (*Sqinn) MustExecOne ¶
MustExecOne is like ExecOne except it panics on error.
func (*Sqinn) Open ¶
Open opens a database. The filename can be ":memory:" or any filesystem path, e.g. "/tmp/test.db". Sqinn keeps the database open until Close is called. After Close has been called, this Sqinn instance can be terminated with Terminate, or Open can be called again, either on the same database or on a different one. For every Open there should be a Close call.
For further details, see https://www.sqlite.org/c3ref/open.html.
func (*Sqinn) Prepare ¶
Prepare prepares a statement, using the provided sql string. To avoid memory leaks, each prepared statement must be finalized after use. Sqinn allows only one prepared statement at at time, preparing a statement while another statement is still active (not yet finalized) will result in a error.
This is a low-level function. Use Exec/Query instead.
For further details, see https://www.sqlite.org/c3ref/prepare.html.
func (*Sqinn) Query ¶
Query executes a SQL statement and returns all rows. Query is used for SELECT statements.
The params argument holds a list of bind parameters. Values must be of type int, int64, float64, string, []byte or nil.
The colTypes argument holds a list of column types that the query yields.
Query returns all resulting rows at once. There is no way to interrupt a Query while it is running. If a Query yields more data than can fit into memory, the behavior is undefined, most likely an out-of-memory condition will crash your program. It is up to the caller to make sure that all queried data fits into memory. The sql 'LIMIT' operator may be helpful.
Each returned Row contains a slice of values. The number of values per row is equal to the length of colTypes.
If an error occurs, it will return (nil, err).
func (*Sqinn) Reset ¶
Reset resets the current statement to its initial state.
This is a low-level function. Use Exec/Query instead.
For further details, see https://www.sqlite.org/c3ref/reset.html.
func (*Sqinn) SqinnVersion ¶
SqinnVersion returns the version of the Sqinn executable. If an error occurs, it returns ("", err).
func (*Sqinn) SqliteVersion ¶
SqliteVersion returns the SQLite library version Sqinn was built with. If an error occurs, it returns ("", err).
func (*Sqinn) Step ¶
Step advances the current statement to the next row or to completion. It returns true if there are more rows available, false if not.
This is a low-level function. Use Exec/Query instead.
For further details, see https://www.sqlite.org/c3ref/step.html.
type StdLogger ¶
type StdLogger struct { // Logger will be used for writing log outputs. // If Logger is nil, the log package default output will be used. Logger *log.Logger }
A StdLogger logs to a stdlib log.Logger or to the log's standard logger.
type StringValue ¶
type StringValue struct { // Set is false if the value is NULL, otherwise true. Set bool // Value is the string value. Value string }
A StringValue holds a nullable string value. The zero value is not set (a.k.a. NULL).
func (StringValue) IsNull ¶ added in v1.1.1
func (v StringValue) IsNull() bool
IsNull returns true if the value is NULL, otherwise true.