Using MySQL with Go

This document provides examples of how to connect to and interact with a MySQL database using the Go programming language. It covers installation, connection management, basic queries, and transactions.

Prerequisites

• Go installed and configured.
• MySQL server running and accessible.

1. Install Dependencies

Use the go get command to install the required MySQL driver.

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

2. Import Dependencies

Import the necessary packages in your Go code. Note the blank import _ "github.com/go-sql-driver/mysql". This registers the MySQL driver with the database/sql package.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

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

3. Establishing a Connection

The following function demonstrates how to open a connection to a MySQL database, configure connection pool settings, and handle potential errors.

func GetConnection() *sql.DB {
	db, err := sql.Open("mysql", "user:password@tcp(host:port)/database_name")
	if err != nil {
		log.Fatalf("Error opening database: %s", err)
	}

	// Set connection pool settings
	db.SetMaxIdleConns(10)          // Minimum number of idle connections in the pool
	db.SetMaxOpenConns(100)         // Maximum number of open connections to the database
	db.SetConnMaxIdleTime(5 * time.Minute) // Maximum amount of time a connection may be idle before being closed.
	db.SetConnMaxLifetime(60 * time.Minute) // Maximum amount of time a connection may be reused.

	// Test the connection
	err = db.Ping()
	if err != nil {
		log.Fatalf("Error pinging database: %s", err)
	}

	return db
}

func main() {
	db := GetConnection()
	defer db.Close()

	fmt.Println("Successfully connected to MySQL!")
	// Your database operations will go here...
}

Explanation:

• sql.Open("mysql", "user:password@tcp(host:port)/database_name"): Opens a database connection using the MySQL driver.
  • Replace "user", "password", "host", "port", and "database_name" with your actual database credentials. For example: "root:secret@tcp(localhost:3306)/my_database".
• db.SetMaxIdleConns(10): Sets the maximum number of connections in the idle connection pool.
• db.SetMaxOpenConns(100): Sets the maximum number of open connections to the database.
• db.SetConnMaxIdleTime(5 * time.Minute): Sets the maximum amount of time a connection may be idle.
• db.SetConnMaxLifetime(60 * time.Minute): Sets the maximum lifetime of a connection. Connections older than this will be closed and re-established. Using time.Hour or multiples thereof is considered best practice, as it is both easier to reason through and less error-prone.
• db.Ping(): Verifies that the connection to the database is active. It's good practice to call Ping() after opening the connection to catch any connection errors early.
• defer db.Close(): Ensures that the database connection is closed when the main function exits. This is important to release resources.
• Logging: Using the log package is generally a better practice than panic in a real-world application, as panic will abruptly terminate the program.

4. Query Examples

These examples demonstrate common database operations (insert, select, and parameterized queries).

4.1. Insert Example

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

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

func GetConnection() *sql.DB {
	db, err := sql.Open("mysql", "user:password@tcp(host:port)/database_name")
	if err != nil {
		log.Fatalf("Error opening database: %s", err)
	}

	// Set connection pool settings
	db.SetMaxIdleConns(10)          // Minimum number of idle connections in the pool
	db.SetMaxOpenConns(100)         // Maximum number of open connections to the database
	db.SetConnMaxIdleTime(5 * time.Minute) // Maximum amount of time a connection may be idle before being closed.
	db.SetConnMaxLifetime(60 * time.Minute) // Maximum amount of time a connection may be reused.

	// Test the connection
	err = db.Ping()
	if err != nil {
		log.Fatalf("Error pinging database: %s", err)
	}

	return db
}

func main() {
	db := GetConnection()
	defer db.Close()

	ctx := context.Background() // Use a background context

	query := "INSERT INTO customer(id, name) VALUES (?, ?)" // Use placeholders
	result, err := db.ExecContext(ctx, query, 3, "Charlie") // Pass parameters

	if err != nil {
		log.Fatalf("Error inserting data: %s", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		log.Fatalf("Error getting affected rows: %s", err)
	}
	fmt.Printf("INSERT Success. Rows affected: %d\n", rowsAffected)
}

Key Improvements:

• Using Placeholders: Changed to use parameterized queries with ? placeholders instead of directly inserting values into the query string. This is crucial for preventing SQL injection vulnerabilities. Critically important.
• Context: added context.Background() so the database operation can be be controlled
• Error Checking: added rowsAffected after result

4.2. GET Example (Query)

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

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

func GetConnection() *sql.DB {
	db, err := sql.Open("mysql", "user:password@tcp(host:port)/database_name")
	if err != nil {
		log.Fatalf("Error opening database: %s", err)
	}

	// Set connection pool settings
	db.SetMaxIdleConns(10)          // Minimum number of idle connections in the pool
	db.SetMaxOpenConns(100)         // Maximum number of open connections to the database
	db.SetConnMaxIdleTime(5 * time.Minute) // Maximum amount of time a connection may be idle before being closed.
	db.SetConnMaxLifetime(60 * time.Minute) // Maximum amount of time a connection may be reused.

	// Test the connection
	err = db.Ping()
	if err != nil {
		log.Fatalf("Error pinging database: %s", err)
	}

	return db
}


func main() {
	db := GetConnection()
	defer db.Close()

	ctx := context.Background()

	query := "SELECT id, name FROM customer"
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		log.Fatalf("Error querying data: %s", err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		err := rows.Scan(&id, &name)
		if err != nil {
			log.Fatalf("Error scanning row: %s", err)
		}
		fmt.Printf("Id: %d, Name: %s\n", id, name)
	}

	if err := rows.Err(); err != nil {
		log.Fatalf("Error iterating rows: %s", err)
	}

	fmt.Println("GET Success")
}

Key improvements:

• Uses int id type: id should have the correct data type that matches your table schema.
• Error Handling in Loop: Checks for errors during rows.Scan().
• Checks for errors after iteration: Checks for errors after iterating with rows.Err().
• Projection: Selects only the id and name columns for better performance.

4.3. GET With Params Example (Parameterized Query)

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

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

func GetConnection() *sql.DB {
	db, err := sql.Open("mysql", "user:password@tcp(host:port)/database_name")
	if err != nil {
		log.Fatalf("Error opening database: %s", err)
	}

	// Set connection pool settings
	db.SetMaxIdleConns(10)          // Minimum number of idle connections in the pool
	db.SetMaxOpenConns(100)         // Maximum number of open connections to the database
	db.SetConnMaxIdleTime(5 * time.Minute) // Maximum amount of time a connection may be idle before being closed.
	db.SetConnMaxLifetime(60 * time.Minute) // Maximum amount of time a connection may be reused.

	// Test the connection
	err = db.Ping()
	if err != nil {
		log.Fatalf("Error pinging database: %s", err)
	}

	return db
}

func main() {
	db := GetConnection()
	defer db.Close()

	ctx := context.Background()

	username := "test"
	password := "password123" // Replace with your test password

	query := "SELECT id, name FROM customer WHERE name = ? AND password = ?"
	rows, err := db.QueryContext(ctx, query, username, password)
	if err != nil {
		log.Fatalf("Error querying data: %s", err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		err := rows.Scan(&id, &name)
		if err != nil {
			log.Fatalf("Error scanning row: %s", err)
		}
		fmt.Printf("Id: %d, Name: %s\n", id, name)
	}

	if err := rows.Err(); err != nil {
		log.Fatalf("Error iterating rows: %s", err)
	}

	fmt.Println("GET with Params Success")
}

Key Improvements:

• SQL Injection Prevention: Uses parameterized queries (? placeholders) to prevent SQL injection vulnerabilities. Always use parameterized queries when accepting user input.
• Example Values: replaced the username and password to be more descriptive

5. Transactions

Transactions ensure that a series of database operations are treated as a single unit of work. If any operation within the transaction fails, the entire transaction is rolled back, maintaining data consistency.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

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

func GetConnection() *sql.DB {
	db, err := sql.Open("mysql", "user:password@tcp(host:port)/database_name")
	if err != nil {
		log.Fatalf("Error opening database: %s", err)
	}

	// Set connection pool settings
	db.SetMaxIdleConns(10)          // Minimum number of idle connections in the pool
	db.SetMaxOpenConns(100)         // Maximum number of open connections to the database
	db.SetConnMaxIdleTime(5 * time.Minute) // Maximum amount of time a connection may be idle before being closed.
	db.SetConnMaxLifetime(60 * time.Minute) // Maximum amount of time a connection may be reused.

	// Test the connection
	err = db.Ping()
	if err != nil {
		log.Fatalf("Error pinging database: %s", err)
	}

	return db
}

func main() {
	db := GetConnection()
	defer db.Close()

	ctx := context.Background()

	tx, err := db.BeginTx(ctx, nil) // Start a transaction
	if err != nil {
		log.Fatalf("Error starting transaction: %s", err)
	}

	defer func() {
		if p := recover(); p != nil {
			err := tx.Rollback()
			if err != nil {
				log.Fatalf("Error during rollback: %s", err)
			}
			panic(p) // Re-throw the panic after rollback
		} else if err != nil {
			err := tx.Rollback()
			if err != nil {
				log.Fatalf("Error during rollback: %s", err)
			}
		} else {
			err = tx.Commit()
			if err != nil {
				log.Fatalf("Error during commit: %s", err)
			}
			fmt.Println("Transaction committed successfully")
		}
	}()

	name := "NewUser"
	password := "SecurePassword"

	query := "INSERT INTO customer(name, password) VALUES (?, ?)"
	_, err = tx.ExecContext(ctx, query, name, password)
	if err != nil {
		log.Fatalf("Error executing statement: %s", err) // Critical to log error
	}

	// ... Additional database operations within the transaction ...

	fmt.Println("Transaction completed successfully")
}

Key Improvements:

• BeginTx with Context: Uses db.BeginTx to start a transaction with a context. This allows you to control the transaction's lifecycle (e.g., setting a timeout).
• Error Handling and Rollback: Includes comprehensive error handling and rollback logic. The defer statement ensures that the transaction is either committed or rolled back, even if a panic occurs. The recover() block handles panics and performs a rollback before re-throwing the panic. This pattern is the recommended way to handle transactions in Go.
• Clearer Error Messages: Improved error messages to provide more context.
• Use of Defer block: The defer block now handles panics and errors more robustly.
• SQL injection: use the placeholder instead of the actual value.

Important Notes

• Error Handling: Always check for errors after each database operation. Use log.Fatal or appropriate error handling to prevent unexpected program behavior.
• Security: Never hardcode database credentials directly into your code. Use environment variables or configuration files to store sensitive information. Be especially diligent in preventing SQL injection vulnerabilities by using parameterized queries.
• Connection Pooling: Use connection pooling (as demonstrated in the GetConnection function) to improve performance by reusing database connections.
• Context: Use context.Context for managing the lifecycle of database operations, including setting deadlines and cancellation signals.

This comprehensive guide provides a solid foundation for working with MySQL databases in Go. Remember to adapt the examples to your specific use cases and follow best practices for security and error handling.