Click here to Skip to main content
15,882,552 members
Articles / Sqlite

Golang SQLite Simple Example

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
10 Mar 2020CPOL1 min read 56.1K   2   2
A simple example of how to use SQLite in Golang
In this post, you will see how to use SQLite in Golang with the help of a simple example. After listing the requirements, we will look at the code, followed by instructions on how to compile the code and run the application. An issue with CGO and a solution has been mentioned at the end of the article.

Image 1

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.

SQL
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

golang-sqlite-sample-output

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

This article was originally posted at https://johnpili.com/golang-sqlite-simple-example

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Product Manager PSI
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionGood post Pin
John Dovey2-Feb-21 6:46
John Dovey2-Feb-21 6:46 
QuestionBEGIN/COMMIT Pin
Pete Lomax Member 1066450516-Mar-20 11:33
professionalPete Lomax Member 1066450516-Mar-20 11:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.