Documentation
¶
Overview ¶
Package driver provides a database/sql driver for SQLite.
Importing package driver registers a database/sql driver named "sqlite3". You may also need to import package embed.
import _ "github.com/ncruces/go-sqlite3/driver" import _ "github.com/ncruces/go-sqlite3/embed"
The data source name for "sqlite3" databases can be a filename or a "file:" URI.
Default transaction mode ¶
The TRANSACTION mode can be specified using "_txlock":
sql.Open("sqlite3", "file:demo.db?_txlock=immediate")
Possible values are: "deferred" (the default), "immediate", "exclusive". Regardless of "_txlock":
- a linearizable transaction is always "exclusive";
- a serializable transaction is always "immediate";
- a read-only transaction is always "deferred".
Working with time ¶
The time encoding/decoding format can be specified using "_timefmt":
sql.Open("sqlite3", "file:demo.db?_timefmt=sqlite")
Possible values are: "auto" (the default), "sqlite", "rfc3339";
- "auto" encodes as RFC 3339 and decodes any format supported by SQLite;
- "sqlite" encodes as SQLite and decodes any format supported by SQLite;
- "rfc3339" encodes and decodes RFC 3339 only.
If you encode as RFC 3339 (the default), consider using the TIME collating sequence to produce a time-ordered sequence.
To scan values in other formats, sqlite3.TimeFormat.Scanner may be helpful. To bind values in other formats, sqlite3.TimeFormat.Encode them before binding.
When using a custom time struct, you'll have to implement database/sql/driver.Valuer and database/sql.Scanner.
The Value method should ideally serialise to a time format supported by SQLite. This ensures SQL date and time functions work as they should, and that your schema works with other SQLite tools. sqlite3.TimeFormat.Encode may help.
The Scan method needs to take into account that the value it receives can be of differing types. It can already be a time.Time, if the driver decoded the value according to "_timefmt" rules. Or it can be a: string, int64, float64, []byte, nil, depending on the column type and what whoever wrote the value. sqlite3.TimeFormat.Decode may help.
Setting PRAGMAs ¶
PRAGMA statements can be specified using "_pragma":
sql.Open("sqlite3", "file:demo.db?_pragma=busy_timeout(10000)")
If no PRAGMAs are specified, a busy timeout of 1 minute is set.
Order matters: encryption keys, busy timeout and locking mode should be the first PRAGMAs set, in that order.
Example ¶
package main
import (
"database/sql"
"fmt"
"log"
"os"
_ "github.com/ncruces/go-sqlite3/driver"
_ "github.com/ncruces/go-sqlite3/embed"
_ "github.com/ncruces/go-sqlite3/vfs/memdb"
)
var db *sql.DB
type Album struct {
ID int64
Title string
Artist string
Price float32
}
func main() {
// Get a database handle.
var err error
db, err = sql.Open("sqlite3", "./recordings.db")
if err != nil {
log.Fatal(err)
}
defer os.Remove("./recordings.db")
defer db.Close()
// Create a table with some data in it.
err = albumsSetup()
if err != nil {
log.Fatal(err)
}
albums, err := albumsByArtist("John Coltrane")
if err != nil {
log.Fatal(err)
}
fmt.Printf("Albums found: %v\n", albums)
// Hard-code ID 2 here to test the query.
alb, err := albumByID(2)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Album found: %v\n", alb)
albID, err := addAlbum(Album{
Title: "The Modern Sound of Betty Carter",
Artist: "Betty Carter",
Price: 49.99,
})
if err != nil {
log.Fatal(err)
}
fmt.Printf("ID of added album: %v\n", albID)
}
func albumsSetup() error {
_, err := db.Exec(`
DROP TABLE IF EXISTS album;
CREATE TABLE album (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title VARCHAR(128) NOT NULL,
artist VARCHAR(255) NOT NULL,
price DECIMAL(5,2) NOT NULL
);
`)
if err != nil {
return err
}
_, err = db.Exec(`
INSERT INTO album
(title, artist, price)
VALUES
('Blue Train', 'John Coltrane', 56.99),
('Giant Steps', 'John Coltrane', 63.99),
('Jeru', 'Gerry Mulligan', 17.99),
('Sarah Vaughan', 'Sarah Vaughan', 34.98)
`)
if err != nil {
return err
}
return nil
}
// albumsByArtist queries for albums that have the specified artist name.
func albumsByArtist(name string) ([]Album, error) {
var albums []Album
rows, err := db.Query("SELECT * FROM album WHERE artist = ?", name)
if err != nil {
return nil, fmt.Errorf("albumsByArtist %q: %w", name, err)
}
defer rows.Close()
for rows.Next() {
var alb Album
if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
return nil, fmt.Errorf("albumsByArtist %q: %w", name, err)
}
albums = append(albums, alb)
}
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("albumsByArtist %q: %w", name, err)
}
return albums, nil
}
// albumByID queries for the album with the specified ID.
func albumByID(id int64) (Album, error) {
var alb Album
row := db.QueryRow("SELECT * FROM album WHERE id = ?", id)
if err := row.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
if err == sql.ErrNoRows {
return alb, fmt.Errorf("albumsById %d: no such album", id)
}
return alb, fmt.Errorf("albumsById %d: %w", id, err)
}
return alb, nil
}
// addAlbum adds the specified album to the database,
// returning the album ID of the new entry
func addAlbum(alb Album) (int64, error) {
result, err := db.Exec("INSERT INTO album (title, artist, price) VALUES (?, ?, ?)", alb.Title, alb.Artist, alb.Price)
if err != nil {
return 0, fmt.Errorf("addAlbum: %w", err)
}
id, err := result.LastInsertId()
if err != nil {
return 0, fmt.Errorf("addAlbum: %w", err)
}
return id, nil
}
Output: Albums found: [{1 Blue Train John Coltrane 56.99} {2 Giant Steps John Coltrane 63.99}] Album found: {2 Giant Steps John Coltrane 63.99} ID of added album: 5
Example (CustomTime) ¶
db, err := sql.Open("sqlite3", "file:/time.db?vfs=memdb")
if err != nil {
log.Fatal(err)
}
defer db.Close()
_, err = db.Exec(`
CREATE TABLE data (
id INTEGER PRIMARY KEY,
date_time TEXT
) STRICT;
`)
if err != nil {
log.Fatal(err)
}
// This one will be returned as string to [sql.Scanner] because it doesn't
// pass the driver's round-trip test when it tries to figure out if it's
// a time. 2009-11-17T20:34:58.650Z goes in, but parsing and formatting
// it with [time.RFC3338Nano] results in 2009-11-17T20:34:58.65Z. Though
// the times are identical, the trailing zero is lost in the string
// representation so the driver considers the conversion unsuccessful.
c1 := CustomTime{time.Date(
2009, 11, 17, 20, 34, 58, 650000000, time.UTC)}
// Store our custom time in the database.
_, err = db.Exec(`INSERT INTO data (date_time) VALUES(?)`, c1)
if err != nil {
log.Fatal(err)
}
var strc1 string
// Retrieve it as a string, the result of Value().
err = db.QueryRow(`
SELECT date_time
FROM data
WHERE id = last_insert_rowid()
`).Scan(&strc1)
if err != nil {
log.Fatal(err)
}
fmt.Println("in db:", strc1)
var resc1 CustomTime
// Retrieve it as our custom time type, going through Scan().
err = db.QueryRow(`
SELECT date_time
FROM data
WHERE id = last_insert_rowid()
`).Scan(&resc1)
if err != nil {
log.Fatal(err)
}
fmt.Println("custom time:", resc1)
// This one will be returned as [time.Time] to [sql.Scanner] because it does
// pass the driver's round-trip test when it tries to figure out if it's
// a time. 2009-11-17T20:34:58.651Z goes in, and parsing and formatting
// it with [time.RFC3339Nano] results in 2009-11-17T20:34:58.651Z.
c2 := CustomTime{time.Date(
2009, 11, 17, 20, 34, 58, 651000000, time.UTC)}
// Store our custom time in the database.
_, err = db.Exec(`INSERT INTO data (date_time) VALUES(?)`, c2)
if err != nil {
log.Fatal(err)
}
var strc2 string
// Retrieve it as a string, the result of Value().
err = db.QueryRow(`
SELECT date_time
FROM data
WHERE id = last_insert_rowid()
`).Scan(&strc2)
if err != nil {
log.Fatal(err)
}
fmt.Println("in db:", strc2)
var resc2 CustomTime
// Retrieve it as our custom time type, going through Scan().
err = db.QueryRow(`
SELECT date_time
FROM data
WHERE id = last_insert_rowid()
`).Scan(&resc2)
if err != nil {
log.Fatal(err)
}
fmt.Println("custom time:", resc2)
Output: in db: 2009-11-17T20:34:58.650Z scan type string: 2009-11-17T20:34:58.650Z custom time: 2009-11-17 20:34:58.65 +0000 UTC in db: 2009-11-17T20:34:58.651Z scan type time: 2009-11-17 20:34:58.651 +0000 UTC custom time: 2009-11-17 20:34:58.651 +0000 UTC
Example (Json) ¶
db, err := driver.Open("file:/json.db?vfs=memdb")
if err != nil {
log.Fatal(err)
}
defer db.Close()
_, err = db.Exec(`
CREATE TABLE orders (
cart_id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
cart BLOB -- stored as JSONB
) STRICT;
`)
if err != nil {
log.Fatal(err)
}
type CartItem struct {
ItemID string `json:"id"`
Name string `json:"name"`
Quantity int `json:"quantity,omitempty"`
Price int `json:"price,omitempty"`
}
type Cart struct {
Items []CartItem `json:"items"`
}
// convert to JSONB on insertion
_, err = db.Exec(`INSERT INTO orders (user_id, cart) VALUES (?, jsonb(?))`, 123, sqlite3.JSON(Cart{
[]CartItem{
{ItemID: "111", Name: "T-shirt", Quantity: 1, Price: 250},
{ItemID: "222", Name: "Trousers", Quantity: 1, Price: 600},
},
}))
if err != nil {
log.Fatal(err)
}
var total string
err = db.QueryRow(`
SELECT total(json_each.value -> 'price')
FROM orders, json_each(cart -> 'items')
WHERE cart_id = last_insert_rowid()
`).Scan(&total)
if err != nil {
log.Fatal(err)
}
fmt.Println("total:", total)
var cart Cart
err = db.QueryRow(`
SELECT json(cart) -- convert to JSON on retrieval
FROM orders
WHERE cart_id = last_insert_rowid()
`).Scan(sqlite3.JSON(&cart))
if err != nil {
log.Fatal(err)
}
for _, item := range cart.Items {
fmt.Printf("id: %s, name: %s, quantity: %d, price: %d\n",
item.ItemID, item.Name, item.Quantity, item.Price)
}
Output: total: 850 id: 111, name: T-shirt, quantity: 1, price: 250 id: 222, name: Trousers, quantity: 1, price: 600
Index ¶
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func Open ¶
Open opens the SQLite database specified by dataSourceName as a database/sql.DB.
Open accepts zero, one, or two callbacks (nil callbacks are ignored). The first callback is called when the driver opens a new connection. The second callback is called before the driver closes a connection. The sqlite3.Conn can be used to execute queries, register functions, etc.
func Savepoint ¶
Savepoint establishes a new transaction savepoint.
https://sqlite.org/lang_savepoint.html
Example ¶
package main
import (
"fmt"
"log"
"github.com/ncruces/go-sqlite3/driver"
_ "github.com/ncruces/go-sqlite3/embed"
_ "github.com/ncruces/go-sqlite3/vfs/memdb"
)
func main() {
db, err := driver.Open("file:/svpt.db?vfs=memdb")
if err != nil {
log.Fatal(err)
}
defer db.Close()
_, err = db.Exec(`CREATE TABLE users (id INT, name VARCHAR(10))`)
if err != nil {
log.Fatal(err)
}
err = func() error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
stmt, err := tx.Prepare(`INSERT INTO users (id, name) VALUES (?, ?)`)
if err != nil {
return err
}
defer stmt.Close()
_, err = stmt.Exec(0, "go")
if err != nil {
return err
}
_, err = stmt.Exec(1, "zig")
if err != nil {
return err
}
savept := driver.Savepoint(tx)
_, err = stmt.Exec(2, "whatever")
if err != nil {
return err
}
err = savept.Rollback()
if err != nil {
return err
}
_, err = stmt.Exec(3, "rust")
if err != nil {
return err
}
return tx.Commit()
}()
if err != nil {
log.Fatal(err)
}
rows, err := db.Query(`SELECT id, name FROM users`)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id, name string
err = rows.Scan(&id, &name)
if err != nil {
log.Fatal(err)
}
fmt.Printf("%s %s\n", id, name)
}
}
Output: 0 go 1 zig 3 rust
Types ¶
type Conn ¶
Conn is implemented by the SQLite database/sql driver connections.
It can be used to access SQLite features like online backup:
db, err := driver.Open("temp.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
conn, err := db.Conn(context.TODO())
if err != nil {
log.Fatal(err)
}
err = conn.Raw(func(driverConn any) error {
conn := driverConn.(driver.Conn)
return conn.Raw().Backup("main", "backup.db")
})
if err != nil {
log.Fatal(err)
}
type SQLite ¶
type SQLite struct {
// contains filtered or unexported fields
}
SQLite implements database/sql/driver.Driver.
func (*SQLite) Open ¶
Open implements database/sql/driver.Driver.
func (*SQLite) OpenConnector ¶
OpenConnector implements database/sql/driver.DriverContext.
Source Files