In this tutorial, we will see a sample program to learn how to use Golang and MySQL for database CRUD operation. CRUD is an acronym for "create", "read", "update" and "delete". CRUD operation is the basic data operation of database.
In this example, we will create an interface as the database front end to handle these operations. We have an Employee table, which contains Employee information such as id, name and city. Using this table, we must use MySQL to execute CRUD.
Using Git Bash, first install the driver for Go's MySQL database package. Run the following command and install the MySQL driver
go get -u github.com/go-sql-driver/mysql
1. Open MySQL database management tool.
2. Create a new database "goblog"
Execute the following SQL query to create a table named Employee in the MySQL database. We will use this table in all future operations.
DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` ( `id` int(6) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `city` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
Let's create a file called main Go and put the following code in it.
We usually import database / sql and use sql to execute database queries on the database.
The dbConn function opens a connection to the MySQL driver.
We will create a structure of Employee with the following attributes: Id, name and city.
package main
import (
"database/sql"
"log"
"net/http"
"text/template"
_ "github.com/go-sql-driver/mysql"
)
type Employee struct {
Id int
Name string
City string
}
func dbConn() (db *sql.DB) {
dbDriver := "mysql"
dbUser := "root"
dbPass := "root"
dbName := "goblog"
db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
if err != nil {
panic(err.Error())
}
return db
}
var tmpl = template.Must(template.ParseGlob("form/*"))
func Index(w http.ResponseWriter, r *http.Request) {
db := dbConn()
selDB, err := db.Query("SELECT * FROM Employee ORDER BY id DESC")
if err != nil {
panic(err.Error())
}
emp := Employee{}
res := []Employee{}
for selDB.Next() {
var id int
var name, city string
err = selDB.Scan(&id, &name, &city)
if err != nil {
panic(err.Error())
}
emp.Id = id
emp.Name = name
emp.City = city
res = append(res, emp)
}
tmpl.ExecuteTemplate(w, "Index", res)
defer db.Close()
}
func Show(w http.ResponseWriter, r *http.Request) {
db := dbConn()
nId := r.URL.Query().Get("id")
selDB, err := db.Query("SELECT * FROM Employee WHERE id=?", nId)
if err != nil {
panic(err.Error())
}
emp := Employee{}
for selDB.Next() {
var id int
var name, city string
err = selDB.Scan(&id, &name, &city)
if err != nil {
panic(err.Error())
}
emp.Id = id
emp.Name = name
emp.City = city
}
tmpl.ExecuteTemplate(w, "Show", emp)
defer db.Close()
}
func New(w http.ResponseWriter, r *http.Request) {
tmpl.ExecuteTemplate(w, "New", nil)
}
func Edit(w http.ResponseWriter, r *http.Request) {
db := dbConn()
nId := r.URL.Query().Get("id")
selDB, err := db.Query("SELECT * FROM Employee WHERE id=?", nId)
if err != nil {
panic(err.Error())
}
emp := Employee{}
for selDB.Next() {
var id int
var name, city string
err = selDB.Scan(&id, &name, &city)
if err != nil {
panic(err.Error())
}
emp.Id = id
emp.Name = name
emp.City = city
}
tmpl.ExecuteTemplate(w, "Edit", emp)
defer db.Close()
}
func Insert(w http.ResponseWriter, r *http.Request) {
db := dbConn()
if r.Method == "POST" {
name := r.FormValue("name")
city := r.FormValue("city")
insForm, err := db.Prepare("INSERT INTO Employee(name, city) VALUES(?,?)")
if err != nil {
panic(err.Error())
}
insForm.Exec(name, city)
log.Println("INSERT: Name: " + name + " | City: " + city)
}
defer db.Close()
http.Redirect(w, r, "/", 301)
}
func Update(w http.ResponseWriter, r *http.Request) {
db := dbConn()
if r.Method == "POST" {
name := r.FormValue("name")
city := r.FormValue("city")
id := r.FormValue("uid")
insForm, err := db.Prepare("UPDATE Employee SET name=?, city=? WHERE id=?")
if err != nil {
panic(err.Error())
}
insForm.Exec(name, city, id)
log.Println("UPDATE: Name: " + name + " | City: " + city)
}
defer db.Close()
http.Redirect(w, r, "/", 301)
}
func Delete(w http.ResponseWriter, r *http.Request) {
db := dbConn()
emp := r.URL.Query().Get("id")
delForm, err := db.Prepare("DELETE FROM Employee WHERE id=?")
if err != nil {
panic(err.Error())
}
delForm.Exec(emp)
log.Println("DELETE")
defer db.Close()
http.Redirect(w, r, "/", 301)
}
func main() {
log.Println("Server started on: http://localhost:8080")
http.HandleFunc("/", Index)
http.HandleFunc("/show", Show)
http.HandleFunc("/new", New)
http.HandleFunc("/edit", Edit)
http.HandleFunc("/insert", Insert)
http.HandleFunc("/update", Update)
http.HandleFunc("/delete", Delete)
http.ListenAndServe(":8080", nil)
}Now it's time to build the template file for our CRUD application. form creates the folder main in the location we created go.
a) Let's index Tmpl creates a file named in the form folder and puts the following code into it.
{{ define "Index" }}
{{ template "Header" }}
{{ template "Menu" }}
<h2> Registered </h2>
<table border="1">
<thead>
<tr>
<td>ID</td>
<td>Name</td>
<td>City</td>
<td>View</td>
<td>Edit</td>
<td>Delete</td>
</tr>
</thead>
<tbody>
{{ range . }}
<tr>
<td>{{ .Id }}</td>
<td> {{ .Name }} </td>
<td>{{ .City }} </td>
<td><a href="/show?id={{ .Id }}">View</a></td>
<td><a href="/edit?id={{ .Id }}">Edit</a></td>
<td><a href="/delete?id={{ .Id }}">Delete</a><td>
</tr>
{{ end }}
</tbody>
</table>
{{ template "Footer" }}
{{ end }}
b) Now, header Tmpl creates another file in the same form folder and puts the following code into it.
{{ define "Header" }}
<!DOCTYPE html>
<html lang="en-US">
<head>
<title>Golang Mysql Curd Example</title>
<meta charset="UTF-8" />
</head>
<body>
<h1>Golang Mysql Curd Example</h1>
{{ end }}c) Now, footer Tmpl creates another file named in the same form folder and puts the following code into it.
{{ define "Footer" }}
</body>
</html>
{{ end }}d) Now, menu Tmpl creates another file named in the same form folder and puts the following code into it.
{{ define "Menu" }}
<a href="/">HOME</a> |
<a href="/new">NEW</a>
{{ end }}e) Next, we must show Tmpl creates a file for the project details page, so create the file again in the form folder.
{{ define "Show" }}
{{ template "Header" }}
{{ template "Menu" }}
<h2> Register {{ .Id }} </h2>
<p>Name: {{ .Name }}</p>
<p>City: {{ .City }}</p><br /> <a href="/edit?id={{ .Id }}">Edit</a></p>
{{ template "Footer" }}
{{ end }}f) Now let's create a new blade file for creating a new item. It's new The calling file form inside tmpl.
{{ define "New" }}
{{ template "Header" }}
{{ template "Menu" }}
<h2>New Name and City</h2>
<form method="POST" action="insert">
<label> Name </label><input type="text" name="name" /><br />
<label> City </label><input type="text" name="city" /><br />
<input type="submit" value="Save user" />
</form>
{{ template "Footer" }}
{{ end }}
g) Finally, we need edit Tmpl creates a file for the update project, so create this file again in the form folder.
{{ define "Edit" }}
{{ template "Header" }}
{{ template "Menu" }}
<h2>Edit Name and City</h2>
<form method="POST" action="update">
<input type="hidden" name="uid" value="{{ .Id }}" />
<label> Name </label><input type="text" name="name" value="{{ .Name }}" /><br />
<label> City </label><input type="text" name="city" value="{{ .City }}" /><br />
<input type="submit" value="Save user" />
</form><br />
{{ template "Footer" }}
{{ end }}
go run main.go
http://localhost:8080/