Golang — With MySql

In the Previous Article, we discussed about basics of Go Programming. In this article, we will make a simple Go Application, to do CRUD operations on a Student object with a mysql database.

Before we start to code, we need to import go-sql-driver library.

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

Next, we add the required imports for creating database connector.

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

Now we create a connection object to open a MySQL connection.

var db *sql.DB
db, err:= sql.Open("mysql","root:password@(localhost:3306)/student")

Once the connector is initialised, we call Ping function to test the connection.

err = db.Ping()

In sql.DB object, we have some in-built functions to set connection properties.

db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(time.Second * 10)

Following is the complete function to connect to mysql database.

var db *sql.DB

func InitializeMySQL() {
   dBConnection, err := sql.Open("mysql", "root:password@(localhost:3306)/student")
   if err != nil {
       fmt.Println("Connection Failed!!")
   }
        err = dBConnection.Ping()
        if err != nil {
            fmt.Println("Ping Failed!!")
        }
   db = dBConnection
   dBConnection.SetMaxOpenConns(10)
   dBConnection.SetMaxIdleConns(5)
   dBConnection.SetConnMaxLifetime(time.Second * 10)
}

func GetMySQLConnection() *sql.DB {
   return db
}

Once we have database connector in place, we can start adding functions to perform read and write operations.

We create a statement object, by calling Prepare method on the query.

sqlQuery := "INSERT student SET name = ?, age = ?"
stmt, err := db.Prepare(sqlQuery)

For Insert/Update/Delete operations, queries can be run using Exec command and passing parameters in function call.

res, err := stmt.Exec(student.Name, student.Age)

For Read operations, queries can be run using Query command and passing parameters in function call,

sqlQuery := "SELECT id, name, age FROM student WHERE id = ?"
stmt, err := db.Prepare(sqlQuery)
res, err := stmt.Query(studentID)

And returned values can be parsed through scan function.

for res.Next() {
    res.Scan(&student.StudentID, &student.Name, &student.Age)
}

It is important to close statement and result objects(returned from Query function), to avoid leaks and to reuse connections.

res.Close()
stmt.Close()

Following are the sample functions to perform CRUD operations on a Student object.

func AddStudent(student model.Student) (int64, int64, error) {
   sqlQuery := "INSERT student SET name = ?, age = ?"
   stmt, err := db.Prepare(sqlQuery)
   defer closeStmt(stmt)
   if err != nil {
      return 0, 0, err
   }
   res, err := stmt.Exec(student.Name, student.Age)
   if err != nil {
      return 0, 0, err
   }
   rowsAffected, err := res.RowsAffected()
   if err != nil {
      return 0, 0, err
   }
   lastInsertedId, err := res.LastInsertId()
   return rowsAffected, lastInsertedId, err
}

func UpdateStudent(student model.Student) (int64, error) {
   sqlQuery := "UPDATE student SET name = ?, age = ? WHERE id = ?"
   stmt, err := db.Prepare(sqlQuery)
   defer closeStmt(stmt)
   if err != nil {
      return 0, err
   }
   res, err := stmt.Exec(student.Name, student.Age, student.StudentID)
   if err != nil {
      return 0, err
   }
   rowsAffected, err := res.RowsAffected()
   if err != nil {
      return 0, err
   }
   return rowsAffected, err
}

func DeleteStudent(studentID int64) (int64, error) {
   sqlQuery := "DELETE FROM student WHERE id = ?"
   stmt, err := db.Prepare(sqlQuery)
   defer closeStmt(stmt)
   if err != nil {
      return 0, err
   }
   res, err := stmt.Exec(studentID)
   if err != nil {
      return 0, err
   }
   rowsAffected, err := res.RowsAffected()
   if err != nil {
      return 0, err
   }
   return rowsAffected, err
}

func GetStudent(studentID int64) (model.Student, error) {
   sqlQuery := "SELECT id, name, age FROM student WHERE id = ?"
   stmt, err := db.Prepare(sqlQuery)
   defer closeStmt(stmt)
   var student model.Student
   if err != nil {
      return student, err
   }
   res, err := stmt.Query(studentID)
   defer closeRows(res)
   if err != nil {
      return student, err
   }
   if res.Next() {
      res.Scan(&student.StudentID, &student.Name, &student.Age)
   }
   return student, err
}

func closeRows(rows *sql.Rows) {
   if rows != nil {
      rows.Close()
   }
}

func closeStmt(stmt *sql.Stmt) {
   if stmt != nil {
      stmt.Close()
   }
}

Complete Code for this tutorial can be accessed through the GitHub Repo.

I hope you find it helpful. If you have any suggestions, please feel free to drop a comment.

In the Next Article, we will discuss how to setup file logging for a Go Application.