The upper/db tour

< back
15/18
next >

a) SQL Builder: Update, Insert and Delete

The Update method creates and returns an Updater that you can use to build an UPDATE statement:

q := sess.SQL().
  Update("authors").
  Set("first_name = ?", "Edgar Allan").
  Where("id = ?", eaPoe.ID)

res, err := q.Exec()

The InsertInto method creates and returns an Inserter that you can use to build an INSERT statement:

res, err = sess.SQL().
  InsertInto("books").
  Columns(
    "title",
    "author_id",
    "subject_id",
  ).
  Values(
    "Brave New World",
    45,
    11,
  ).
  Exec()

In this case, using Columns is not mandatory. You can pass a struct to the Values method so it is mapped to columns and values, as shown below:

book := Book{
  Title:    "The Crow",
  AuthorID: eaPoe.ID,
}

res, err = sess.SQL().
  InsertInto("books").
  Values(book).
  Exec()

The DeleteFrom method creates and returns a Deleter that you can use to build a DELETE query:

q := sess.SQL().
  DeleteFrom("books").
  Where("title", "The Crow")

res, err := q.Exec()

b) Raw SQL

If none of the previous methods described are enough to express your query, you can use raw SQL. Look at the db.SQL interface to learn about all available methods for building and executing raw SQL statements.

res, err := sess.SQL().Exec(`UPDATE authors SET first_name = ? WHERE id = ?`, "Edgar
Allan", eaPoe.ID)
...

res, err := sess.SQL().Exec(`INSERT INTO authors VALUES`)
...

res, err := sess.SQL().Exec(`DELETE authors WHERE id = ?`, "Edgar Allan", eaPoe.ID)

< back
15/18
next >

              package main

import (
	"fmt"
	"log"

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

var settings = postgresql.ConnectionURL{
	Database: "booktown",
	Host:     "postgres",
	User:     "demo",
	Password: "b4dp4ss",
	Options: map[string]string{
		"sslmode": "disable", // Disable TLS
	},
}

// Book represents a record from the "books" table.
type Book struct {
	ID        uint   `db:"id,omitempty"`
	Title     string `db:"title"`
	AuthorID  uint   `db:"author_id,omitempty"`
	SubjectID uint   `db:"subject_id,omitempty"`
}

// Author represents a record from the "authors" table.
type Author struct {
	ID        uint   `db:"id,omitempty"`
	LastName  string `db:"last_name"`
	FirstName string `db:"first_name"`
}

// Subject represents a record from the "subjects" table.
type Subject struct {
	ID       uint   `db:"id,omitempty"`
	Subject  string `db:"subject"`
	Location string `db:"location"`
}

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

	db.LC().SetLevel(db.LogLevelDebug)

	var eaPoe Author

	// We use sqlbuilder.Selector to retrieve the last name "Poe" from the
	// "authors" table.
	err = sess.SQL().
		SelectFrom("authors").
		Where("last_name", "Poe"). // Or Where("last_name = ?", "Poe")
		One(&eaPoe)
	if err != nil {
		log.Fatal("Query: ", err)
	}
	fmt.Printf("eaPoe: %#v\n", eaPoe)

	// We use sqlbuilder.Updater to correct the typo in the name "Edgar Allen".
	res, err := sess.SQL().
		Update("authors").
		Set("first_name = ?", "Edgar Allan"). // Or Set("first_name", "Edgar Allan").
		Where("id = ?", eaPoe.ID).            // Or Where("id", eaPoe.ID)
		Exec()
	if err != nil {
		fmt.Printf("Query: %v. This is expected on the read-only sandbox.\n", err)
	}

	// We use sqlbuilder.Inserter to add a new book under "Edgar Allan Poe".
	book := Book{
		Title:    "The Crow",
		AuthorID: eaPoe.ID,
	}
	res, err = sess.SQL().
		InsertInto("books").
		Values(book). // Or Columns(c1, c2, c2, ...).Values(v1, v2, v2, ...).
		Exec()
	if err != nil {
		fmt.Printf("Query: %v. This is expected on the read-only sandbox.\n", err)
	}
	if res != nil {
		id, _ := res.LastInsertId()
		fmt.Printf("New book id: %d\n", id)
	}

	// We use sqlbuilder.Deleter to erase the book we've just created (and any
	// other book with the same name).
	q := sess.SQL().
		DeleteFrom("books").
		Where("title", "The Crow")
	fmt.Printf("Compiled query: %v\n", q)

	_, err = q.Exec()
	if err != nil {
		fmt.Printf("Query: %v. This is expected on the read-only sandbox\n", err)
	}
}

            