Golang SQL Tutorial [CRUD Operation]

GO

Author: Tuan Nguyen
Reviewer: Deepak Prasad

You may use Go to build applications that use a wide range of databases. There are many built-in libraries to help us connect our applications to databases. Topics in this section describe how to use the standard library’s database/sql package to access relational databases.

database/sql: Package sql provides a generic interface around SQL (or SQL-like) databases. The sql package must be used in conjunction with a database driver. See https://golang.org/s/sqldrivers for a list of drivers. Drivers that do not support context cancelation will not return until after the query is completed. For usage examples, see the wiki page at https://golang.org/s/sqlwiki.

In this tutorial, you’ll create a database, then write code to access and do some operations (create, read, update, delete) to the database.

 

Topics we will cover hide
Prerequisites
Set up SQL database
Connect Golang with SQL
CRUD operation with Golang SQL
Summary
References

Prerequisites

  • MySQL: Follow MySql installation or check if you have installed a relational database management system (DBMS)
  • Go compiler: Download and install Go quickly with the steps described on the official page.
  • A command terminal: Go works well using any terminal on Linux and Mac, and on PowerShell or cmd in Windows
  • Go IDE (optional): On-the-fly error detection and suggestions for fixes, quick and safe refactorings with one-step undo, intelligent code completion, dead code detection, and documentation hints help all Go developers, from newbies to experienced professionals, to create fast, efficient, and reliable code.

 

Set up SQL database

The following steps guide you on how to create a new database. To build the database and table and to add data, use the CLI for the DBMS itself. In this article, we will create a table to store information about students in a school. The code here uses MySQL CLI. For the other DBMSes, you can refer to the documentation to see similar features.

Open a terminal, log in to your DBMS, as in the following example for MySQL:

// login
mysql -u root -p

Create a new database and change to the database you just created so you can work with it:

create database students

// change to students db
use students

Output:

Golang SQL Tutorial [CRUD Operation]

For more articles about SQL commands, access our SQL section. In this tutorial, we will save the SQL query to a file and execute that file using the command line:

SQL file:

DROP TABLE IF EXISTS student;
CREATE TABLE student(
  id         INT AUTO_INCREMENT NOT NULL,
  name      VARCHAR(128) NOT NULL,
  email     VARCHAR(255) NOT NULL,
  age      INT NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO student
  (name, email, age)
VALUES
  ('Ana Grey', 'email1@gmail.com', 20),
  ('Jeru Steps', 'email1@gmail.com', 22),
  ('Charlice Honey', 'email1@gmail.com', 25),
  ('Harry Potter', 'email1@gmail.com', 24);

Run the script you just made from the mysql command prompt:

source /path/to/create-tables.sql

You can test with SELECT statement if your tables already created:

select * from student;

Output:

+----+----------------+------------------+-----+
| id | name           | email            | age |
+----+----------------+------------------+-----+
|  1 | Ana Grey       | email1@gmail.com |  20 |
|  2 | Jeru Steps     | email1@gmail.com |  22 |
|  3 | Charlice Honey | email1@gmail.com |  25 |
|  4 | Harry Potter   | email1@gmail.com |  24 |
+----+----------------+------------------+-----+

 

Connect Golang with SQL

Start writing your Go code now that you have a database with some data. You will require both the database/sql itself and a driver for the particular database you want to use in order to use it.

Although specific drivers encourage it, you shouldn't typically utilize driver packages directly. (Our judgment is that it is typically a bad idea.) If at all possible, your code should only make references to types declared under database/sql. By doing so, you can change the underlying driver (and subsequently the database you're accessing) with a few changes to your code and avoid making your code dependent on the driver. In your browser, visit the SQLDrivers  page to identify a driver you can use. For this MySQL  tutorial, you’ll use Go-MySQL-Driver.

Run the command below to install the driver:

go get -u github.com/go-sql-driver/mysql

 

Get a database handle and connect

Next, write some Go code that allows you to access a database using a database handle. We have to use a pointer to an sql.DB struct, which represents access to a specific database.

package main

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

	"github.com/go-sql-driver/mysql"
)

var db *sql.DB

func main() {
	// the db server information
	config := mysql.Config{
		User:   "root",
		Passwd: "1234",
		Net:    "tcp",
		Addr:   "127.0.0.1:3306",
		DBName: "students",
	}

	// get a database handle.
	var err error
	db, err = sql.Open("mysql", config.FormatDSN())
	if err != nil {
		log.Fatal(err)
	}

	pingErr := db.Ping()
	if pingErr != nil {
		log.Fatal(pingErr)
	}
	fmt.Println("Connected!")
}

Output:

Connected!

Explanation:

  • Make a database variable with the type *sql.DB to handle your connection
  • Use the MySQL driver’s Config – and the type’s FormatDSN -– to collect connection properties and format them into a DSN for a connection string.
  • Check for an error from sql.Open. It could fail if, for example, your database connection specifics weren’t well-formed.
  • Call DB.Ping to confirm that connecting to the database works. At run time, sql.Open might not immediately connect, depending on the driver. You’re using Ping here to confirm that the database/sql package can connect when it needs to.
  • Check for an error from Ping, in case the connection failed.

 

CRUD operation with Golang SQL

Query operation

First of all, we need to create a struct to store a record that returns from the database. You’ll use this to hold row data returned from the query.

type Student struct {
    ID     int64
    Name   string
    Email  string
    Age    int
}

Here is the function which returns all the students we have in the database. We will use db.Query() function to do that:

func (db *DB) Query(query string, args ...any) (*Rows, error): Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func listAll() ([]Student, error) {
	var students []Student

	rows, err := db.Query("SELECT * FROM student")
	if err != nil {
		return nil, fmt.Errorf("error in query all student: %v", err)
	}
	defer rows.Close()

	// Loop through rows, using Scan to assign record to slice
	for rows.Next() {
		var std Student
		if err := rows.Scan(&std.ID, &std.Name, &std.Email, &std.Age); err != nil {
			return nil, fmt.Errorf("error in query all student: %v", err)
		}
		students = append(students, std)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error in query all student: %v", err)
	}
	return students, nil
}

Output:

[{1 Ana Grey email1@gmail.com 20} {2 Jeru Steps email1@gmail.com 22} {3 Charlice Honey email1@gmail.com 25} {4 Harry Potter email1@gmail.com 24}]

 

Create operation

Here is the function which uses the SQL INSERT statement to add a new row to the database:

func addStudent(std Student) (int64, error) {
	result, err := db.Exec("INSERT INTO student (name, email, age) VALUES (?, ?, ?)", std.Name, std.Email, std.Age)
	if err != nil {
		return 0, fmt.Errorf("add student: %v", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("add student: %v", err)
	}
	return id, nil
}

and call it from main function:

	stds, err := listAll()
	if err != nil {
		fmt.Println("some error")
	} else {
		fmt.Println(stds)
	}

	newStudent := Student{
		Name:  "Ron Ron",
		Email: "ron@gmail.com",
		Age:   21,
	}

	lastId, err := addStudent(newStudent)
	if err == nil {
		fmt.Println("Last insert id:", lastId)
	}

	stds, err = listAll()
	if err != nil {
		fmt.Println("some error")
	} else {
		fmt.Println(stds)
	}

Output:

Connected!
[{1 Ana Grey email1@gmail.com 20} {2 Jeru Steps email1@gmail.com 22} {3 Charlice Honey email1@gmail.com 25} {4 Harry Potter email1@gmail.com 24}]
Last insert id: 5
[{1 Ana Grey email1@gmail.com 20} {2 Jeru Steps email1@gmail.com 22} {3 Charlice Honey email1@gmail.com 25} {4 Harry Potter email1@gmail.com 24} {5 Ron Ron ron@gmail.com 21}]

 

Update record by id

We still can use EXEC function to update a record:

func (tx *Tx) Exec(query string, args ...any) (Result, error): Exec executes a query that doesn't return rows. For example an INSERT and UPDATE.

Here is an example of updating a record with id=1

	newStudent := Student{
		Name:  "Ron Ron",
		Email: "ron@gmail.com",
		Age:   21,
	}

	lastId, err := updateStudent(1, newStudent)
	if err == nil {
		fmt.Println("Last update id:", lastId)
	}

	stds, err = listAll()
	if err != nil {
		fmt.Println("some error")
	} else {
		fmt.Println(stds)
	}

Here is the updateStudent() function:

func updateStudent(stdId int, std Student) (int64, error) {
	result, err := db.Exec("UPDATE student SET name =?,  email= ?,  age= ? WHERE id=?", std.Name, std.Email, std.Age, stdId)
	if err != nil {
		return 0, fmt.Errorf("update student: %v", err)
	}
	id, err := result.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("update student: %v", err)
	}
	return id, nil
}

Output:

Connected!
[{1 Ana Grey email1@gmail.com 20} {2 Jeru Steps email1@gmail.com 22} {3 Charlice Honey email1@gmail.com 25} {4 Harry Potter email1@gmail.com 24}]
Last update id: 1
[{1 Ron Ron ron@gmail.com 21} {2 Jeru Steps email1@gmail.com 22} {3 Charlice Honey email1@gmail.com 25} {4 Harry Potter email1@gmail.com 24}]

 

Delete record by id

We can use EXEC() function introduced below to delete a record:

Here is the delete student by id function:

func deleteStudent(stdId int) (int64, error) {
	result, err := db.Exec("DELETE from student WHERE id=?", stdId)
	if err != nil {
		return 0, fmt.Errorf("delete student: %v", err)
	}
	id, err := result.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("delete student: %v", err)
	}
	return id, nil
}

The main function:

	_, err = deleteStudent(3)
	if err != nil {
		fmt.Println("some error")
	} else {
		stds, err = listAll()
		fmt.Println(stds)
	}

Output:

Connected!
[{1 Ana Grey email1@gmail.com 20} {2 Jeru Steps email1@gmail.com 22} {3 Charlice Honey email1@gmail.com 25} {4 Harry Potter email1@gmail.com 24}]
[{1 Ana Grey email1@gmail.com 20} {2 Jeru Steps email1@gmail.com 22} {4 Harry Potter email1@gmail.com 24}]

 

Completed code with CRUD Operation

package main

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

	"github.com/go-sql-driver/mysql"
)

var db *sql.DB

func main() {
	// the db server information
	config := mysql.Config{
		User:   "root",
		Passwd: "1234",
		Net:    "tcp",
		Addr:   "127.0.0.1:3306",
		DBName: "students",
	}

	// get a database handle.
	var err error
	db, err = sql.Open("mysql", config.FormatDSN())
	if err != nil {
		log.Fatal(err)
	}

	pingErr := db.Ping()
	if pingErr != nil {
		log.Fatal(pingErr)
	}
	fmt.Println("Connected!")

	stds, err := listAll()
	fmt.Println("Initial records:")
	if err != nil {
		fmt.Println("some error")
	} else {
		fmt.Println(stds)
	}

	upStudent := Student{
		Name:  "Ron Ron",
		Email: "ron@gmail.com",
		Age:   21,
	}

	lastId, err := updateStudent(1, upStudent)
	fmt.Println("------")
	if err == nil {
		fmt.Println("Last update id:", lastId)
	}

	stds, err = listAll()
	if err != nil {
		fmt.Println("some error")
	} else {
		stds, err = listAll()
		fmt.Println("After update a record:")
		fmt.Println(stds)
	}

	newStudent := Student{
		Name:  "Karma Karma",
		Email: "karma@gmail.com",
		Age:   22,
	}

	lastId, err = addStudent(newStudent)
	if err != nil {
		fmt.Println("some error")
	} else {
		fmt.Println("------")
		stds, err = listAll()
		fmt.Println("After add a record:")
		fmt.Println(stds)
	}

	_, err = deleteStudent(3)
	if err != nil {
		fmt.Println("some error")
	} else {
		fmt.Println("------")
		stds, err = listAll()
		fmt.Println("After delete a record:")
		fmt.Println(stds)
	}
}

type Student struct {
	ID    int64
	Name  string
	Email string
	Age   int
}

func listAll() ([]Student, error) {
	var students []Student

	rows, err := db.Query("SELECT * FROM student")
	if err != nil {
		return nil, fmt.Errorf("error in query all student: %v", err)
	}
	defer rows.Close()

	// Loop through rows, using Scan to assign record to slice
	for rows.Next() {
		var std Student
		if err := rows.Scan(&std.ID, &std.Name, &std.Email, &std.Age); err != nil {
			return nil, fmt.Errorf("error in query all student: %v", err)
		}
		students = append(students, std)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error in query all student: %v", err)
	}
	return students, nil
}

func addStudent(std Student) (int64, error) {
	result, err := db.Exec("INSERT INTO student (name, email, age) VALUES (?, ?, ?)", std.Name, std.Email, std.Age)
	if err != nil {
		return 0, fmt.Errorf("add student: %v", err)
	}
	id, err := result.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("add student: %v", err)
	}
	return id, nil
}

func updateStudent(stdId int, std Student) (int64, error) {
	result, err := db.Exec("UPDATE student SET name =?,  email= ?,  age= ? WHERE id=?", std.Name, std.Email, std.Age, stdId)
	if err != nil {
		return 0, fmt.Errorf("update student: %v", err)
	}
	id, err := result.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("update student: %v", err)
	}
	return id, nil
}

func deleteStudent(stdId int) (int64, error) {
	result, err := db.Exec("DELETE from student WHERE id=?", stdId)
	if err != nil {
		return 0, fmt.Errorf("delete student: %v", err)
	}
	id, err := result.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("delete student: %v", err)
	}
	return id, nil
}

 

Summary

Within this tutorial, we built a console application that uses Golang’s native database package and the Go-MySQL-Driver package to connect and perform CRUD operations on a SQL Server. Feel free to clone the below code example and use the application as a starting point for your own Golang application. database.

 

References

https://pkg.go.dev/database/sql

 

Tuan Nguyen

Tuan Nguyen

He is proficient in Golang, Python, Java, MongoDB, Selenium, Spring Boot, Kubernetes, Scrapy, API development, Docker, Data Scraping, PrimeFaces, Linux, Data Structures, and Data Mining. With expertise spanning these technologies, he develops robust solutions and implements efficient data processing and management strategies across various projects and platforms. You can connect with him on his LinkedIn profile.

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment