Golang SQLite3 Tutorial [With Examples]

In this tutorial, we will examine some examples of working with sqlite in Golang. Go talks to SQL databases using the package database/sql. This package provides a generic interface to databases that use SQL. It allows you to connect to and interface with different databases by providing the queries and statements to execute.

In addition to the package database/sql, you need access to a specific driver to connect to the required database. The driver works together with the database/sql package, implementing the details to interface with the desired database engine. These database drivers are not part of Go’s standard library and are generally developed and maintained by the open source community.

For this application, you’ll use the go-sqlite3 driver. This driver uses C bindings to connect to SQLite, so you need to have CGO enabled and a C compiler available. CGO is part of the Go tools and the standard way to call C/C++ libraries from Go.

There are numerous SQLite database drivers in Go, but some of them do not support the database/sql interface standards.

• https://github.com/mattn/go-sqlite3 supports database/sql, based on cgo.
• https://github.com/feyeleanor/gosqlite3 doesn't support database/sql, based on cgo.
• https://github.com/phf/go-sqlite3 doesn't support database/sql, based on cgo.

 

Setting the environment

Install SQLite3 on Windows

Step 1 − Open the download page SQLite download page, and download precompiled binaries for Windows.

It should be noted that I am running the demo on Windows 10. If you have a different operating system, please select the appropriate download option.

 

Step 2 − Exact zip file to any folder you want

 

Step 3 − Add the above path to your PATH environment variable. You can go to command line to check if the installation is completed:

 

For Windows, you need to install a C compiler and toolchain, such as TDM-GCC or MINGW. If you see this error when run first example, you have to install gcc/g++ compilers and libraries:

exec: "gcc": executable file not found in %PATH%

Follow this step:

Step 1: Go to MinGW website and click to Mingw-builds.

Step 2: Download one of the zip file, extract file to any folder.

Step 3: Add the extract folder path to your PATH environment variable.

 

Install SQLite3 on Linux

Th rpm name or the package name will vary as per distribution. For Linux, you can use gcc, which is available with most Linux distributions by default. If it isn’t installed, install it using your distribution’s package manager.

To install SQLite and it's dependencies on Ubuntu or Debian based environment, you can use

$ sudo apt-get install sqlite3 libsqlite3-dev gcc

 

To install SQLite and it's dependencies in CentOS, Rocky Linux or similar environment, you can use

# yum -y install sqlite gcc

 

Install Golang SQLite3 driver

The sql package in Go provides a general interface to SQL (or SQL-like) databases. A database driver must be used in conjunction with the sql package.

So, in this tutorial, we will use the first driver. Make sure that CGO is enabled in your go workspace.

To install this package, run the below command:

go get github.com/mattn/go-sqlite3

 

Check connection to SQLite3 Database

Firstly, we will test connection from our application to sqlite3. We will query and print out sqlite version:

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func main() {

	db, err := sql.Open("sqlite3", ":memory:")

	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	var version string
	err = db.QueryRow("SELECT SQLITE_VERSION()").Scan(&version)

	if err != nil {
		log.Fatal(err)
	}

	fmt.Println(version)
}

Output:

3.39.2

Explanation:

• _ "github.com/mattn/go-sqlite3": If a package is imported with a blank identifier, the package's init function is called. The driver is registered using this function.
• db, err := sql.Open("sqlite3", ":memory:"): open a database specified by database driver name and a driver-specific data source name.
• defer db.Close(): close the connection
• The QueryRow executes a query that is expected to return at most one row. The column from the matched row is copied into the version variable by the Scan function.

 

Perform CRUD Operation using golang sqlite driver

Create a database table and Read from the Table

In this example we will create a new database books.db and insert a Table with a single row as an example:

package main

import (
	"database/sql"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

// Book is a placeholder for book
type Book struct {
	id     int
	name   string
	author string
}

func main() {
	db, err := sql.Open("sqlite3", "books.db")
	if err != nil {
		log.Println(err)
	}

	// Create table
	statement, err := db.Prepare("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, isbn INTEGER, author VARCHAR(64), name VARCHAR(64) NULL)")
	if err != nil {
		log.Println("Error in creating table")
	} else {
		log.Println("Successfully created table books!")
	}
	statement.Exec()

	// Create
	statement, _ = db.Prepare("INSERT INTO books (name, author, isbn) VALUES (?, ?, ?)")
	statement.Exec("A Tale of Two Cities", "Charles Dickens", 140430547)
	log.Println("Inserted the book into database!")

	// Read
	rows, _ := db.Query("SELECT id, name, author FROM books")
	var tempBook Book
	for rows.Next() {
		rows.Scan(&tempBook.id, &tempBook.name, &tempBook.author)
		log.Printf("ID:%d, Book:%s, Author:%s\n", tempBook.id,
			tempBook.name, tempBook.author)
	}
}

Output:

]# go run main.go 
2022/10/01 23:11:26 Successfully created table books!
2022/10/01 23:11:26 Inserted the book into database!
2022/10/01 23:11:26 ID:1, Book:A Tale of Two Cities, Author:Charles Dickens

The same can be verified inside the sqlite database of books.db:

 

Update and Delete Operation using sqlite driver

Now let us perform some update and delete operation on our sqlite database using the go driver:

package main

import (
	"database/sql"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

// Book is a placeholder for book
type Book struct {
	id     int
	name   string
	author string
}

func main() {
	db, err := sql.Open("sqlite3", "books.db")
	if err != nil {
		log.Println(err)
	}

	// Create table
	statement, err := db.Prepare("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, isbn INTEGER, author VARCHAR(64), name VARCHAR(64) NULL)")
	if err != nil {
		log.Println("Error in creating table")
	} else {
		log.Println("Successfully created table books!")
	}
	statement.Exec()

	// Create
	statement, _ = db.Prepare("INSERT INTO books (name, author, isbn) VALUES (?, ?, ?)")
	statement.Exec("A Tale of Two Cities", "Charles Dickens", 140430547)
	log.Println("Inserted the book into database!")

	// Read before Update
	rows, _ := db.Query("SELECT id, name, author FROM books")
	var tempBook Book
	for rows.Next() {
		rows.Scan(&tempBook.id, &tempBook.name, &tempBook.author)
		log.Printf("ID:%d, Book:%s, Author:%s\n", tempBook.id,
			tempBook.name, tempBook.author)
	}

	// Update
	statement, _ = db.Prepare("update books set name=? where id=?")
	statement.Exec("A Tale of Three Cities", 1)
	log.Println("Successfully updated the book in database!")

	// Read after Update
	rows, _ = db.Query("SELECT id, name, author FROM books")

	for rows.Next() {
		rows.Scan(&tempBook.id, &tempBook.name, &tempBook.author)
		log.Printf("ID:%d, Book:%s, Author:%s\n", tempBook.id,
			tempBook.name, tempBook.author)
	}

	// Delete
	statement, _ = db.Prepare("delete from books where id=?")
	statement.Exec(1)
	log.Println("Successfully deleted the book in database!")

}

Output:

# go run main.go 
2022/10/01 23:21:46 Successfully created table books!
2022/10/01 23:21:46 Inserted the book into database!
2022/10/01 23:21:46 ID:1, Book:A Tale of Two Cities, Author:Charles Dickens
2022/10/01 23:21:46 Successfully updated the book in database!
2022/10/01 23:21:46 ID:1, Book:A Tale of Three Cities, Author:Charles Dickens
2022/10/01 23:21:46 Successfully deleted the book in database!

 

Different go sqlite driver functions

Go sqlite3 Exec

The Exec function executes a query without returning any rows. First of all, we run a query to create 'students' table:

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func main() {

	db, err := sql.Open("sqlite3", "test.db")

	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	sts := `
DROP TABLE IF EXISTS students;
CREATE TABLE students(id INTEGER PRIMARY KEY, name TEXT, score REAL);
INSERT INTO students(name, score) VALUES('Anna',8.5);
INSERT INTO students(name, score) VALUES('Bob',7.5);
INSERT INTO students(name, score) VALUES('Claire',9.5);
INSERT INTO students(name, score) VALUES('Charlie',6.5);
INSERT INTO students(name, score) VALUES('Daniel',8.0);
INSERT INTO students(name, score) VALUES('Hellen',7.0);
INSERT INTO students(name, score) VALUES('Hummer',7.5);
INSERT INTO students(name, score) VALUES('John',10);
`
	// run the query
	_, err = db.Exec(sts)

	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("table created")
}

Output:

table created

And test.db will be created in the working folder.

 

Select rows with Query

The Query method runs a SELECT query that returns rows. The optional arguments are for any query placeholder parameters. Here's an example of query all students who have score > 8:

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func main() {

	db, err := sql.Open("sqlite3", "test.db")

	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	rows, err := db.Query("SELECT * FROM students where score > 8")

	if err != nil {
		log.Fatal(err)
	}

	defer rows.Close()
        
        // iterate through all the records
	for rows.Next() {
		var id int
		var name string
		var score float64
		err = rows.Scan(&id, &name, &score)
		if err != nil {
			log.Fatal(err)
		}

		fmt.Printf("%v %v %v\n", id, name, score)
	}
}

Output:

1 Anna 8.5
3 Claire 9.5
8 John 10

 

Select rows with Prepare statement

Instead of directly writing the values into the statements, we use placeholders. Prepared statements improve database security and performance. This below example shows how to use Prepared Statement to query students with 7 < score < 9

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func main() {

	db, err := sql.Open("sqlite3", "test.db")

	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	preState, err := db.Prepare("SELECT * FROM students WHERE score > ? and score < ?")
	minQueryScore := 7
	maxQueryScore := 9
	rows, err := preState.Query(minQueryScore, maxQueryScore)

	if err != nil {
		log.Fatal(err)
	}

	defer rows.Close()

	for rows.Next() {

		var id int
		var name string
		var score float64

		err = rows.Scan(&id, &name, &score)

		if err != nil {
			log.Fatal(err)
		}

		fmt.Printf("%v %v %v\n", id, name, score)
	}
}

Output:

1 Anna 8.5
2 Bob 7.5
5 Daniel 8
7 Hummer 7.5

 

Print affected rows

The RowsAffected function returns the number of rows that have been affected by an update, insert, or delete statement.

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

func main() {

	db, err := sql.Open("sqlite3", "test.db")

	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	res, err := db.Exec(`INSERT INTO students(name, score) VALUES('Kelly',8.0);
	INSERT INTO students(name, score) VALUES('Kai',9.5);
	INSERT INTO students(name, score) VALUES('Ben',9.0);
	INSERT INTO students(name, score) VALUES('Bin',7.5);`)

	if err != nil {
		log.Fatal(err)
	}

	numOfRow, err := res.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("The statement has affected %d rows\n", numOfRow)
}

Output:

The statement has affected 4 rows

 

Summary

In this tutorial, we have performed some operations (Create, Read, ...) with sqlite in Go. Using sqlite in Golang is easy because we have many Go-sqlite drivers and we can write a raw sql query and run it within Query() function.

 

References

https://www.sqlite.org/index.html
https://github.com/mattn/go-sqlite3