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.
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 🙂
References
- https://golang.org/doc/
- https://golang.org/pkg/
- https://golang.org/pkg/fmt/
- https://golang.org/pkg/fmt/#Println
- https://golang.org/pkg/database/sql/
- https://golang.org/pkg/database/sql/driver/.
- https://godoc.org/github.com/go-sql-driver/mysql
Hello, folks, I am a founder of idineshkrishnan.com. I love open source technologies, If you find my tutorials are useful, please consider making donations to these charities.
No Responses