Connect to MariaDB in Golang

We show you the code to connect to MariaDB 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 our table in the database. We assume that you already have a MariaDB server running and a database. So in MariaDB 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 MariaDB

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

go mod init yourdomain.com/connectmariadb

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:

./connectmariadb

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

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

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 MariaDB in Golang is useful.

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