How to use Go with MySQL

How to implement MySQL with Go

In order to communicate with MySQL using Go, MySQL must be installed in your local machine so that you are able to use it. We will also use the database/sql package in Go’s standard library with any of the MySQL drivers so that connection to MySQL can be achieved. Some of the available drivers for MySQL are:

  • github.com/go-sql-driver/mysql/
  • github.com/siddontang/go-mysql/
  • github.com/ziutek/mymysql

For this shot, we will be using the github.com/go-sql-driver/mysql/.

How to use MySQL with Go (basic CRUD)

CRUD stands for Create, Read, Update, Delete. Below are steps for how to set up a connection as well as read from MySQL database using Go:

  1. First, we confirm if MySQL is installed in our machine using the mysql --version in our terminal. The resulting version means we have MySQL installed.
  2. Next, we install mysql driver package in our project: go get -u github.com/go-sql-driver/mysql.
  3. Finally, we import database/sql and github.com/go-sql-driver/mysql in our program.
package main
import (
  "database/sql"
  _ "github.com/go-sql-driver/mysql"
  "fmt"
  "log"
)
func main(){
  fmt.Println("How to Use Go with MySQL")
}
  1. Now that our database is set up, we can create a database object using the sql.Open("<driverName>","<databaseSource>") command. This command returns a pointer to the database. The driver name for our project is "mysql", and the databaseSource is a string of "<databaseUserName>:<password>@tcp(localhost:<port>)/<databaseName>".
// already imported used packages in the first code block
func main(){
  db,err := sql.Open("mysql","root:password@tcp(localhost:3306)/testdb")      //new
  defer db.Close()    //new
  errCheck(err)       //new
}
//new
func errCheck(err error){
  if err != nil{
    log.Fatal(err)
  }
}
  1. We will then confirm the availability of our database using the Ping() method from the database/sql package. If an error is returned, it means that the database is unavailable.
func main(){
  err = db.Ping()   //new
  errCheck(err)     //new
}
  1. With the availability of our database, we can now perform some CRUD activities on any table (todos in our case) present in the database using SQL queries.
type Todo struct {

    ID   int    `json:"id"`

    Name string `json:"name"`

}



var db *sql.DB



func main(){



    db,_ = sql.Open("mysql","root:password@tcp(localhost:3306)/testdb")      // modified 

  

    getAll() // get all the todos in the database

    getASingleTodo(1) // get the first todo

    

}



func getAll(){

    // get all todo from the todos table

    // note err has already been defined earlier as well as the errCheck function

    todos, err := db.Query("SELECT * FROM todos")

    errCheck(err)



    for todos.Next() {

        var todo Todo

        // for each row, scan the result into our todo composite object

        err = todos.Scan(&todo.ID, &todo.Name)

        errCheck(err)

        // print out the todo's Name attribute

        log.Printf(todo.Name)

    }

}



func getASingleTodo(id int){

    // get a single todo based on the id 

    var todo Todo

    err := db.QueryRow("SELECT * FROM todos where id = ?", id).Scan(&todo.ID, &todo.Name)

    errCheck(err)

    log.Printf("%d) %s\n",todo.ID,todo.Name)

}

Conclusion

We have seen how to set up MySQL to enable us to use Go with it as well as read data from it. There are also other queries that we can send to the database using Go – click here for more information.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)