Golang MySQL CRUD Example Tutorial

Author:

In this tutorial, we will learn how to create, read, update and delete records to/from the MySQL database in Golang with examples.

Prerequisites

  • Install MySQL Server
  • Install Go
  • Install MySQL workbench

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 github.com/go-sql-driver/mysql

Database Set up

Let's use below SQL statement to create a database in the MySQL server:

create database demo

After creating a database, use the below SQL script to create a students table in the database:

CREATE TABLE `students` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Golang - MySQL Insert into Database

In this example, we will insert a single record in the students table.

Let's create a file named "insert_db.go" and add the following content to it:


package main

import (
    "database/sql"
    "fmt"
    "log"

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

func main() {

    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/demo")
    defer db.Close()

    if err != nil {
        log.Fatal(err)
    }

	sql := "INSERT INTO students(email, first_name, last_name) VALUES ('admin@gmail.com', 'admin','admin')"
			
    res, err := db.Exec(sql)

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

    lastId, err := res.LastInsertId()

    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("The last inserted row id: %d\n", lastId)
}

Output:

G:\GoLang\examples>go run insert_db.go
The last inserted row id: 1

After running the above Go example, a single student record has inserted, You can use the above example to insert as many records that you want by just changing the student details in an insert SQL query.

2. Golang MySQL Select Example

In the previous example, we have inserted data into the database,

In this example, we will retrieve or select data from the students table.

Let's create a file named "select_db.go" and add the following content to it:


package main

import (
    "database/sql"
    "fmt"
    "log"

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

type Student struct {
    Id         int
    Email       string
    First_Name string
	Last_Name string
}

func main() {

    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/demo")
    defer db.Close()

    if err != nil {
        log.Fatal(err)
    }

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

    defer res.Close()

    if err != nil {
        log.Fatal(err)
    }

    for res.Next() {

        var student Student
        err := res.Scan(&student.Id, &student.Email, &student.First_Name, &student.Last_Name)

        if err != nil {
            log.Fatal(err)
        }

        fmt.Printf("%v\n", student)
    }
}

Output:

G:\GoLang\examples>go run select_db.go
{1 admin@gmail.com admin admin}

After running the above Go example, you retrieve all the student records from the database.

3. Golang - MySQL Update Example

In this example, we will update the first record that we have inserted in the first example in the students table.

Let's create a file named "update_db.go" and add the following content to it:


package main
 
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)
 
type Student struct {
    Id         int
    Email       string
    First_Name string
	Last_Name string
}
 
func main() {
	db, e := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/demo")
    ErrorCheck(e)
 
    // close database after all work is done
    defer db.Close()
 
    PingDB(db)
 
    //Update db
    stmt, e := db.Prepare("update students set First_Name=? where id=?")
    ErrorCheck(e)
 
    // execute
    res, e := stmt.Exec("Ramesh", "1")
    ErrorCheck(e)
 
    a, e := res.RowsAffected()
    ErrorCheck(e)
 
    fmt.Println(a)
 
    
}
 
func ErrorCheck(err error) {
    if err != nil {
        panic(err.Error())
    }
}
 
func PingDB(db *sql.DB) {
    err := db.Ping()
    ErrorCheck(err)
}

Output:

G:\GoLang\examples>go run go_example.go
1

4. Golang - MySQL Delete Example

In this example, we will delete the first record in students table.

Let's create a file named "delete_db.go" and add the following content to it:


package main
 
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)
 
type Student struct {
    Id         int
    Email       string
    First_Name string
	Last_Name string
}
 
func main() {
	db, e := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/demo")
    ErrorCheck(e)
 
    // close database after all work is done
    defer db.Close()
 
    PingDB(db)
 
    // delete data
    stmt, e := db.Prepare("delete from students where id=?")
    ErrorCheck(e)
 
    // delete 1st student
    res, e := stmt.Exec("1")
    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)
}

Output:

G:\GoLang\examples>go run go_example.go
1

Conclusion

In this tutorial, we have seen how to create, read, update and delete records to/from the MySQL database in Golang with examples.

Free Spring Boot Tutorial | Full In-depth Course | Learn Spring Boot in 10 Hours

Watch this course on YouTube at Spring Boot Tutorial | Fee 10 Hours Full Course

Comments

Post a Comment