Insert/Read from a one-to-many database using pq

Getting Help
1

Hello, I’ve recently learned a bit about SQL and I’ve been looking at getting a data structure setup in Go.
I’ve been able to insert and read from the database but I’m having a hard time understanding how I should setup a one-to-many relationship using the pq library for postgres. As of right now I have two structs defined as such

type ImageSearch struct {
	ID          int
	SearchQuery string
	ImageInfo   []ImageInfo
}

type ImageInfo struct {
	ID            int
	Name          string
	ImageURL      string
	ThumbnailURL  string
	HostPageURL   string
	ImageSearchID int
}

Then I am creating the tables like this

func CreateSchema() {
	imageSearch := `
	CREATE TABLE IF NOT EXISTS image_search(
		id SERIAL PRIMARY KEY NOT NULL,	
		search_query TEXT NOT NULL
	);`

	imageInfo := `
	CREATE TABLE IF NOT EXISTS image_info(
		id SERIAL PRIMARY KEY NOT NULL,	
		name TEXT NOT NULL,
		image_url TEXT NOT NULL,
		thumbnail_url TEXT NOT NULL,
		hostpage_url TEXT NOT NULL,
		image_search_id INT references image_search(ID)
	);`

	_, err1 := DB.Exec(imageSearch)
	if err1 != nil {
		log.Fatal(err1)
	}
	_, err2 := DB.Exec(imageInfo)
	if err2 != nil {
		log.Fatal(err2)
	}
}

What I’m basically doing is using an API that gets images, I want to save the string that I used to search along with all of the different records returned, in this case 8 of them. Each of them has various information.

If anyone could steer me in the right direction I’d really appreciate it, thank you!

2

I don’t have a Postgres laying around, but this works with sqlite3:

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/mattn/go-sqlite3"
)

var DB *sql.DB

func main() {
	log.SetFlags(log.Lshortfile)

	db, err := sql.Open("sqlite3", "image.db")
	if err != nil {
		log.Fatalln(err)
	}

	CreateSchema(db)

	//
	// insert
	//

	query := "search query"

	res, err := db.Exec("insert into image_search (search_query) values (?)", query)
	if err != nil {
		log.Fatalln(err)
	}

	id, err := res.LastInsertId()
	if err != nil {
		log.Fatalln(err)
	}

	for i := 0; i < 8; i++ {
		name := fmt.Sprintf("name%d", i)
		image := fmt.Sprintf("image%d", i)
		thumbnail := fmt.Sprintf("thumbnail%d", i)
		hostpage := fmt.Sprintf("hostpage%d", i)

		_, err := db.Exec("insert into image_info (name, image_url, thumbnail_url, hostpage_url, image_search_id) values (?, ?, ?, ?, ?)", name, image, thumbnail, hostpage, id)
		if err != nil {
			log.Fatalln(err)
		}
	}

	//
	// retrieve
	//

	var search ImageSearch

	err = db.QueryRow("select id, search_query from image_search limit 1").Scan(&search.ID, &search.SearchQuery)
	if err != nil {
		log.Fatalln(err)
	}

	rows, err := db.Query("select id, name, image_url, thumbnail_url, hostpage_url from image_info where image_search_id = ?", search.ID)
	if err != nil {
		log.Fatalln(err)
	}
	for rows.Next() {
		var info ImageInfo
		err = rows.Scan(&info.ID, &info.Name, &info.ImageURL, &info.ThumbnailURL, &info.HostPageURL)
		if err != nil {
			log.Fatalln(err)
		}
		info.ImageSearchID = search.ID

		search.ImageInfo = append(search.ImageInfo, info)
	}

	//
	// results
	//

	log.Println(search)

}

type ImageSearch struct {
	ID          int
	SearchQuery string
	ImageInfo   []ImageInfo
}

type ImageInfo struct {
	ID            int
	Name          string
	ImageURL      string
	ThumbnailURL  string
	HostPageURL   string
	ImageSearchID int
}

func CreateSchema(DB *sql.DB) {
	imageSearch := `
	CREATE TABLE IF NOT EXISTS image_search(
		id INTEGER PRIMARY KEY,
		search_query TEXT NOT NULL
	);`

	imageInfo := `
	CREATE TABLE IF NOT EXISTS image_info(
		id INTEGER PRIMARY KEY,	
		name TEXT NOT NULL,
		image_url TEXT NOT NULL,
		thumbnail_url TEXT NOT NULL,
		hostpage_url TEXT NOT NULL,
		image_search_id INTEGER references image_search(id)
	);`

	_, err1 := DB.Exec(imageSearch)
	if err1 != nil {
		log.Fatal(err1)
	}
	_, err2 := DB.Exec(imageInfo)
	if err2 != nil {
		log.Fatal(err2)
	}
}

Running this code produces:

$ go run main.go
main.go:81: {1 search query [{1 name0 image0 thumbnail0 hostpage0 1} {2 name1 image1 thumbnail1 hostpage1 1} {3 name2 image2 thumbnail2 hostpage2 1} {4 name3 image3 thumbnail3 hostpage3 1} {5 name4 image4 thumbnail4 hostpage4 1} {6 name5 image5 thumbnail5 hostpage5 1} {7 name6 image6 thumbnail6 hostpage6 1} {8 name7 image7 thumbnail7 hostpage7 1}]}

I had to change the definitions of image_info.id and image_search.id to get the ids to be automatically created.

The queries and Go are database agnostic enough that they should just work with Postgres.

You might like the tutorial series at http://go-database-sql.org

2 Likes
3

That looks like it’ll work perfectly. Thank you very much.

4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.