Go Web Application MySQL

Go MySQL

To connect Database is one of important part for Web Application.

Get user data, item data, etc… There are a lot of cases to get and create data base data.

In the past entry, explained how to connect MySQL

Steps

To support MySQL Connect, we have multiple prepartions.

• Prepare MySQL database
• Create Table
• Get go driver
• Prepare connection codes
• Prepare handler function for end point
• Call connection from main.go

MySQL prepartion

Create MySQL Database under this condition

User	root
Password	abc4po@g
DB Name	gosample
Port	3306

And, Insert following table and data

CREATE TABLE `gosample`.`posts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `content` VARCHAR(255) NULL,
  `author` VARCHAR(255) NULL,
  PRIMARY KEY (`id`));

INSERT INTO posts (content, author) VALUES ('ABDCE', 'Mumu-man');
INSERT INTO posts (content, author) VALUES ('teketeke', 'Takeyan');

MySQL Driver

Go prepares several RDBMS driver.

Please check this for SQL drivers

To use MySQL driver, you need to get using

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

Connection codes

Let’s prepare connection codes.

mysqlweb.go

package web

import (
	"database/sql"
	"fmt"
	"log"
	"time"
)

var DbConn *sql.DB

type connectionstr struct {
	User     string
	Password string
	Host     string
	Port     int
	DBName   string
}

func SetupDatabase() {
	var err error

	connection := &connectionstr{"root", "abc4po@g", "localhost", 3306, "gosample"}

	DbConn, err = sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%d)/%s",
		connection.User,
		connection.Password,
		connection.Host,
		connection.Port,
		connection.DBName))

	if err != nil {
		log.Fatal(err)
	}
	DbConn.SetMaxOpenConns(4)                   // Sets the maximum number of open connections to the database
	DbConn.SetMaxIdleConns(4)                   // Sets the maximum number of connections in the idle connection pool
	DbConn.SetConnMaxLifetime(60 * time.Second) // Sets the maximum amount of time a connection may be used

Now, DbConn is connection to use MySQL data.

Last 4 lines are setting of connection and connection pooling.

main.go

Let’s use above connection from main function.

package main

import (
	"fmt"
	"net/http"
	"github.com/DJ110/gorefs/web"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
    // MySQL
    web.SetupDatabase()
}

It’s just call above function from main(). And need to import driver.

Next is handler for SQL.

sqlweb.go

package web

import (
	"context"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"time"
)

type Posts struct {
	Id      int
	Content string
	Author  string
}

func SelectSQL(w http.ResponseWriter, _ *http.Request) {
	results, err := DbConn.Query(`select * from posts`)
	if err != nil {
		log.Fatal(err)
	}

	defer results.Close()

	list := []Posts{}

	for results.Next() {
		var post Posts
		results.Scan(&post.Id, &post.Content, &post.Author)
		fmt.Println(post)
		list = append(list, post)
	}
	data, err := json.Marshal(list)
	if err != nil {
		fmt.Println("json marshal error")
		return
	}
	w.Write([]byte(data))
}

func UpdateSQL(w http.ResponseWriter, _ *http.Request) {
	result, err := DbConn.Exec(`UPDATE posts SET author=? WHERE id=?`, "Dokkan", 1)
	if err != nil {
		log.Fatal(err)
	}
	row, err := result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("number of affected rows %d", row)
}

func SQLWithContext(w http.ResponseWriter, _ *http.Request) {
	ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
	defer cancel()

	row, err := DbConn.QueryContext(ctx, `select * from posts`)
	if err != nil {
		log.Fatal(err)
	}
	defer row.Close()

	list := []Posts{}

	for row.Next() {
		var post Posts
		row.Scan(&post.Id, &post.Content, &post.Author)
		list = append(list, post)
	}
	data, err := json.Marshal(list)
	if err != nil {
		fmt.Println("json marshal error")
		return
	}
	w.Write([]byte(data))
}