Go – CRUD Operations with MySQL in Go Language

on May 3, 2019

Post Views: 4,917

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,

Creating a database and table in MySQL
Installing MySQL package in Golang
Getting connection for MySQL in Golang
Insert a row in MySQL using Golang
Select all rows in MySQL using Golang
Select a specific row in MySQL using Golang
Update a row in MySQL using Golang
Delete a row in MySQL using Golang
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));“

#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“

#3 Getting connection for MySQL in Golang

func connect() *sql.DB {
db, err := sql.Open("mysql", "root:root@/go_db") // username:password@/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:root@/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 🙂