search

bvinc / go-sqlite-lite

SQLite driver for the Go programming language
BSD 3-Clause "New" or "Revised" License
327 stars 34 forks source link
go golang sqlite sqlite3

readme

GoDoc Build Status Build status Coverage Status Go Report Card

go-sqlite-lite

go-sqlite-lite is a SQLite driver for the Go programming language. It is designed with the following goals in mind.

Most database drivers include a layer to work nicely with the Go database/sql interface, which introduces connection pooling and behavior differences from pure SQLite. This driver does not include a database/sql interface.

Releases

Getting started

import "github.com/bvinc/go-sqlite-lite/sqlite3"

Acquiring a connection

conn, err := sqlite3.Open("mydatabase.db")
if err != nil {
    ...
}
defer conn.Close()

// It's always a good idea to set a busy timeout
conn.BusyTimeout(5 * time.Second)

Executing SQL

err = conn.Exec(`CREATE TABLE student(name TEXT, age INTEGER)`)
if err != nil {
    ...
}
// Exec can optionally bind parameters
err = conn.Exec(`INSERT INTO student VALUES (?, ?)`, "Bob", 18)
if err != nil {
    ...
}

Using Prepared Statements

stmt, err := conn.Prepare(`INSERT INTO student VALUES (?, ?)`)
if err != nil {
    ...
}
defer stmt.Close()

// Bind the arguments
err = stmt.Bind("Bill", 18)
if err != nil {
    ...
}
// Step the statement
hasRow, err := stmt.Step()
if err != nil {
    ...
}
// Reset the statement
err = stmt.Reset()
if err != nil {
    ...
}

Using Prepared Statements Conveniently

stmt, err := conn.Prepare(`INSERT INTO student VALUES (?, ?)`)
if err != nil {
    ...
}
defer stmt.Close()

// Exec binds arguments, steps the statement to completion, and always resets the statement
err = stmt.Exec("John", 19)
if err != nil {
    ...
}

Using Queries Conveniently

// Prepare can prepare a statement and optionally also bind arguments
stmt, err := conn.Prepare(`SELECT name, age FROM student WHERE age = ?`, 18)
if err != nil {
    ...
}
defer stmt.Close()

for {
    hasRow, err := stmt.Step()
    if err != nil {
        ...
    }
    if !hasRow {
        // The query is finished
        break
    }

    // Use Scan to access column data from a row
    var name string
    var age int
    err = stmt.Scan(&name, &age)
    if err != nil {
        ...
    }
    fmt.Println("name:", name, "age:", age)
}
// Remember to Reset the statement if you would like to Bind new arguments and reuse the prepared statement

Getting columns that might be NULL

Scan can be convenient to use, but it doesn't handle NULL values. To get full control of column values, there are column methods for each type.

name, ok, err := stmt.ColumnText(0)
if err != nil {
    // Either the column index was out of range, or SQLite failed to allocate memory
    ...
}
if !ok {
    // The column was NULL
}

age, ok, err := stmt.ColumnInt(1)
if err != nil {
    // Can only fail if the column index is out of range
    ...
}
if !ok {
    // The column was NULL
}

ColumnBlob returns a nil slice in the case of NULL.

blob, err := stmt.ColumnBlob(i)
if err != nil {
    // Either the column index was out of range, or SQLite failed to allocate memory
    ...
}
if blob == nil {
    // The column was NULL
}

Using Transactions

// Equivalent to conn.Exec("BEGIN")
err := conn.Begin()
if err != nil {
    ...
}

// Do some work
...

// Equivalent to conn.Exec("COMMIT")
err = conn.Commit()
if err != nil {
    ...
}

Using Transactions Conveniently

With error handling in Go, it can be pretty inconvenient to ensure that a transaction is rolled back in the case of an error. The WithTx method is provided, which accepts a function of work to do inside of a transaction. WithTx will begin the transaction and call the function. If the function returns an error, the transaction will be rolled back. If the function succeeds, the transaction will be committed. WithTx can be a little awkward to use, but it's necessary. For example:

err := conn.WithTx(func() error {
    return insertStudents(conn)
})
if err != nil {
    ...
}

func insertStudents(conn *sqlite3.Conn) error {
    ...
}

Advanced Features

Credit

This project began as a fork of https://github.com/mxk/go-sqlite/

FAQ

If a database/sql interface is required, please use https://github.com/mattn/go-sqlite3 . In my experience, using a database/sql interface with SQLite is painful. Connection pooling causes unnecessary overhead and weirdness. Transactions using Exec("BEGIN") don't work as expected. Your connection does not correspond to SQLite's concept of a connection. PRAGMA commands do not work as expected. When you hit SQLite errors, such as locking or busy errors, it's difficult to discover why since you don't know which connection received which SQL and in what order.

This driver was forked from mxk/go-sqlite-driver. It hadn't been maintained in years and used an ancient version of SQLite. A large number of features were removed, reworked, and renamed. A lot of smartness and state was removed. It is now much easier to upgrade to newer versions of SQLite since the codec feature was removed. The behavior of methods now lines up closely with the behavior of SQLite's C API.

The crawshaw driver is pretty well thought out and solves a lot of the same problems as this driver. There are a few places where our philosophies differ. The crawshaw driver defaults (when flags of 0 are given) to SQLite shared cache mode and WAL mode. The default WAL synchronous mode is changed. Prepared statements are transparently cached. Connection pools are provided. I would be opposed to making most of these changes to this driver. I would like this driver to provide a default, light, and unsurprising SQLite experience.

No finalizers are used in this driver. You are responsible for closing connections and statements. While I mostly agree with finalizers for cleaning up most accidental resource leaks, in this case, finalizers may fix errors such as locking errors while debugging only to find that the code works unreliably in production. Removing finalizers makes the behavior consistent.

go-sqlite-lite is as thread safe as SQLite. SQLite with this driver is compiled with -DSQLITE_THREADSAFE=2 which is Multi-thread mode. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. This applies to goroutines. A single database connection should not be used simultaneously between two goroutines.

It is safe to use separate connection instances concurrently, even if they are accessing the same database file. For example:

// ERROR (without any extra synchronization)
c, _ := sqlite3.Open("sqlite.db")
go use(c)
go use(c)
// OK
c1, _ := sqlite3.Open("sqlite.db")
c2, _ := sqlite3.Open("sqlite.db")
go use(c1)
go use(c2)

Consult the SQLite documentation for more information.

https://www.sqlite.org/threadsafe.html

Opening new connections is cheap and connection pooling is generally unnecessary for SQLite. I would recommend that you open a new connection for each request that you're handling. This ensures that each request is handled separately and the normal rules of SQLite database/table locking apply.

If you've decided that pooling connections provides you with an advantage, it would be outside the scope of this package and something that you would need to implement and ensure works as needed.

License

This project is licensed under the BSD license.