func TestConnectionGet(t *testing.T) {
connection := db.NewConnection(GetDB(t))
err := LoadFixtures(connection)
test.Fatal(t, err, nil)
user := new(AppUser)
err = connection.QueryRow("SELECT name as Name,email as Email from users ;").GetResult(user)
test.Fatal(t, err, nil)
test.Fatal(t, user.Name, "John Doe")
user2 := AppUser{}
err = connection.QueryRow("SELECT * from users ;").GetResult(user2)
test.Fatal(t, err, db.ErrNotAPointer)
}
func TestRowGetResult(t *testing.T) {
connection := db.NewConnection(GetDB(t))
err := LoadFixtures(connection)
test.Fatal(t, err, nil)
result := map[string]interface{}{}
err = connection.CreateQueryBuilder().
Select("u.*").
From("users", "u").
Where("u.name = ?").
QueryRow("John Doe").
GetResult(&result)
test.Fatal(t, err, nil)
test.Fatal(t, result["email"], "*****@*****.**")
}
func TestConnectionSelect(t *testing.T) {
connection := db.NewConnection(GetDB(t))
result, err := connection.Exec("INSERT INTO users(name,email) values('john doe','*****@*****.**'),('jane doe','*****@*****.**');")
test.Fatal(t, err, nil)
r, err := result.RowsAffected()
test.Fatal(t, err, nil)
test.Fatal(t, r, int64(2), "2 records should have been created")
t.Log(result.LastInsertId())
// test query
users := []*AppUser{}
err = connection.Query("SELECT users.name as Name, users.email as Email from users ORDER BY users.id ASC ;").GetResults(&users)
test.Fatal(t, err, nil)
t.Logf("%#v", users)
test.Fatal(t, users[0].Name, "john doe")
}
func ExampleConnection() {
t := test.ExampleTester{log.New(os.Stderr, "log-tester", log.LstdFlags)}
// Define a type that represents a table
type TestUser struct {
ID int64 `sql:"column:id"`
Name string `sql:"column:name"` // db field name will match the content of the tag if declared
Email string `sql:"column:email"` // fieldnames are not automatically lower-cased to match db field names
Created time.Time `sql:"column:created"`
Nickname string `sql:"column:nick_name,persistzerovalue"` // allow empty strings or zero values to be persisted
PhoneNumbers []string `sql:"-"` // ignore field
}
var err error
// initialize the driver
DB, err := sql.Open("sqlite3", ":memory:")
test.Fatal(t, err, nil)
// create a connection
connection := db.NewConnection("sqlite3", DB)
connection.SetLogger(logger.NewDefaultLogger())
// create a table
_, err = connection.Exec(`
CREATE TABLE users(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
nick_name VARCHAR(255) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT(datetime('now'))
);`)
test.Fatal(t, err, nil)
user := &TestUser{Name: "John Doe", Email: "*****@*****.**"}
// Insert a new user into the database
result, err := connection.Insert("users", user)
test.Fatal(t, err, nil)
id, err := result.LastInsertId()
test.Fatal(t, err, nil)
fmt.Println("last inserted id", id)
// insert another user thanks to the query builder
result, err = connection.CreateQueryBuilder().
Insert("users").
SetValue("name", "?").
SetValue("email", "?").
SetValue("nick_name", "?").
Exec("Jane Doe", "*****@*****.**", "Jannie")
test.Fatal(t, err, nil)
// fetch the first inserted user
candidate := &TestUser{}
err = connection.QueryRow("SELECT u.* FROM users u WHERE u.id = ?", id).
GetResult(candidate)
test.Fatal(t, err, nil)
fmt.Println("candidate.Name:", candidate.Name)
// update the record
candidate.Name = "John Robert Doe"
result, err = connection.Update("users", map[string]interface{}{"id": candidate.ID}, candidate)
test.Fatal(t, err, nil)
affectedRows, err := result.RowsAffected()
test.Fatal(t, err, nil)
fmt.Println("rows affected by update:", affectedRows)
// delete the record
result, err = connection.Delete("users", map[string]interface{}{"id": candidate.ID})
test.Fatal(t, err, nil)
affectedRows, err = result.RowsAffected()
test.Fatal(t, err, nil)
fmt.Println("rows affected by delete:", affectedRows)
// let' make sure there is only one user in the database
var count int
err = connection.CreateQueryBuilder().
Select("COUNT(u.id)").From("users", "u").
QueryRow().
GetSingleResult(&count)
test.Fatal(t, err, nil)
fmt.Println("user count:", count)
// Output:
// last inserted id 1
// candidate.Name: John Doe
// rows affected by update: 1
// rows affected by delete: 1
// user count: 1
}
func GetConnection(t *testing.T) *db.DefaultConnection {
return db.NewConnection(GetDB(t))
}
