Involved Source Files 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".
# Datatypes In SQLite
SQLite is dynamically typed.
Columns can mostly hold any value regardless of their declared type.
SQLite supports most [driver.Value] types out of the box,
but bool and [time.Time] require special care.
Booleans can be stored on any column type and scanned back to a *bool.
However, if scanned to a *any, booleans may either become an
int64, string or bool, depending on the declared type of the column.
If you use BOOLEAN for your column type,
1 and 0 will always scan as true and false.
# Working with time
Time values can similarly be stored on any column type.
The time encoding/decoding format can be specified using "_timefmt":
sql.Open("sqlite3", "file:demo.db?_timefmt=sqlite")
Special 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.
You can also set "_timefmt" to an arbitrary [sqlite3.TimeFormat] or [time.Layout].
If you encode as RFC 3339 (the default),
consider using the TIME [collating sequence] to produce time-ordered sequences.
If you encode as RFC 3339 (the default),
time values will scan back to a *time.Time unless your column type is TEXT.
Otherwise, if scanned to a *any, time values may either become an
int64, float64 or string, depending on the time format and declared type of the column.
If you use DATE, TIME, DATETIME, or TIMESTAMP for your column type,
"_timefmt" will be used to decode values.
To scan values in custom formats, [sqlite3.TimeFormat.Scanner] may be helpful.
To bind values in custom 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 encode 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, or nil,
depending on the column type and 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.savepoint.gotime.goutil.go
Code Examples
//go:build linux || darwin || windows || freebsd || openbsd || netbsd || dragonfly || illumos || sqlite3_flock || sqlite3_dotlk
package main
// Adapted from: https://go.dev/doc/tutorial/database-access
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) {
// An albums slice to hold data from returned rows.
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()
// Loop through rows, using Scan to assign column data to struct fields.
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) {
// An album to hold data from the returned row.
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
}
Package-Level Type Names (total 2)
/* sort by: | */
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)
}
defer conn.Close()
err = conn.Raw(func(driverConn any) error {
conn := driverConn.(driver.Conn)
return conn.Raw().Backup("main", "backup.db")
})
if err != nil {
log.Fatal(err)
} Begin starts and returns a new transaction.
Deprecated: Drivers should implement ConnBeginTx instead (or additionally). BeginTx starts and returns a new transaction.
If the context is canceled by the user the sql package will
call Tx.Rollback before discarding and closing the connection.
This must check opts.Isolation to determine if there is a set
isolation level. If the driver does not support a non-default
level and one is set or if there is a non-default isolation level
that is not supported, an error must be returned.
This must also check opts.ReadOnly to determine if the read-only
value is true to either set the read-only transaction property if supported
or return an error if it is not supported. Close invalidates and potentially stops any current
prepared statements and transactions, marking this
connection as no longer in use.
Because the sql package maintains a free pool of
connections and only calls Close when there's a surplus of
idle connections, it shouldn't be necessary for drivers to
do their own connection caching.
Drivers must ensure all network calls made by Close
do not block indefinitely (e.g. apply a timeout). Prepare returns a prepared statement, bound to this connection. PrepareContext returns a prepared statement, bound to this connection.
context is for the preparation of the statement,
it must not store the context within the statement itself.( Conn) Raw() *sqlite3.Conn
Conn : github.com/prometheus/common/expfmt.Closer
Conn : database/sql/driver.Conn
Conn : database/sql/driver.ConnBeginTx
Conn : database/sql/driver.ConnPrepareContext
Conn : io.Closer
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.
The pages are generated with Goldsv0.8.2. (GOOS=linux GOARCH=amd64)
Golds is a Go 101 project developed by Tapir Liu.
PR and bug reports are welcome and can be submitted to the issue list.
Please follow @zigo_101 (reachable from the left QR code) to get the latest news of Golds.
