Efficient Parent-Child Table Data Insertion with Go, MySQL, and sqlx

Introduction

Backend development in Go (Golang) has been gaining popularity due to its performance and efficiency. In this article, we will explore how to simultaneously insert data into parent and child tables in MySQL using the sqlx library in Go. This guide is packed with a variety of code examples suitable for beginners to intermediate programmers.

Basic Concepts

Let’s briefly go over the basic concepts of database operations in Go. The sqlx library extends the functionalities of the standard database/sql package, offering a more intuitive and user-friendly API.

Inserting Data into the Parent Table

Code Example 1: Simple INSERT Statement

type Parent struct {
    ID   int    `db:"id"`
    Name string `db:"name"`
}

// Insert data into the parent table
func InsertParent(db *sqlx.DB, p Parent) error {
    query := `INSERT INTO parent (name) VALUES (:name)`
    _, err := db.NamedExec(query, p)
    return err
}

Explanation

In this code, we use the Parent structure to insert data into the parent table. The NamedExec method of sqlx allows us to map the structure’s field names to the table’s column names.

Inserting Data into the Child Table

Code Example 2: INSERT Statement with Transaction

type Child struct {
    ID       int    `db:"id"`
    ParentID int    `db:"parent_id"`
    Name     string `db:"name"`
}

// Insert data into the child table
func InsertChild(db *sqlx.DB, c Child) error {
    tx, err := db.Beginx()
    if err != nil {
        return err
    }

    query := `INSERT INTO child (parent_id, name) VALUES (:parent_id, :name)`
    _, err = tx.NamedExec(query, c)
    if err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

Explanation

Here, we use a transaction to insert data into the child table. This approach ensures data integrity and safe insertion.

Simultaneous Data Insertion into Parent and Child Tables

Code Example 3: Insertion Utilizing Parent-Child Relationship

// Simultaneous data insertion into parent and child tables
func InsertParentAndChild(db *sqlx.DB, p Parent, c Child) error {
    // Insert into the parent table
    if err := InsertParent(db, p); err != nil {
        return err
    }

    // Insert into the child table
    c.ParentID = p.ID // Set the parent table's ID
    if err := InsertChild(db, c); err != nil {
        return err
    }

    return nil
}

Explanation

In this code, we first insert data into the parent table, then use its ID for inserting into the child table. This method allows for efficient storage of related data in the database.

Conclusion

We have introduced methods for efficiently inserting data into parent and child tables using Go, MySQL, and sqlx. These examples will help deepen your understanding of database operations in real-world projects.