In this post, I will show you a simple example how to use SQLite in Go (Golang). SQLite is one of the popular embedded, file-based database in the market used by companies like Apple, Airbus, Google, Skype, Autodesk and Dropbox. You can check out the list of well-know SQLite user in this link https://www.sqlite.org/famous.html

Requirements

  • Knowledge in Terminal or command prompt
  • An installed Go 1.19 or latest
  • Knowledge in compiling Go codes
  • Visual Studio Code (Optional)

Let’s begin

We will use a library from https://github.com/mattn/go-sqlite3 to handle SQLite.

Using Visual Studio Code or just the command line. Create a folder for this example.

mkdir golang-sqlite
cd golang-sqlite

Next we will start writing our main.go using the codes shown below. I’ve added comments to help us understand what the line of code does.

package main

import (
	"database/sql"
	"log"
	"os"

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

func main() {
	os.Remove("sqlite-database.db") // I delete the file to avoid duplicated records. SQLite is a file based database.

	log.Println("Creating sqlite-database.db...")
	file, err := os.Create("sqlite-database.db") // Create SQLite file
	if err != nil {
		log.Fatal(err.Error())
	}
	file.Close()
	log.Println("sqlite-database.db created")

	sqliteDatabase, _ := sql.Open("sqlite3", "./sqlite-database.db") // Open the created SQLite File
	defer sqliteDatabase.Close() // Defer Closing the database
	createTable(sqliteDatabase) // Create Database Tables

        // INSERT RECORDS
	insertStudent(sqliteDatabase, "0001", "Liana Kim", "Bachelor")
	insertStudent(sqliteDatabase, "0002", "Glen Rangel", "Bachelor")
	insertStudent(sqliteDatabase, "0003", "Martin Martins", "Master")
	insertStudent(sqliteDatabase, "0004", "Alayna Armitage", "PHD")
	insertStudent(sqliteDatabase, "0005", "Marni Benson", "Bachelor")
	insertStudent(sqliteDatabase, "0006", "Derrick Griffiths", "Master")
	insertStudent(sqliteDatabase, "0007", "Leigh Daly", "Bachelor")
	insertStudent(sqliteDatabase, "0008", "Marni Benson", "PHD")
	insertStudent(sqliteDatabase, "0009", "Klay Correa", "Bachelor")

        // DISPLAY INSERTED RECORDS
	displayStudents(sqliteDatabase)
}

func createTable(db *sql.DB) {
	createStudentTableSQL := `CREATE TABLE student (
		"idStudent" integer NOT NULL PRIMARY KEY AUTOINCREMENT,		
		"code" TEXT,
		"name" TEXT,
		"program" TEXT		
	  );` // SQL Statement for Create Table

	log.Println("Create student table...")
	statement, err := db.Prepare(createStudentTableSQL) // Prepare SQL Statement
	if err != nil {
		log.Fatal(err.Error())
	}
	statement.Exec() // Execute SQL Statements
	log.Println("student table created")
}

// We are passing db reference connection from main to our method with other parameters
func insertStudent(db *sql.DB, code string, name string, program string) {
	log.Println("Inserting student record ...")
	insertStudentSQL := `INSERT INTO student(code, name, program) VALUES (?, ?, ?)`
	statement, err := db.Prepare(insertStudentSQL) // Prepare statement. This is good to avoid SQL injections
	if err != nil {
		log.Fatalln(err.Error())
	}
	_, err = statement.Exec(code, name, program)
	if err != nil {
		log.Fatalln(err.Error())
	}
}

func displayStudents(db *sql.DB) {
	row, err := db.Query("SELECT * FROM student ORDER BY name")
	if err != nil {
		log.Fatal(err)
	}
	defer row.Close()
	for row.Next() { // Iterate and fetch the records from result cursor
		var id int
		var code string
		var name string
		var program string
		row.Scan(&id, &code, &name, &program)
		log.Println("Student: ", code, " ", name, " ", program)
	}
}

Afterwards, we will need to initialize the go modules.

go mod init golang-sqlite
go mod tidy

The output of go mod tidy command should like something like this:

[email protected]:~/workspace/golang-sqlite$ go mod tidy
go: finding module for package github.com/mattn/go-sqlite3
go: downloading github.com/mattn/go-sqlite3 v1.14.16
go: found github.com/mattn/go-sqlite3 in github.com/mattn/go-sqlite3 v1.14.16

Compiling the code and running the application

Inside our example folder type the following commands to compile

go build

To run the compiled application

./golang-sqlite

Result output

golang-sqlite-simple-example-output.png

Issues with CGO

During this writing I was having issues compiling for other system on my macOS. This is because of CGO related or compiler issue. To save time, I end up compiling on a particular OS.

Conclusion

Using SQLite in Golang is easy. I uploaded the complete project code on github https://github.com/johnpili/golang-sqlite.