The pgx Files 06: Select

14 Aug 2016

Welcome back to The pgx Files! We are still using the users table we created in part two. Today, we look at selecting a single row from a table using pgx.

Here are the contents of our users table from last time, as seen from a psql session:

# select * from users;
┌──────────────────────────────────────┬──────────┬──────────┬────────────┬───────────┐
│                  id                  │ username │ password │ first_name │ last_name │
├──────────────────────────────────────┼──────────┼──────────┼────────────┼───────────┤
│ 3a6d8008-fec2-412d-acc6-2767a950c5e7 │ manni    │ foo      │ Manni      │ Wood      │
└──────────────────────────────────────┴──────────┴──────────┴────────────┴───────────┘
(1 row)

Here's how to fetch a user, and deal with expected errors (user not found) versues unexpected errors (everything else).

package main

import (
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx"
	"github.com/manniwood/playground/pgxfiles"
	"github.com/satori/go.uuid"
)

func main() {
	if len(os.Args) != 2 {
		log.Fatal("Please enter a user id (UUID)")
	}
	user_id := os.Args[1]
	id, err := uuid.FromString(user_id)
	if err != nil {
		fmt.Fprintf(os.Stderr, "This is not a UUID: \"%s\".\n", user_id)
		os.Exit(1)
	}

	conn := util.Connect("user fetcher")
	defer conn.Close()

	var username string
	var password string
	var firstName string
	var lastName string
	err = conn.QueryRow(`
	select username,
	       password,
	       first_name,
	       last_name
	  from users
	 where id = $1`, id).Scan(&username, &password, &firstName, &lastName)
	if err != nil {
		if err == pgx.ErrNoRows {
			fmt.Fprintf(os.Stderr, "User with id %s not found.\n", id)
		} else {
			fmt.Fprintf(os.Stderr, "Unexpected error trying to find user: %v\n", err)
		}
		os.Exit(1)
	}

	fmt.Printf("Found user %s %s\n", firstName, lastName)
}

$ ./getuser 3a6d8008-fec2-412d-acc6-2767a950c5e7
Found user Manni Wood

$ ./getuser 3a6d8008-fec2-412d-acc6-000000000000
User with id 3a6d8008-fec2-412d-acc6-000000000000 not found.

Pretty straightforward stuff!

Next on The pgx Files: only querying for the data you need!