SQL Files

Load driver-specific SQL and CQL files into typed query models using db.LoadModel and db.DialectSQL.

The db package can load SQL or CQL queries from driver-specific files.

This is useful when you want to keep query text outside Go code while still using the shared db API.

SQL files are especially useful for:

larger queries
multi-driver applications
keeping SQL readable
separating query text from repository code
using different SQL syntax per driver
sharing one Go query model across MySQL, PostgreSQL, and Scylla

File Names

Each driver uses its own model file name.

Driver	File
MySQL	`model.sql`
Postgres	`model.psql`
Scylla	`model.cql`

The active connection decides which file is loaded.

For example, if the active connection is PostgreSQL, db.LoadModel loads:

model.psql

If the active connection is MySQL, it loads:

model.sql

If the active connection is Scylla, it loads:

model.cql

Query Sections

SQL files are split into named sections.

Each section starts with a line in this format:

--SectionName

Example:

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

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

--CountUsers
SELECT COUNT(*)
FROM users

The section name is used as the key in your Go query model.

Query Model

Define a Go struct that matches the section names.

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

Then load the queries 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 second argument is the directory where the model file is located.

db.LoadModel(conn, ".", &queries)

loads the active driver file from the current directory.

How Loaded Queries Are Stored

Each field is a db.DialectSQL.

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

When a MySQL connection loads model.sql, the MySQL field is filled.

queries.GetUser.Mysql

When a PostgreSQL connection loads model.psql, the PostgreSQL field is filled.

queries.GetUser.Postgres

When a Scylla connection loads model.cql, the Scylla field is filled.

queries.GetUser.Scylla

Most application code should not access these fields directly.

Prefer dialect helpers such as:

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

or low-level query selection:

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

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

MySQL File Example

model.sql

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

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

--CountUsers
SELECT COUNT(*)
FROM users

MySQL uses ? placeholders.

PostgreSQL File Example

model.psql

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

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

--CountUsers
SELECT COUNT(*)
FROM users

PostgreSQL uses numbered placeholders such as $1, $2, and $3.

Scylla File Example

model.cql

--ListUsers
SELECT * FROM users_by_created_at

--GetUser
SELECT * FROM users_by_id
WHERE id = ?

--CountUsers
SELECT count
FROM users_count
WHERE bucket = ?

Scylla uses ? placeholders and query-driven tables.

Complete Example

This example loads queries from the active driver file and uses shared dialect helpers.

package main

import (
	"context"
	"time"

	"github.com/netlifeguru/db"
)

type User struct {
	ID        any       `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

type Queries struct {
	ListUsers  db.DialectSQL `json:"ListUsers"`
	GetUser    db.DialectSQL `json:"GetUser"`
	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 ListUsers(ctx context.Context, conn db.Conn, queries Queries) ([]User, error) {
	return db.ListDialect[User](ctx, conn, queries.ListUsers)
}

func GetUser(ctx context.Context, conn db.Conn, queries Queries, id any) (User, bool, error) {
	return db.GetDialect[User](ctx, conn, queries.GetUser, id)
}

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

Low-Level Usage

Use db.Dialect when you want the selected query as a db.Query.

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

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

This is useful when:

you want to inspect the selected query
you want to log query metadata
you want to pass a db.Query to lower-level helpers
you are using ExecQuery, GetQuery, ValueQuery, or MapsQuery

Insert Queries

Insert queries can also be stored in SQL files.

The query shape may differ by driver.

MySQL model.sql:

--InsertUser
INSERT INTO users (name, email, active)
VALUES (?, ?, ?)

PostgreSQL model.psql:

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

Scylla model.cql:

--InsertUserByID
INSERT INTO users_by_id (id, email, name, active, created_at)
VALUES (?, ?, ?, ?, ?)

For MySQL:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return 0, err
}

result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
	return 0, err
}

return result.LastInsertId(), nil

For PostgreSQL:

q, err := db.Dialect(conn, queries.InsertUser, 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

For Scylla, generate the ID in application code and pass it into the selected query.

id := gocql.TimeUUID()
createdAt := time.Now().UTC()

q, err := db.Dialect(conn, queries.InsertUserByID, id, email, name, active, createdAt)
if err != nil {
	return "", err
}

if _, err := db.ExecQuery(ctx, conn, q); err != nil {
	return "", err
}

return id.String(), nil

Section Names

Section names must match the json tags in your query model.

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

SQL file:

--ListUsers
SELECT * FROM users

If the section name does not match, the field will not be populated.

Invalid SQL

When loading a model file, the active driver analyzes each SQL section.

If a section contains invalid syntax for the driver’s placeholder or string parsing rules, db.LoadModel returns an error.

Examples include:

unterminated single-quoted strings
unterminated double-quoted identifiers
unterminated block comments
invalid PostgreSQL placeholders
unterminated PostgreSQL dollar-quoted strings

This helps catch invalid SQL files during startup instead of later during runtime.

When to Use SQL Files

Use SQL files when:

queries are large
queries are easier to read outside Go code
you support multiple database drivers
you want the same section names across different SQL dialects
you want startup-time query loading
you want repository code to use db.DialectSQL

When Not to Use SQL Files

Do not use SQL files when simple inline queries are clearer.

For example:

users, err := db.List[User](ctx, conn, `
	SELECT * FROM users
`)

For small single-driver applications, inline SQL can be easier to maintain.

Use SQL files when they reduce duplication or improve clarity.

Recommended Pattern

Load queries during application startup.

queries, err := LoadQueries(conn)
if err != nil {
	return err
}

Pass the loaded query model into repositories or services.

users, err := ListUsers(ctx, conn, queries)

Keep repository code focused on execution:

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

Keep driver-specific SQL in:

model.sql
model.psql
model.cql

A standalone example is available in the examples repository:

PostgreSQL multi-driver SQL files

The db package can load SQL or CQL queries from driver-specific files.

This is useful when you want to keep query text outside Go code while still using the shared db API.

SQL files are especially useful for:

larger queries
multi-driver applications
keeping SQL readable
separating query text from repository code
using different SQL syntax per driver
sharing one Go query model across MySQL, PostgreSQL, and Scylla

File Names

Each driver uses its own model file name.

Driver	File
MySQL	`model.sql`
Postgres	`model.psql`
Scylla	`model.cql`

The active connection decides which file is loaded.

For example, if the active connection is PostgreSQL, db.LoadModel loads:

model.psql

If the active connection is MySQL, it loads:

model.sql

If the active connection is Scylla, it loads:

model.cql

Query Sections

SQL files are split into named sections.

Each section starts with a line in this format:

--SectionName

Example:

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

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

--CountUsers
SELECT COUNT(*)
FROM users

The section name is used as the key in your Go query model.

Query Model

Define a Go struct that matches the section names.

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

Then load the queries 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 second argument is the directory where the model file is located.

db.LoadModel(conn, ".", &queries)

loads the active driver file from the current directory.

How Loaded Queries Are Stored

Each field is a db.DialectSQL.

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

When a MySQL connection loads model.sql, the MySQL field is filled.

queries.GetUser.Mysql

When a PostgreSQL connection loads model.psql, the PostgreSQL field is filled.

queries.GetUser.Postgres

When a Scylla connection loads model.cql, the Scylla field is filled.

queries.GetUser.Scylla

Most application code should not access these fields directly.

Prefer dialect helpers such as:

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

or low-level query selection:

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

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

MySQL File Example

model.sql

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

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

--CountUsers
SELECT COUNT(*)
FROM users

MySQL uses ? placeholders.

PostgreSQL File Example

model.psql

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

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

--CountUsers
SELECT COUNT(*)
FROM users

PostgreSQL uses numbered placeholders such as $1, $2, and $3.

Scylla File Example

model.cql

--ListUsers
SELECT * FROM users_by_created_at

--GetUser
SELECT * FROM users_by_id
WHERE id = ?

--CountUsers
SELECT count
FROM users_count
WHERE bucket = ?

Scylla uses ? placeholders and query-driven tables.

Complete Example

This example loads queries from the active driver file and uses shared dialect helpers.

package main

import (
	"context"
	"time"

	"github.com/netlifeguru/db"
)

type User struct {
	ID        any       `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

type Queries struct {
	ListUsers  db.DialectSQL `json:"ListUsers"`
	GetUser    db.DialectSQL `json:"GetUser"`
	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 ListUsers(ctx context.Context, conn db.Conn, queries Queries) ([]User, error) {
	return db.ListDialect[User](ctx, conn, queries.ListUsers)
}

func GetUser(ctx context.Context, conn db.Conn, queries Queries, id any) (User, bool, error) {
	return db.GetDialect[User](ctx, conn, queries.GetUser, id)
}

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

Low-Level Usage

Use db.Dialect when you want the selected query as a db.Query.

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

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

This is useful when:

you want to inspect the selected query
you want to log query metadata
you want to pass a db.Query to lower-level helpers
you are using ExecQuery, GetQuery, ValueQuery, or MapsQuery

Insert Queries

Insert queries can also be stored in SQL files.

The query shape may differ by driver.

MySQL model.sql:

--InsertUser
INSERT INTO users (name, email, active)
VALUES (?, ?, ?)

PostgreSQL model.psql:

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

Scylla model.cql:

--InsertUserByID
INSERT INTO users_by_id (id, email, name, active, created_at)
VALUES (?, ?, ?, ?, ?)

For MySQL:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return 0, err
}

result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
	return 0, err
}

return result.LastInsertId(), nil

For PostgreSQL:

q, err := db.Dialect(conn, queries.InsertUser, 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

For Scylla, generate the ID in application code and pass it into the selected query.

id := gocql.TimeUUID()
createdAt := time.Now().UTC()

q, err := db.Dialect(conn, queries.InsertUserByID, id, email, name, active, createdAt)
if err != nil {
	return "", err
}

if _, err := db.ExecQuery(ctx, conn, q); err != nil {
	return "", err
}

return id.String(), nil

Section Names

Section names must match the json tags in your query model.

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

SQL file:

--ListUsers
SELECT * FROM users

If the section name does not match, the field will not be populated.

Invalid SQL

When loading a model file, the active driver analyzes each SQL section.

If a section contains invalid syntax for the driver’s placeholder or string parsing rules, db.LoadModel returns an error.

Examples include:

unterminated single-quoted strings
unterminated double-quoted identifiers
unterminated block comments
invalid PostgreSQL placeholders
unterminated PostgreSQL dollar-quoted strings

This helps catch invalid SQL files during startup instead of later during runtime.

When to Use SQL Files

Use SQL files when:

queries are large
queries are easier to read outside Go code
you support multiple database drivers
you want the same section names across different SQL dialects
you want startup-time query loading
you want repository code to use db.DialectSQL

When Not to Use SQL Files

Do not use SQL files when simple inline queries are clearer.

For example:

users, err := db.List[User](ctx, conn, `
	SELECT * FROM users
`)

For small single-driver applications, inline SQL can be easier to maintain.

Use SQL files when they reduce duplication or improve clarity.

Recommended Pattern

Load queries during application startup.

queries, err := LoadQueries(conn)
if err != nil {
	return err
}

Pass the loaded query model into repositories or services.

users, err := ListUsers(ctx, conn, queries)

Keep repository code focused on execution:

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

Keep driver-specific SQL in:

model.sql
model.psql
model.cql

A standalone example is available in the examples repository:

PostgreSQL multi-driver SQL files

SQL Files

File Names

Query Sections

Query Model

How Loaded Queries Are Stored

MySQL File Example

PostgreSQL File Example

Scylla File Example

Complete Example

Low-Level Usage

Insert Queries

Section Names

Invalid SQL

When to Use SQL Files

When Not to Use SQL Files

Recommended Pattern

On this page

SQL Files

File Names

Query Sections

Query Model

How Loaded Queries Are Stored

MySQL File Example

PostgreSQL File Example

Scylla File Example

Complete Example

Low-Level Usage

Insert Queries

Section Names

Invalid SQL

When to Use SQL Files

When Not to Use SQL Files

Recommended Pattern

On this page