Dialect SQL Overview

Dialect SQL is used when the same application can run with different database drivers, but each driver needs its own SQL or CQL text.

This is common when supporting multiple databases such as:

• MySQL
• Postgres
• Scylla

The Go application code can stay mostly the same, while the selected driver decides which query text should be executed.

Basic Idea

A db.DialectSQL value stores query text for each supported driver.

type DialectSQL struct {
	Postgres   string `json:"postgres"`
	Mysql      string `json:"mysql"`
	Scylla     string `json:"scylla"`
}

At runtime, the active connection selects the correct query.

q, err := db.Dialect(conn, queries.GetUser, id)
if err != nil {
	return User{}, false, err
}

return db.GetQuery[User](ctx, conn, q)

Or use a direct dialect helper:

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

When to Use Dialect SQL

Use Dialect SQL when:

• your application can run with different database drivers
• SQL syntax differs between drivers
• placeholder styles differ between drivers
• inserts, limits, joins, or functions differ between engines
• Scylla uses a different query table than SQL databases
• queries are loaded from SQL model files
• you want shared Go code with driver-specific query text

For single-driver applications, direct helpers such as db.List, db.Get, db.Value, and db.Maps are usually simpler.

Available Helpers

Dialect helpers mirror the regular select helpers.

Direct Dialect Helpers

Use direct dialect helpers when you want to select and execute the query in one step.

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

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

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

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

This is usually the most convenient style when the query comes from a loaded model.

Low-Level Dialect Flow

Use the low-level flow when you want to inspect, pass, or modify the selected query object before execution.

q, err := db.Dialect(conn, queries.ListUsers, 10)
if err != nil {
	return nil, err
}

return db.ListQuery[User](ctx, conn, q)

This is equivalent to using:

return db.ListDialect[User](ctx, conn, queries.ListUsers, 10)

Query Model Struct

A common pattern is to store queries in a struct.

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

Each field contains query text for multiple drivers.

queries.GetUser.Mysql
queries.GetUser.Postgres
queries.GetUser.Scylla

Application code usually should not select those fields manually.

Instead, pass the db.DialectSQL value to a dialect helper.

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

Loading Queries From SQL Files

Dialect SQL is commonly used 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 selected driver decides which model file is loaded.

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

The SQL Files guide covers the model format and loading behavior in detail.

Example SQL Sections

A SQL model file is usually split into named sections.

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC
LIMIT ?

--GetUser
SELECT *
FROM users
WHERE id = ?
LIMIT 1

For PostgreSQL, the same logical queries use PostgreSQL syntax.

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC
LIMIT $1

--GetUser
SELECT *
FROM users
WHERE id = $1
LIMIT 1

For Scylla, the query may target a query table.

--GetUser
SELECT *
FROM users_by_id
WHERE id = ?
LIMIT 1

Driver Selection

The active connection chooses the query text.

If the selected query is empty, dialect helpers return an error.

This prevents accidentally executing an undefined query for the active driver.

Placeholder Differences

Dialect SQL does not rewrite placeholders.

Each driver query must use the placeholder syntax required by that driver.

Example:

db.GetDialect[User](ctx, conn, queries.GetUser, id)

The Go call is the same, but the selected query text is different for each driver.

Choosing the Right API

Recommended Usage

For most dialect SQL reads, use the direct helpers.

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

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

Use the low-level db.Dialect function when you want to create a db.Query first.

q, err := db.Dialect(conn, queries.GetUser, id)
if err != nil {
	return User{}, false, err
}

return db.GetQuery[User](ctx, conn, q)

Next Step

Continue with the dedicated Dialect SQL pages for complete examples of:

• ListDialect
• GetDialect
• ValueDialect
• MapsDialect