How to insert Data in JSONB Field of Postgres using GORM

Here are detailed steps and code examples for inserting data into JSONB fields in PostgreSQL.

Step 1: Define Data Structure

First, define a Go struct that maps to your PostgreSQL table. Assume you have a users table with an info column of type JSONB.

go
package main

import (
    "gorm.io/gorm"
)

type User struct {
    gorm.Model
    Name string
    Info Info  `gorm:"type:jsonb"`
}

type Info struct {
    Age  int
    City string
}

In this example, the Info struct maps to the JSONB field.

Step 2: Connect to the Database

Use GORM to connect to the PostgreSQL database.

go
package main

import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

func setupDB() *gorm.DB {
    dsn := "host=localhost user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }
    return db
}

Ensure you replace the DSN (Data Source Name) with your database configuration.

Step 3: Create Tables and Insert Data

Ensure your table is created and insert data containing the JSONB field.

go
package main

func main() {
    db := setupDB()

    // Auto-migrate
    db.AutoMigrate(&User{})

    // Insert data
    user := User{
        Name: "John Doe",
        Info: Info{
            Age:  30,
            City: "New York",
        },
    }
    db.Create(&user)
}

In this example, the AutoMigrate function automatically creates or adjusts the existing table structure based on the struct. The Create function inserts new records into the database.

Step 4: Verify and Debug

During development, ensure your data is inserted correctly. Use PostgreSQL query tools (such as pgAdmin or command-line tools) to check the data in the users table.

By following these steps, you should be able to successfully insert data into JSONB fields in PostgreSQL and effectively manage this data using GORM with Go. Remember to validate input data and handle errors to ensure the robustness of your application and data integrity.