go-sqlite3: github.com/mattn/go-sqlite3 Index | Files | Directories

package sqlite3

import "github.com/mattn/go-sqlite3"

Package sqlite3 provides interface to SQLite3 databases.

This works as a driver for database/sql.


go get github.com/mattn/go-sqlite3

Supported Types

Currently, go-sqlite3 supports the following data types.

|go        | sqlite3           |
|nil       | null              |
|int       | integer           |
|int64     | integer           |
|float64   | float             |
|bool      | integer           |
|[]byte    | blob              |
|string    | text              |
|time.Time | timestamp/datetime|

SQLite3 Extension

You can write your own extension module for sqlite3. For example, below is an extension for a Regexp matcher operation.

#include <pcre.h>
#include <string.h>
#include <stdio.h>
#include <sqlite3ext.h>

static void regexp_func(sqlite3_context *context, int argc, sqlite3_value **argv) {
  if (argc >= 2) {
    const char *target  = (const char *)sqlite3_value_text(argv[1]);
    const char *pattern = (const char *)sqlite3_value_text(argv[0]);
    const char* errstr = NULL;
    int erroff = 0;
    int vec[500];
    int n, rc;
    pcre* re = pcre_compile(pattern, 0, &errstr, &erroff, NULL);
    rc = pcre_exec(re, NULL, target, strlen(target), 0, 0, vec, 500);
    if (rc <= 0) {
      sqlite3_result_error(context, errstr, 0);
    sqlite3_result_int(context, 1);

#ifdef _WIN32
int sqlite3_extension_init(sqlite3 *db, char **errmsg,
      const sqlite3_api_routines *api) {
  return sqlite3_create_function(db, "regexp", 2, SQLITE_UTF8,
      (void*)db, regexp_func, NULL, NULL);

It needs to be built as a so/dll shared library. And you need to register the extension module like below.

		Extensions: []string{

Then, you can use this extension.

rows, err := db.Query("select text from mytable where name regexp '^golang'")

Connection Hook

You can hook and inject your code when the connection is established. database/sql doesn't provide a way to get native go-sqlite3 interfaces. So if you want, you need to set ConnectHook and get the SQLiteConn.

				ConnectHook: func(conn *sqlite3.SQLiteConn) error {
					sqlite3conn = append(sqlite3conn, conn)
					return nil

Go SQlite3 Extensions

If you want to register Go functions as SQLite extension functions, call RegisterFunction from ConnectHook.

regex = func(re, s string) (bool, error) {
	return regexp.MatchString(re, s)
				ConnectHook: func(conn *sqlite3.SQLiteConn) error {
					return conn.RegisterFunc("regexp", regex, true)

See the documentation of RegisterFunc for more details.


Package Files

backup.go callback.go doc.go error.go sqlite3.go sqlite3_context.go sqlite3_go18.go sqlite3_load_extension.go sqlite3_other.go sqlite3_type.go


const (
const (

Run-Time Limit Categories. See: http://www.sqlite.org/c3ref/c_limit_attached.html

const ErrNoMask C.int = 0xff

ErrNoMask is mask code.


var (
    ErrError      = ErrNo(1)  /* SQL error or missing database */
    ErrInternal   = ErrNo(2)  /* Internal logic error in SQLite */
    ErrPerm       = ErrNo(3)  /* Access permission denied */
    ErrAbort      = ErrNo(4)  /* Callback routine requested an abort */
    ErrBusy       = ErrNo(5)  /* The database file is locked */
    ErrLocked     = ErrNo(6)  /* A table in the database is locked */
    ErrNomem      = ErrNo(7)  /* A malloc() failed */
    ErrReadonly   = ErrNo(8)  /* Attempt to write a readonly database */
    ErrInterrupt  = ErrNo(9)  /* Operation terminated by sqlite3_interrupt() */
    ErrIoErr      = ErrNo(10) /* Some kind of disk I/O error occurred */
    ErrCorrupt    = ErrNo(11) /* The database disk image is malformed */
    ErrNotFound   = ErrNo(12) /* Unknown opcode in sqlite3_file_control() */
    ErrFull       = ErrNo(13) /* Insertion failed because database is full */
    ErrCantOpen   = ErrNo(14) /* Unable to open the database file */
    ErrProtocol   = ErrNo(15) /* Database lock protocol error */
    ErrEmpty      = ErrNo(16) /* Database is empty */
    ErrSchema     = ErrNo(17) /* The database schema changed */
    ErrTooBig     = ErrNo(18) /* String or BLOB exceeds size limit */
    ErrConstraint = ErrNo(19) /* Abort due to constraint violation */
    ErrMismatch   = ErrNo(20) /* Data type mismatch */
    ErrMisuse     = ErrNo(21) /* Library used incorrectly */
    ErrNoLFS      = ErrNo(22) /* Uses OS features not supported on host */
    ErrAuth       = ErrNo(23) /* Authorization denied */
    ErrFormat     = ErrNo(24) /* Auxiliary database format error */
    ErrRange      = ErrNo(25) /* 2nd parameter to sqlite3_bind out of range */
    ErrNotADB     = ErrNo(26) /* File opened that is not a database file */
    ErrNotice     = ErrNo(27) /* Notifications from sqlite3_log() */
    ErrWarning    = ErrNo(28) /* Warnings from sqlite3_log() */

result codes from http://www.sqlite.org/c3ref/c_abort.html

var (
    ErrIoErrRead              = ErrIoErr.Extend(1)
    ErrIoErrShortRead         = ErrIoErr.Extend(2)
    ErrIoErrWrite             = ErrIoErr.Extend(3)
    ErrIoErrFsync             = ErrIoErr.Extend(4)
    ErrIoErrDirFsync          = ErrIoErr.Extend(5)
    ErrIoErrTruncate          = ErrIoErr.Extend(6)
    ErrIoErrFstat             = ErrIoErr.Extend(7)
    ErrIoErrUnlock            = ErrIoErr.Extend(8)
    ErrIoErrRDlock            = ErrIoErr.Extend(9)
    ErrIoErrDelete            = ErrIoErr.Extend(10)
    ErrIoErrBlocked           = ErrIoErr.Extend(11)
    ErrIoErrNoMem             = ErrIoErr.Extend(12)
    ErrIoErrAccess            = ErrIoErr.Extend(13)
    ErrIoErrCheckReservedLock = ErrIoErr.Extend(14)
    ErrIoErrLock              = ErrIoErr.Extend(15)
    ErrIoErrClose             = ErrIoErr.Extend(16)
    ErrIoErrDirClose          = ErrIoErr.Extend(17)
    ErrIoErrSHMOpen           = ErrIoErr.Extend(18)
    ErrIoErrSHMSize           = ErrIoErr.Extend(19)
    ErrIoErrSHMLock           = ErrIoErr.Extend(20)
    ErrIoErrSHMMap            = ErrIoErr.Extend(21)
    ErrIoErrSeek              = ErrIoErr.Extend(22)
    ErrIoErrDeleteNoent       = ErrIoErr.Extend(23)
    ErrIoErrMMap              = ErrIoErr.Extend(24)
    ErrIoErrGetTempPath       = ErrIoErr.Extend(25)
    ErrIoErrConvPath          = ErrIoErr.Extend(26)
    ErrLockedSharedCache      = ErrLocked.Extend(1)
    ErrBusyRecovery           = ErrBusy.Extend(1)
    ErrBusySnapshot           = ErrBusy.Extend(2)
    ErrCantOpenNoTempDir      = ErrCantOpen.Extend(1)
    ErrCantOpenIsDir          = ErrCantOpen.Extend(2)
    ErrCantOpenFullPath       = ErrCantOpen.Extend(3)
    ErrCantOpenConvPath       = ErrCantOpen.Extend(4)
    ErrCorruptVTab            = ErrCorrupt.Extend(1)
    ErrReadonlyRecovery       = ErrReadonly.Extend(1)
    ErrReadonlyCantLock       = ErrReadonly.Extend(2)
    ErrReadonlyRollback       = ErrReadonly.Extend(3)
    ErrReadonlyDbMoved        = ErrReadonly.Extend(4)
    ErrAbortRollback          = ErrAbort.Extend(2)
    ErrConstraintCheck        = ErrConstraint.Extend(1)
    ErrConstraintCommitHook   = ErrConstraint.Extend(2)
    ErrConstraintForeignKey   = ErrConstraint.Extend(3)
    ErrConstraintFunction     = ErrConstraint.Extend(4)
    ErrConstraintNotNull      = ErrConstraint.Extend(5)
    ErrConstraintPrimaryKey   = ErrConstraint.Extend(6)
    ErrConstraintTrigger      = ErrConstraint.Extend(7)
    ErrConstraintUnique       = ErrConstraint.Extend(8)
    ErrConstraintVTab         = ErrConstraint.Extend(9)
    ErrConstraintRowID        = ErrConstraint.Extend(10)
    ErrNoticeRecoverWAL       = ErrNotice.Extend(1)
    ErrNoticeRecoverRollback  = ErrNotice.Extend(2)
    ErrWarningAutoIndex       = ErrWarning.Extend(1)

result codes from http://www.sqlite.org/c3ref/c_abort_rollback.html

var SQLiteTimestampFormats = []string{

    "2006-01-02 15:04:05.999999999-07:00",
    "2006-01-02 15:04:05.999999999",
    "2006-01-02 15:04:05",
    "2006-01-02 15:04",

SQLiteTimestampFormats is timestamp formats understood by both this module and SQLite. The first format in the slice will be used when saving time values into the database. When parsing a string from a timestamp or datetime column, the formats are tried in order.

func Version Uses

func Version() (libVersion string, libVersionNumber int, sourceID string)

Version returns SQLite library version information.

type ErrNo Uses

type ErrNo int

ErrNo inherit errno.

func (ErrNo) Error Uses

func (err ErrNo) Error() string

Error return error message from errno.

func (ErrNo) Extend Uses

func (err ErrNo) Extend(by int) ErrNoExtended

Extend return extended errno.

type ErrNoExtended Uses

type ErrNoExtended int

ErrNoExtended is extended errno.

func (ErrNoExtended) Error Uses

func (err ErrNoExtended) Error() string

Error return error message that is extended code.

type Error Uses

type Error struct {
    Code         ErrNo         /* The error code returned by SQLite */
    ExtendedCode ErrNoExtended /* The extended error code returned by SQLite */
    // contains filtered or unexported fields

Error implement sqlite error code.

func (Error) Error Uses

func (err Error) Error() string

type SQLiteBackup Uses

type SQLiteBackup struct {
    // contains filtered or unexported fields

SQLiteBackup implement interface of Backup.

func (*SQLiteBackup) Close Uses

func (b *SQLiteBackup) Close() error

Close close backup.

func (*SQLiteBackup) Finish Uses

func (b *SQLiteBackup) Finish() error

Finish close backup.

func (*SQLiteBackup) PageCount Uses

func (b *SQLiteBackup) PageCount() int

PageCount return count of pages.

func (*SQLiteBackup) Remaining Uses

func (b *SQLiteBackup) Remaining() int

Remaining return whether have the rest for backup.

func (*SQLiteBackup) Step Uses

func (b *SQLiteBackup) Step(p int) (bool, error)

Step to backs up for one step. Calls the underlying `sqlite3_backup_step` function. This function returns a boolean indicating if the backup is done and an error signalling any other error. Done is returned if the underlying C function returns SQLITE_DONE (Code 101)

type SQLiteConn Uses

type SQLiteConn struct {
    // contains filtered or unexported fields

SQLiteConn implement sql.Conn.

func (*SQLiteConn) AutoCommit Uses

func (c *SQLiteConn) AutoCommit() bool

AutoCommit return which currently auto commit or not.

func (*SQLiteConn) Backup Uses

func (c *SQLiteConn) Backup(dest string, conn *SQLiteConn, src string) (*SQLiteBackup, error)

Backup make backup from src to dest.

func (*SQLiteConn) Begin Uses

func (c *SQLiteConn) Begin() (driver.Tx, error)

Begin transaction.

func (*SQLiteConn) BeginTx Uses

func (c *SQLiteConn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)

BeginTx implement ConnBeginTx.

func (*SQLiteConn) Close Uses

func (c *SQLiteConn) Close() error

Close the connection.

func (*SQLiteConn) Exec Uses

func (c *SQLiteConn) Exec(query string, args []driver.Value) (driver.Result, error)

Exec implements Execer.

func (*SQLiteConn) ExecContext Uses

func (c *SQLiteConn) ExecContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Result, error)

ExecContext implement ExecerContext.

func (*SQLiteConn) GetLimit Uses

func (c *SQLiteConn) GetLimit(id int) int

GetLimit returns the current value of a run-time limit. See: sqlite3_limit, http://www.sqlite.org/c3ref/limit.html

func (*SQLiteConn) LoadExtension Uses

func (c *SQLiteConn) LoadExtension(lib string, entry string) error

LoadExtension load the sqlite3 extension.

func (*SQLiteConn) Ping Uses

func (c *SQLiteConn) Ping(ctx context.Context) error

Ping implement Pinger.

func (*SQLiteConn) Prepare Uses

func (c *SQLiteConn) Prepare(query string) (driver.Stmt, error)

Prepare the query string. Return a new statement.

func (*SQLiteConn) PrepareContext Uses

func (c *SQLiteConn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)

PrepareContext implement ConnPrepareContext.

func (*SQLiteConn) Query Uses

func (c *SQLiteConn) Query(query string, args []driver.Value) (driver.Rows, error)

Query implements Queryer.

func (*SQLiteConn) QueryContext Uses

func (c *SQLiteConn) QueryContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, error)

QueryContext implement QueryerContext.

func (*SQLiteConn) RegisterAggregator Uses

func (c *SQLiteConn) RegisterAggregator(name string, impl interface{}, pure bool) error

RegisterAggregator makes a Go type available as a SQLite aggregation function.

Because aggregation is incremental, it's implemented in Go with a type that has 2 methods: func Step(values) accumulates one row of data into the accumulator, and func Done() ret finalizes and returns the aggregate value. "values" and "ret" may be any type supported by RegisterFunc.

RegisterAggregator takes as implementation a constructor function that constructs an instance of the aggregator type each time an aggregation begins. The constructor must return a pointer to a type, or an interface that implements Step() and Done().

The constructor function and the Step/Done methods may optionally return an error in addition to their other return values.

See _example/go_custom_funcs for a detailed example.

func (*SQLiteConn) RegisterCollation Uses

func (c *SQLiteConn) RegisterCollation(name string, cmp func(string, string) int) error

RegisterCollation makes a Go function available as a collation.

cmp receives two UTF-8 strings, a and b. The result should be 0 if a==b, -1 if a < b, and +1 if a > b.

cmp must always return the same result given the same inputs. Additionally, it must have the following properties for all strings A, B and C: if A==B then B==A; if A==B and B==C then A==C; if A<B then B>A; if A<B and B<C then A<C.

If cmp does not obey these constraints, sqlite3's behavior is undefined when the collation is used.

func (*SQLiteConn) RegisterCommitHook Uses

func (c *SQLiteConn) RegisterCommitHook(callback func() int)

RegisterCommitHook sets the commit hook for a connection.

If the callback returns non-zero the transaction will become a rollback.

If there is an existing commit hook for this connection, it will be removed. If callback is nil the existing hook (if any) will be removed without creating a new one.

func (*SQLiteConn) RegisterFunc Uses

func (c *SQLiteConn) RegisterFunc(name string, impl interface{}, pure bool) error

RegisterFunc makes a Go function available as a SQLite function.

The Go function can have arguments of the following types: any numeric type except complex, bool, []byte, string and interface{}. interface{} arguments are given the direct translation of the SQLite data type: int64 for INTEGER, float64 for FLOAT, []byte for BLOB, string for TEXT.

The function can additionally be variadic, as long as the type of the variadic argument is one of the above.

If pure is true. SQLite will assume that the function's return value depends only on its inputs, and make more aggressive optimizations in its queries.

See _example/go_custom_funcs for a detailed example.

func (*SQLiteConn) RegisterRollbackHook Uses

func (c *SQLiteConn) RegisterRollbackHook(callback func())

RegisterRollbackHook sets the rollback hook for a connection.

If there is an existing rollback hook for this connection, it will be removed. If callback is nil the existing hook (if any) will be removed without creating a new one.

func (*SQLiteConn) RegisterUpdateHook Uses

func (c *SQLiteConn) RegisterUpdateHook(callback func(int, string, string, int64))

RegisterUpdateHook sets the update hook for a connection.

The parameters to the callback are the operation (one of the constants SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE), the database name, the table name, and the rowid.

If there is an existing update hook for this connection, it will be removed. If callback is nil the existing hook (if any) will be removed without creating a new one.

func (*SQLiteConn) SetLimit Uses

func (c *SQLiteConn) SetLimit(id int, newVal int) int

SetLimit changes the value of a run-time limits. Then this method returns the prior value of the limit. See: sqlite3_limit, http://www.sqlite.org/c3ref/limit.html

type SQLiteContext Uses

type SQLiteContext C.sqlite3_context

SQLiteContext behave sqlite3_context

func (*SQLiteContext) ResultBlob Uses

func (c *SQLiteContext) ResultBlob(b []byte)

ResultBlob sets the result of an SQL function. See: sqlite3_result_blob, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultBool Uses

func (c *SQLiteContext) ResultBool(b bool)

ResultBool sets the result of an SQL function.

func (*SQLiteContext) ResultDouble Uses

func (c *SQLiteContext) ResultDouble(d float64)

ResultDouble sets the result of an SQL function. See: sqlite3_result_double, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultInt Uses

func (c *SQLiteContext) ResultInt(i int)

ResultInt sets the result of an SQL function. See: sqlite3_result_int, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultInt64 Uses

func (c *SQLiteContext) ResultInt64(i int64)

ResultInt64 sets the result of an SQL function. See: sqlite3_result_int64, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultNull Uses

func (c *SQLiteContext) ResultNull()

ResultNull sets the result of an SQL function. See: sqlite3_result_null, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultText Uses

func (c *SQLiteContext) ResultText(s string)

ResultText sets the result of an SQL function. See: sqlite3_result_text, http://sqlite.org/c3ref/result_blob.html

func (*SQLiteContext) ResultZeroblob Uses

func (c *SQLiteContext) ResultZeroblob(n int)

ResultZeroblob sets the result of an SQL function. See: sqlite3_result_zeroblob, http://sqlite.org/c3ref/result_blob.html

type SQLiteDriver Uses

type SQLiteDriver struct {
    Extensions  []string
    ConnectHook func(*SQLiteConn) error

SQLiteDriver implement sql.Driver.

func (*SQLiteDriver) Open Uses

func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error)

Open database and return a new connection. You can specify a DSN string using a URI as the filename.


go-sqlite3 adds the following query parameters to those used by SQLite:

  Specify location of time format. It's possible to specify "auto".
  Specify value for sqlite3_busy_timeout.
  Specify locking behavior for transactions.  XXX can be "immediate",
  "deferred", "exclusive".
  Enable or disable enforcement of foreign keys.  X can be 1 or 0.
  Enable or disable recursive triggers.  X can be 1 or 0.

type SQLiteResult Uses

type SQLiteResult struct {
    // contains filtered or unexported fields

SQLiteResult implement sql.Result.

func (*SQLiteResult) LastInsertId Uses

func (r *SQLiteResult) LastInsertId() (int64, error)

LastInsertId teturn last inserted ID.

func (*SQLiteResult) RowsAffected Uses

func (r *SQLiteResult) RowsAffected() (int64, error)

RowsAffected return how many rows affected.

type SQLiteRows Uses

type SQLiteRows struct {
    // contains filtered or unexported fields

SQLiteRows implement sql.Rows.

func (*SQLiteRows) Close Uses

func (rc *SQLiteRows) Close() error

Close the rows.

func (*SQLiteRows) ColumnTypeDatabaseTypeName Uses

func (rc *SQLiteRows) ColumnTypeDatabaseTypeName(i int) string

ColumnTypeDatabaseTypeName implement RowsColumnTypeDatabaseTypeName.

func (*SQLiteRows) ColumnTypeNullable Uses

func (rc *SQLiteRows) ColumnTypeNullable(i int) (nullable, ok bool)

ColumnTypeNullable implement RowsColumnTypeNullable.

func (*SQLiteRows) ColumnTypeScanType Uses

func (rc *SQLiteRows) ColumnTypeScanType(i int) reflect.Type

ColumnTypeScanType implement RowsColumnTypeScanType.

func (*SQLiteRows) Columns Uses

func (rc *SQLiteRows) Columns() []string

Columns return column names.

func (*SQLiteRows) DeclTypes Uses

func (rc *SQLiteRows) DeclTypes() []string

DeclTypes return column types.

func (*SQLiteRows) Next Uses

func (rc *SQLiteRows) Next(dest []driver.Value) error

Next move cursor to next.

type SQLiteStmt Uses

type SQLiteStmt struct {
    // contains filtered or unexported fields

SQLiteStmt implement sql.Stmt.

func (*SQLiteStmt) Close Uses

func (s *SQLiteStmt) Close() error

Close the statement.

func (*SQLiteStmt) Exec Uses

func (s *SQLiteStmt) Exec(args []driver.Value) (driver.Result, error)

Exec execute the statement with arguments. Return result object.

func (*SQLiteStmt) ExecContext Uses

func (s *SQLiteStmt) ExecContext(ctx context.Context, args []driver.NamedValue) (driver.Result, error)

ExecContext implement ExecerContext.

func (*SQLiteStmt) NumInput Uses

func (s *SQLiteStmt) NumInput() int

NumInput return a number of parameters.

func (*SQLiteStmt) Query Uses

func (s *SQLiteStmt) Query(args []driver.Value) (driver.Rows, error)

Query the statement with arguments. Return records.

func (*SQLiteStmt) QueryContext Uses

func (s *SQLiteStmt) QueryContext(ctx context.Context, args []driver.NamedValue) (driver.Rows, error)

QueryContext implement QueryerContext.

type SQLiteTx Uses

type SQLiteTx struct {
    // contains filtered or unexported fields

SQLiteTx implemen sql.Tx.

func (*SQLiteTx) Commit Uses

func (tx *SQLiteTx) Commit() error

Commit transaction.

func (*SQLiteTx) Rollback Uses

func (tx *SQLiteTx) Rollback() error

Rollback transaction.



Package sqlite3 imports 16 packages (graph) and is imported by 2557 packages. Updated 2018-02-19. Refresh now. Tools for package owners.