Connect to MySQL in Golang

We show you the code to connect to MySQL in Golang. But before to run this example it is necessary to have a database and a table to connect and also download and install the mysql driver.

» More Golang Examples

package main

import (
	"database/sql"
	"log"

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

//geekole.com

type User struct {
	ID       int    `json:"id"`
	Username string `json:"username"`
	Password string `json:"password"`
	Email    string `json:"email"`
}

func main() {
	db, err := sql.Open("mysql", "user01:password01@tcp(localhost:3306)/mydatabase?parseTime=true")

	if err != nil {
		log.Print(err.Error())
	}
	defer db.Close()

	results, err := db.Query("SELECT id, username, password, email FROM users")
	if err != nil {
		panic(err.Error())
	}

	for results.Next() {
		var user User
		err = results.Scan(&user.ID, &user.Username, &user.Password, &user.Email)
		if err != nil {
			panic(err.Error())
		}
		log.Printf(user.Username + " " + user.Password + " " + user.Email)
	}
}

First, we must create a table in the database. We assume that you already have a MySQL server running and a database. So in MySQL console you can run this CREATE script:

CREATE TABLE users (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  username varchar(100) NOT NULL,
  password varchar(100) NOT NULL,
  email varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
)

For this example we add some rows for test. You can use the next sql script

INSERT INTO users (username,password,email) VALUES
	 ('user1','123','myemail1@test.com'),
	 ('user2','123','myemail2@test.com'),
	 ('user3','123','myemail3@test.com');

Run the code and connect to MySQL

It is important that you run the following commands in the same folder as your project.

For this example, first we run the init module command.

go mod init yourdomain.com/connectmysql

Install the mysql driver

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

Save the code and compile:

go build

And finally, execute the binary file. If you are using Windows, then you must run an .exe:

./connectmysql

We can see the list of all the rows in our “users” table.

And a more complete example with CREATE, INSERT, UPDATE and DELETE

Note: It is very important that you repeat all the previous steps in each project for best results. As well as not having an existing table with the same name in your database for this example.

package main

import (
	"database/sql"
	"log"
	"strconv"

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

//geekole.com

type User struct {
	ID       int    `json:"id"`
	Username string `json:"username"`
	Password string `json:"password"`
	Email    string `json:"email"`
}

func main() {
	db, err := sql.Open("mysql", "user01:password01@tcp(localhost:3306)/mydatabase?parseTime=true")

	if err != nil {
		log.Print(err.Error())
	}
	defer db.Close()

	_, errCreate := db.Exec(`CREATE TABLE users (
		id bigint(20) NOT NULL AUTO_INCREMENT,
		username varchar(100) NOT NULL,
		password varchar(100) NOT NULL,
		email varchar(100) NOT NULL,
		PRIMARY KEY (id)
	  )`)
	if errCreate != nil {
		panic(err.Error())
	}

	for i := 1; i <= 10; i++ {
		_, errInsert := db.Exec(`INSERT INTO users(username, password, email) VALUES (?, ?, ?)`,
			"user"+strconv.Itoa(i),
			strconv.Itoa(i),
			"myemail"+strconv.Itoa(i)+"@test.com")
		if errInsert != nil {
			panic(err.Error())
		}
	}

	_, errDelete := db.Exec(`DELETE FROM users WHERE id=?`, 4)
	if errDelete != nil {
		panic(err.Error())
	}

	_, errUPDATE := db.Exec(`UPDATE users SET username=? WHERE id=?`, "useraname", 1)
	if errUPDATE != nil {
		panic(err.Error())
	}

	results, errSelect := db.Query(`SELECT id, username, password, email FROM users`)
	if errSelect != nil {
		panic(err.Error())
	}

	for results.Next() {
		var user User
		err = results.Scan(&user.ID, &user.Username, &user.Password, &user.Email)
		if err != nil {
			panic(err.Error())
		}
		log.Printf(user.Username + " " + user.Password + " " + user.Email)
	}
}

The output will be something like this:

We hope this example of how to connect to MySQL Golang is useful.

Se more about sql types on golang: https://pkg.go.dev/database/sql#pkg-types