Accessing a Relational Database in Golang

Time to read: 8 minutes

June 03, 2024

Written by

Anumadu Udodiri Moses

Contributor

Reviewed by

Matthew Setter

Twilion

Accessing a Relational Database in Go

Every time you visit an application that requires authentication, such as allowing users to log in or register, the application must use a database. The database and the stored data in it allow the application to remember you.

There are different types of databases, but this tutorial focuses on relational databases (RDB) and how a Go application can be connected to one. Data is stored in a relational database in tables. These tables have relationships with each other.

This tutorial will explore connecting a Go application to an SQLite database, and show how to perform CRUD (Create, Read, Update, and Delete) operations, and joining queries.

Prerequisite

To follow along, the following are required

The latest version of Go (1.22 at the time of writing)
SQLite
Your preferred text editor or IDE
Prior experience with relational databases
Working knowledge of Go

Key relational database concepts

Data in an RDB is stored in tables, similar to spreadsheets, consisting of rows (records) and columns (properties). Let's examine some fundamental concepts of RDBs:

Table: A table in a relational database is a collection of related data. It consists of rows and columns with descriptive names.
Row: A row is a collection of information about a particular record in a table. An example of this can be a row containing a user's first name, last name, and date of birth.
Column: A column contains descriptive properties or attributes of the data stored in each row. For example, the user's first name, last name, and date of birth
Primary keys: A primary key is a unique identifier for records in a row within a database table, much like a fingerprint for each record. It ensures that each entry is distinct. Similar to unique identifiers in the real world such as ISBNs for books, flight numbers for airline trips, or registration numbers for courses.
Foreign keys: Foreign keys link two tables together. They contain the column in the joining table with the column in the joined table.
Indexes: Indexes help speed up the retrieval of data by providing rapid access paths to the stored information. For example, an index could allow a database to quickly locate all records where the last name is "Smith" without scanning every entry in the table.

Popular relational databases

PostgreSQL, MySQL, and SQLite are among the most popular relational database systems, each effectively serving different needs.

PostgreSQL is renowned for its robustness, extensive feature set, and substantial compliance with SQL standards, making it a favorite for enterprises and complex applications requiring advanced functionality.
MySQL is widely acclaimed for its speed and reliability. It is commonly used in web applications and is known for its ease of use and efficient performance.
SQLite offers a unique advantage with its serverless architecture, embedding directly into applications with minimal setup, making it ideal for environments with limited resources.

We will use SQLite for this tutorial, because it is quicker and easier to set up, and does not require a separate server or a complex configuration

Build the Go application

We need to create a fresh project using the command below to get started.

mkdir go-database-tutorial
cd go-database-tutorial
go mod init go-database-tutorial
touch main.go

Open the main.go file and add the following code to it

package main

import (
    "database/sql"
    "fmt"
    "log"

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

var db *sql.DB

func main() {
    var err error
    db, err := sql.Open("sqlite3", "./school.db")
    if err != nil {
   	 log.Fatal("Failed to connect to the database:", err)
    }

    err = initDB(db)
    if err != nil {
        log.Fatal("Failed to initialize database:", err)
    }
    defer db.Close()
    fmt.Println("Successfully connected to the database.")
}

func initDB(db *sql.DB) (error) {
    sqlStmt := `
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        age INTEGER
    );`
    if _, err := db.Exec(sqlStmt); err != nil {
        return fmt.Errorf("failed to create table: %v", err)
    }
    return nil
}

The code above creates a database named school.db, creates a table called students, and connects our application to the database.

initDB holds the logic for the database connection and table creation. It accepts a string named dataSourceName, which will be the database name when the function is called. It uses the *sql.DB pointer to establish the database connection and create the table.

To get this code to work, we must run the following command to install the github.com/mattn/go-sqlite3 package .

go get github.com/mattn/go-sqlite3

Once the installation is complete, we can test using the command below.

go run main.go

If everything is done right, the following should be printed in your console.

Successfully connected to the database.

That's it! We have successfully connected our Go application to an SQLite database. Nice and smooth.

Add CRUD functionality

Now, let's take it further by performing a create, read, update, and delete (CRUD) database operation. Let's create a struct of type Student in your main.go file. Just below the import statement, add the following.

type Student struct {
    Name string
    ID, Age int
}

Add the create operation

We need to create a function for the create operation. Below the initDB() function, add the following.

func createData(db *sql.DB, student Student) (int64, error) {
    result, err := db.Exec("INSERT INTO students (name, age) VALUES (?, ?)", student.Name, student.Age)
    if err != nil {
        return 0, fmt.Errorf("insertData: %v", err)
    }
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("insertData: %v", err)
    }
    return id, nil
}

The createData() function adds a new student's data into the students table, and is structured as follows:

Parameters: It takes a Student struct containing two fields: Name and Age
Database operation: The function executes an SQL insert statement to add the student's name and age to the students table. The placeholders (?) in the SQL query are replaced by the student's name and age to protect against SQL injection attacks .
Error handling: If the createData() function runs with an error, it immediately returns an error message with a detailed explanation of what went wrong
Retrieve ID: After successfully inserting the data, the function retrieves the newly added student's ID. This ID helps uniquely identify the student within the database.
Return values: If the insertion is successful, it returns the new student's ID. Otherwise, if an error occurs, it returns 0 for the ID and an error message.

We need to call the function in the main() function. To do that, add the following just below fmt.Println("Successfully connected to the database.") in the main() function.

studentID, err := createData(db, Student{Name: "John Doe", Age: 19})
if err != nil {
    log.Fatal("Failed to insert data:", err)
}
fmt.Printf("ID of added student: %v\n", studentID)

We can test that this works using the command below.

go run main.go

You should see the following output:

Successfully connected to the database.
ID of added student: 1

Add the read operation

Let's proceed to retrieving data from the student table. Add the following function below the function named createData().

func readData(db *sql.DB) error {
    rows, err := db.Query("SELECT id, name, age FROM students")
    if err != nil {
        return fmt.Errorf("query error: %v", err)
    }
    defer rows.Close()
    for rows.Next() {
        record := Student{}
        if err := rows.Scan(&record.ID, &record.Name, &record.Age); err != nil {
            return fmt.Errorf("scan error: %v", err)
        }
        fmt.Printf("%+v", record)
    }
    if err = rows.Err(); err != nil {
        return fmt.Errorf("rows error: %v", err)
    }
    return nil
}

The readData() function retrieves and displays student data from a database. Let's explain how it works.

Query Execution: It executes an SQL query to fetch the id, name, and age columns from the students table. If the query fails, it returns an error.
Data Processing: It scans the values into variables for each row and prints them out. Errors during scanning are returned immediately.
Error Handling: After processing all rows, it checks for any residual errors and returns them
Resource Management: It ensures all database resources are correctly closed after use

The next step is to call readData() in the main() function. Add the following code at the end of the main() function.

if err := readData(db); err != nil {
    log.Fatal("Failed to read data:", err)
}

It is important to note that leaving the call to createData() in main() will create a new record every time the app is run. It might be wise to consider commenting out this call, if not removing it altogether.

As you test other functions throughout the tutorial, always remember to comment out or remove any methods that you don't intend to call.

Add the update operation

In this section, we will carry out update operations. To get started, let's create a function for this action. Add the following at the bottom of main.go after the readData() function.

func updateData(db *sql.DB, student Student) (Student, error) {
    result, err := db.Exec("UPDATE students SET age = ? WHERE name = ?", student.Age, student.Name)
    if err != nil {
        return Student{}, fmt.Errorf("updateData: %v", err)
    }
    rowsAffected, err := result.RowsAffected()
    if err != nil {
        return Student{}, fmt.Errorf("updateData: getting rows affected: %v", err)
    }
    if rowsAffected == 0 {
        return Student{}, fmt.Errorf("updateData: no rows affected, student not found")
    }
    return student, nil
}

The updateData() function updates a student's age in a database for the student name provided. Here’s a simple explanation of how it functions:

Updating data: The function takes a Student object as input and uses it to update the student's age in the database where the student's name matches
Error checking: If the update command fails, it returns an error message to inform you of what went wrong
Confirming changes: This step checks how many rows in the database were affected by the update. If no rows are affected, no student with that name was found.
Returning results: If the update is successful, it returns the Student object. If not, it provides an error explaining whether the operation failed or the student was not found.

Let's call updateData() in main() by adding the following code just below the call to readData().

student, err := updateData(db, Student{Name: "<<STUDENT NAME IN DB>>", Age: <<STUDENT AGE IN DB>>})
if err != nil {
    log.Fatal("Failed to insert data:", err)
}
fmt.Printf("ID of updated student: %v\n", student)

Replace <<STUDENT NAME IN DB>> and <<STUDENT AGE IN DB>> with the corresponding student details in your database, then run the code again to update the student.

Add a delete operation

Let's now create a function for the delete operation. This function will accept the name of the student to be deleted as an argument. Add the code below in main.go, just below the updateData() function.

func deleteData(db *sql.DB, student Student) (Student, error) {
result, err := db.Exec("DELETE FROM students WHERE name = ?", student.Name)
	if err != nil {
		return Student{}, fmt.Errorf("deleteStudentByName: %v", err)
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return Student{}, fmt.Errorf("deleteData: getting rows affected: %v", err)
	}
	if rowsAffected == 0 {
		return Student{}, fmt.Errorf("deleteData: no rows affected, student not found")
	}
	return student, nil
}

The function takes a student struct and deletes the user with the name provided in the function call. To use this function, add the following code at the bottom of the main() function.

student, err := deleteData(db, Student{Name: "<<STUDENT NAME IN DB>>", Age: <<STUDENT AGE IN DB>>})
if err != nil {
log.Fatal("Delete failed:", err)
}
fmt.Printf("ID of deleted student: %v\n", student)

We have completed the CRUD operation, so this would be a good time to test. Replace the placeholders with the corresponding database value and comment out method calls that you might not need for each test. Then, use the command below to run the code.

go run main.go

Add some advanced database operations

SQL joins explained

You will most certainly encounter SQL joins when developing applications that handle complex data relationships. Joins allow you to combine rows from two or more tables based on a related column between them, often a foreign key.

Types of joins:

INNER JOIN: This type of join returns records with matching values in both tables
LEFT JOIN (or LEFT OUTER JOIN): In this type of join, all records from the left table and the matched records from the right table are returned. If there is no match, the result is NULL for the right table
RIGHT JOIN (or RIGHT OUTER JOIN): This is the opposite of a LEFT JOIN; it returns all records from the right table and the matched records from the left table
FULL JOIN (or FULL OUTER JOIN): Returns all records when a match exists in either the left or right table

Integrate students and courses

Let's take a practical approach by making a simple join SQL query to our students table and a new courses table. Let's start by creating a courses table related to our pre-existing students table, with a reference to students via a foreign key.

Update the initDB() function with the code below, and replace the placeholders with real data.

func initDB(dataSourceName string) error {
    studentTableSQL := `
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        age INTEGER
    );

    INSERT INTO students (name, age) VALUES ('<<STUDENT NAME IN DB>>', <<STUDENT DOB IN DB>>);
    `
    if _, err := db.Exec(studentTableSQL); err != nil {
        return fmt.Errorf("failed to create table: %v", err)
    }
    courseTableSQL := `
    CREATE TABLE IF NOT EXISTS courses (
        course_id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_name TEXT NOT NULL,
        student_id INTEGER,
        FOREIGN KEY(student_id) REFERENCES students(id)
    );
    
    INSERT INTO courses (course_name, student_id) VALUES ('Introduction to Programming', <<STUDENT ID IN DB>>);
    INSERT INTO courses (course_name, student_id) VALUES ('Advanced Database Systems', <<STUDENT ID IN DB>>);
    `
    if _, err := db.Exec(courseTableSQL); err != nil {
        return fmt.Errorf("failed to create table: %v", err)
    }
    return nil
}

This revised function creates a courses table where:

course_id is the primary key
course_name is a text field that cannot be NULL
student_id links each course to a student in the students table

Let's use an inner join to retrieve data from these tables. We will create a new function for this. Add the following to the end of the main.go file.

func innerJoin(db *sql.DB, student Student) error {
    query := `SELECT students.name, courses.course_name
    FROM students
    JOIN courses ON students.id = courses.student_id
    WHERE students.name = ?`
    rows, err := db.Query(query, student.Name)
    if err != nil {
        return fmt.Errorf("query error: %v", err)
    }
    defer rows.Close()
    for rows.Next() {
        var name string
        var courseName string
        if err := rows.Scan(&name, &courseName); err != nil {
            return fmt.Errorf("scan error: %v", err)
        }
        fmt.Println(name, courseName)
    }
    if err = rows.Err(); err != nil {
        return fmt.Errorf("rows error: %v", err)
    }
    return nil
}

The function performs an SQL inner join between the students and courses tables using the student's ID as the linking identifier. The function filters the results to include only those entries where the student's name matches the studentName parameter passed to the function. It executes this query with a placeholder to prevent SQL injection by using parameterized queries.

If the query successfully retrieves rows, it iterates over them, extracting the student and course names and printing the result. If any errors occur during the query execution, row scanning, or while checking for errors post-iteration, the function handles them gracefully by returning a formatted error message.

Now, we need to call innerJoin() in main(). Add the following at the end of the main() function.

studentName := "<<STUDENT NAME IN DB>>"
if err = innerJoin(db, Student{Name: studentName}); err != nil {
    log.Fatal(err)
}

For the scope of this tutorial, we will not explore joins in SQL too deeply. However, if you want to explore this topic further, check out the documentation.

That's how to access relational databases in Go

We have come to the end of this tutorial. If you followed along to this point, thumbs up. Let's recap the key concepts we explored.

We covered the essentials of setting up and accessing an SQLite database in Go. We took it further by performing CRUD operations and executing SQL join queries.

SQLite is an excellent choice for applications that require a lightweight, self-contained database engine. For further learning, visit the SQLite documentation and the database/sql package's documentation.

Moses Anumadu is a software developer and online educator who loves to write clean, maintainable code. He's creating something awesome for Laravel developers with Laravel.

The database icon in the tutorial's main image was created by phatplus on Flaticon.

Related Resources

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.