This is a creation in Article, where the information may have evolved or changed.
In this tutorial, we is going to see a example program to learn how to do database CRUD operations using Golang and MYSQ L. CRUD is a acronym for Create, Read, Update, and Delete. CRUD operations is basic data manipulation for database.
In this example, we is going to create a interface as database front end to handle these operations. We have an employee table containing Employee information like ID, name and city. With the This table, we have the to perform CRUD using MySQL.
Step 1:prepare and Import MySQL driver into your project
Using Git Bash First install driver for Go's MySQL database package. Run below command and install MySQL driver ' s
Go get-u github.com/go-sql-driver/mysql
Now create Goblog Database
1. Open Phpmyadmin/sqlyog or what ever MySQL database management tool, that is using.
2. Create a new database "Goblog"
Step 2:creating the Employee Table
Execute the following SQL query to create a table named Employee inside your MySQL database. We'll use the This table for all the future operations.
DROP TABLE IF EXISTS ' employee '; CREATE TABLE ' employee ' ( ' id ' int (6) unsigned not NULL auto_increment, ' name ' varchar (+) ' NOT null ', ' City ' VA Rchar (+) not NULL, PRIMARY KEY (' id ')) engine=innodb auto_increment=1 DEFAULT charset=latin1;
Step 3:creating Struct, Handler and Handler Function
Let's create a file named main.go
and put the following code inside it.
We usually import database/sql and use SQL to execute database queries on the database.
function dbconn
opens connection with MySQL driver.
We'll create Employee
struct that have following Properties:id, Name and city.
Package Mainimport ("Database/sql" "Log" "Net/http" "Text/template" _ "Github.com/go-sql-driver/mysql") Typ E 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 St Ring 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 ("CIT Y ") 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 ("CIT Y ") 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)}
Step 4:creating Template Files
Now it's time to build the Template files of our CRUD application. Create form
folder at same location where we have created main.go
.
A) Let's create a file named Index.tmpl
inside the folder and put the form
following code inside it.
{{define ' Index '}} {{Template ' Header '}} {{Template "menu" }}
b) Now create another file named Header.tmpl
inside the same form
folder and put the following code inside it.
{{define "Header"}}<! DOCTYPE html>
c) Now create another file named Footer.tmpl
inside the same form
folder and put the following code inside it.
{{define ' Footer '}} </body>
D) Now create another file named Menu.tmpl
inside the same form
folder and put the following code inside it.
{{Define "menu"}}<a href= "/" >HOME</a> | <a href= "/new" >new</a>{{End}}
E) Next, we have to create Show.tmpl
the file for item details page, so again create the this file in form
folder.
{{define ' Show '}} {{Template ' Header '}} {{Template ' menu '} }
f) Now we create new Blade file for Create new item, it's call New.tmpl
file inside form
.
{{define ' New '}} {{Template ' Header '}} {{Template ' menu '}}
g) At last, we need to the Create Edit.tmpl
file for update item, so again create the this file in form
folder.
{{define ' Edit '}} {{Template ' Header '}} {{Template ' menu '}}
After a long journey finally we ' ve created all files of our CRUD application with Golang and MySQL.
Run the following command
Go Run main.go
Load the following URL
http://localhost:8080/