Run your first Go web server and read write value in MySQL

I wrote past few posts based on my notes from The Go Progamming Language by Donovan. In this article, we will try to make a go serverand expose rest APIs to read an write values from MySQL database.

We will assume that your mySQL server is running at 3306 port in your local computer.

net/http

net/http package of Go provides HTTP client and server implementations. It also has a built in web-server. We are going to make a homepage in this section. Let’s begin.

We will first make a project in the go path and make a main package inside it. We will make a go file called main.go inside the main package.

I am using GoLand as the editor and AuthenticationService in the name of my project. See the image above for the project structure.

Next we import net/http library.

go get -u github.com/gorilla/mux

We are next going to use gorilla/mux package to create routes with named parameters, GET/POST handler. This package will make it easier to use the parameters of APIs.

package main

import (
	"fmt"
	"github.com/gorilla/mux"
	"net/http"
)

func main(){
	router := mux.NewRouter();
	router.HandleFunc("/", welcome)

	http.ListenAndServe(":8090", router)
}

func welcome(writer http.ResponseWriter, request *http.Request) {
	fmt.Fprintf(writer, "Welcome! you were expected.")
}

In the main, we have initialised a router to define different endpoints. However, here we have just root, i.e. localhost:8090. When someone hits at localhost:8090, we have handler function called welcome. In this case, welcome returns a message called, “Welcome! you were expected”.

Now, let’s change this welcome to render a page to show options to signup or signin.

Create a html page called index.html. We will use bootstrap for css and js. It’s okay if you don’t know css, js or bootstrap. Just copy paste the below code in a file called index.html.

<!doctype html>
<html lang="en">
<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">

    <title>Hello,Go world!</title>
</head>
<body>
<div class="container">
    <br>
    <br>
    <h1>Hello, Go world!</h1>
    <br>
    <br>
    <form action="/signin">
        <div class="form-group">
            <label for="exampleInputEmail1">Email address</label>
            <input type="email" class="form-control" name="exampleInputEmail1" aria-describedby="emailHelp">
            <small id="emailHelp" class="form-text text-muted">We'll never share your email with anyone else.</small>
        </div>
        <div class="form-group">
            <label for="exampleInputPassword1">Password</label>
            <input type="password" class="form-control" name="exampleInputPassword1">
        </div>
        <button type="submit" class="btn btn-primary">Submit</button>
    </form>
</div>



<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704+h835Lr+6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous"></script>
</body>
</html>

Next, we need to tell our Go file to render this index.html.

func welcome(writer http.ResponseWriter, request *http.Request) {
	tmpl := template.Must(template.ParseFiles("index.html"))
	tmpl.Execute(writer, nil)
}

We have to change our welcome method to the one above. Here, we are just saying go to process index.html file and return it whenever user types localhost:8090 in browser.

Run again and you should see the following screen:

The whole html code translates to the above page.

Database

Install the mySQL driver by:

go get -u github.com/go-sql-driver/mysql

I have created a DB called information and a table called users. Here’s the schema.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` text NOT NULL,
  `password` text NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `unique_username` (`username`(5))
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

Next up, we are gonna make a handler called signin, which will be called when user presses the submit button in the html page above.

We will register a new end point in main function.

router.HandleFunc("/signin", signin)

And we will make a function called signin to handle the logic.

func signin(writer http.ResponseWriter, request *http.Request) {
	username := request.FormValue("exampleInputEmail1")
	password := request.FormValue("exampleInputPassword1")
	db, _ := sql.Open("mysql", "root:welcome@(127.0.0.1:3306)/information?parseTime=true")

	passwordFromDB := ""
	query := `SELECT password FROM users WHERE username = ?`
	err := db.QueryRow(query, username).Scan(&passwordFromDB)
	print(err)
	if password == passwordFromDB {
		fmt.Fprintf(writer, "Congratulations "+username+" You are successfully signed in.")
	} else {
		fmt.Fprintf(writer, "Oops! Username and password did not match.")
	}

}

Here, we have created a db connection to connect to our DB information. Then we queried it according to the given username. If the password matches, we are signed in else error message is thrown out.

Sign Up

So far, we have talked about sign in. But how do we make people register themselves.

We will add a link as shown below to provide an option to the user for sign up in the index.html.

<a class="btn btn-primary" href="/signup" role="button">Not a user, sign up now.</a>

We will now repeat the process, what we did in sign in. We will make a html page called register.html.

<!doctype html>
<html lang="en">
<head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">

    <title>Hello,Go world!</title>
</head>
<body>
<div class="container">
    <br>
    <br>
    <h1>Register, Go world!</h1>
    <br>
    <br>
    <form action="/createUser">
        <div class="form-group">
            <label for="exampleInputEmail1">Email address</label>
            <input type="email" class="form-control" name="exampleInputEmail1" aria-describedby="emailHelp">
        </div>
        <div class="form-group">
            <label for="exampleInputPassword1">Password</label>
            <input type="password" class="form-control" name="exampleInputPassword1">
        </div>
        <button type="submit" class="btn btn-primary">Register me</button>
    </form>
</div>



<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704+h835Lr+6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous"></script>
</body>
</html>

We will now create a handler for /signup in main function and a function to return this html page.

router.HandleFunc("/signup", signup) //in main func

And,

func signup(writer http.ResponseWriter, request *http.Request) {
	tmpl := template.Must(template.ParseFiles("register.html"))
	tmpl.Execute(writer, nil)
}

Now, the only part remaining is an handler to /createUser (which is form action in register.html)

func createUser(writer http.ResponseWriter, request *http.Request) {
	username := request.FormValue("exampleInputEmail1")
	password := request.FormValue("exampleInputPassword1")
	db, _ := sql.Open("mysql", "root:welcome@(127.0.0.1:3306)/information?parseTime=true")
	result, _ := db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, username, password, time.Now())
	if result == nil {
		log.Fatal("Error")
		fmt.Fprintf(writer, "Oops! try again later.")
	} else {
		fmt.Fprintf(writer, "Congratulations "+username+" You are successfully regsitered.")
	}

}

Finally, our screen looks like this:

And we are done.

We have created a simple sign up/ sign in service using mySQL and Go as language.

Github download link

Reference:

If you liked this article and would like one such blog to land in your inbox every week, consider subscribing to our newsletter: https://skillcaptain.substack.com

net/http

Database

Sign Up

Github download link

Share this:

Like this:

Related

Leave a ReplyCancel reply