In this article, we are going to learn how to connect Go with MySQL database and how to do basic operations. So, let’s start:

Table of Contents

1. Install MySQL Driver Package
2. Connect to MySQL Database
3. Create a Table & Struct
4. Merge Step 2 & 3
5. Insert Operation
6. Read Operation
7. Update Operation
8. Delete Operation

Install MySQL Driver Package

To query all sorts of SQL databases, Go has database/sql package by default. Go does not include any database driver. Let’s install a database driver using this command:

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

Connect to MySQL Database

We need to import these packages:

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

Now we’re going to create a function to connect to MySQL database:

func dbConn() (db *sql.DB) {
    dbDriver := "mysql"
    dbUser := "root"
    dbPass := ""
    dbName := "gotest"
    db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
    if err != nil {
        log.Fatal(err)
    }
    return db
}

To test the connection just add this line in the main() function:

db := dbConn()

To close the connection, we need to write this:

defer db.Close()

Create a Table & Struct

Let’s create a users table. Here’s the statement:

query := `
    CREATE TABLE users (
        id INT AUTO_INCREMENT,
        username TEXT NOT NULL,
        password TEXT NOT NULL,
        PRIMARY KEY (id)
    );`
if _, err := db.Exec(query); err != nil {
    log.Fatal(err)
}

We need to create a struct too. A struct is a type that contains named fields. Let’s create a struct for the users table:

type User struct {
    id        int
    username  string
    password  string
}

Merge Step 2 & 3

In this step, we’re going to merge all the things that we were done. The merged code:

package main

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

// User struct
type User struct {
    id        int
    username  string
    password  string
}

// Database connection
func dbConn() (db *sql.DB) {
    dbDriver := "mysql"
    dbUser := "root"
    dbPass := ""
    dbName := "gotest"
    db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@/"+dbName)
    if err != nil {
        log.Fatal(err)
    }
    return db
}

func main() {

    // DB connection
    db := dbConn()

     // Create users table
    query := `
        CREATE TABLE users (
            id INT AUTO_INCREMENT,
            username TEXT NOT NULL,
            password TEXT NOT NULL,
            PRIMARY KEY (id)
        );`
    if _, err := db.Exec(query); err != nil {
        log.Fatal(err)
    }

    // OPERATION WILL BE DONE HERE (Insert, Read, Update, Delete)

    // DB connection close
    defer db.Close()
}

To run this file, just we need to type go run main.go command. From now we’ll do the operations under the comment:

// OPERATION WILL BE DONE HERE (Insert, Read, Update, Delete)

As we’ve already created users table, we don’t need to keep the table creation code.

Insert Operation

We’ll insert a user into the database. We can write code like this:

// Insert a new user
username := "obydul"
password := "123456"

insert, err := db.Exec("INSERT INTO users (username, password) VALUES (?, ?)", username, password)
if err != nil {
    log.Fatal(err)
}

id, err := insert.LastInsertId()
log.Println("Last inserted ID: ", id)

Read Operation

To retrieve all users from the database, we can write code like:

// Retrieve all users
rows, err := db.Query(`SELECT id, username, password FROM users`)
if err != nil {
    log.Fatal(err)
}

var users []User
for rows.Next() {
    var u User

    err := rows.Scan(&u.id, &u.username, &u.password)
    if err != nil {
        log.Fatal(err)
    }
    users = append(users, u)
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
}
log.Println(users)

Update Operation

Now I’m going to show how we can update a user data:

// Update user
id := 1
username := "obydul"
password := "654321"

update, err := db.Prepare("UPDATE users SET username=?, password=? WHERE id=?")
if err != nil {
    panic(err.Error())
}
update.Exec(username, password, id)

Delete Operation

To delete a user, we can write the code like:

// Delete single user
delete, err := db.Prepare("DELETE FROM users WHERE id = ?")
if err != nil {
    log.Fatal(err)
}
delete.Exec(1) // user id

The tutorial is over. Thanks for reading.