This article explains how to connect GoLang to Mysql. Mysql is the most popular and widely used open source Database and as you know Go lang is the new ❤ . When implementing a Web application, Mysql is one of the necessary bindings to have. In order Implement GoLang MySql connection, you would require GoLang Mysql driver. This driver will provide the Mysql access to your application and you can perform MySql queries in GoLang.
To demonstrate GoLang MySql connection, am taking the example of Ajax Auto Search Box. Basically, we’ll have a textbox and whatever you will write into it, you will get the response from the server based on the text written inside the textbox.
Since results are already stored in the MySQL database table, So we will be using some kind of Mysql query to fetch it, we’ll go through that down the road. Here I will be using the plain ES6 Javascript on the client side, You can use any front-end framework of your choice.
Also read, how to make HTTP(CURL) request in Go lang.
1. Understanding the project structure
1. As I said earlier, here we will create an application, which will imitate the functionality of Ajax Auto Search, in other words, autocomplete functionality. Here we will give a very little bit of styling to our web application just to make it look presentable. Since we are using plain Javascript and there is not much on the client side to understand, hence we will focus on the Backend part only.
2. Inside the client folder, we will write down the Javascript scripts and inside view folder, we will write down the MARKUP.
2. Creating a GoLang Server
1.Create a server.go in the root of the project, which will be our entry point for the project. Here we will make a connection with the MySql database and we will define our application routes.
=>Inside the main()
function, First we are printing some information.
=>Then using connectDatabse()
function, we will make a MySql connection.
=>In the next line, we will create route
variable, which will hold Route instance.
=>Then AddApproutes()
function register application routes.
=>And at the end, using http.ListenAndServe()
we will start our GO server.
server.go:
package main import ( "fmt" "log" "net/http" "github.com/gorilla/mux" ) func main() { fmt.Println("Server will start at http://localhost:8000/") connectDatabse() route := mux.NewRouter() AddApproutes(route) log.Fatal(http.ListenAndServe(":8000", route)) }
3. Connecting GoLang to MySql
1.Create a db.go in the root of the project, Here we will connect our GoLang app with MySql database.
=> In the below code, first we have included the database/sql
package and then go-mysql-driver
.
=> Then we have created variable db
, which will hold the MySql connection. This db
variable will be avaialble inside all the files under the main
package.
=> Inside theconnectDatabse()
function we will create MySql connection as shown below,
db.go:
package main import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" ) var db *sql.DB var err error func connectDatabse() { fmt.Println("Database connected.") db,err = sql.Open("mysql","root:root@/test") }
4. Adding GoLang routes in the application
1.Create a routes.go in the root of the project, Here we will register application routes. Here we will usegorilla/mux
package to register routes.
=>The function setStaticFolder()
will tell the GoLang route that /public
folder will contain all the static files.
=> Then AddApproutes()
function will register all the routes in the application. Here we have only one route to add which will be used by the FrontEnd javascript.
routes.go:
package main import ( "fmt" "net/http" "github.com/gorilla/mux" ) func setStaticFolder(route *mux.Router) { fs := http.FileServer(http.Dir("./public/")) route.PathPrefix("/public/").Handler(http.StripPrefix("/public/", fs)) } // AddApproutes will add the routes for the application func AddApproutes(route *mux.Router) { setStaticFolder(route) route.HandleFunc("/", renderHome) route.HandleFunc("/users/{name}", getUsers).Methods("GET") fmt.Println("Routes are Loded.") }
5. Using GoLang MySql connection to run MySql queries
1.Create a routes-handlers.go in the root of the project, Here we will create functions which will be called by the routes that we just added in the previous section.
2.In the below code we will write two functions listed below,
renderHome()
will render the html page.getUsers()
will return the list of users. we have written the MySqlLIKE
query to fetch the result from Table.returnErrorResponse()
function will return the error response.
=> In the below, using http.ServeFile
we are rendering the home page underrenderHome()
function.
=> getUsers()
expects two parameters request and response respectively.
=> Then inside getUsers()
function we have created two variables, user and users.
- The
user
variable will hold the instance of the mysql user data inside for loop. - The
users
variable will hold the array of users.
=> Then we have used MySql Like
query to fetch the record from the database.
=> Later we have applied for loop
on the mysql records in order to fetch the result.
=> And rest of code is just for error handling.
routes-handlers.go:
package main import ( "encoding/json" "fmt" "net/http" "github.com/gorilla/mux" ) // User is Interface for user details. type User struct { ID int Name string Lname string Country string } func renderHome(response http.ResponseWriter, request *http.Request) { http.ServeFile(response, request, "views/index.html") } func getUsers(response http.ResponseWriter, request *http.Request) { var ( user User users []User ) username := mux.Vars(request)["name"] rows, err := db.Query("SELECT * FROM users where name like '%" + username + "%'") if err != nil { fmt.Println(err) returnErrorResponse(response, request) } for rows.Next() { rows.Scan(&user.ID, &user.Name, &user.Lname, &user.Country) users = append(users, user) } defer rows.Close() jsonResponse, jsonError := json.Marshal(users) if jsonError != nil { fmt.Println(jsonError) returnErrorResponse(response, request) } if jsonResponse == nil { returnErrorResponse(response, request) } else { response.Header().Set("Content-Type", "application/json") response.Write(jsonResponse) } } func returnErrorResponse(response http.ResponseWriter, request *http.Request) { jsonResponse, err := json.Marshal("It's not you it's me.") if err != nil { panic(err) } response.Header().Set("Content-Type", "application/json") response.WriteHeader(http.StatusInternalServerError) response.Write(jsonResponse) }
6. Conclusion
So this was a small demonstration of GoLang and Mysql database connectivity. In this article, frontend part was not that interesting, so I did not add the code scripting and Markup here. You will find the whole project when you will download the source code.
Also, comment down below if you have any questions regarding this article. I’ll be happy to help, Till then Happy going 😀.