Guides > Databases > Mysql > Go

This guide will walk you through the process of connecting to a MySQL database using Go within your Sealos DevBox project.

Prerequisites

A Sealos DevBox project with Go environment
A MySQL database created using the Database app in Sealos

Install Required Packages

In your Cursor terminal, install the necessary packages:

go get github.com/go-sql-driver/mysql
go get github.com/joho/godotenv

These commands install:

github.com/go-sql-driver/mysql: A MySQL driver for Go's database/sql package
github.com/joho/godotenv: A Go port of the Ruby dotenv library

Connection Setup

Set up the environment variables

First, let's set up the environment variables for our database connection. Create a .env file in your project root with the following content:

.env

DB_HOST=your_database_host
DB_PORT=3306
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database_name

Replace the placeholders with your actual MySQL credentials from the Database app in Sealos.

Create the main.go file

Create a new file named main.go with the following content:

main.go

package main
 
import (
	"database/sql"
	"fmt"
	"log"
	"os"
 
	_ "github.com/go-sql-driver/mysql"
	"github.com/joho/godotenv"
)
 
// Employee struct represents the structure of our data
type Employee struct {
	ID       int
	Name     string
	Position string
}
 
// connectDB establishes a connection to the MySQL database
func connectDB() (*sql.DB, error) {
	// Load environment variables from .env file
	err := godotenv.Load()
	if err != nil {
		log.Fatal("Error loading .env file")
	}
 
	// Retrieve database connection details from environment variables
	dbHost := os.Getenv("DB_HOST")
	dbPort := os.Getenv("DB_PORT")
	dbUser := os.Getenv("DB_USER")
	dbPassword := os.Getenv("DB_PASSWORD")
	dbName := os.Getenv("DB_NAME")
 
	// First, connect without specifying the database
	dsnWithoutDB := fmt.Sprintf("%s:%s@tcp(%s:%s)/", dbUser, dbPassword, dbHost, dbPort)
	db, err := sql.Open("mysql", dsnWithoutDB)
	if err != nil {
		return nil, err
	}
 
	// Create the database if it doesn't exist
	_, err = db.Exec("CREATE DATABASE IF NOT EXISTS " + dbName)
	if err != nil {
		return nil, err
	}
 
	// Close the connection and reconnect with the database specified
	db.Close()
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", dbUser, dbPassword, dbHost, dbPort, dbName)
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
 
	// Verify the connection
	err = db.Ping()
	if err != nil {
		return nil, err
	}
 
	fmt.Println("Successfully connected to the database")
	return db, nil
}
 
// createTable creates the employees table if it doesn't exist
func createTable(db *sql.DB) error {
	_, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS employees (
			id INT AUTO_INCREMENT PRIMARY KEY,
			name VARCHAR(100) NOT NULL,
			position VARCHAR(100) NOT NULL
		)
	`)
	return err
}
 
// insertEmployee inserts a new employee into the database
func insertEmployee(db *sql.DB, name, position string) error {
	_, err := db.Exec("INSERT INTO employees (name, position) VALUES (?, ?)", name, position)
	return err
}
 
// getEmployees retrieves all employees from the database
func getEmployees(db *sql.DB) ([]Employee, error) {
	rows, err := db.Query("SELECT id, name, position FROM employees")
	if err != nil {
		return nil, err
	}
	defer rows.Close()
 
	var employees []Employee
	for rows.Next() {
		var emp Employee
		err := rows.Scan(&emp.ID, &emp.Name, &emp.Position)
		if err != nil {
			return nil, err
		}
		employees = append(employees, emp)
	}
	return employees, nil
}
 
func main() {
	// Connect to the database
	db, err := connectDB()
	if err != nil {
		log.Fatal(err)
	}
	// Ensure the database connection is closed when the function exits
	defer func() {
		if err := db.Close(); err != nil {
			log.Printf("Error closing database connection: %v", err)
		} else {
			fmt.Println("Database connection closed successfully")
		}
	}()
 
	// Create the employees table
	err = createTable(db)
	if err != nil {
		log.Fatal(err)
	}
 
	// Insert sample employees
	err = insertEmployee(db, "John Doe", "Developer")
	if err != nil {
		log.Fatal(err)
	}
 
	err = insertEmployee(db, "Jane Smith", "Designer")
	if err != nil {
		log.Fatal(err)
	}
 
	// Retrieve and display all employees
	employees, err := getEmployees(db)
	if err != nil {
		log.Fatal(err)
	}
 
	fmt.Println("Employees:")
	for _, emp := range employees {
		fmt.Printf("ID: %d, Name: %s, Position: %s\n", emp.ID, emp.Name, emp.Position)
	}
 
	// The database connection will be closed automatically when main() exits
	// due to the defer statement at the beginning of the function
}

Let's break down the main components of this code:

Imports: We import necessary packages, including database/sql for database operations and github.com/go-sql-driver/mysql as the MySQL driver.
Employee struct: Defines the structure for our employee data.
connectDB function: Loads environment variables, constructs the connection string, and establishes a connection to the database.
createTable function: Creates the employees table if it doesn't exist.
insertEmployee function: Inserts a new employee into the database.
getEmployees function: Retrieves all employees from the database.
main function: Orchestrates the program flow, demonstrating database connection, table creation, data insertion, and retrieval.

Usage

To run the application, use the following command in your Cursor terminal:

go run main.go

This will execute the main function, demonstrating the connection to the database, table creation, data insertion, and querying.

Best Practices

Use environment variables for database credentials.
Always handle potential errors using proper error checking.
Close the database connection after operations are complete.
Use prepared statements for queries to prevent SQL injection.
Consider using a connection pool for better performance in production environments.

Troubleshooting

If you encounter connection issues:

Verify your database credentials in the .env file.
Ensure your MySQL database is running and accessible.
Check for any network restrictions in your DevBox environment.
Confirm that the required packages are correctly installed.

For more detailed information on using MySQL with Go, refer to the go-sql-driver/mysql documentation.

Go

On this page