Golang MySQL CRUD Example

MySQL is one of the most used relational database engines in the world. It provides a simple and clear interface as well as integrations to many different programming languages. In this post, we will connect to MySQL with Golang.

Installing MySQL

Installing MySQL is pretty simple in any OS. There are multiple ways it can be installed. One can download a standalone installer or simply download a stack distribution installer like XAMPP. Both will work the same.

After installing MySQL, open the command prompt to check if MySQL is installed properly.

Run this command:

mysql -V

It will show the version of MySQL installed as shown below.

Mysql Version

Make sure you have a user account setup. If you have installed via XAMPP then from PHPMyAdmin it will be easy to create a user.

As you can see, below is the option of adding a new user. Simply add one with all privileges. Or, you can use root as well, although it doesn’t come with a password. So, you may need to set it yourself.

Required package

To connect to MySQL we need a driver. Here is the driver that we are going to use. To install it into GOPATH we simply run this command.

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

It will install the package.

Go MySQL example

Now, we will take a look at how to use MySQL with Go. Here we are using prepared statements. That is the best way to handle SQL database.

Database structure

This database consists of posts that have id as primary key, name of post and text of the post.

So, we define structure of data that will be used.

type Post struct {
	Id   int
	Name string
	Text string
}

Insert into database

To insert first we create a connection and check if it’s working correctly.

db, e := sql.Open("mysql", "<user>:<password>@/<databasename>")
ErrorCheck(e)

// close database after all work is done
defer db.Close()
PingDB(db)

Where the ping function as following:

func PingDB(db *sql.DB) {
	err := db.Ping()
	ErrorCheck(err)
}

The error checking function is simple.

func ErrorCheck(err error) {
	if err != nil {
		panic(err.Error())
	}
}

The table of posts looks like:

Now, we can start inserting data into it using code.

// INSERT INTO DB
// prepare	
stmt, e := db.Prepare("insert into posts(id, Name, Text) values (?, ?, ?)")
ErrorCheck(e)

//execute
res, e := stmt.Exec("5", "Post five", "Contents of post 5")
ErrorCheck(e)

id, e := res.LastInsertId()
ErrorCheck(e)

fmt.Println("Insert id", id)

Post Insertion

After insert the posts table becomes:

Update data

Now, we will update the post five. Here is how we can do that.

//Update db
stmt, e := db.Prepare("update posts set Text=? where id=?")
ErrorCheck(e)

// execute
res, e := stmt.Exec("This is post five", "5")
ErrorCheck(e)

a, e := res.RowsAffected()
ErrorCheck(e)

fmt.Println(a)   // 1

After updating the data the table looks as follows:

Query data

Now, we can simply query all data. We run through all the rows and take it into the struct.

// query all data
rows, e := db.Query("select * from posts")
ErrorCheck(e)

// declare empty post variable
var post = Post{}

// iterate over rows
for rows.Next() {
	e = rows.Scan(&post.Id, &post.Name, &post.Text)
	ErrorCheck(e)
	fmt.Println(post)
}

Delete data

Deleting data is easy and simple. Here is an example of delete by id.

// delete data
stmt, e := db.Prepare("delete from posts where id=?")
ErrorCheck(e)

// delete 5th post
res, e := stmt.Exec("5")
ErrorCheck(e)

// affected rows
a, e := res.RowsAffected()
ErrorCheck(e)

fmt.Println(a)     // 1

As can be seen, everything works perfectly.

The full code

Here is the full source code of the program.

package main

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

type Post struct {
	Id   int
	Name string
	Text string
}

func main() {
	db, e := sql.Open("mysql", "rootuser:pass1234@/posts")
	ErrorCheck(e)

	// close database after all work is done
	defer db.Close()

	PingDB(db)

	// INSERT INTO DB
	// prepare
	stmt, e := db.Prepare("insert into posts(id, Name, Text) values (?, ?, ?)")
	ErrorCheck(e)

	//execute
	res, e := stmt.Exec("5", "Post five", "Contents of post 5")
	ErrorCheck(e)

	id, e := res.LastInsertId()
	ErrorCheck(e)

	fmt.Println("Insert id", id)

	//Update db
	stmt, e := db.Prepare("update posts set Text=? where id=?")
	ErrorCheck(e)

	// execute
	res, e := stmt.Exec("This is post five", "5")
	ErrorCheck(e)

	a, e := res.RowsAffected()
	ErrorCheck(e)

	fmt.Println(a)

	// query all data
	rows, e := db.Query("select * from posts")
	ErrorCheck(e)

	var post = Post{}

	for rows.Next() {
		e = rows.Scan(&post.Id, &post.Name, &post.Text)
		ErrorCheck(e)
		fmt.Println(post)
	}

	// delete data
	stmt, e := db.Prepare("delete from posts where id=?")
	ErrorCheck(e)

	// delete 5th post
	res, e := stmt.Exec("5")
	ErrorCheck(e)

	// affected rows
	a, e := res.RowsAffected()
	ErrorCheck(e)

	fmt.Println(a) // 1
}

func ErrorCheck(err error) {
	if err != nil {
		panic(err.Error())
	}
}

func PingDB(db *sql.DB) {
	err := db.Ping()
	ErrorCheck(err)
}