Skip to content
sqlite3 binding for go
Go C
Find file
Pull request Compare This branch is 412 commits ahead of patrickxb:master.
Latest commit 693b5e4 Mar 30, 2016 @gwenn Update README.md
Failed to load latest commit information.
shell Add completion methods for database, table and column names Jan 7, 2015
.gitignore Improve error handling by giving access to details. Dec 25, 2011
.travis.yml Skip some tests when cgocheck is enabled. Feb 21, 2016
LICENSE Adds LICENSE file. Apr 22, 2013
README.md Update README.md Mar 30, 2016
backup.go Rename NewBackup parameters. Feb 1, 2015
backup_test.go Improve test coverage. Mar 16, 2014
bench_test.go Trim tail. Jul 31, 2015
blob.go Remove some deferred free calls Oct 31, 2014
blob_test.go Improve test coverage Mar 22, 2014
busy_test.go Skip some tests when cgocheck is enabled. Feb 21, 2016
cache.go Improve doc comments. Feb 23, 2014
cache_test.go Skip some tests when cgocheck is enabled. Feb 21, 2016
config.c Introduce methods to change memory-mapped IO region. Oct 15, 2014
config.go Add binding to sqlite3_compileoption_used Oct 3, 2014
config_extra.go Try to fix Travis build. Oct 15, 2014
config_extra_test.go Try to fix Travis build. Oct 15, 2014
config_test.go Introduce methods to change memory-mapped IO region. Oct 15, 2014
csv.go Panic if column index is out of range. Apr 12, 2015
csv_test.go Skip some tests when cgocheck is enabled. Feb 21, 2016
date.go Misc Jun 1, 2014
date_test.go Improve date/time handling. Mar 22, 2014
driver.go It seems that driver.Result LastInsertId/RowsAffected cannot be lazil… Mar 3, 2016
driver_test.go Fix Travis build Nov 30, 2014
example_test.go Update main example. Jul 6, 2014
function.c Misc Oct 3, 2014
function.go Document API entries that can't be used with cgocheck enabled. Feb 21, 2016
function_test.go Skip some tests when cgocheck is enabled. Feb 21, 2016
hook.c Golint May 22, 2014
hook.go Document API entries that can't be used with cgocheck enabled. Feb 21, 2016
hook_test.go Skip some tests when cgocheck is enabled. Feb 21, 2016
intarray.c Add missing intarray C file. Jun 24, 2014
intarray.go Remove some deferred free calls Oct 31, 2014
intarray_test.go Go lint and vet. Jul 6, 2014
limit.go Benchmark IntArray module. Jun 21, 2014
limit_test.go Benchmark IntArray module. Jun 21, 2014
meta.go Panic if column index is out of range. Apr 12, 2015
meta_extra.go Panic if column index is out of range. Apr 12, 2015
meta_extra_test.go Panic if column index is out of range. Apr 12, 2015
meta_test.go Panic if column index is out of range. Apr 12, 2015
pool.go Add missing doc Nov 1, 2014
pool_test.go Exclude limit and pool from default build. Mar 1, 2014
pragma.go Introduce methods to change memory-mapped IO region. Oct 15, 2014
pragma_test.go Introduce testing.Skip. Oct 18, 2014
sqlite.go Document that LastErrror is not threadsafe. Mar 20, 2015
sqlite_test.go I don't know how to check current go version is greater than 1.6 Feb 21, 2016
stmt.go Skip some tests when cgocheck is enabled. Feb 21, 2016
stmt_test.go Panic if column index is out of range. Apr 12, 2015
test.csv First draft of a CSV module for loading CSV file as virtual table. Feb 15, 2014
trace.c Misc Oct 3, 2014
trace.go Document API entries that can't be used with cgocheck enabled. Feb 21, 2016
trace_test.go Skip some tests when cgocheck is enabled. Feb 21, 2016
util.go Go lint and vet. Jul 6, 2014
vtab.c Fix indentation. Jan 23, 2015
vtab.go Document API entries that can't be used with cgocheck enabled. Feb 21, 2016
vtab_test.go Skip some tests when cgocheck is enabled. Feb 21, 2016

README.md

Yet another SQLite binding based on:

There are two layers:

  • one matching the SQLite API (with Backup, Blob, user-defined Function/Module, ...).
  • and another implementing the "database/sql/driver" interface.

GoDoc

Build Status

Caveat

With Go 1.6, some features do not work anymore without GODEBUG=cgocheck=0 (see Cgo major change). It seems that the solution is a global variable/lock (see here). "I would prefer not to" do this.

Custom build

If your OS does not bundle SQLite3 development files (or old ones):

  • download and copy SQLite3 files
$ cp ~/Downloads/sqlite-amalgamation-xxx/sqlite3.{c,h} $GOPATH/src/github.com/gwenn/gosqlite
  • patch sqlite.go file
-#cgo LDFLAGS: -lsqlite3
+#cgo CFLAGS: -I.
+#cgo CFLAGS: -DSQLITE_ENABLE_COLUMN_METADATA=1

Features (not supported by database/sql/driver):

  • Named bind parameters.
  • Partial scan: scan values may be partially scanned (by index or name) or skipped/ignored by passing nil pointer(s).
  • Null value: by default, empty string and zero time are bound to NULL for prepared statement's parameters (no need for NullString, NullTime but still supported).
  • Null value: Stmt.Scan methods return default Go zero value (0, "", ...) for SQL NULL (no need for NullInt64, NullString, NullTime but still supported).
  • Correctly retrieve the time returns by select current_timestamp statement or others expressions: in SQLite, expression affinity is NONE.
  • Full control over connection pool
  • No restrictive converter
  • Support for metadata
  • Nested transaction support

Changes:

Open supports flags.
Conn.Exec handles multiple statements (separated by semicolons) properly.
Conn.Prepare can optionally bind as well.
Conn.Prepare can reuse already prepared Stmt.
Conn.Close ensures that all dangling statements are finalized.
Stmt.Exec is renamed in Stmt.Bind and a new Stmt.Exec method is introduced to bind and step.
Stmt.Bind uses native sqlite3_bind_x methods and failed if unsupported type.
Stmt.NamedBind can be used to bind by name.
Stmt.Next returns a (bool, os.Error) couple like Reader.Read.
Stmt.Scan uses native sqlite3_column_x methods.
Stmt.NamedScan is added. It's compliant with go-dbi.
Stmt.ScanByIndex/ScanByName are added to test NULL value.

Currently, the weak point of the binding is the Scan methods: The original implementation is using this strategy:

  • convert the stored value to a []byte by calling sqlite3_column_blob,
  • convert the bytes to the desired Go type with correct feedback in case of illegal conversion,
  • but apparently no support for NULL value.

Using the native sqlite3_column_x implies:

  • optimal conversion from the storage type to Go type (when they match),
  • lossy conversion when types mismatch (select cast('M' as int); --> 0),
  • NULL value can be returned only for **type, otherwise a default value (0, false, "") is returned.

SQLite logs (SQLITE_CONFIG_LOG) can be activated by:

  • ConfigLog function
  • or export SQLITE_LOG=1

Similar projects created after Jul 17, 2011:

https://github.com/mattn/go-sqlite3 (Nov 11, 2011)
https://github.com/mxk/go-sqlite (Feb 12, 2013)

Additions:

Conn.Exists
Conn.OneValue

Conn.OpenVfs
Conn.EnableFkey/IsFKeyEnabled
Conn.Changes/TotalChanges
Conn.LastInsertRowid
Conn.Interrupt
Conn.Begin/BeginTransaction(type)/Commit/Rollback
Conn.GetAutocommit
Conn.EnableLoadExtension/LoadExtension
Conn.IntegrityCheck

Stmt.Insert/ExecDml/Select/SelectOneRow
Stmt.BindParameterCount/BindParameterIndex(name)/BindParameterName(index)
Stmt.ClearBindings
Stmt.ColumnCount/ColumnNames/ColumnIndex(name)/ColumnName(index)/ColumnType(index)
Stmt.ReadOnly
Stmt.Busy

Blob:
ZeroBlobLength
Conn.NewBlobReader
Conn.NewBlobReadWriter

Meta:
Conn.Databases
Conn.Tables/Views/Indexes
Conn.Columns
Conn.ForeignKeys
Conn.TableIndexes/IndexColumns

Time:
JulianDay
JulianDayToUTC
JulianDayToLocalTime
UnixTime, JulianTime and TimeStamp used to persist go time in formats supported by SQLite3 date functions.

Trace:
Conn.BusyHandler
Conn.Profile
Conn.ProgressHandler
Conn.SetAuthorizer
Conn.Trace
Stmt.Status

Hook:
Conn.CommitHook
Conn.RollbackHook
Conn.UpdateHook

Function:
Conn.CreateScalarFunction
Conn.CreateAggregateFunction

Virtual Table (partial support):
Conn.CreateModule
Conn.DeclareVTab

GC:

Although Go is gced, there is no destructor (see http://www.airs.com/blog/archives/362).
In the gosqlite wrapper, no finalizer is used.
So users must ensure that C resources (database connections, prepared statements, BLOBs, Backups) are destroyed/deallocated by calling Conn.Close, Stmt.Finalize, BlobReader.Close, Backup.Close.

Therefore, sqlite3_close/sqlite3_next_stmt are used by Conn.Close to free the database connection and all dangling statements (not sqlite3_close_v2) (see http://sqlite.org/c3ref/close.html).

Benchmarks:

$ go test -bench . -benchmem

BenchmarkValuesScan   500000          6265 ns/op          74 B/op          3 allocs/op
BenchmarkScan     500000          4994 ns/op          41 B/op          4 allocs/op
BenchmarkNamedScan    500000          4960 ns/op          93 B/op          7 allocs/op

BenchmarkInsert   500000          4085 ns/op          16 B/op          1 allocs/op
BenchmarkNamedInsert      500000          4798 ns/op          64 B/op          4 allocs/op

BenchmarkDisabledCache    100000         19841 ns/op         117 B/op          3 allocs/op
BenchmarkEnabledCache    2000000           790 ns/op          50 B/op          1 allocs/op

BenchmarkLike    1000000          2605 ns/op           0 B/op          0 allocs/op
BenchmarkHalf     500000          4988 ns/op          33 B/op          1 allocs/op
BenchmarkRegexp   500000          5557 ns/op           8 B/op          1 allocs/op
Something went wrong with that request. Please try again.