Building a Key-Value Store Using MySQL and Go

In this article, we'll explore how to build a simple Key-Value Store on top of a MySQL database using Go. This approach combines the efficiency of relational databases with the simplicity of key-value storage, providing a flexible, reliable, and scalable solution for various applications.

While specialized NoSQL databases like Redis or DynamoDB are often used for key-value stores, using MySQL offers several advantages, such as ACID compliance, robust querying capabilities, and ease of integration with existing relational data models.

Step 1: MySQL Database Setup

We start by setting up a MySQL database and table. The table will store key-value pairs in a simple schema with two main columns: key_name and value.

const (
	DB_USER     = "root"
	DB_PASSWORD = "xyz"
	DB_NAME     = "kvstore"
)        

The schema for the table:

CREATE TABLE IF NOT EXISTS items (
   id INT AUTO_INCREMENT,
   key_name VARCHAR(255) NOT NULL UNIQUE,
   value TEXT NOT NULL,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY(id)
);        

The above code creates an items table with a key_name, value, and a timestamp for when the record was created. The key_name is set to be unique, so duplicate entries are prevented

Step 2: Setting Up the Go Server

In the main function, we initialize the MySQL connection using sql.Open and set up the HTTP routes to handle different operations:

db, err = sql.Open("mysql", fmt.Sprintf("%s:%s@/%s", DB_USER, DB_PASSWORD, DB_NAME))
if err != nil {
	log.Fatalf("Error opening database: %v", err)
}
defer db.Close()

if err := db.Ping(); err != nil {
	log.Fatalf("Error pinging database %v", err)
}

createTable()

http.HandleFunc("/get", handleGet)
http.HandleFunc("/put", handlePut)
http.HandleFunc("/delete", handleDelete)

log.Println("Server is listening on port 8080")
server := &http.Server{Addr: ":8080"}
go server.ListenAndServe()        

This code opens the connection to the MySQL database and sets up the HTTP routes /get, /put, and /delete to handle respective operations. The server listens on port 8080.

Step 3: Implementing the API Endpoints

PUT: Adding or Updating Key-Value Pairs

The PUT operation allows us to add new key-value pairs or update existing ones. This is done using an INSERT INTO ... ON DUPLICATE KEY UPDATE query:

func handlePut(w http.ResponseWriter, r *http.Request) {
	var item Item
	if err := json.NewDecoder(r.Body).Decode(&item); err != nil {
		jsonResponse(w, Response{Status: "error", Message: "invalid Request payload"}, http.StatusBadRequest)
		return
	}

	if item.Key == "" || item.Value == "" {
		jsonResponse(w, Response{Status: "error", Message: "key and value are required"}, http.StatusBadRequest)
		return
	}

	err := putItem(item.Key, item.Value)
	if err != nil {
		jsonResponse(w, Response{Status: "error", Message: "Internal Server error"}, http.StatusInternalServerError)
		return
	}

	jsonResponse(w, Response{Status: "success", Message: "key value pair added/updated successfully"}, http.StatusOK)
}

func putItem(key, value string) error {
	mu.Lock()
	defer mu.Unlock()

	query := `
	 INSERT INTO items (key_name, value)
	 VALUES (?,?)
	 ON DUPLICATE KEY UPDATE value = VALUES(value)
	`
	_, err := db.Exec(query, key, value)
	return err
}        

Here, putItem locks the critical section using a sync.Mutex to avoid race conditions during concurrent write operations.

GET: Retrieving Values by Key

The GET operation fetches the value corresponding to a key using a simple SQL query:

func handleGet(w http.ResponseWriter, r *http.Request) {
	key := r.URL.Query().Get("key")
	if key == "" {
		jsonResponse(w, Response{Status: "error", Message: "Key is required"}, http.StatusBadRequest)
        return
	}

	value, err := getItem(key)

	if err == sql.ErrNoRows {
		jsonResponse(w, Response{Status: "error",  Message: "Key not found"}, http.StatusNotFound)
		return
	} else if err != nil {
		jsonResponse(w, Response{Status: "error", Message: "Internal Server Error"}, http.StatusInternalServerError)
        log.Printf("Error retrieving item: %v", err)
        return
	}

	jsonResponse(w, Response{Status: "Success", Data: &Item{Key: key, Value: value}}, http.StatusOK)
}

func getItem(key string) (string, error) {
	var value string
	err := db.QueryRow("SELECT value from items where key_name=?", key).Scan(&value)
	return value, err
}
        

In getItem, we run a SQL query to fetch the value for a given key. If the key is not found, we return an appropriate error response.

DELETE: Removing Key-Value Pairs

The DELETE operation allows us to remove key-value pairs based on the key:

func handleDelete(w http.ResponseWriter, r *http.Request) {
	key := r.URL.Query().Get("key")

	if key == "" {
		jsonResponse(w, Response{Status: "error", Message: "key is required"}, http.StatusBadRequest)
		return
	}

	err := deleteKey(key)

	if err == sql.ErrNoRows {
		jsonResponse(w, Response{Status: "error", Message: "key not found"}, http.StatusNotFound)
		return
	} else if err != nil {
		jsonResponse(w, Response{Status: "error", Message: "Internal Server Error"}, http.StatusInternalServerError)
		log.Printf("Error deleting item: %v", err)
		return
	}

	jsonResponse(w, Response{Status: "success", Message: "key deleted Successfully"}, http.StatusOK)
}

func deleteKey(key string) error {
	mu.Lock()
	defer mu.Unlock()

	result, err := db.Exec("DELETE from items where key_name = ?", key)

	if err != nil {
		return err
	}

	rowsAffected, _ := result.RowsAffected()

	if rowsAffected == 0 {
		return sql.ErrNoRows
	}

	return nil
}        

Conclusion

This simple Key-Value store built on MySQL using Go provides a solid foundation for storing data in a key-value model, while leveraging the benefits of a relational database. It's a flexible solution for developers who are already using MySQL and prefer not to introduce additional infrastructure.

The code can be further expanded to include more features such as:

• Expiration policies: To automatically delete keys after a certain period.
• Caching: To improve read performance by integrating a cache layer like Redis.
• Batch operations: For bulk data handling.

By using this approach, you maintain the simplicity of a Key-Value store while ensuring the reliability and ACID compliance of MySQL.