Raymond Raymond

Go: Access Data in SQLite

event 2021-12-25 visibility 522 comment 0 insights toc
more_vert
insights Stats
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:

2021122532903-image.png

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/
20211225220217-image.png

20211225220247-image.png

20211225220305-image.png

20211225220357-image.png

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

20211225220423-image.png

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:

20211225234632-image.png

More examples

You can find more examples here:

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

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts