Bulk insert from csv in postgres using golang without using for loop

1
votes

I am new in Golang,

  • I have csv file which have data looks like this
field1,field2
1.1,2
1.2,3
1.3,2
  • i want to insert all csv data into db table using golang without using for loop .. i am using postgres database

also don't want to use sql raw query

i am using gorm ORM

2
databasepostgresqlgogo-gorm
Can you show what you've tried so far? What loop specifically are you trying to avoid? The loop reading the csv records, the loop inserting the individual records? Can you show what data structure you're using to represent the csv data in Go? - mkopriva
yes i don't want to loop through csv records - M_x
If not SQL, what do you want to use then? Are you using some ORM? - mkopriva
Standard golang executes raw SQL to insert records into a database. - mkopriva
As mentioned, use batch insert, then. - Riwen

2 Answers

7
votes

You can use pgx library for that:

    filename := "foo.csv"
    dbconn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        panic(err)
    }
    defer dbconn.Release()
    f, err := os.Open(filename)
    if err != nil {
        panic(err)
    }
    defer func() { _ = f.Close() }()
    res, err := dbconn.Conn().PgConn().CopyFrom(context.Background(), f, "COPY csv_test FROM STDIN (FORMAT csv)")
    if err != nil {
        panic(err)
    }
    fmt.Print(res.RowsAffected())
1
votes

You can parse the CSV into a slice of typed structures using an external library, and then insert them all at once using GORM's batch insert.

Here is an example :

import (
    "os"

    "github.com/gocarina/gocsv"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

// Entry defines both the CSV layout and database schema
type Entry struct {
    gorm.Model

    Field1 float64 `csv:"field1"`
    Field2 float64 `csv:"field2"`
}

func main() {
    // Open the CSV file for reading
    file, err := os.Open("data.csv")
    if err != nil {
        panic(err)
    }
    defer file.Close()

    // Parse CSV into a slice of typed data `[]Entry` (just like json.Unmarshal() does)
    // The builtin package `encoding/csv` does not support unmarshaling into a struct
    // so you need to use an external library to avoid writing for-loops.
    var entries []Entry
    err = gocsv.Unmarshal(file, &entries)
    if err != nil {
        panic(err)
    }

    // Open a postgres database connection using GORM
    db, err := gorm.Open(postgres.Open("host=localhost user=postgres password=dev dbname=foo port=5432 sslmode=disable TimeZone=Europe/Paris"))
    if err != nil {
        panic(err)
    }

    // Create `entries` table if not exists
    err = db.AutoMigrate(&Entry{})
    if err != nil {
        panic(err)
    }

    // Save all the records at once in the database
    result := db.Create(entries)
    if result.Error != nil {
        panic(result.Error)
    }
}