Prerequisite queries for the multi-insert example

contact_final.go

package models

import (
	"database/sql"
	"fmt"
	"strings"

	"github.com/pkg/errors"
)

type Contact struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
	Email     string `db:"email"`
}

type Contacts []Contact

func (cs Contacts) splitInGroups(groupSize int) []Contacts {
	if len(cs) <= groupSize {
		return []Contacts{cs}
	}

	var contactsGroups []Contacts
	for i := 0; i < len(cs); i += groupSize {
		end := i + groupSize
		if end > len(cs) {
			end = len(cs)
		}

		contactsGroups = append(contactsGroups, cs[i:end])
	}

	return contactsGroups
}

func (cs Contacts) BulkInsert(db *sql.DB) error {
	contacsGroups := cs.splitInGroups(20000)

	for _, contacts := range contacsGroups {
		var (
			placeholders []string
			vals         []interface{}
		)

		for index, contact := range contacts {
			placeholders = append(placeholders, fmt.Sprintf("($%d,$%d,$%d)",
				index*3+1,
				index*3+2,
				index*3+3,
			))

			vals = append(vals, contact.FirstName, contact.LastName, contact.Email)
		}

		txn, err := db.Begin()
		if err != nil {
			return errors.Wrap(err, "could not start a new transaction")
		}

		insertStatement := fmt.Sprintf("INSERT INTO contacts(first_name,last_name,email) VALUES %s", strings.Join(placeholders, ","))
		_, err = txn.Exec(insertStatement, vals...)
		if err != nil {
			txn.Rollback()
			return errors.Wrap(err, "failed to insert multiple records at once")
		}

		if err := txn.Commit(); err != nil {
			return errors.Wrap(err, "failed to commit transaction")
		}
	}

	return nil
}

contact_Initial.go

package models

import (
	"database/sql"
	"fmt"
	"strings"

	"github.com/pkg/errors"
)

type Contact struct {
	FirstName string `db:"first_name"`
	LastName  string `db:"last_name"`
	Email     string `db:"email"`
}

type Contacts []Contact

func (cs Contacts) BulkInsert(db *sql.DB) error {
	var (
		placeholders []string
		vals         []interface{}
	)

	for index, contact := range cs {
		placeholders = append(placeholders, fmt.Sprintf("($%d,$%d,$%d)",
			index*3+1,
			index*3+2,
			index*3+3,
		))

		vals = append(vals, contact.FirstName, contact.LastName, contact.Email)
	}

	txn, err := db.Begin()
	if err != nil {
		return errors.Wrap(err, "could not start a new transaction")
	}

	insertStatement := fmt.Sprintf("INSERT INTO contacts(first_name,last_name,email) VALUES %s", strings.Join(placeholders, ","))
	_, err = txn.Exec(insertStatement, vals...)
	if err != nil {
		txn.Rollback()
		return errors.Wrap(err, "failed to insert multiple records at once")
	}

	if err := txn.Commit(); err != nil {
		return errors.Wrap(err, "failed to commit transaction")
	}

	return nil
}

generator.go

package generator

import (
	"multi-insert/models"

	"github.com/jackc/fake"
)

func GenerateDummyContacts(numberOfContacts int) models.Contacts {
	var contacts models.Contacts
	for i := 0; i < numberOfContacts; i++ {
		contacts = append(contacts, models.Contact{
			FirstName: fake.FirstName(),
			LastName:  fake.LastName(),
			Email:     fake.EmailAddress(),
		})
	}

	return contacts
}

main.go

package main

import (
	"fmt"
	"log"
	"multi-insert/generator"
	"time"

	_ "github.com/lib/pq"
)

func main() {
	if err := setupDB(); err != nil {
		log.Fatal(err)
	}

	dummyContacts := generator.GenerateDummyContacts(recordsToInsert)

	// Just to get an idea of how long its taking us to insert that many records (roughly speaking)
	n := time.Now()

	if err := dummyContacts.BulkInsert(DB); err != nil {
		log.Fatal(err)
	}

	fmt.Printf("Inserting %d took %s\n", recordsToInsert, time.Since(n))
}

queries.sql

// Creating dummy role
CREATE ROLE dummy WITH LOGIN PASSWORD 'dummy';
// Creating database for the example
CREATE DATABASE notebook WITH OWNER dummy;

// Creating contacts table for this exercise
CREATE TABLE IF NOT EXISTS contacts(
first_name character varying(70) NOT NULL DEFAULT '',
last_name character varying(70) NOT NULL DEFAULT '',
email character varying(70) NOT NULL DEFAULT '');

setup.go

package main

import (
	"database/sql"

	"github.com/pkg/errors"
)

var (
	DB              *sql.DB
	recordsToInsert = int(1000)
)

func setupDB() error {
	var err error

	DB, err = sql.Open("postgres", "postgres://dummy:[email protected]:5432/notebook?sslmode=disable")
	if err != nil {
		return errors.Wrap(err, "failed to connect to the DB")
	}

	_, err = DB.Exec("DELETE FROM contacts;")
	if err != nil {
		return errors.Wrap(err, "failed to clear the contacts table")
	}

	return nil
}