example_test.go

package dbx

import (
	"fmt"
	"log"

	_ "modernc.org/sqlite"
)

func ExampleQuery_Bind() {
	db, err := Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	_, _ = db.NewQuery(`CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, status TEXT)`).Execute()
	_, _ = db.Insert("users", Params{"name": "alice", "status": "active"}).Execute()
	_, _ = db.Insert("users", Params{"name": "bob", "status": "disabled"}).Execute()

	var names []string
	err = db.NewQuery(`SELECT name FROM users WHERE status={:status} ORDER BY id`).
		Bind(Params{"status": "active"}).
		Column(&names)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println(names)
	// Output: [alice]
}

func ExampleDB_Select() {
	db, err := Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	_, _ = db.NewQuery(`CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, status TEXT)`).Execute()
	_, _ = db.Insert("users", Params{"name": "alice", "status": "active"}).Execute()
	_, _ = db.Insert("users", Params{"name": "alex", "status": "active"}).Execute()
	_, _ = db.Insert("users", Params{"name": "bob", "status": "disabled"}).Execute()

	type row struct {
		ID   int64  `db:"id"`
		Name string `db:"name"`
	}

	var rows []row
	err = db.Select("id", "name").
		From("users").
		Where(And(HashExp{"status": "active"}, Like("name", "al"))).
		OrderBy("id ASC").
		All(&rows)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println(len(rows), rows[0].Name, rows[1].Name)
	// Output: 2 alice alex
}

func ExampleDB_Model() {
	db, err := Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	_, _ = db.NewQuery(`CREATE TABLE user (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT UNIQUE)`).Execute()

	type user struct {
		ID    int64  `db:"id,pk"`
		Name  string `db:"name"`
		Email string `db:"email"`
	}

	u := &user{Name: "alice", Email: "a@example.com"}
	if err := db.Model(u).Insert(); err != nil {
		log.Fatal(err)
	}
	fmt.Println(u.ID > 0)

	// Output: true
}

func ExampleDB_Upsert() {
	db, err := Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	_, _ = db.NewQuery(`CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT UNIQUE, name TEXT)`).Execute()
	_, _ = db.Insert("users", Params{"email": "a@example.com", "name": "alice"}).Execute()

	_, err = db.Upsert("users", Params{
		"email": "a@example.com",
		"name":  "alice-v2",
	}, "email").Execute()
	if err != nil {
		log.Fatal(err)
	}

	var name string
	err = db.NewQuery("SELECT name FROM users WHERE email={:email}").
		Bind(Params{"email": "a@example.com"}).
		Row(&name)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(name)

	// Output: alice-v2
}