sqlite

README

go-sqlite

This module provides an alternative interface for sqlite, including:

• Opening in-memory databases and databases by file path;
• Transactions (committing changes and rolling back on errors);
• Reading results into a struct, map or slice.
• Reflection on databases (schemas, tables, columns, indexes, etc);
• Attaching and detaching databases by schema name;
• Executing arbitrary statements or building statements programmatically;

Presently the module is in development and the API is subject to change.

Opening and creating databases

You can create a new in-memory database using the New method, or you can open an existing file-based database using the Open method. Both methods take an optional *time.Location argument which allows interpretation of time values with time zone. For example,

package main

import (
  "github.com/djthorpe/go-sqlite/pkg/sqlite"
)

func main() {
  db, err := sqlite.New() // Open in-memory database with local time zone
  if err != nil {
    // ...
  }
  defer db.Close()

  path := // ...
  db, err := sqlite.Open(path,time.UTC) // Open file database with UTC time zone
  if err != nil {
    // ...
  }
  defer db.Close()
}

Use db.Close() to release database resources.

Executing queries and transactions

Statements are executed using the db.Exec method on a query. In order to create a query from a string, use the Q() method (see below for information on building SQL statements). You can include bound parameters after the query:

For example,

package main

import (
  "github.com/djthorpe/go-sqlite/pkg/sqlite"
  . "github.com/djthorpe/go-sqlite/pkg/lang"
)

func main() {
  db, err := sqlite.New() // Open in-memory database with local time zone
  if err != nil {
    // ...
  }
  defer db.Close()

  if result,err := db.Exec(Q("CREATE TABLE test (a TEXT,b TEXT)")); err != nil {
    // ...
  } else {
    fmt.Println(result)
  }
}

The result object returned (of type SQResult) contains fields LastInsertId and RowsAffected which may or may not be set depending on the query executed. To return data, a result set is returned which should allows you to iterate across the results as a map of values, a slice of values:

package main

import (
  "github.com/djthorpe/go-sqlite/pkg/sqlite"
  . "github.com/djthorpe/go-sqlite/pkg/lang"
)

func main() {
  db, err := sqlite.New() // Open in-memory database with local time zone
  if err != nil {
    // ...
  }
  defer db.Close()

  rs,err := db.Select(Q("SELECT a,b FROM test WHERE a=?"),"foo")
  if err != nil {
    // ...
  }
  defer rs.Close()
  for {
    row := rs.Next()
    if row == nil {
      break
    }
    // ...
  }
}

You can also create a block of code, which when returning any error will rollback any database snapshot, or else commit the snapshot if no error occurred:

package main

import (
  "github.com/djthorpe/go-sqlite/pkg/sqlite"
  . "github.com/djthorpe/go-sqlite/pkg/lang"
)

func main() {
  db, err := sqlite.New() // Open in-memory database with local time zone
  if err != nil {
    // ...
  }
  defer db.Close()

  db.Do(func (txn sqlite.SQTransaction) error {
    _, err := txn.Exec(Q("..."))
    if err != nil {
      // Rollback any database changes
      return err
    }
    
    // Perform further operatins here...

    // Return success, commit transaction
    return nil
  })
}

Supported Column Types

The following types are supported, and expect the declared column types:

Scalar Type	Column Type
int64	INTEGER
float64	FLOAT
string	TEXT
bool	BOOL
time.Time	TIMESTAMP or DATETIME
[]byte	BLOB

If you pass other integer and unsigned integer types into the Exec and Query functions then they are converted to one of the above types. You can also define methods MarshalSQ and UnmarshalSQ in order to convert your custom types into scalar types. For example, the following methods convert between supported scalar types:

type CustomParam struct {
	A, B string
}

func (c CustomParam) MarshalSQ() (interface{}, error) {
	if data, err := json.Marshal(c); err != nil {
		return nil, err
	} else {
		return string(data), err
	}
}

func (c *CustomParam) UnmarshalSQ(v interface{}) error {
	if data, ok := v.(string); ok {
		return json.Unmarshal([]byte(data), c)
	} else {
		return fmt.Errorf("Invalid type: %T", v)
	}
}

Attaching databases by schema name

You can load additional databases to a database by schema name. Use Attach and Detach to attach and detach databases. For example,

Database reflection

Building statements programmatically

A statement builder can be used for generating SQL statements programmatially. It is intended you use the following primitves to build your statements:

• P is a placeholder for a value, which binds to the corresponding placeholder in Query or Exec methods;
• V() is the value function;
• N() is the name function, which corresponds to a table or column name;
• Q() is the quote function, which allows insertation or execution or arbitary queries;
• S() is the select function, which builds up a SELECT statement;

In order to use these primitives within your code, it is suggested you import the laguage namespace directly into your code. For example:

package main

import (
  . "github.com/djthorpe/go-sqlite/pkg/lang"
)

func main() {
  s := S(N("a"),N("b").Distinct().Where(N("a").Is(P))
  fmt.Println(s) // Prints SELECT DISTINCT * FROM a,b WHERE a=?
}

If the symbols P,V,N,Q or S clash with any symbols in your code namespace, you can import the package without the dot prefix and refer to the sumbols prefixed with lang. instead.

Reading results into a struct, map or slice

Importing data

Documentation

Overview

Package sqlite provides a go interface to sqlite.

Please see https://github.com/djthorpe/go-sqlite/blob/master/README.md for information on this module.

Index

• Constants
• type SQAlter
• type SQAuth
• type SQAuthFlag
  • func (v SQAuthFlag) Is(q SQAuthFlag) bool
  • func (v SQAuthFlag) String() string
  • func (v SQAuthFlag) StringFlag() string
• type SQClass
• type SQColumn
• type SQComparison
• type SQConnection
• type SQDrop
• type SQExpr
• type SQFlag
  • func (v SQFlag) FlagString() string
  • func (f SQFlag) String() string
• type SQForeignKey
• type SQImportConfig
• type SQImportDecoder
• type SQImportWriter
• type SQImportWriterFunc
• type SQImporter
• type SQIndexView
• type SQInsert
• type SQIterator
• type SQKey
• type SQObjects
• type SQPool
• type SQResult
• type SQSelect
• type SQSource
• type SQStatement
• type SQTable
• type SQTransaction
• type SQTxnFlag
  • func (v SQTxnFlag) Is(q SQTxnFlag) bool
• type SQUpdate
• type SQWriteHook

Constants

const (
	// TagName defines the tag name used for struct tags
	TagName = "sqlite"
)

Types

type SQAlter

type SQAlter interface {
	SQStatement

	// Alter operation
	AddColumn(SQColumn) SQStatement
	DropColumn(SQColumn) SQStatement
}

SQAlter defines an alter table statement

type SQAuth

type SQAuth interface {
	// CanSelect is called to authenticate a SELECT
	CanSelect(context.Context) error

	// CanTransaction is called for BEGIN, COMMIT, or ROLLBACK
	CanTransaction(context.Context, SQAuthFlag) error

	// CanExec is called to authenticate an operation other then SELECT
	CanExec(context.Context, SQAuthFlag, string, ...string) error
}

SQAuth is an interface for authenticating an action

type SQAuthFlag

type SQAuthFlag uint

const (
	SQLITE_AUTH_TABLE       SQAuthFlag = 1 << iota // Table Object
	SQLITE_AUTH_INDEX                              // Index Object
	SQLITE_AUTH_VIEW                               // View Object
	SQLITE_AUTH_TRIGGER                            // Trigger Object
	SQLITE_AUTH_VTABLE                             // Virtual Table Object
	SQLITE_AUTH_TEMP                               // Temporary Object
	SQLITE_AUTH_TRANSACTION                        // Transaction
	SQLITE_AUTH_CREATE                             // Create operation
	SQLITE_AUTH_DROP                               // Drop operation
	SQLITE_AUTH_INSERT                             // Insert operation
	SQLITE_AUTH_DELETE                             // Delete operation
	SQLITE_AUTH_ALTER                              // Alter operation
	SQLITE_AUTH_ANALYZE                            // Analyze  operation
	SQLITE_AUTH_PRAGMA                             // Pragma operation
	SQLITE_AUTH_READ                               // Read column operation
	SQLITE_AUTH_UPDATE                             // Update column operation
	SQLITE_AUTH_FUNCTION                           // Execute function operation
	SQLITE_AUTH_BEGIN                              // Begin txn operation
	SQLITE_AUTH_COMMIT                             // Commit txn operation
	SQLITE_AUTH_ROLLBACK                           // Rollback txn operation
	SQLITE_AUTH_MIN                    = SQLITE_AUTH_TABLE
	SQLITE_AUTH_MAX                    = SQLITE_AUTH_ROLLBACK
	SQLITE_AUTH_NONE        SQAuthFlag = 0
)

func (SQAuthFlag) Is

func (v SQAuthFlag) Is(q SQAuthFlag) bool

Is any of the flags in q

func (SQAuthFlag) String

func (v SQAuthFlag) String() string

func (SQAuthFlag) StringFlag

func (v SQAuthFlag) StringFlag() string

type SQClass

type SQClass interface {
	// Create class in the named database with modification flags
	Create(SQConnection, string, SQFlag) error

	// Read all objects from the class and return the iterator
	// TODO: Need sort, filter, limit, offset
	Read(SQConnection) (SQIterator, error)

	// Insert objects, return rowids
	Insert(SQConnection, ...interface{}) ([]SQResult, error)

	// Update objects by primary key, return rowids
	Update(SQConnection, ...interface{}) ([]SQResult, error)

	// Upsert objects by primary key, return rowids
	Upsert(SQConnection, ...interface{}) ([]SQResult, error)

	// Delete objects from the database by primary key
	Delete(SQConnection, ...interface{}) ([]SQResult, error)

	// Set a foreign key reference to parent class and columns. Panic
	// on error, and return same class
	ForeignKey(SQClass, ...string) SQClass
}

SQClass is a class definition, which can be a table or view

type SQColumn

type SQColumn interface {
	SQStatement

	// Properties
	Name() string
	Type() string
	Nullable() bool
	Primary() string

	// Modifiers
	NotNull() SQColumn
	WithType(string) SQColumn
	WithAlias(string) SQSource
	WithPrimary() SQColumn
	WithAutoIncrement() SQColumn
	WithDefault(v interface{}) SQColumn
	WithDefaultNow() SQColumn
}

SQColumn represents a column definition

type SQComparison

type SQComparison interface {
	SQStatement

	// Negate the comparison
	Not() SQComparison
}

SQComparison defines a comparison between two expressions

type SQConnection

type SQConnection interface {

	// Execute a transaction with context, rollback on any errors
	// or cancelled context
	Do(context.Context, SQTxnFlag, func(SQTransaction) error) error

	// Schemas returns a list of all the schemas in the database
	Schemas() []string

	// Tables returns a list of tables in a schema
	Tables(string) []string

	// Filename returns a filename for a schema, returns empty
	// string if in-memory database
	Filename(string) string

	// ColumnsForTable returns the columns in a schema and table
	ColumnsForTable(string, string) []SQColumn

	// ColumnsForIndex returns the column names associated with schema and index
	ColumnsForIndex(string, string) []string

	// IndexesForTable returns the indexes associated with a schema and table
	IndexesForTable(string, string) []SQIndexView

	// Views returns a list of view names in a schema
	Views(string) []string

	// Modules returns a list of modules. If an argument is
	// provided, then only modules with those name prefixes
	// matched
	Modules(...string) []string
}

SQConnection is an sqlite connection to one or more databases

type SQDrop

type SQDrop interface {
	SQStatement

	IfExists() SQDrop
}

SQDrop defines a drop for tables, views, indexes, and triggers

type SQExpr

type SQExpr interface {
	SQStatement

	// And, Or, Not
	Or(interface{}) SQExpr
}

SQExpr defines any expression

type SQFlag

type SQFlag uint

const (
	// Create flags
	SQLITE_FLAG_DELETEIFEXISTS SQFlag = 1 << iota // Delete existing database objects if they already exist
	SQLITE_FLAG_UPDATEONINSERT                    // Update existing object if a unique constraint fails

	// Other constants
	SQLITE_FLAG_NONE SQFlag = 0
	SQLITE_FLAG_MIN         = SQLITE_FLAG_DELETEIFEXISTS
	SQLITE_FLAG_MAX         = SQLITE_FLAG_UPDATEONINSERT
)

func (SQFlag) FlagString

func (v SQFlag) FlagString() string

func (SQFlag) String

func (f SQFlag) String() string

type SQForeignKey

type SQForeignKey interface {
	// Modifiers
	OnDeleteCascade() SQForeignKey
}

SQForeignKey represents a foreign key constraint

type SQImportConfig

type SQImportConfig struct {
	// Schema defines the table schema to import into. Optional.
	Schema string `sqlite:"schema"`

	// Name defines the table name to import into, if empty will be inferred
	// from the import source URL
	Name string `sqlite:"name"`

	// Ext defines the extension to infer the mimetype from. Optional.
	Ext string `sqlite:"ext"`

	// Header when true indicates the first line of a CSV file is a header
	Header bool `sqlite:"header"`

	// TrimSpace when true indicates the CSV file should be trimmed of whitespace
	// for each field
	TrimSpace bool `sqlite:"trimspace"`

	// Comment defines the character which indicates a line is a comment. Optional.
	Comment rune `sqlite:"comment"`

	// Delimiter defines the character which indicates a field delimiter. Optional.
	Delimiter rune `sqlite:"delimiter"`

	// LazyQuotes when true indicates the CSV file should allow non-standard quotes.
	LazyQuotes bool `sqlite:"lazyquotes"`

	// Overwrite existing table (will append data otherwise)
	Overwrite bool `sqlite:"overwrite"`
}

type SQImportDecoder

type SQImportDecoder interface {
	// Read from the source, return column names and values. May
	// return nil to skip a write. Returns io.EOF when no more data is available.
	Read() ([]string, []interface{}, error)
}

type SQImportWriter

type SQImportWriter interface {
	// Begin the writer process for a destination and return a writer callback
	Begin(name, schema string) (SQImportWriterFunc, error)

	// End the transaction with success (true) or failure (false). On failure, rollback
	End(bool) error
}

SQImportWriter is an interface for writing decoded rows to a destination

type SQImportWriterFunc

type SQImportWriterFunc func([]string, []interface{}) error

SQWriterFunc callback invoked for each array of columns and values from decoder

type SQImporter

type SQImporter interface {
	// ReadWrite will read from the source, and write to destination. This function
	// should be called multiple times until io.EOF is returned, indicating that
	// no more data is available.
	ReadWrite(SQImportDecoder, SQImportWriter) error

	// Return the URL of the source
	URL() *url.URL

	// Return the Table name for the destination
	Name() string

	// Return a decoder for a mimetype or file extension (when starts with a .)
	// Will return nil if no decoder is available. The mimetype can include
	// the character set (e.g. text/csv;charset=utf-8)
	Decoder(string) (SQImportDecoder, error)
}

type SQIndexView

type SQIndexView interface {
	SQStatement
	SQSource

	// Return properties
	Unique() bool
	Table() string
	Columns() []string
	Auto() bool

	// Modifiers
	IfNotExists() SQIndexView
	WithTemporary() SQIndexView
	WithUnique() SQIndexView
	WithAuto() SQIndexView
}

SQIndexView defines a create index or view statement

type SQInsert

type SQInsert interface {
	SQStatement

	DefaultValues() SQInsert
	WithConflictDoNothing(...string) SQInsert
	WithConflictUpdate(...string) SQInsert
}

SQInsert defines an insert or replace statement

type SQIterator

type SQIterator interface {
	// Next returns the next object in the iterator, or nil if there are no more
	Next() interface{}

	// RowId returns the last read row, should be called after Next()
	RowId() int64

	// Close releases any resources associated with the iterator
	Close() error
}

SQIterator is an iterator for a Read operation

type SQKey

type SQKey uint

const (
	SQKeyNone SQKey = iota
	SQKeyInsert
	SQKeySelect
	SQKeyWrite
	SQKeyDelete
	SQKeyGetRowId
	SQKeyMax
)

type SQObjects

type SQObjects interface {
	SQConnection

	// Create classes with named database and modification flags
	Create(string, SQFlag, ...SQClass) error

	// Write objects to database
	Write(v ...interface{}) ([]SQResult, error)

	// Read objects from database
	Read(SQClass) (SQIterator, error)

	// Write objects to database, call hook after each write
	WriteWithHook(SQWriteHook, ...interface{}) ([]SQResult, error)

	// Delete objects from the database
	Delete(v ...interface{}) ([]SQResult, error)
}

SQObjects is an sqlite connection but adds ability to read, write and delete

type SQPool

type SQPool interface {
	// Close waits for all connections to be released and then releases resources
	Close() error

	// Get a connection from the pool, and return it to the pool when the context
	// is cancelled or it is put back using the Put method. If there are no
	// connections available or an error occurs, nil is returned.
	Get(context.Context) SQConnection

	// Cur returns the current number of used connections
	Cur() int64

	// Return connection to the pool
	Put(SQConnection)

	// Max returns the maximum number of connections allowed
	Max() int64

	// SetMax allowed connections released from pool. Note this does not change
	// the maximum instantly, it will settle to this value over time. Set as value
	// zero to disable opening new connections
	SetMax(int64)
}

SQPool is an sqlite connection pool

type SQResult

type SQResult interface {
	// Return next row, returns nil when all rows consumed
	Next() []interface{}

	// Return next map of values, or nil if no more rows
	NextMap() map[string]interface{}

	// NextQuery executes the next query or returns io.EOF
	NextQuery(...interface{}) error

	// Close the rows, and free up any resources
	Close() error

	// Return Last RowID inserted of last statement
	LastInsertId() int64

	// Return number of changes made of last statement
	RowsAffected() uint64
}

SQResult increments over returned rows from a query

type SQSelect

type SQSelect interface {
	SQStatement

	// Set select flags
	WithDistinct() SQSelect
	WithLimitOffset(limit, offset uint) SQSelect

	// Destination columns for results
	To(...SQSource) SQSelect

	// Where and order clauses
	Where(...interface{}) SQSelect
	Order(...SQSource) SQSelect
}

SQSelect defines a select statement

type SQSource

type SQSource interface {
	SQStatement
	SQExpr

	// Return name, schema, type
	Name() string
	Schema() string
	Alias() string

	// Modify the source
	WithName(string) SQSource
	WithSchema(string) SQSource
	WithType(string) SQColumn
	WithAlias(string) SQSource
	WithDesc() SQSource

	// Insert, replace or upsert a row with named columns
	Insert(...string) SQInsert
	Replace(...string) SQInsert

	// Drop objects
	DropTable() SQDrop
	DropIndex() SQDrop
	DropTrigger() SQDrop
	DropView() SQDrop

	// Create objects
	CreateTable(...SQColumn) SQTable
	CreateVirtualTable(string, ...string) SQIndexView
	CreateIndex(string, ...string) SQIndexView
	//CreateView(SQSelect, ...string) SQIndexView
	ForeignKey(...string) SQForeignKey

	// Alter objects
	AlterTable() SQAlter

	// Update and delete data
	Update(...string) SQUpdate
	Delete(...interface{}) SQStatement
}

SQSource defines a table or column name

type SQStatement

type SQStatement interface {
	Query() string
}

SQStatement is any statement which can be prepared or executed

type SQTable

type SQTable interface {
	SQStatement

	IfNotExists() SQTable
	WithTemporary() SQTable
	WithoutRowID() SQTable
	WithIndex(...string) SQTable
	WithUnique(...string) SQTable
	WithForeignKey(SQForeignKey, ...string) SQTable
}

SQTable defines a table of columns and indexes

type SQTransaction

type SQTransaction interface {
	// Query and return a set of results
	Query(SQStatement, ...interface{}) (SQResult, error)
}

SQTransaction is an sqlite transaction

type SQTxnFlag

type SQTxnFlag uint

const (
	SQLITE_TXN_DEFAULT SQTxnFlag = (1 << iota)
	SQLITE_TXN_IMMEDIATE
	SQLITE_TXN_EXCLUSIVE
	SQLITE_TXN_SNAPSHOT
	SQLITE_TXN_NO_FOREIGNKEY_CONSTRAINTS
)

func (SQTxnFlag) Is

func (v SQTxnFlag) Is(q SQTxnFlag) bool

Is any of the flags in q

type SQUpdate

type SQUpdate interface {
	SQStatement

	// Modifiers
	WithAbort() SQUpdate
	WithFail() SQUpdate
	WithIgnore() SQUpdate
	WithReplace() SQUpdate
	WithRollback() SQUpdate

	// Where clause
	Where(...interface{}) SQUpdate
}

SQUpdate defines an update statement

type SQWriteHook

type SQWriteHook func(SQResult, interface{}) error