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:
- First, we confirm if MySQL is installed in our machine using the
mysql --versionin our terminal. The resulting version means we have MySQL installed. - Next, we install
mysql driver packagein our project:go get -u github.com/go-sql-driver/mysql. - Finally, we import
database/sqlandgithub.com/go-sql-driver/mysqlin 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")
}- 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 thedatabaseSourceis 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)
}
}- We will then confirm the availability of our database using the
Ping()method from thedatabase/sqlpackage. If an error is returned, it means that the database is unavailable.
func main(){
err = db.Ping() //new
errCheck(err) //new
}
- 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.
RELATED TAGS
CONTRIBUTOR
View all Courses
