Connect Go with MySQL and Basic Operations (Insert, Read, Update, Delete)

Published on Jan 25, 2020

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:

main.go
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. ?

Comments (0)