Query database records

The Collection method takes the name of a table in the database and returns a value that satisfies the db.Collection interface:

booksTable := sess.Collection("books")

One of the methods defined by the db.Collection interface is Find.

We’ll use Find to search for specific records within the collection hierarchy. Find returns a db.Result object, which is delimited by the condition passed to Find and can contain zero, one, or many database records.

The db.Result API works the same on all supported databases.

The following query fetches and maps all the records from the “books” table:

var books []Book

res := booksTable.Find()
err := res.All(&books)

You can build the query to return records in different ways, such as sorted by title (descending order):

var books []Book

res := booksTable.Find().OrderBy("-title")
err := res.All(&books)

Use One instead of All if you want to retrieve a single record from the set:

var book Book

res := booksTable.Find(db.Cond{"id": 4})
err := res.One(&book)

You can also determine the total number of records in the result-set with Count:

res := booksTable.Find()

total, err := res.Count()
...

There are many options for you to define queries depending on your database type. Take a look here.

Query builder and raw SQL

In the particular case of adapters for SQL databases, you can also choose to use a query builder (for more control over your query):

q := sess.SQL().Select().From("books")

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

… or raw SQL (for absolute control over your query):

rows, err := sess.SQL().Query("SELECT * FROM books")
// rows is a regular *sql.Rows object.

Given that the example in this tour is based on a SQL database, we’ll elaborate hereunder on the use of both a) the SQL builder and b) raw SQL.

< back
6/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"`
	Title     string `db:"title"`
	AuthorID  uint   `db:"author_id"`
	SubjectID uint   `db:"subject_id"`
}

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

	// "books" is a table that already exists in our database.
	booksTable := sess.Collection("books")

	// Use Find to create a result set (db.Result).
	res := booksTable.Find()

	// The result set can be modified by chaining different db.Result methods
	// (like Where, And, OrderBy, Select Limit, and Group). Keep in mind that
	// db.Result is immutable so you'll probably have to reassign the variable
	// that is holding that object.
	res = res.OrderBy("-title") // ORDER BY title DESC

	// Result sets are lazy, meaning that the query will be built or sent to the
	// database until one of the methods that require database interaction is
	// used (for example, One or All).
	var books []Book
	if err := res.All(&books); err != nil {
		log.Fatal("res.All: ", err)
	}

	// The All method copies every single record in the result set into a Go slice.
	fmt.Printf("Records in the %q table:\n", booksTable.Name())
	for _, book := range books {
		fmt.Printf("%d:\t%q\n", book.ID, book.Title)
	}
	fmt.Println("")

	// Find out how many elements the result set has with Count.
	total, err := res.Count()
	if err != nil {
		log.Fatal("Count: ", err)
	}
	fmt.Printf("There are %d records on %q", total, booksTable.Name())
	fmt.Println("")

	// Since result sets are stateless and immutable, they can be reused many
	// times on different queries.
	recordsThatBeginWithP := res.And("title LIKE", "P%") // WHERE ... AND title LIKE 'P%'

	// The original `res` result set is not altered.
	total1, err := res.Count()
	if err != nil {
		log.Fatal("Count: ", err)
	}

	// ... while the new result set is modified.
	total2, err := recordsThatBeginWithP.Count()
	if err != nil {
		log.Fatal("Count: ", err)
	}

	fmt.Printf("There are still %d records on %q\n", total1, booksTable.Name())
	fmt.Printf("And there are %d records on %q that begin with \"P\"\n", total2, booksTable.Name())
}

            