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)
	}
}

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

Plain text

Copy to clipboard

Open code in new window

EnlighterJS 3 Syntax Highlighter

INSERT INTO users (username,password,email) VALUES

('user1','123','myemail1@test.com'),

('user2','123','myemail2@test.com'),

('user3','123','myemail3@test.com');

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

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.

go mod init yourdomain.com/connectmysql

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

go build

./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