How to insert Go structs with nested JSON fields into PostgreSQL databases using sqlx?

Inserting a Struct in PostgreSQL Database

Background

When utilizing Go lang with PostgreSQL as the database, the "github.com/lib/pq" driver enables database connectivity. However, manually inserting data from complex structs with nested JSON fields into the database can be tedious due to the extensive number of fields and values.

Using Named Parameters with sqlx

Fortunately, the github.com/jmoiron/sqlx library provides a solution with its NamedExec function. This function allows you to pass a struct with annotated field names (using the db tag) as a named parameter, simplifying the insertion process.

Example Implementation

Consider the following struct:

<code class="go">type ApplyLeave1 struct {
    LeaveId           int       `db:"leaveid"`
    EmpId             string    `db:"empid"`
    SupervisorEmpId   string    `db:"supervisorid"`
}</code>

To insert this struct into a database table, you can use the following code:

<code class="go">import (
    _ "github.com/lib/pq"
    "github.com/jmoiron/sqlx"
    "log"
)

// Define the database connection.
db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
    log.Fatalln(err)
}

// Prepare the SQL query with named parameters.
query := `INSERT INTO TABLENAME(leaveid, empid, supervisorid) 
          VALUES(:leaveid, :empid, :supervisorid)`

// Create an instance of the struct to be inserted.
var leave1 ApplyLeave1

// Execute the query with the named parameters.
_, err = db.NamedExec(query, leave1)
if err != nil {
    log.Fatalln(err)
}</code>

This approach significantly simplifies the insertion process, eliminating the need to manually specify each field and value.

Inserting JSON Arrays

While the sqlx library does not directly support JSON array insertion, you can use PostgreSQL's jsonb data type to store JSON data. To insert a JSON array into a jsonb column, you can first convert it into a string and then use the sqlx query builder to insert it.

For example, given the following struct with a JSON array field:

<code class="go">type ApplyLeave1 struct {
    LeaveId           int       `db:"leaveid"`
    EmpId             string    `db:"empid"`
    SupervisorEmpId   string    `db:"supervisorid"`
    Certificates     []CertificateInfo `db:"certificates"`
}</code>

You can insert it into a PostgreSQL database using the following code:

<code class="go">// Convert the JSON array to a string.
certificatesJSON, err := json.Marshal(leave1.Certificates)
if err != nil {
    log.Fatalln(err)
}

// Prepare the SQL query with named parameters.
query := `INSERT INTO TABLENAME(leaveid, empid, supervisorid, certificates) 
          VALUES(:leaveid, :empid, :supervisorid, :certificates)`

// Create an instance of the struct to be inserted.
var leave1 ApplyLeave1

// Execute the query with the named parameters.
_, err = db.NamedExec(query, map[string]interface{}{
    "leaveid":      leave1.LeaveId,
    "empid":        leave1.EmpId,
    "supervisorid": leave1.SupervisorEmpId,
    "certificates": string(certificatesJSON),
})
if err != nil {
    log.Fatalln(err)
}</code>

This approach allows you to insert complex structs with JSON arrays into PostgreSQL databases using a convenient and efficient method.

The above is the detailed content of How to insert Go structs with nested JSON fields into PostgreSQL databases using sqlx?. For more information, please follow other related articles on the PHP Chinese website!