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 gosqlitecd .\gosqlite\
go mod init app.kontext.tech/gosqlite
code .
* I'm using Visual Studio Code as editor. ***code .*** will open the IDE for gosqlitedirectory.
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 gcctoolchain 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 --versiongcc.exe (tdm64-1) 10.3.0Copyright (C) 2020 Free Software Foundation, Inc.This is free software; see the source for copying conditions. There is NOwarranty; 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 customerwith two columns: cust_idand 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 customertable.
Output:
More examples
You can find more examples here: