ValueQuery
Use db.ValueQuery to read a single scalar value from a prepared db.Query value.
Use db.ValueQuery when you already have a db.Query value and expect one scalar value.
It reads one selected column from zero or one row.
total, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
return 0, false, err
}
if !found {
return 0, false, nil
}ValueQuery is the query-object version of db.Value.
Function
func ValueQuery[T any](ctx context.Context, c db.Querier, q db.Query) (T, bool, error)ValueQuery accepts:
- a
context.Context - a connection or query-capable value
- a
db.Query
It returns:
T, found, errorfound is false when the query returns no rows.
When to Use ValueQuery
Use db.ValueQuery when:
- you already created a
db.Query - the query returns exactly one selected column
- you need a scalar value
- query creation and query execution are separate steps
- the query was created by
db.Rawordb.Dialect - you are reading a count, ID, flag, aggregate, or simple lookup value
For direct query strings, use db.Value.
Create a Query Object
Create a db.Query using db.Raw.
For MySQL and Scylla, use ? placeholders.
const countUsersQuery = `
SELECT COUNT(*)
FROM users
WHERE active = ?
`q, err := db.Raw(countUsersQuery, true)
if err != nil {
return 0, false, err
}For PostgreSQL, the query uses numbered placeholders.
const countUsersQuery = `
SELECT COUNT(*)
FROM users
WHERE active = $1
`The Go code stays the same:
q, err := db.Raw(countUsersQuery, true)
if err != nil {
return 0, false, err
}Execute the Query Object
Pass the query object into db.ValueQuery.
total, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
return 0, false, err
}
if !found {
return 0, false, nil
}
return total, true, nilComplete Query Example
This example creates a db.Query and reads the number of active users.
package main
import (
"context"
"github.com/netlifeguru/db"
)
const countActiveUsersQuery = `
SELECT COUNT(*)
FROM users
WHERE active = ?
`
func CountActiveUsersQuery(ctx context.Context, conn db.Conn) (int64, bool, error) {
q, err := db.Raw(countActiveUsersQuery, true)
if err != nil {
return 0, false, err
}
return db.ValueQuery[int64](ctx, conn, q)
}Complete Usage Example
This example connects to the database, executes the query-object helper, and prints the result.
package main
import (
"context"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
"github.com/joho/godotenv"
)
func main() {
err := godotenv.Load()
if err != nil {
log.Println(".env file not found, I'm using system env variables")
}
conn, err := connectDB()
if err != nil {
log.Fatal(err)
}
ctx := context.Background()
total, found, err := CountActiveUsersQuery(ctx, conn)
if err != nil {
log.Fatal(err)
}
if !found {
log.Println("count not found")
return
}
fmt.Printf("active users: %d\n", total)
}PostgreSQL Returning Example
ValueQuery can also be used with PostgreSQL RETURNING.
const insertUserQuery = `
INSERT INTO users (name, email, active)
VALUES ($1, $2, $3)
RETURNING id
`
func InsertUserQuery(ctx context.Context, conn db.Conn, name string, email string, active bool) (int64, error) {
q, err := db.Raw(insertUserQuery, name, email, active)
if err != nil {
return 0, err
}
id, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
return 0, err
}
if !found {
return 0, errors.New("insert did not return id")
}
return id, nil
}Single Column Requirement
db.ValueQuery expects the query result to contain exactly one selected column.
Good:
SELECT COUNT(*) FROM usersGood:
SELECT email FROM users WHERE id = ?Not suitable:
SELECT id, email FROM users WHERE id = ?For multiple columns, use db.GetQuery or db.ListQuery.
Empty Result
If the query returns no rows, db.ValueQuery returns found = false.
value, found, err := db.ValueQuery[string](ctx, conn, q)
if err != nil {
return "", false, err
}
if !found {
return "", false, nil
}Too Many Rows
db.ValueQuery expects zero or one row.
If the query returns more than one row, it returns an error.
Use a unique condition, aggregate query, or LIMIT 1 when appropriate.
Raw Query Validation
db.Raw returns an error when the query string is empty.
q, err := db.Raw("")
if err != nil {
return 0, false, err
}This prevents empty query objects from being executed accidentally.
Query Objects From Dialect SQL
ValueQuery is often used together with db.Dialect.
q, err := db.Dialect(conn, queries.CountUsers)
if err != nil {
return 0, false, err
}
return db.ValueQuery[int64](ctx, conn, q)For direct dialect usage, you can also use db.ValueDialect.
total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)Related Helpers
Use db.Value when you want to pass the query string directly.
total, found, err := db.Value[int64](ctx, conn, countUsersQuery, true)Use db.ListQuery when the query object can return multiple typed rows.
users, err := db.ListQuery[User](ctx, conn, q)Use db.GetQuery when the query object returns zero or one typed row.
user, found, err := db.GetQuery[User](ctx, conn, q)Use db.MapsQuery when the query object returns dynamic map rows.
rows, err := db.MapsQuery(ctx, conn, q)Related Examples
Standalone examples are available in the examples repository: