Golang SQLite

SQLite is a simple, minimalistic, fast, highly reliable SQL database engine. It is a very popular and efficient choice for testing due to its simplicity and availability.

In this article, you will discover how to work with SQLite in the Go programming language. This tutorial uses a very simple example making it suitable for beginners.

Requirements

To follow along with this tutorial, you will need the following:

Latest version of the Go compiler.
A text editor of your choice.
Basic Knowledge of Go.

Once you have the requirements met, we can proceed.

Environment Setup

The first step is to create a test environment to build a go application. Start by creating a directory using the command:

mkdir sqlite-test

next, navigate into the directory and initialize using the command:

cd sqlite-test
go mod init sqlite-test

Once you have your modules initialized, create a go main file:

touch main.go

Next, import the go-sqlite-3 package. This package allows us to work with SQLite databases using Go.

Add the following line to your main.go file:

import _ "github.com/mattn/go-sqlite3"

Run the command below to install the packages.:

go mod tidy

Alternatively, you can run the command below to install the go-sqlite3 package manually:

go get github.com/mattn/go-sqlite3

Create Database File

Since SQLite is a file-based database, we need to create a database file to store the data. We can do this using the os.Create() method.

An example is as shown:

file, err := os.Create("database.db")
if err != nil {
log.Fatal(err)
}
file.Close()
}

Create Table Function

To add a table to the database, we can open the file for reading and then add the specified table. In this example, we will create a function that takes raw SQL commands to add a new table.

An example is as shown:

func createTable(db *sql.DB) {
users_table := `CREATE TABLE users (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"FirstName" TEXT,
"LastName" TEXT,
"Dept" TEXT,
"Salary" INT);`
query, err := db.Prepare(users_table)
if err != nil {
log.Fatal(err)
}
query.Exec()
fmt.Println("Table created successfully!")
}

In the example above, we create function that takes a raw SQL Query and executes against the specified database. This will create a database table in SQLite.

Insert Record Function

The next part is to build a function that allows us to insert records into the table. An example code is as shown:

func addUsers(db *sql.DB, FirstName string, LastName string, Dept string, Salary int) {
records := `INSERT INTO users(FirstName, LastName, Dept, Salary) VALUES (?, ?, ?, ?)`
query, err := db.Prepare(records)
if err != nil {
log.Fatal(err)
}
_, err = query.Exec(FirstName, LastName, Dept, Salary)
if err != nil {
log.Fatal(err)
}
}

Fetch Records Function

The final function we need to create is one that fetches the records stored in the database, an example is as shown:

func fetchRecords(db *sql.DB) {
record, err := db.Query("SELECT * FROM users")
if err != nil {
log.Fatal(err)
}
defer record.Close()
for record.Next() {
var id int
var FirstName string
var LastName string
var Dept string
var Salary int
record.Scan(&id, &FirstName, &LastName, &Dept, &Salary)
fmt.Printf("User: %d %s %s %s %d", id, FirstName, LastName, Dept, Salary)
}
}

The function above uses the cursor iterator to fetch the rows from the database.

Execute Queries on the Database

Once we have our functions declared, we need to call them to add the records into the database. We will start by opening the database file. Next, we call the createTable() and addUsers() methods.

Example code is as shown:

database, _ := sql.Open("sqlite3", "databsase.db")
createTable(database)

addUsers(database, "Ankita", "Maudie", "Game Developer", 140000)
addUsers(database, "Emiliana", "Alfiya", "Bakend Developer", 120000)
addUsers(database, "Emmet", "Brian", "DevOps Developer", 110000)
addUsers(database, "Reidun", "Jorge", "Dtabase Developer", 140000)
addUsers(database, "Tyrone", "Silvia", "Front-End Developer", 109000)
defer database.Close()
// fetch records:
fetchRecords(database)

The section above opens the database for read and inserts the records using the addUsers() method.

Compile & Run

To build the program, you will need to install the GCC compiler on your system. This is required by the go-sqlite3 package. Check the resource below to learn how to install the dependencies on your system.

https://github.com/mattn/go-sqlite3

You can now build the code as:

go build main.go

You can run the program as:

./main.exe

The code should return the records stored in the database. An example output is as shown:

Conclusion

This guide discusses how to use SQLite database in the Golang using the go-sqlite3 package. Check the docs to learn more.