Summary - database/sql
- Go course
- Advanced
- Lesson 30/68
- 4 min
Combine Query and Transaction to write a user record.
database/sql summary
In this group you registered a driver with _, set up the pool with sql.Open + Ping, ran queries with Query/QueryRow/Exec, wrote safe queries with placeholders and Prepare, and managed transactions with Begin/Commit/Rollback.
Three habits: don't forget rows.Close() and rows.Err(), never concatenate input into SQL, and always use tx inside a transaction.
A repository layer
In real applications SQL code is gathered in a repository type; the rest of the code only sees its methods:
type UrunRepo struct{ db *sql.DB }
func (r UrunRepo) Ad(id int) (string, error) {
var ad string
err := r.db.QueryRow("SELECT ad FROM urunler WHERE id = ?", id).Scan(&ad)
return ad, err
}In tests this type can be replaced by an interface and tried with fake data.
Practice Repository interface
Define type UrunRepo interface { Ad(id int) (string, error) }. Write the Ad method for a map-based type memRepo map[int]string (return errors.New("yok") if missing). Print r.Ad(1) with var r UrunRepo = memRepo{1: "Kalem"}.
Starter code
package main
import (
"errors"
"fmt"
)
func main() {
// buraya yaz
}Sample solution
package main
import (
"errors"
"fmt"
)
type UrunRepo interface {
Ad(id int) (string, error)
}
type memRepo map[int]string
func (m memRepo) Ad(id int) (string, error) {
ad, ok := m[id]
if !ok {
return "", errors.New("yok")
}
return ad, nil
}
func main() {
var r UrunRepo = memRepo{1: "Kalem"}
fmt.Println(r.Ad(1))
}Expected output
Kalem <nil>
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Why check <code>rows.Err()</code> after the loop?
- To tell whether Next returned false because of an error or the end Correct answer
- To get the row count
- To open a transaction
- To close the connection
Explanation: Next returns false both at the end and on a read error; rows.Err() tells the difference.
2 Scan the results
var ad string
err := db.QueryRow("SELECT ad FROM urunler WHERE id = ?", 3).____(&ad)var ad string
err := db.QueryRow("SELECT ad FROM urunler WHERE id = ?", 3).Scan(&ad)Explanation: Scan copies the column values into the given pointers.
3 Group summary
| Term | Meaning |
|---|---|
sql.Open + Ping |
Pool and connection test |
Query + rows.Next |
Reading many rows |
? / $1 |
Safe parameter |
Begin / Commit |
All-or-nothing work |