In this post, I will show you a simple example how to use SQLite in 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 checkout the list of well-know SQLite user in this link https://www.sqlite.org/famous.html
Requirements
- Knowledge in Terminal or command prompt
- A running Golang installation
- Knowledge in 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 codes 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 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. You can checkout the code from the following links:
https://github.com/johnpili/golang-sqlite.