How to iterate over an array and insert into a database?

Question

go

How to iterate through the array and write everything to the database?
There is an example of the usual iteration of an array with output to the console

package main

import "fmt"

type User struct {
  UserName string
  Category string
  Age      int
}

type Users []User

func (u Users) NameList() []string {
  var list []string
  for _, user := range u {
    list = append(list, user.UserName)
  }
  return list
}

func main() {
  users := Users{
    User{UserName: "Bryan", Category: "Human", Age: 33},
    User{UserName: "Jane", Category: "Rocker", Age: 25},
    User{UserName: "Nancy", Category: "Mother", Age: 40},
    User{UserName: "Chris", Category: "Dude", Age: 19},
    User{UserName: "Martha", Category: "Cook", Age: 52},
  }

  UserList := users.NameList()

  fmt.Println(UserList)
}

Can you explain on this example how to do all this insert into the database?

psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
    "password=%s dbname=%s sslmode=disable",
    host, port, user, password, dbname)
  db, err := sql.Open("postgres", psqlInfo)
  if err != nil {
    panic(err)
  }
  defer db.Close()

I know how to connect to the database, it’s just as banal how to add / delete / update, etc., but I don’t understand how to enumerate the array data ...

Can you direct me? Or an example or some kind of article
Thank you in advance

Answer 1

If you take your code as a basis, something like this can be done

package main

import "fmt"

type User struct {
  UserName string
  Category string
  Age      int
}

func main() {

  users := []User{
    User{UserName: "Bryan", Category: "Human", Age: 33},
    User{UserName: "Jane", Category: "Rocker", Age: 25},
    User{UserName: "Nancy", Category: "Mother", Age: 40},
    User{UserName: "Chris", Category: "Dude", Age: 19},
    User{UserName: "Martha", Category: "Cook", Age: 52},
  }

  // подключаетесь к базе
  db, _ := sql.Open(...)
  defer db.Close()

  for _, user := range users {
      fmt.Println(user)
      // делаете запрос в базу
      db.Exec("INSERT INTO users (name, category, age) VALUES ($1, $2, $3)", user.UserName, user.Category, user.Age)
  }
}

If there is a lot of data to insert into the database, this option will slow down, in this case it is better to insert in transactions

tx, _ := db.Begin()  // начинаете транзакцию
for _, user := range users {
    fmt.Println(user)
    // делаете запрос в базу (тут уже не db, а tx.Exec)
    tx.Exec("INSERT INTO users (name, category, age) VALUES ($1, $2, $3)", user.UserName, user.Category, user.Age)
}
tx.Commit() // завершаете транзакцию, данные сохраняются в базе