Use MySQL database in Golang

In this blog, I am going to make a web application that will store the data of the signup users in the MySQL database.

MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL).

MySQL is one of the most recognizable technologies in the modern big data ecosystem. Often called the most popular database and currently enjoying widespread, effective use regardless of industry, it’s clear that anyone involved with enterprise data or general IT should at least aim for a basic familiarity with MySQL.

With MySQL, even those new to relational systems can immediately build fast, powerful, and secure data storage systems. MySQL’s programmatic syntax and interfaces are also perfect gateways into the wide world of other popular query languages and structured data stores.

This data is organized according to the relational model. In this model, tables consist of rows and columns, and relationships between data elements all follow a strict logical structure. An RDBMS is simply the set of software tools used to actually implement, manage, and query such a database.

Often beginners in Golang don't know how to store data in MySQL because there are not many resources online. This blog will definitely help you understand the concept of Golang with MySQL.

Prerequisites

Install Golang.
Install MySQL.

Set up the root password in Linux and Windows

sudo mysql_secure_installation will help you set up the root password through Command Prompt in Linux.
On Windows, there will be a GUI in which you will have to set up the root password.

Implementation

The first thing you need to do is to open the terminal and type mysql -u root -p. It will open MySQL for you.
Enter the password that you have set before.
After opening the database, write show databases;. It will show the list of all the databases that are present by default.
You can create your own database by typing create database <database-name>;. You can give any database name you want.
If you type show databases;, it will show the newly created databases also.
Now move inside the database that you just created by typing use <database-name>.
To check whether the tables are present in the database or not. Type `show tables. It will show you the list of tables. If they're not present then you have to create one.

Making a main template with some pages

main.go

package main

import (
    "fmt"
    "html/template"
    "log"
    "net/http"
)

func MakeTemplate(path string) template.Template {
  return *template.Must(template.ParseFiles(path))
}

var homeTmpl = MakeTemplate("views/templates/home.html")
var signupTmpl = MakeTemplate("views/templates/signup.html")
var loginTmpl = MakeTemplate("views/templates/login.html")

func HomePage(w http.ResponseWriter) {
  if err := homeTmpl.Execute(w, nil); err != nil {
      log.Fatal(err)
  }
}

func SignupPage(w http.ResponseWriter) {
  if err := signupTmpl.Execute(w, nil); err != nil {
      log.Fatal(err)
  }
}

func LoginPage(w http.ResponseWriter) {
  if err := loginTmpl.Execute(w, nil); err != nil {
        log.Fatal(err)
  }
}

func main() {
  http.HandleFunc("/", HomePage)
  http.HandleFunc("/signup", SignupPage)
  http.HandleFunc("/login", LoginPage)
  fmt.Println("Starting the server at port: 8080")
  if err := http.ListenAndServe(":8080", nil); err != nil {
      log.Fatal(err)
  }
}

You can better understand this code if you have read my previous blog in which I have shown you how you can create a web app in golang. Here you can read it.
In this code, there are three functions HomePage(), SignupPage() and LoginPage(). These three functions load and run the home, signup, and login templates, and they are executed in the main() function that will run the code in the browser with a provided port number 8080.
In this code, there is another function called MakeTemplate() that will return the common code(template.Must(template.ParseFiles(path))) that is used to take all the templates paths.

You can make an HTML home, and login page for yourself but in this blog but my focus is on the signup page.

views/templates/signup.html

<!DOCTYPE html>
<html>
<body>
  <form action="/signup" style="border:1px solid #ccc">
    <div class="container">
      <h1 style="text-align:center">Sign Up</h1>
      <p>Please fill in this form to create an account.</p>
      <hr>

      <label for="fname"><b>First Name</b></label>
      <input type="text" placeholder="Enter First Name" name="fname" required>

      <label for="lname"><b>Last Name</b></label>
      <input type="text" placeholder="Enter Last Name" name="lname" required>

      <label for="email"><b>Email</b></label>
      <input type="text" placeholder="Enter Email" name="email" required>

      <label for="password"><b>Password</b></label>
      <input type="password" placeholder="Enter Password" name="password" required>

      <label for="confirm-pasword"><b>Confirm Password</b></label>
      <input type="password" placeholder="Confirm Password" name="confirm-password" required>

      <div>
        <button type="submit" class="signupbtn">Sign Up</button>
      </div>

    </div>
  </form>
</body>
</html>

This signup form contains the first name, last name, email address, password, and the confirm password. This data will be stored inside the database.

main.go

type SignupForm struct {
    Fname           string
    Lname           string
    Email           string
    Password        string
    ConfirmPassword string
};

func SignupPage(w http.ResponseWriter, r *http.Request) {
    if r.Method == "GET" {
        if err := signupTmpl.Execute(w, nil); err != nil {
            log.Fatal(err)
        }
    } else if r.Method == "POST" {
        values := SignupForm{
            Fname:           r.FormValue("fname"),
            Lname:           r.FormValue("lname"),
            Email:           r.FormValue("email"),
            Password:        r.FormValue("password"),
            ConfirmPassword: r.FormValue("confirm-password"),
        }
    }
}

There is a structure added in the main.go file that contains some variables. These variables will be used to save the form values that the user will enter.
In the SignupPage() function, I have added two http methods(GET and POST). If the method is GET, then open the signup template. If the method is POST, then focus on taking the form values and later on in this blog these form values will take the data and you will be redirected to the login page to login into your account.
The SignupForm structure is used inside the SignupPage() function. It will take the form values and perform functionalities to it.

Use the database

database/db.SQL

CREATE TABLE IF NOT EXISTS SignupForm {
    id INT PRIMARY KEY AUTO_INCREMENT
    fname VARCHAR(255) NOT NULL,
    lname VARCHAR(255) NOT NULL,
    emails VARCHAR(255) NOT NULL UNIQUE,
    passwords VARCHAR(255) NOT NULL
}

Create a table in MySQL by the name of SignupForm. It will contain the id, fname, lname, emails, and password.
Except for the id, all the elements have the character data type. One of them is emails that is unique.

database/mysql.go

package database

import (
    "database/sql"
    "fmt"
    "io/ioutil"
    "log"
    "strings"

   _ "github.com/go-sql-driver/mysql"
)

func Connection() (db *sql.DB) {
    db_user := "root"
    db_password := "anypassword"
    db_address := "127.0.0.1"
    db_db := "database-name"
    s := fmt.Sprintf("%s:%s@tcp(%s:3306)/%s", db_user, db_password, db_address, db_db)
    db, err := sql.Open("mysql", s)
    if err != nil {
        log.Fatal(err)
    }
    return db
}

First, we have to establish the connection with the database.
Insert the db_user, db_password, db_address, and the database name. These four things will be allocated to the s named variable after building a connection.
After building a connection, use the sql.Open to register itself with database/sql, which is conventionally the same as the package name to avoid confusion. For example, it’s mysql for github.com/go-sql-driver/mysql.
s in the sql.Open means that we’re connecting to the db_db database inside a local MySQL server instance.

func CreateTable(file string, number int) (db *sql.DB) {
    db = Connection()
    query, err := ioutil.ReadFile("database/" + file)
    if err != nil {
        log.Fatal(err)
    }
    requests := strings.Split(string(query), ";")[number]

    stmt, err := db.Prepare(requests)
    if err != nil {
        log.Fatal(err)
    }

    _, err = stmt.Exec()
    if err != nil {
        log.Fatal(err)
    }
    return db
}

In the first part of the CreateTable() function, the ioutil.ReadFile will read the db.SQL file that contains the table creation query.
In the second part, the queries will be splitted with the semi-colon sign ; in between. It means that if there are multiple queries then those queries will be splitted with the help of the ;.
The third part is the Prepare statement. It prepares a SQL statement and assigns it a name, stmt_name, by which to refer to the statement later. It binds the values to the parameters, and the database executes the statement.
After preparing it in the third part, it will execute that statement.

func InsertSignup(value [4]string) {
    db := CreateTable("db.SQL", 0)
    q := "INSERT INTO SignupForm(fname, lname, emails, passwords) VALUES(?, ?, ?, ?)"
    insert, err := db.Prepare(q)
    if err != nil {
        log.Fatal(err)
    }
    defer insert.Close()

    if len(value[2]) != 0 && len(value[3]) != 0 {
        _, err = insert.Exec(value[0], value[1], value[2], value[3])
        if err != nil {
            log.Fatal(err)
        }
    }
}

In the InsertSignup() function, the CreateTable() function is called that will create the table.
After that, there is a query written that will insert the fname, lname, emails, and passwords if a user tries to signup.
This query will be prepared to further execute and insert the values in the database.
After the preparation, it will check the email and password whether they are empty or not. If they are not empty then they execute the process and insert the values into the database.

Use the database function in the SignupPage() function

main.go

func SignupPage(w http.ResponseWriter, r *http.Request) {
    ...
        note := [4]string{values.Fname, values.Lname, values.Email, values.Password, values.ConfirmPassword}
        database.InsertSignup(note)
        if err := loginTmpl.Execute(w, nil); err != nil {
            log.Fatal(err)
        }
    }  // else-if statement curly braces of POST method.
}

After taking the form values, it's time to insert them into the database by using the above function in the SigupnPage() function.
There are four string values that are used in an array and are equal to the note variable.
This note variable will be used as an argument in the InsertSignup() function to insert the data into the database.
After signup and inserting the data, the login page will open as we have discussed above.

I hope I make it clear to you how you can store the data in MySQL using Golang. I have done my best to make you understand the process. If you still have doubts then you can ask me in the comment section below or you can follow me on Twitter.

Thank you!

Use MySQL database in Golang