ValueDialect

Use db.ValueDialect to read a single scalar value using driver-specific SQL selected from db.DialectSQL.

Use db.ValueDialect when you want to read one scalar value from a db.DialectSQL query.

ValueDialect selects the correct SQL or CQL text for the active driver and reads one selected column from zero or one row.

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)
if err != nil {
	return 0, false, err
}

if !found {
	return 0, false, nil
}

Function

func ValueDialect[T any](ctx context.Context, c db.DialectQuerier, q db.DialectSQL, args ...any) (T, bool, error)

ValueDialect accepts:

a context.Context
a dialect-capable connection
a db.DialectSQL value
optional query arguments

It returns:

T, found, error

found is false when the query returns no rows.

When to Use ValueDialect

Use db.ValueDialect when:

the query returns exactly one selected column
you need a scalar result
the application may run with different database drivers
SQL syntax differs between drivers
placeholder styles differ between drivers
queries are loaded from SQL model files
you are reading a count, ID, flag, aggregate, or simple lookup value

For direct query strings, use db.Value.

For query objects, use db.ValueQuery.

Define Query Model

A query model stores one logical scalar query with driver-specific SQL.

type Queries struct {
	CountUsers db.DialectSQL `json:"CountUsers"`
}

Application code should usually pass the whole db.DialectSQL value to db.ValueDialect.

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)

SQL File Example

For MySQL, model.sql may contain:

--CountUsers
SELECT COUNT(*)
FROM users

For PostgreSQL, model.psql may contain:

--CountUsers
SELECT COUNT(*)
FROM users

For Scylla, model.cql may contain:

--CountUsers
SELECT COUNT(*)
FROM users_by_status
WHERE status = ?

PostgreSQL Returning Example

ValueDialect can also be used for PostgreSQL RETURNING queries.

For PostgreSQL, model.psql may contain:

--InsertUser
INSERT INTO users (name, email, active)
VALUES ($1, $2, $3)
RETURNING id

The matching query model can include:

type Queries struct {
	InsertUser db.DialectSQL `json:"InsertUser"`
}

Then call:

id, found, err := db.ValueDialect[int64](
	ctx,
	conn,
	queries.InsertUser,
	name,
	email,
	active,
)

if err != nil {
	return 0, err
}

if !found {
	return 0, errors.New("insert did not return id")
}

return id, nil

For MySQL inserts, use db.Insert and read result.LastInsertId() instead.

Load Queries

Load the query model with db.LoadModel.

func LoadQueries(conn db.Conn) (Queries, error) {
	var queries Queries

	if err := db.LoadModel(conn, ".", &queries); err != nil {
		return Queries{}, err
	}

	return queries, nil
}

The active driver decides which model file is loaded:

model.sql  -> MySQL
model.psql -> PostgreSQL
model.cql  -> Scylla

Complete Query Example

This example loads the query model and uses db.ValueDialect.

package main

import (
	"context"

	"github.com/netlifeguru/db"
)

type Queries struct {
	CountUsers db.DialectSQL `json:"CountUsers"`
}

func LoadQueries(conn db.Conn) (Queries, error) {
	var queries Queries

	if err := db.LoadModel(conn, ".", &queries); err != nil {
		return Queries{}, err
	}

	return queries, nil
}

func CountUsersDialect(ctx context.Context, conn db.Conn, queries Queries) (int64, bool, error) {
	return db.ValueDialect[int64](ctx, conn, queries.CountUsers)
}

Complete Usage Example

This example connects to the database, loads SQL from the active driver model, and prints a scalar value.

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()

	queries, err := LoadQueries(conn)
	if err != nil {
		log.Fatal(err)
	}

	total, found, err := CountUsersDialect(ctx, conn, queries)
	if err != nil {
		log.Fatal(err)
	}

	if !found {
		log.Println("count not found")
		return
	}

	fmt.Printf("users: %d\n", total)
}

Single Column Requirement

db.ValueDialect expects the selected query to return exactly one column.

Good:

SELECT COUNT(*)
FROM users

Good:

SELECT active
FROM users
WHERE id = ?

Not suitable:

SELECT id, email
FROM users
WHERE id = ?

For multiple columns, use db.GetDialect or db.ListDialect.

Empty Result

If the selected query returns no rows, db.ValueDialect returns found = false.

value, found, err := db.ValueDialect[string](ctx, conn, queries.GetEmail, id)
if err != nil {
	return "", false, err
}

if !found {
	return "", false, nil
}

Too Many Rows

db.ValueDialect expects zero or one row.

If the selected query returns more than one row, it returns an error.

Use a unique condition, aggregate query, or LIMIT 1 when appropriate.

Driver Selection

ValueDialect selects the query field based on the active driver.

Active driver	Selected query
MySQL	`queries.CountUsers.Mysql`
Postgres	`queries.CountUsers.Postgres`
Scylla	`queries.CountUsers.Scylla`

If the selected query is empty, ValueDialect returns an error.

Arguments

Arguments are passed to the selected query.

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers, status)

For MySQL, the selected query may use:

WHERE status = ?

For PostgreSQL:

WHERE status = $1

For Scylla:

WHERE status = ?

The Go call stays the same, but the SQL or CQL text must match the active driver.

Low-Level Equivalent

ValueDialect is a convenience helper.

This:

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)

is equivalent to:

q, err := db.Dialect(conn, queries.CountUsers)
if err != nil {
	return 0, false, err
}

total, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
	return 0, false, err
}

Use the low-level form when you need access to the selected db.Query.

Use db.ListDialect when the dialect query can return multiple typed rows.

users, err := db.ListDialect[User](ctx, conn, queries.ListUsers, 10)

Use db.GetDialect when the dialect query returns zero or one typed row.

user, found, err := db.GetDialect[User](ctx, conn, queries.GetUser, id)

Use db.MapsDialect when the dialect query returns dynamic map rows.

rows, err := db.MapsDialect(ctx, conn, queries.EventsReport, 10)

Standalone examples are available in the examples repository:

Dialect Value

Use db.ValueDialect when you want to read one scalar value from a db.DialectSQL query.

ValueDialect selects the correct SQL or CQL text for the active driver and reads one selected column from zero or one row.

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)
if err != nil {
	return 0, false, err
}

if !found {
	return 0, false, nil
}

Function

func ValueDialect[T any](ctx context.Context, c db.DialectQuerier, q db.DialectSQL, args ...any) (T, bool, error)

ValueDialect accepts:

a context.Context
a dialect-capable connection
a db.DialectSQL value
optional query arguments

It returns:

T, found, error

found is false when the query returns no rows.

When to Use ValueDialect

Use db.ValueDialect when:

the query returns exactly one selected column
you need a scalar result
the application may run with different database drivers
SQL syntax differs between drivers
placeholder styles differ between drivers
queries are loaded from SQL model files
you are reading a count, ID, flag, aggregate, or simple lookup value

For direct query strings, use db.Value.

For query objects, use db.ValueQuery.

Define Query Model

A query model stores one logical scalar query with driver-specific SQL.

type Queries struct {
	CountUsers db.DialectSQL `json:"CountUsers"`
}

Application code should usually pass the whole db.DialectSQL value to db.ValueDialect.

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)

SQL File Example

For MySQL, model.sql may contain:

--CountUsers
SELECT COUNT(*)
FROM users

For PostgreSQL, model.psql may contain:

--CountUsers
SELECT COUNT(*)
FROM users

For Scylla, model.cql may contain:

--CountUsers
SELECT COUNT(*)
FROM users_by_status
WHERE status = ?

PostgreSQL Returning Example

ValueDialect can also be used for PostgreSQL RETURNING queries.

For PostgreSQL, model.psql may contain:

--InsertUser
INSERT INTO users (name, email, active)
VALUES ($1, $2, $3)
RETURNING id

The matching query model can include:

type Queries struct {
	InsertUser db.DialectSQL `json:"InsertUser"`
}

Then call:

id, found, err := db.ValueDialect[int64](
	ctx,
	conn,
	queries.InsertUser,
	name,
	email,
	active,
)

if err != nil {
	return 0, err
}

if !found {
	return 0, errors.New("insert did not return id")
}

return id, nil

For MySQL inserts, use db.Insert and read result.LastInsertId() instead.

Load Queries

Load the query model with db.LoadModel.

func LoadQueries(conn db.Conn) (Queries, error) {
	var queries Queries

	if err := db.LoadModel(conn, ".", &queries); err != nil {
		return Queries{}, err
	}

	return queries, nil
}

The active driver decides which model file is loaded:

model.sql  -> MySQL
model.psql -> PostgreSQL
model.cql  -> Scylla

Complete Query Example

This example loads the query model and uses db.ValueDialect.

package main

import (
	"context"

	"github.com/netlifeguru/db"
)

type Queries struct {
	CountUsers db.DialectSQL `json:"CountUsers"`
}

func LoadQueries(conn db.Conn) (Queries, error) {
	var queries Queries

	if err := db.LoadModel(conn, ".", &queries); err != nil {
		return Queries{}, err
	}

	return queries, nil
}

func CountUsersDialect(ctx context.Context, conn db.Conn, queries Queries) (int64, bool, error) {
	return db.ValueDialect[int64](ctx, conn, queries.CountUsers)
}

Complete Usage Example

This example connects to the database, loads SQL from the active driver model, and prints a scalar value.

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()

	queries, err := LoadQueries(conn)
	if err != nil {
		log.Fatal(err)
	}

	total, found, err := CountUsersDialect(ctx, conn, queries)
	if err != nil {
		log.Fatal(err)
	}

	if !found {
		log.Println("count not found")
		return
	}

	fmt.Printf("users: %d\n", total)
}

Single Column Requirement

db.ValueDialect expects the selected query to return exactly one column.

Good:

SELECT COUNT(*)
FROM users

Good:

SELECT active
FROM users
WHERE id = ?

Not suitable:

SELECT id, email
FROM users
WHERE id = ?

For multiple columns, use db.GetDialect or db.ListDialect.

Empty Result

If the selected query returns no rows, db.ValueDialect returns found = false.

value, found, err := db.ValueDialect[string](ctx, conn, queries.GetEmail, id)
if err != nil {
	return "", false, err
}

if !found {
	return "", false, nil
}

Too Many Rows

db.ValueDialect expects zero or one row.

If the selected query returns more than one row, it returns an error.

Use a unique condition, aggregate query, or LIMIT 1 when appropriate.

Driver Selection

ValueDialect selects the query field based on the active driver.

Active driver	Selected query
MySQL	`queries.CountUsers.Mysql`
Postgres	`queries.CountUsers.Postgres`
Scylla	`queries.CountUsers.Scylla`

If the selected query is empty, ValueDialect returns an error.

Arguments

Arguments are passed to the selected query.

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers, status)

For MySQL, the selected query may use:

WHERE status = ?

For PostgreSQL:

WHERE status = $1

For Scylla:

WHERE status = ?

The Go call stays the same, but the SQL or CQL text must match the active driver.

Low-Level Equivalent

ValueDialect is a convenience helper.

This:

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)

is equivalent to:

q, err := db.Dialect(conn, queries.CountUsers)
if err != nil {
	return 0, false, err
}

total, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
	return 0, false, err
}

Use the low-level form when you need access to the selected db.Query.

Use db.ListDialect when the dialect query can return multiple typed rows.

users, err := db.ListDialect[User](ctx, conn, queries.ListUsers, 10)

Use db.GetDialect when the dialect query returns zero or one typed row.

user, found, err := db.GetDialect[User](ctx, conn, queries.GetUser, id)

Use db.MapsDialect when the dialect query returns dynamic map rows.

rows, err := db.MapsDialect(ctx, conn, queries.EventsReport, 10)

Standalone examples are available in the examples repository:

Dialect Value

ValueDialect

Function

When to Use ValueDialect

Define Query Model

SQL File Example

PostgreSQL Returning Example

Load Queries

Complete Query Example

Complete Usage Example

Single Column Requirement

Empty Result

Too Many Rows

Driver Selection

Arguments

Low-Level Equivalent

On this page

ValueDialect

Function

When to Use ValueDialect

Define Query Model

SQL File Example

PostgreSQL Returning Example

Load Queries

Complete Query Example

Complete Usage Example

Single Column Requirement

Empty Result

Too Many Rows

Driver Selection

Arguments

Low-Level Equivalent

On this page