Golang SQLite Simple Example

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

Requirements

• Knowledge of Terminal or command prompt
• A running Golang installation
• Knowledge of compiling Golang codes
• Visual Studio Code (optional)

Let’s Begin

We will use a Golang library from https://github.com/mattn/go-sqlite3. To install this library, we will use the following command below:

go get github.com/mattn/go-sqlite3

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 code shown below. I’ve added comments to help you 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)
	}
}

Compiling the Code and Running the Application

Inside our example folder, type the following commands to compile:

go build

To run the code:

./golang-sqlite

Issues with CGO

During this writing, I was having issues compiling for other systems 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. You can checkout the code from the following link:
https://github.com/johnpili/golang-sqlite or at https://skypaste.com/l57isan0