SQLite

Golang SQLite Examples

Golang is an open-source programming language. It supports different types of database tools. SQLite is a popular relational database engine and Golang has a database/SQL package that can be used to connect with the SQLite database and perform different types of database tasks. The way of connecting with SQLite database and performing different types of database operations such as creating the table, inserting the records into the table, reading the data from the table, updating the data of the table, and deleting the records from the table are shown in this tutorial using the Golang script.

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:

$ go version

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:

$ sqlite3 --version

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:

$ mkdir golang-sqlite
$ cd golang-sqlite

D. Run the following command to download the necessary Golang package to use SQLite with Golang:

$ go get github.com/mattn/go-sqlite3

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:

  1. Create an SQLite Database
  2. Make an SQLite Database Connection
  3. Create a New Table
  4. Insert the Data into the Table
  5. Read the Data from the Table
  6. Update the Data of the Table
  7. 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.

package main

//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:

$ go run db_connect.go

Run the “ls” command to check whether the “test.db” database file is created or not:

$ ls

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.

package main

//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:

$ go run db_connect2.go

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.

package main

//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:

$ go run create_table.go

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:

$ sqlite3 test.db

Run the “.tables” SQLite command from the SQLite command prompt to check the table names of the database:

sqlite3 > .tables

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.

package main

//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', 'nehal@gmail.com','36, Dhanmondi 2, Dhaka','01844657342'),
                      (2, 'Abir Chowdhury', 'Gold', 'abir@gmail.com','102, Mirpur 10, Dhaka','01994563423'),
                      (3, 'Mirza Abbas', 'Silver', 'abbas@gmail.com','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:

$ go run insert_table.go

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:

$ sqlite3 test.db

Run the following SELECT query to read all content of the “members” table:

sqlite3 > SELECT * FROM members;

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.

package main

//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:

$ go run select_table.go

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.

package main

//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:

$ go run update_table.go

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:

$ sqlite3 test.db

Run the following SELECT query to read all content of the “members” table:

sqlite3 > SELECT * FROM members;

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.

package main

//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:

$ go run delete_table.go

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:

$ sqlite3 test.db

Run the following SELECT query to read all content of the “members” table:

sqlite3 > SELECT * FROM members;

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.

About the author

Fahmida Yesmin

I am a trainer of web programming courses. I like to write article or tutorial on various IT topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.