Prerequisites:
You have to complete the following tasks before practicing the examples in this tutorial:
A. Install the Golang language in the system if it is not installed before. Run the following command to check the installed version of Golang:
B. Install the sqlite3 in the system if it has not been installed before. Sqlite3 is installed by default on Ubuntu 20+. Run the following command to check the installed version of sqlite3:
C. Run the following commands to create a folder named “golang-sqlite” and go to that folder where the SQLite database file and the Golang file will be stored:
$ cd golang-sqlite
D. Run the following command to download the necessary Golang package to use SQLite with Golang:
Database Operations Using Golang and SQLite
The basic database operations using the SQLite database and Golang script are shown in this tutorial. The list of database tasks that are covered in this tutorial is mentioned in the following:
- Create an SQLite Database
- Make an SQLite Database Connection
- Create a New Table
- Insert the Data into the Table
- Read the Data from the Table
- Update the Data of the Table
- Delete the Data from the Table
Create an SQLite Database Using Golang
Create a Golang file with the following script that creates a new SQLite database file named “test.db”. Four modules are imported into the script. The “fmt” module is used to print the output in the terminal. The “log” module is used to abort the program when any error appears. The “os” module is used to create the SQLite database and handle the error. The “go-sqlite3” module is used to make connections with the SQLite database through Golang. The Create() method of the “os” module is used here to create an SQLite database. If the database is created successfully, a success message is printed in the terminal. Otherwise, the program will be terminated. The Close() method is called to close the database connection.
//Import the necessary modules
import (
"fmt"
"log"
"os"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//Create a new SQLite database
db, err := os.Create("test.db")
//Check for any error
if err != nil{
//Add the error message to the log
log.Fatal(err)
}else{
//Print the success message
fmt.Println("Database is created.")
}
//Close the database connection
db.Close()
}
Run the following command to execute the “db_connect.go” file:
Run the “ls” command to check whether the “test.db” database file is created or not:
The following output shows that the success message, “Database is created”, is printed in the output and the “test.db” file is created in the current location:
Connect with an SQLite Database Using Golang
Create a Golang file with the following script that opens the “test.db” database file and make the connection with the database. The database/SQL module of Golang is imported here to do this task. The open() method is used here to make the connection with the existing SQLite database. If the database is connected successfully, a success message is printed in the terminal. Otherwise, the program will be terminated.
//Import the necessary modules
import (
"fmt"
"log"
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//Create a new SQLite database
db, err := sql.Open("sqlite3", "test.db")
//Check for any error
if err != nil{
//Add the error message to the log
log.Fatal(err)
}else{
//Print the success message
fmt.Println("Database is connected successfully.")
}
//Close the database connection
db.Close()
}
Run the following command to execute the “db_connect2.go” file:
The following output shows that the “test.db” database file is opened and connected successfully. The success message, “Database is connected successfully”, is printed in the output:
Create a Table in an SQLite Database Using Golang
Create a Golang file with the following script that creates a database table named “members” inside the “test.db” database file. The Exec() method is used here to execute the CREATE TABLE query that creates the “members” table with six fields and a primary key. If the table is created successfully in the database, a success message is printed. Otherwise, the program will be terminated.
//Import the necessary modules
import (
"fmt"
"log"
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//Create a new sqlite database
db, err := sql.Open("sqlite3", "test.db")
//Check for any error
if err != nil{
//Add the error message to the log
log.Fatal(err)
}else{
const query string = `
CREATE TABLE IF NOT EXISTS members (
id INTEGER NOT NULL PRIMARY KEY,
name CHAR(40) NOT NULL,
mtype CHAR(100) NOT NULL,
email CHAR(50),
address TEXT NOT NULL,
mobile CHAR(25) NOT NULL);`
_, err := db.Exec(query);
if err != nil {
//Add the error message to the log
log.Fatal(err)
}else{
//Print the success message
fmt.Println("Table is created successfully.")
}
}
//Close the database connection
db.Close()
}
Run the following command to execute the “create_table.go” file:
The following output shows that the table is created successfully in the database. The success message, “Table is created successfully”, is printed in the terminal:
You can log in to SQLite to check whether the table is created or not in the database.
Run the following command to run SQLite with the “test.db” database:
Run the “.tables” SQLite command from the SQLite command prompt to check the table names of the database:
The following output shows that the database contains the “members” table that is created by executing the Golang script:
Insert the Records into the SQLite Table Using Golang
Multiple records can be inserted into the table by executing the INSERT query multiple times or by adding multiple values in a single INSERT query. Create a Golang file with the following script that inserts three records into the “members” table of the “test.db” database file using a single INSERT query. The Exec() method is used here to execute the INSERT query. If the records are inserted successfully in the table, a success message is printed. Otherwise, the program will be terminated.
//Import the necessary modules
import (
"fmt"
"log"
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//Create a new SQLite database
db, err := sql.Open("sqlite3", "test.db")
//Check for any error
if err != nil{
//Add the error message to the log
log.Fatal(err)
}else{
const query string = `
INSERT INTO members (id, name, mtype, email, address, mobile)
VALUES(1, 'Nehal Ahmed', 'Silver', '[email protected]','36, Dhanmondi 2, Dhaka','01844657342'),
(2, 'Abir Chowdhury', 'Gold', '[email protected]','102, Mirpur 10, Dhaka','01994563423'),
(3, 'Mirza Abbas', 'Silver', '[email protected]','12, Jigatala, Dhaka','01640006710');`
//Execute the query
_, err := db.Exec(query);
if err != nil {
//Add the error message to the log
log.Fatal(err)
}else{
//Print the success message
fmt.Println("Records inserted successfully.")
}
}
//Close the database connection
db.Close()
}
Run the following command to execute the “insert_table.go” file:
The following output shows that records are inserted successfully into the table. The success message, “Records inserted successfully”, is printed in the output:
You can log in to SQLite to check whether the three records are inserted successfully or not in the “members” table.
Run the following command to run SQLite with the “test.db” database:
Run the following SELECT query to read all content of the “members” table:
The following output shows that the “members” table contains three records that are inserted by the Golang script:
Read the Data of the SQLite Table Using Golang
Create a Golang file with the following script that reads the records of the “members” table. You have to define the variables in Golang to store the values that are returned from the SELECT query. In this script, five fields are retrieved from the “members” table using the SELECT query. So, five variables are defined in the script to store the values of the five fields of the “members” table where the value of the “mtype” field is “Silver”. The Query() method is used here to execute the SELECT query. Next, the “for” loop is used to read the result set that is returned by the SELECT query. The printf() function is used to print the formatted values of the result set.
//Import the necessary modules
import (
"fmt"
"log"
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//Create a new SQLite database
db, err := sql.Open("sqlite3", "test.db")
//Check for any error
if err != nil{
//Add the error message to the log
log.Fatal(err)
}else{
//Declare variables to store the row values
var id int
var name string
var mtype string
var address string
var mobile string
const query string = `SELECT id, name, mtype, address, mobile
FROM members
WHERE mtype = 'Silver';`
//Execute the query
rows, err := db.Query(query);
if err != nil {
//Add the error message to the log
log.Fatal(err)
}else{
//Print the success message
fmt.Println("Records of all silver members:")
fmt.Println("ID\tName\t\tMember Type\tAddress\t\tContact No")
for rows.Next() {
rows.Scan(&id, &name, &mtype, &address, &mobile)
fmt.Printf("%d\t %s\t %s\t %s\t %s\n", id, name, mtype, address , mobile)
}
}
}
//Close the database connection
db.Close()
}
Run the following command to execute the “select_table.go” file:
Two records exist in the “members” table that contains “Silver” in the “mtype” field. The following output shows two records of the “members” table that are returned by the SELECT query:
Update the Data of the SQLite Table Using Golang
Create a Golang file with the following script that changes the value of the mobile field of the “members” table that contains the id value of 2. The Exec() method is used here to execute the UPDATE query. If the record is updated successfully in the table, a success message is printed. Otherwise, the program will be terminated.
//Import the necessary modules
import (
"fmt"
"log"
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//Create a new SQLite database
db, err := sql.Open("sqlite3", "test.db")
//Check for any error
if err != nil{
//Add the error message to the log
log.Fatal(err)
}else{
const query string = `
UPDATE members SET mobile = '018563452390' WHERE id = 2;`
_, err := db.Exec(query);
if err != nil {
//Add the error message to the log
log.Fatal(err)
}else{
//Print the success message
fmt.Println("Record is updated successfully.")
}
}
//Close the database connection
db.Close()
}
Run the following command to execute the “update_table.go” file:
In the “members” table, the second row contains the id value of 2. The following output shows that the record of the table is updated successfully. The success message, “Record is updated successfully”, is printed in the terminal:
You can log in to SQLite to check whether any record of the members table is updated or not.
Run the following command to run SQLite with the “test.db” database:
Run the following SELECT query to read all content of the “members” table:
The value of the mobile field of the 2nd record of the members table is “01994563423” before executing the script. The following output shows that the value of the mobile field of the 2nd record of the members table is changed to “018563452390” after executing the script:
Delete the Data of the SQLite Table Using Golang
Create a Golang file with the following script that deletes the 3rd record of the “members” table that contains the id value of 3. The Exec() method is used here to execute the DELETE query. If the record is deleted successfully from the table, a success message is printed. Otherwise, the program will be terminated.
//Import the necessary modules
import (
"fmt"
"log"
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
func main() {
//Create a new SQLite database
db, err := sql.Open("sqlite3", "test.db")
//Check for any error
if err != nil{
//Add the error message to the log
log.Fatal(err)
}else{
//Define the delete query
const query string = `DELETE FROM members WHERE id = 3;`
//Execute the query
_, err := db.Exec(query);
if err != nil {
//Add the error message to the log
log.Fatal(err)
}else{
//Print the success message
fmt.Println("Record is deleted successfully.")
}
}
//Close the database connection
db.Close()
}
Run the following command to execute the “delete_table.go” file:
The following output shows that the 3rd record of the “members” table is deleted successfully. The success message, “Record is deleted successfully”, is printed in the terminal:
You can log in to SQLite to check whether any record of the members table is deleted or not.
Run the following command to run SQLite with the “test.db” database:
Run the following SELECT query to read all content of the “members” table:
The following output shows that the value of the 3rd record of the members table is deleted after executing the script and the other two records are printed in the output:
Conclusion
Both SQLite and Golang are popular now for their simplicity and lightweight features. Any simple database-based application can be implemented easily using these tools. The major part of any application is to implement the CRUD operation. The methods of implementing CRUD operation using Golang script and SQLite database are explained in this tutorial using multiple Golang files. If you are a new learner of the Go language and the SQLite database, this tutorial will help you learn these from the basic.
