The upper/db tour

< back
11/18
next >

a) SQL Builder: Select

All the adapters for SQL databases come with a handy SQL builder that you can use to compose queries.

You can access the SQL builder on SQL adapters using the SQL() method:

builder := sess.SQL()

There are different methods you can use to begin your query, for instance SelectFrom:

q := sess.SQL().
  SelectFrom("books")

SelectFrom returns a [Selector][2] and some Selector methods return Selector too, so you can chain method calls like this:

q := sess.SQL().
  SelectFrom("books").
  Where("title LIKE ?", "P%")

q := sess.SQL().
  SelectFrom("books")

q = q.Where("title LIKE ?", "P%")

Note that we’re reassigning q in the last example. This is because queries are immutable and methods do not affect the caller, like in the next case (where q is not affected by Where):

q := sess.SelectFrom("books")

p := q.Where("title LIKE ?", "P%").
  OrderBy("title")

You can use All or One to compile, execute, and map results into a Go type:

var books []Book
err := q.All(&books)

var book Book
err := q.One(&book)

The Selector interface also features a special Iterator method to create an iterator and go through results one by one:

iter := q.Iterator()
defer iter.Close()

for iter.Next(&book) {
  // ...
}

if err := iter.Err(); err != nil {
  // ...
}

< back
11/18
next >

              package main

import (
	"fmt"
	"log"

	"github.com/upper/db/v4/adapter/cockroachdb"
)

var settings = cockroachdb.ConnectionURL{
	Database: `booktown`,
	Host:     `cockroachdb.demo.upper.io`,
	User:     `demouser`,
	Password: `demop4ss`,
}

type Book struct {
	ID        uint   `db:"id,omitempty"`
	Title     string `db:"title"`
	AuthorID  uint   `db:"author_id,omitempty"`
	SubjectID uint   `db:"subject_id,omitempty"`
}

func main() {
	sess, err := cockroachdb.Open(settings)
	if err != nil {
		log.Fatal("Open: ", err)
	}
	defer sess.Close()

	// The Collection / Find / Result syntax was created with compatibility
	// across all supported databases in mind. However, sometimes it might not be
	// enough for all your needs, especially when working with complex queries.

	// In such a case, you can also use the SQL builder.
	q := sess.SQL().SelectFrom("books")

	// `q` is a `sqlbuilder.Selector`, you can chain any of its other methods
	// that also return `Selector`.
	q = q.OrderBy("title")

	// Note that queries are immutable, here `p` is a completely independent
	// query.
	p := q.Where("title LIKE ?", "P%")

	// Queries are not compiled nor executed until you use methods like `One` or
	// `All`.
	var booksQ, booksP []Book
	if err := q.All(&booksQ); err != nil {
		log.Fatal("q.All: ", err)
	}

	// The `Iterator` method is a way to go through large result sets from top to
	// bottom.
	booksP = make([]Book, 0, len(booksQ))
	iter := p.Iterator()
	var book Book
	for iter.Next(&book) {
		booksP = append(booksP, book)
	}

	// Remember to check for error values at the end of the loop.
	if err := iter.Err(); err != nil {
		log.Fatal("iter.Err: ", err)
	}
	// ... and to free up any locked resources.
	if err := iter.Close(); err != nil {
		log.Fatal("iter.Close: ", err)
	}

	// Listing all books
	fmt.Printf("All books:\n")
	for _, book := range booksQ {
		fmt.Printf("Book %d:\t%q\n", book.ID, book.Title)
	}
	fmt.Println("")

	// Listing books that begin with P
	fmt.Printf("Books that begin with \"P\":\n")
	for _, book := range booksP {
		fmt.Printf("Book %d:\t%q\n", book.ID, book.Title)
	}
	fmt.Println("")
}

            