Go – CRUD Operations with MySQL in Go Language

Hello everyone, In this tutorial, we will learn how to perform CRUD operations with MySQL in Go language. The MySQL is the database, which is used to store the data or information in the file-system for later use, the CRUD (create, read, update and delete) operations are the most common and frequent activity for many software applications and this tutorial has multiple example programs to perform the CRUD operations with MySQL database using Go programming language.

How to perform a CRUD Operations with MySQL in Go Language.
Go – CRUD Operations with MySQL in Go Language

We going to cover the following scenarios in this tutorial,

  1. Creating a database and table in MySQL
  2. Installing MySQL package in Golang
  3. Getting connection for MySQL in Golang
  4. Insert a row in MySQL using Golang
  5. Select all rows in MySQL using Golang
  6. Select a specific row in MySQL using Golang
  7. Update a row in MySQL using Golang
  8. Delete a row in MySQL using Golang
  9. Full Example

#1 Creating a database and table in MySQL

In order to store data or information in the MySQL database, first of all, we need to create the database and table.

Create a database – “create database go_db;

Select a database – “use go_db;

Create a table in database – “create table customer(id int, name varchar(100));

How to perform a CRUD Operations with MySQL in Go Language.
Creating a database and table in MySQL

#2 Installing MySQL package in Golang

The Go-MySQL is a driver to perform SQL operations on MySQL database, we need to install this driver, in order to perform all CRUD operations with MySQL in Go language.

for more details, please visit here – https://godoc.org/github.com/go-sql-driver/mysql

To install the package use this command in the terminal – “go get -u github.com/go-sql-driver/mysql

Installing MySQL package in Golang

#3 Getting connection for MySQL in Golang

func connect() *sql.DB {
	db, err := sql.Open("mysql", "root:[email protected]/go_db") // username:[email protected]/dbname
	if err != nil {
		fmt.Println("Error! Getting connection...")
	}
	return db;
}

#4 Insert a row in MySQL using Golang

func insert(customer Customer) {
	db := connect()
	insert, err := db.Query("INSERT INTO customer VALUES (?, ?)", customer.Id, customer.Name)
	if err != nil {
		fmt.Println("Error! Inserting records...")
	}
	defer insert.Close()
	defer db.Close()
}

#5 Select all rows in MySQL using Golang

func selectAll() *sql.Rows {
	db := connect()
	results, err := db.Query("SELECT * FROM customer")
	if err != nil {
		fmt.Println("Error! Getting records...")
	}
	defer db.Close()
	return results
}

#6 Select a specific row in MySQL using Golang

func selectById(id int) *sql.Row {
	db := connect()
	result := db.QueryRow("SELECT * FROM customer WHERE id=?", id)
	defer db.Close()
	return result
}

#7 Update a row in MySQL using Golang

func updateById(customer Customer) {
	db := connect()
	db.QueryRow("UPDATE customer SET name=? WHERE id=?", customer.Name, customer.Id)
}

#8 Delete a row in MySQL using Golang

func delete(id int) {
	db := connect()
	db.QueryRow("DELETE FROM customer WHERE id=?", id)
}

#9 Full Example (CRUD Operations with MySQL in Golang)

package main

import (
	"database/sql"
	"fmt"
)
import _ "github.com/go-sql-driver/mysql"

// Customer type
type Customer struct {
	Id int `json:"id"`
	Name string `json:"name"`
}

func main() {

	// inserting a rows
	insert(Customer{101, "Dinesh"})
	insert(Customer{102, "Dinesh"})

	// updating the customer by id
	updateById(Customer{101, "Dinesh Krishnan"})

	// select all customers
	results := selectAll()

	// iterating a results
	for results.Next() {
		var customer Customer
		results.Scan(&customer.Id, &customer.Name)
		fmt.Println(customer.Id, customer.Name)
	}

	// select customer by id
	result := selectById(101)
	var customer Customer
	result.Scan(&customer.Id, &customer.Name)
	fmt.Println(customer.Id, customer.Name)

	// delete a customer by id
	delete(102)
}

// function to get a database connection
func connect() *sql.DB {
	db, err := sql.Open("mysql", "root:[email protected]/go_db")
	if err != nil {
		fmt.Println("Error! Getting connection...")
	}
	return db;
}

// function to insert a row in customer table
func insert(customer Customer) {
	db := connect()
	insert, err := db.Query("INSERT INTO customer VALUES (?, ?)", customer.Id, customer.Name)
	if err != nil {
		fmt.Println("Error! Inserting records...")
	}
	defer insert.Close()
	defer db.Close()
}

// function to select all records from customer table
func selectAll() *sql.Rows {
	db := connect()
	results, err := db.Query("SELECT * FROM customer")
	if err != nil {
		fmt.Println("Error! Getting records...")
	}
	defer db.Close()
	return results
}

// function to select a customer record from table by customer id 
func selectById(id int) *sql.Row {
	db := connect()
	result := db.QueryRow("SELECT * FROM customer WHERE id=?", id)
	defer db.Close()
	return result
}

// function to update a customer record by customer id
func updateById(customer Customer) {
	db := connect()
	db.QueryRow("UPDATE customer SET name=? WHERE id=?", customer.Name, customer.Id)
}

// function to delete a customer by customer id
func delete(id int) {
	db := connect()
	db.QueryRow("DELETE FROM customer WHERE id=?", id)
}

Output

What happened?
If you look at the main() function, we have performed the following steps.

Step 1: Inserted 2 rows
Step 2: Updated 1 row by Id
Step 3: Selected all rows
Step 4: Selected a specific row
Step 5: Deleted a row by Id.

Now, you can refer the screen-shot, I hope you will understand 🙂

How to perform a CRUD Operations with MySQL in Go Language.

References

  1. https://golang.org/doc/
  2. https://golang.org/pkg/
  3. https://golang.org/pkg/fmt/
  4. https://golang.org/pkg/fmt/#Println
  5. https://golang.org/pkg/database/sql/
  6. https://golang.org/pkg/database/sql/driver/.
  7. https://godoc.org/github.com/go-sql-driver/mysql