How you can set up a Go application with MySQL for CRUD operations

To create a simple web CRUD application in Go (Golang) that interacts with MySQL, you need to perform several steps. Below is a basic example of how you can set up a Go application with MySQL for CRUD operations.

Prerequisites

1. Go Installed: Make sure you have Go installed.
2. MySQL Database: You should have a MySQL server running and a database set up for storing data.
3. Go MySQL Driver: You need to install the MySQL driver for Go, which is github.com/go-sql-driver/mysql.

Steps to Create a Golang CRUD Application with MySQL

1. Install MySQL Driver

First, install the MySQL driver for Go:

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

2. Set Up MySQL Database

In MySQL, create a database and a table. Here’s an example:

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

3. Create Go Application

Now, let's create a Go application to perform CRUD operations.

Directory Structure:

go-mysql-crud/
  ├── main.go
  └── go.mod

Code for main.go:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"net/http"

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

var db *sql.DB

func init() {
	var err error
	// Set up database connection (replace with your credentials)
	db, err = sql.Open("mysql", "root:password@tcp(localhost:3306)/testdb")
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	http.HandleFunc("/create", createUser)
	http.HandleFunc("/read", readUsers)
	http.HandleFunc("/update", updateUser)
	http.HandleFunc("/delete", deleteUser)

	log.Println("Server running on http://localhost:8080")
	log.Fatal(http.ListenAndServe(":8080", nil))
}

// Create a new user
func createUser(w http.ResponseWriter, r *http.Request) {
	if r.Method == http.MethodPost {
		name := r.URL.Query().Get("name")
		email := r.URL.Query().Get("email")

		_, err := db.Exec("INSERT INTO users (name, email) VALUES (?, ?)", name, email)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		fmt.Fprintf(w, "User created: %s (%s)\n", name, email)
	}
}

// Read all users
func readUsers(w http.ResponseWriter, r *http.Request) {
	rows, err := db.Query("SELECT id, name, email FROM users")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name, email string
		err := rows.Scan(&id, &name, &email)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		fmt.Fprintf(w, "ID: %d, Name: %s, Email: %s\n", id, name, email)
	}
}

// Update a user
func updateUser(w http.ResponseWriter, r *http.Request) {
	if r.Method == http.MethodPost {
		id := r.URL.Query().Get("id")
		name := r.URL.Query().Get("name")
		email := r.URL.Query().Get("email")

		_, err := db.Exec("UPDATE users SET name = ?, email = ? WHERE id = ?", name, email, id)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		fmt.Fprintf(w, "User updated: ID %s, Name: %s, Email: %s\n", id, name, email)
	}
}

// Delete a user
func deleteUser(w http.ResponseWriter, r *http.Request) {
	if r.Method == http.MethodPost {
		id := r.URL.Query().Get("id")

		_, err := db.Exec("DELETE FROM users WHERE id = ?", id)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		fmt.Fprintf(w, "User deleted: ID %s\n", id)
	}
}

4. Explanation of the Code:

• Database Connection: The init() function sets up the connection to the MySQL database using the sql.Open function.
• HTTP Handlers:
  • /create: Handles POST requests to insert new users.
  • /read: Handles GET requests to fetch all users.
  • /update: Handles POST requests to update a user's information.
  • /delete: Handles POST requests to delete a user by their ID.

5. Running the Application

1. Start the server:

   go run main.go

2. The application will run on http://localhost:8080. You can use tools like curl or Postman to interact with the endpoints.

6. Testing the Application

• Create a User:

  curl -X POST "http://localhost:8080/create?name=John&email=john@example.com"

• Read Users:

  curl "http://localhost:8080/read"

• Update a User:

  curl -X POST "http://localhost:8080/update?id=1&name=John Doe&email=john.doe@example.com"
  

• Delete a User:

  curl -X POST "http://localhost:8080/delete?id=1"

This simple Golang application demonstrates how to create a basic web API with CRUD operations connected to a MySQL database. You can extend it with more features, such as better error handling, form validation, and using more advanced frameworks like Gin or Echo.