driver

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.

The TRANSACTION mode can be specified using "_txlock":

sql.Open("sqlite3", "file:demo.db?_txlock=immediate")

Possible values are: "deferred", "immediate", "exclusive". A read-only transaction is always "deferred", regardless of "_txlock".

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.

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: busy timeout and locking mode should be the first PRAGMAs set, in that order.

Example

//go:build (linux || darwin || windows || freebsd || illumos) && !sqlite3_nosys

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"
)

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
}

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 (Json)

db, err := driver.Open("file:/test.db?vfs=memdb", nil)
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    TEXT
		);
	`)
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"`
}

_, err = db.Exec(`INSERT INTO orders (user_id, cart) VALUES (?, ?)`, 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)

Output:

total: 850

Index

• func Open(dataSourceName string, init func(*sqlite3.Conn) error) (*sql.DB, error)
• func Savepoint(tx *sql.Tx) sqlite3.Savepoint
• type SQLite
  • func (d *SQLite) Open(name string) (driver.Conn, error)
  • func (d *SQLite) OpenConnector(name string) (driver.Connector, error)

Examples

• Package
• Package (Json)
• Savepoint

Functions

func Open

func Open(dataSourceName string, init func(*sqlite3.Conn) error) (*sql.DB, error)

Open opens the SQLite database specified by dataSourceName as a database/sql.DB.

The init function is called by the driver on new connections. The sqlite3.Conn can be used to execute queries, register functions, etc. Any error returned closes the connection and is returned to database/sql.

func Savepoint

func Savepoint(tx *sql.Tx) sqlite3.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/tests/testcfg"
	_ "github.com/ncruces/go-sqlite3/vfs/memdb"
)

func main() {
	db, err := driver.Open("file:/test.db?vfs=memdb", nil)
	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 SQLite

type SQLite struct {
	// Init function is called by the driver on new connections.
	// The [sqlite3.Conn] can be used to execute queries, register functions, etc.
	// Any error returned closes the connection and is returned to [database/sql].
	Init func(*sqlite3.Conn) error
}

SQLite implements database/sql/driver.Driver.

func (*SQLite) Open

func (d *SQLite) Open(name string) (driver.Conn, error)

Open implements database/sql/driver.Driver.

func (*SQLite) OpenConnector

func (d *SQLite) OpenConnector(name string) (driver.Connector, error)

OpenConnector implements database/sql/driver.DriverContext.