Golang CRUD Operations Using MariaDB: A Step-by-Step Guide

MariaDB, a popular open-source relational database management system, can be seamlessly integrated with Go (Golang) to build robust data-driven applications. In this step-by-step guide, we will explore the fundamental CRUD (Create, Read, Update, Delete) operations using MariaDB and Golang. By the end of this article, you will have a clear understanding of how to perform these operations, connecting to a MariaDB database, and handling the results.

Prerequisites

Before we begin, ensure you have the following prerequisites in place:

Go Environment: Install Go if you haven’t already. You can download it from the official Go website.
MariaDB: MariaDB should be installed and running. You’ll need access to a MariaDB instance with appropriate credentials. Ensure that the MariaDB server is reachable from your Go application.
Go MariaDB Driver: To interact with MariaDB from your Go application, you’ll need a MariaDB driver. We will use the “github.com/go-sql-driver/mysql” package. You can install it using go get:
```
go get github.com/go-sql-driver/mysql
```

Setting Up the Database

Before we dive into CRUD operations, let’s create a simple database and table. We’ll assume you have a MariaDB instance running with a database named mydb and a table named users with the following structure:

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Step 1: Connecting to the MariaDB Database

The first step is to establish a connection to the MariaDB database. Create a function to handle this task:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

func connectToMariaDB() (*sql.DB, error) {
    db, err := sql.Open("mysql", "username:password@tcp(localhost:3306)/mydb")
    if err != nil {
        return nil, err
    }

    // Ping the MariaDB server to ensure connectivity
    err = db.Ping()
    if err != nil {
        return nil, err
    }

    fmt.Println("Connected to MariaDB!")
    return db, nil
}

Replace "username" and "password" with your MariaDB credentials, and update the connection details as needed.

Step 2: Performing CRUD Operations

Now that we are connected to the database, let’s explore the CRUD operations.

Create (Insert) Operation

To insert data into the users table, create a function like this:

func createUser(db *sql.DB, username, email string) error {
    _, err := db.Exec("INSERT INTO users (username, email) VALUES (?, ?)", username, email)
    return err
}

Read (Query) Operation

To retrieve data from the users table, create a function like this:

func getUsers(db *sql.DB) ([]User, error) {
    rows, err := db.Query("SELECT * FROM users")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Username, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, user)
    }

    return users, nil
}

Update Operation

To update data in the users table, create a function like this:

func updateUser(db *sql.DB, id int, newUsername, newEmail string) error {
    _, err := db.Exec("UPDATE users SET username=?, email=? WHERE id=?", newUsername, newEmail, id)
    return err
}

Delete Operation

To delete data from the users table, use a function like this:

func deleteUser(db *sql.DB, id int) error {
    _, err := db.Exec("DELETE FROM users WHERE id=?", id)
    return err
}

Step 3: Putting It All Together

Now, let’s put everything together in a simple Go program:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

type User struct {
    ID       int
    Username string
    Email    string
}

func main() {
    db, err := connectToMariaDB()
    if err != nil {
        fmt.Println("Error connecting to MariaDB:", err)
        return
    }
    defer db.Close()

    // Create a user
    createUser(db, "john_doe", "[email protected]")

    // Read users
    users, err := getUsers(db)
    if err != nil {
        fmt.Println("Error querying users:", err)
        return
    }
    fmt.Println("Users:")
    for _, user := range users {
        fmt.Printf("ID: %d, Username: %s, Email: %s\n", user.ID, user.Username, user.Email)
    }

    // Update a user
    updateUser(db, 1, "updated_user", "[email protected]")

    // Delete a user
    deleteUser(db, 1)
}

This program connects to the MariaDB database, performs a sequence of CRUD operations, and displays the results.

Conclusion

In this step-by-step guide, we’ve explored the basics of performing CRUD operations using MariaDB and Go. You’ve learned how to connect to a MariaDB database, create, read, update, and delete records in a table. These fundamental database operations are the building blocks for developing more complex data-driven applications in Go. MariaDB’s reliability and Go’s efficiency make them a powerful combination for your projects.