2. Creating the Dabatase

We will setup a Vagrant virtual machine with MySQL involves a few steps. Vagrant is a tool that allows you to automate the creation and provisioning of virtual machines. Make sure you have VirtualBox (or another supported provider) and Vagrant installed on your host machine.

Create a new directory for the vagrant project

mkdir my-vagrant-mysql
cd my-vagrant-mysql

Run the following command to initialize a new Vagrant project

vagrant init

Modify the file to include the following lines for configuring the virtual machine and specifying the MySQL provisioning

Contents of bootstrap.sh

#!/usr/bin/env bash

DBNAME=dbname
DBUSER=dbuser
DBPASSWD=userpass

mysql -u root -proot -e "CREATE DATABASE $DBNAME"
mysql -u root -proot -e "CREATE USER '$DBUSER'@'%' IDENTIFIED BY '$DBPASSWD';"
mysql -u root -proot -e "GRANT ALL ON *.* TO '$DBUSER'@'%';"
mysql -u root -proot -e "FLUSH PRIVILEGES;"
sudo service mysql restart

# update mysql conf file to allow remote access to the db
sudo sed -i 's/^bind-address\s*=\s*127\.0\.0\.1/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
sudo service mysql restart

Vagrant.configure("2") do |config|
  config.vm.box = "bento/ubuntu-20.04" # or another Ubuntu box

  config.vm.network "forwarded_port", guest: 3306, host: 3306
  config.vm.network "private_network", type: "static", ip: "192.168.33.10"

  config.vm.provision "shell", inline: <<-SHELL
    sudo apt-get update
    sudo apt-get install net-tools
    sudo apt-get install -y mysql-server
    sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';"
  SHELL

  config.vm.provision :shell, path: "bootstrap.sh"

end

This Vagrantfile:

Uses the bento/ubuntu-20.04 box, but you can choose a different box if you prefer.
Forwards the MySQL port (3306) from the guest machine to the host machine.
Installs MySQL during provisioning. Also installs net-tools for the ifconfig command
Creates a static IP 192.168.33.10 so we can ssh to it locally using ssh vagrant@192.168.33.10
- The machine will have two network adapters, one with NAT so it can reach the internet, and other with our private IP so we can ssh to it locally

Run the following command to start the virtual machine.

vagrant up

After the virtual machine is up, SSH into it. The default password is vagrant

vagrant ssh

Inside the virtual machine, you can check if MySQL is installed and running.

sudo systemctl status mysql

Once logged into the VM, you might need to configure MySQL to allow connections from your local machine. Open the MySQL configuration file

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find the bind-address line and change it to:

bind-address = 0.0.0.0

Save the file and restart MySQL:

sudo systemctl restart mysql

Connect to MySQL to verify that it's accessible. The default password is root

mysql -u root -p

Connect to MySQL with our created user to verify that it's accessible. The default password is userpass

mysql -u dbuser -p

Test the database connection from your local machine using mysql shell

$ mysqlsh --uri=dbuser@192.168.33.10:3306

Now you have a Vagrant virtual machine with MySQL set up. You can configure MySQL, create databases, and interact with it as needed.

Our database handler

We will write the following in pkg/config/app.go, this file will wrap our DB object using the GORM (Go Object Relational Mapper) library to connect to a MySQL database

package config

import "github.com/jinzhu/gorm"

var (
	db *gorm.DB
)

func Connect() {
	d, err := gorm.Open("mysql", "root:root@tcp(localhost:3306)/simplerest?charset=utf8mb4&parseTime=True&loc=Local"")
	if err != nil {
		panic(err)
	}
	db = d
}

func GetDB() *gorm.DB {
	return db
}

Breakdown of each part

var (
   db *gorm.DB
)

This declares a package-level variable db of type *gorm.DB. This variable will be used to store the connection to the MySQL database. The GetDB function simply returns the db variable, which holds the reference to the connected MySQL database.

func Connect() {
   d, err := gorm.Open("mysql", "root:root/simplerest?charset=utf8&parseTime=True&loc=Local")
   if err != nil {
      panic(err)
   }
   db = d
}

The Connect function establishes a connection to a MySQL database. It uses the gorm.Open function to create a new database connection. The connection string passed to gorm.Open specifies the MySQL user root, password root, database name simplerest, and additional options such as character set, parse time, and location. If there's an error during the connection process, it panics, which is a way of immediately terminating the program and displaying the error message.