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 routevariable, 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 MySqlLIKEquery 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
uservariable will hold the instance of the mysql user data inside for loop. - The
usersvariable 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 😀.
