In this example i am going to present an example program to learn and create a crud(Create, Read, Update and Delete) using go programming and MySQL.
It is also known as Rest API
Step 1: Import and prepare mysql driver into your project
Installing MySQL database driver for go lang for this run the following command:
$ go get -u github.com/go-sql-driver/mysql
After installing create the database for your crud app for example “gocrud”
Step 2: Creating a table for storing details of employees
for this you can use the following code:
CREATE TABLE `myemployee` (
`id` int(6) unsigned NOT NULL AUTO_INCREMENT,
`fullname` varchar(25) NOT NULL,
`address` varchar(25) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
Step 3: Create a Struct, Handler and Handler Function
for this create a file for example main.go and use the downloading codes inside this file.
package main
import (
"database/sql"
"log"
"net/http"
"text/template"
_ "github.com/go-sql-driver/mysql"
)
type Myemployee struct {
Id int
Full_Name string
Address 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 := Myemployee{}
res := []Myemployee{}
for selDB.Next() {
var id int
var fullname, address string
err = selDB.Scan(&id, &name, &city)
if err != nil {
panic(err.Error())
}
emp.Id = id
emp.Full_Name = fullname
emp.Address = address
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 Myemployee WHERE id=?", nId)
if err != nil {
panic(err.Error())
}
emp := Myemployee{}
for selDB.Next() {
var id int
var fullname, address string
err = selDB.Scan(&id, &fullname, &address)
if err != nil {
panic(err.Error())
}
emp.Id = id
emp.Full_Name = fullname
emp.Address = address
}
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 Myemployee WHERE id=?", nId)
if err != nil {
panic(err.Error())
}
emp := Myemployee{}
for selDB.Next() {
var id int
var fullname, address string
err = selDB.Scan(&id, &fullname, &address)
if err != nil {
panic(err.Error())
}
emp.Id = id
emp.Full_Name = fullname
emp.Address = address
}
tmpl.ExecuteTemplate(w, "Edit", emp)
defer db.Close()
}
func Insert(w http.ResponseWriter, r *http.Request) {
db := dbConn()
if r.Method == "POST" {
fullname := r.FormValue("fullname")
address:= r.FormValue("address")
insForm, err := db.Prepare("INSERT INTO Myemployee(fullname, address) VALUES(?,?)")
if err != nil {
panic(err.Error())
}
insForm.Exec(name, city)
log.Println("INSERT: FullName: " + fullname + " | Address: " + address)
}
defer db.Close()
http.Redirect(w, r, "/", 301)
}
func Update(w http.ResponseWriter, r *http.Request) {
db := dbConn()
if r.Method == "POST" {
fullname := r.FormValue("fullname")
address:= r.FormValue("address")
id := r.FormValue("uid")
insForm, err := db.Prepare("UPDATE Myemployee SET firstname=?, address=? WHERE id=?")
if err != nil {
panic(err.Error())
}
insForm.Exec(fullname, address, id)
log.Println("UPDATE: FullName: " + fullname + " | Address: " + address)
}
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 Myemployee 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)
}
Step 4: Now create template files
For further processing you will need to create template files for your CRUD application.
for this lets create a template file named Index.tmpl and put the following code:
{{ define "Index" }}
{{ template "Header" }}
{{ template "Menu" }}
<h2> Registered </h2>
<table border="1">
<thead>
<tr>
<td>ID</td>
<td>Full Name</td>
<td>Address</td>
<td>View</td>
<td>Edit</td>
<td>Delete</td>
</tr>
</thead>
<tbody>
{{ range . }}
<tr>
<td>{{ .Id }}</td>
<td> {{ .Full_Name }} </td>
<td>{{ .Address }} </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 }}
Create another template file in same folder for this i have created a template file named Header.tmpl
{{ define "Header" }}
<!DOCTYPE html>
<html lang="en-US">
<head>
<title>CRUD app using Golang </title>
<meta charset="UTF-8" />
</head>
<body>
<h1>Golang Mysql Curd Example</h1>
{{ end }}
Again you have to create another template file in same folder for this i have created a template file named Footer.tmpl
{{ define "Footer" }}
</body>
</html>
{{ end }}
Again you have to create another template file in same folder for this i have created a template file named Menu.tmpl
{{ define "Menu" }}
<a href="/">HOME</a> |
<a href="/new">NEW</a>
{{ end }}
Again you have to create another template file in same folder for this i have created a template file named Show.tmpl
{{ define "Show" }}
{{ template "Header" }}
{{ template "Menu" }}
<h2> Register {{ .Id }} </h2>
<p>Name: {{ .Full_Name }}</p>
<p>City: {{ .Address }}</p><br /> <a href="/edit?id={{ .Id }}">Edit</a></p>
{{ template "Footer" }}
{{ end }}
Again you have to create another template file in same folder for this i have created a template file named New.tmpl
{{ define "New" }}
{{ template "Header" }}
{{ template "Menu" }}
<h2>New Name and Address</h2>
<form method="POST" action="insert">
<label> Full Name </label><input type="text" name="fullname" /><br />
<label> Address</label><input type="text" name="Address" /><br />
<input type="submit" value="Submit" />
</form>
{{ template "Footer" }}
{{ end }}
At last create another template file for updating item for this we can create a file named Edit.tmpl
{{ 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> Full Name </label><input type="text" name="fullname" value="{{ .Full_Name }}" /><br />
<label> Address </label><input type="text" name="Address" value="{{ .Address }}" /><br />
<input type="submit" value="Submit" />
</form><br />
{{ template "Footer" }}
{{ end }}
Congratulation! we have created our CRUD application using Golang and MySQL
Now you are good to go..
Run following command:
$ go run main.go
You can view it on following address
http://localhost:8080
Enjoy!!
Happy Coding !
Top comments (0)