Conect Database using MySql

Pada pembahasan sebelumnya, kita telah menyimpan data dalam memori. Namun dalam aplikasi sebenarnya, data biasa disimpan dalam suatu database, baik database relational maupun database nonsql.

Pada bab 4 kita akan melakukan operasi database menggunakan mysql.

• copy folder 03-mvc dan paste dengan nama 04-mysql-db

• pastikan semua import yang mengarah ke 03-mvc diubah ke 04-mysql-db

• di file config/config.json tambahakan konfigurasi untuk database

{
    "server": {
        "address": ":9090"
    },
    "database": {
        "driverName": "mysql",
        "dataSourceName": "root:rebel@/api_go"
    }
}

root adalah user, rebel adalah password, dan api_go adalah nama database.

buatlah database dengan nama api_go, dan table users dengan struktur berikut :

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `email` varchar(128) NOT NULL,
  `password` varchar(120) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email_UNIQUE` (`email`);

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

pada file models/user.go tambahakan field password

pada file controllers/userController.go fungsi CreateUser tambahkan validasi password

if len(user.Password) == 0 {
    json.NewEncoder(w).Encode("Please suplay valid password")
    return
}

buat file repositories/repository.go

package repositories

import (
    "database/sql"
    "github.com/jacky-htg/api-go/04-mysql-db/config"
    "github.com/jacky-htg/api-go/04-mysql-db/libraries"
)

var db *sql.DB
var err error

func init() {
    // Create an sql.DB and check for errors
    db, err = sql.Open(config.GetString("database.driverName"), config.GetString("database.dataSourceName"))
    libraries.CheckError(err)

    // Test the connection to the database
    err = db.Ping()
    libraries.CheckError(err)
}

pada file repositories/userRepository.go tambahkan import:

import (
    "github.com/jacky-htg/api-go/04-mysql-db/models"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jacky-htg/api-go/04-mysql-db/libraries"
)

hapus kode var users []User karena kita tidak lagi menyimpan data users dalam memory.

kemudian ubah kode sebelumnya menjadi seperti berikut :

func GetUsers() ([]models.User) {
    rows, err := db.Query("SELECT id, name, email, password FROM users")

    libraries.CheckError(err)

    var users []models.User

    for rows.Next() {
        var id int64
        var name string
        var email string
        var password string
        err = rows.Scan(&id, &name, &email, &password)
        libraries.CheckError(err)
        users = append(users, models.User{ID:id, Name:name, Email:email})
    }

    return users
}

func CreateUser(user models.User) (models.User)  {
    stmt, err := db.Prepare("INSERT INTO users (name, email, password, created_at) VALUES (?, ?, ?, NOW())")
    libraries.CheckError(err)

    res, err := stmt.Exec(user.Name, user.Email, user.Password)
    libraries.CheckError(err)

    id, err := res.LastInsertId()
    libraries.CheckError(err)

    user.ID = id

    return user
}

func GetUser(paramId int64) (models.User)  {
    rows, err := db.Query("SELECT id, name, email, password FROM users WHERE id=?", paramId)
    libraries.CheckError(err)
    defer rows.Close()

    var id int64
    var name string
    var email string
    var password string

    for rows.Next() {
        err := rows.Scan(&id, &name, &email, &password)
        libraries.CheckError(err)
    }

    err = rows.Err()
    libraries.CheckError(err)

    return models.User{ID:id, Name:name, Email:email}
}

karena ada penambahana field password pada entity user, maka file controllers/userController.go juga mengalamai perubahan menjadi seperti berikut:

package controllers

import (
    "encoding/json"
    "net/http"
    "strconv"
    "github.com/gorilla/mux"
    "github.com/jacky-htg/api-go/04-mysql-db/models"
    "github.com/jacky-htg/api-go/04-mysql-db/repositories"
    "golang.org/x/crypto/bcrypt"
)

func GetUsersEndPoint(w http.ResponseWriter, _ *http.Request) {
    json.NewEncoder(w).Encode(repositories.GetUsers())
}

func CreateUserEndPoint(w http.ResponseWriter, req *http.Request) {
    var user models.User

    user.Name   = req.FormValue("name")
    user.Email  = req.FormValue("email")
    password   := req.FormValue("password")

    // Validation
    if len(user.Name) == 0 {
        json.NewEncoder(w).Encode("Please suplay valid name")
        return
    }

    if len(user.Email) == 0 {
        json.NewEncoder(w).Encode("Please suplay valid email")
        return
    }

    if len(password) == 0 {
        json.NewEncoder(w).Encode("Please suplay valid password")
        return
    }

    user.Password, _ = bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
    json.NewEncoder(w).Encode(repositories.CreateUser(user))
}

func GetUserEndPoint(w http.ResponseWriter, req *http.Request) {
    params := mux.Vars(req)
    id, _  := strconv.ParseInt(params["id"], 10, 64)

    json.NewEncoder(w).Encode(repositories.GetUser(id))
}