Go: Access Data in SQLite

SQLite is a famous embedded file database. This article provides example code to access data in SQLite via Go programming language.

Create a Go module

Run the following commands to create a module:

mkdir gosqlite
cd .\gosqlite\
go mod init kontext.tech/gosqlite
code .

* I'm using Visual Studio Code as editor. `code .` will open the IDE for gosqlite directory.

The empty project looks like the following screenshot:

Install SQLite3 Go package

Install SQLite3 package using the following command:

go get github.com/mattn/go-sqlite3

*I am using mattn/go-sqlite3 package.

If the installation is successful, the following message will print out:

go get: added github.com/mattn/go-sqlite3 v1.14.9

You may encounter the following error if you run the above command on Windows and if gcc toolchain is not available in your system:

cgo: C compiler "gcc" not found: exec: "gcc": executable file not found in %PATH%

To enable it, you can download and install TDM-GCC:

https://jmeubank.github.io/tdm-gcc/download/

*Make sure gcc is included and Add to PATH is also checked.

Wait until the installation is completed and then you can verify using `gcc --version` to verify. 

gcc --version
gcc.exe (tdm64-1) 10.3.0
Copyright (C) 2020 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Create sqlite.go file

Create a file named sqlite.go with the following content:

package main

import (
 "database/sql"
 "fmt"
 "log"
 _ "github.com/mattn/go-sqlite3"
)

func main() {
 fmt.Println("SQLite 3 Go Example!")
 db, err := sql.Open("sqlite3", "./test.db")
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()

 sqlStmt := `
 create table customer (cust_id integer not null primary key, cust_name text);
 delete from customer;
 `
 _, err = db.Exec(sqlStmt)
 if err != nil {
  log.Printf("%q: %s\n", err, sqlStmt)
  return
 }

 tx, err := db.Begin()
 if err != nil {
  log.Fatal(err)
 }
 stmt, err := tx.Prepare("insert into customer(cust_id, cust_name) values(?, ?)")
 if err != nil {
  log.Fatal(err)
 }
 defer stmt.Close()
 for i := 0; i < 10; i++ {
  _, err = stmt.Exec(i, fmt.Sprintf("Customer %02d", i))
  if err != nil {
   log.Fatal(err)
  }
 }
 tx.Commit()

 rows, err := db.Query("select cust_id, cust_name from customer")
 if err != nil {
  log.Fatal(err)
 }
 defer rows.Close()

 for rows.Next() {
  var cust_id int
  var cust_name string
  err = rows.Scan(&cust_id, &cust_name)
  if err != nil {
   log.Fatal(err)
  }
  fmt.Println(cust_id, cust_name)
 }

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

 stmt, err = db.Prepare("select cust_name from customer where cust_id = ?")
 if err != nil {
  log.Fatal(err)
 }
 defer stmt.Close()

 var cust_name string
 err = stmt.QueryRow("3").Scan(&cust_name)
 if err != nil {
  log.Fatal(err)
 }
 fmt.Println(cust_name)

 _, err = db.Exec("delete from customer")
 if err != nil {
  log.Fatal(err)
 }
}

The above script does the following actions:

• Create a SQLite database (via establishing a new connection).
• Create a table named customer with two columns: cust_id and cust_name.
• Start a transaction.
• Insert 10 customer records into the database.
• Commit the transaction.
• Print out all the customers using SELECT statements.
• Print out a customer name using parameterized query.
• Delete all records from customer table.

Output:

More examples

You can find more examples here:

https://github.com/mattn/go-sqlite3/tree/v1.14.9/_example