queries

README

Queries

The queries library for Go implements a file based SQL query management system. Only PostgreSQL is supported.

Installing

  go get -u github.com/boringsql/queries

Usage

Writing and using SQL code in Go code can be inneficient for several reasons. Not only it diminishes the advantages of a statically typed language, it also worsens editor support for syntax highlighting and indentation, and hinders query reusability with other tools. The queries library has been created to address these issues by providing a better way to manage and use SQL queries in Go.

With queries you define your SQL code in files like sql/users.sql

-- name: get-user-by-id
SELECT *
FROM users 
WHERE user_id = :user_id AND deleted_at is null

-- name: update-user-last-login
UPDATE users
SET last_login_at = current_timestamp
WHERE user_id = :user_id

Load all the respective files into QueryStore using three different ways

err = queryStore.LoadFromFile("sql/users.sql")
if err != nil {
  return err
}

err = queryStore.LoadFromDir("sql/")
if err != nil {
  return err
}

//go:embed sql/*
var sqlFS embed.FS
err = queryStore.LoadFromEmbed("sql/")
if err != nil {
  return err
}

Once you get the query loaded you can access them by their name and prepare the named parameter mapping

getUser := queryStore.MustHaveQuery("get-user-by-id")
args := getUser.Prepare(map[string]interface{}{
  "user_id": 123,
})

err = db.Get(&user, getUser.Query(), args...)
if err != nil {
  return err
}

Query format

The recommended use of the queries library is to switch from the default positional parameter notation ($1, $2, etc. - dollar quited sign followed by the parameter position) to psql variable definition. For interoperability with sqlc it also supports @param_name format.

The benefit of the variable definition is better visual control. Other aspect is the inter-operability with other PostgreSQL tools. Notably regresql.

If you prefer the default dolar sign positional parameters, you can skip the argument preparation (queryStore.Prepare) and use the query.Raw.

Parsing and Comments

Queries are identified by -- name: directives. Comments before the directive are skipped. If no name is specified, the file name is used.

-- This comment is skipped
-- name: get-users
SELECT * FROM users WHERE id = :id

Multiple queries can be defined in a single file by using multiple -- name: directives.

Parameters (:param or $1) are detected from SQL code only. Parameters in comments are ignored.

-- name: example
-- :fake_param in comments is ignored
SELECT * FROM users WHERE id = :real_param  -- :another_fake is also ignored

Named parameters are converted to positional notation: :name becomes $1, :email becomes $2, etc.

Query Metadata

You can add metadata to your queries using -- key: value format. Metadata provides a way to attach additional information to queries, such as descriptions, performance requirements, or custom tags.

-- name: get-user-by-email
-- description: Retrieve user by email efficiently
-- max-cost: 100
-- required-nodes: Index Scan
-- timeout: 50ms
SELECT id, name, email
FROM users
WHERE email = :email

Metadata Key Format

• Keys must start with a letter and can contain letters, numbers, underscores, and hyphens
• Keys are case-insensitive (automatically normalized to lowercase)
• The name key is reserved for query naming and not stored as metadata
• Values preserve their original formatting and can contain any characters

Accessing Metadata

Once loaded, you can access metadata in two ways:

query := queryStore.MustHaveQuery("get-user-by-email")

// Direct access to the Metadata map
description := query.Metadata["description"]

// Using the GetMetadata method (case-insensitive key lookup)
if timeout, ok := query.GetMetadata("timeout"); ok {
    fmt.Printf("Query timeout: %s\n", timeout)
}

// Access with any case
if cost, ok := query.GetMetadata("MAX-COST"); ok {
    fmt.Printf("Max cost: %s\n", cost)
}

Common Use Cases

Metadata can be useful for:

• Documentation: Add descriptions and usage notes
• Performance monitoring: Track expected costs, timeouts, and required query plans
• Query categorization: Tag queries by feature, team, or purpose
• Authorization: Store required permissions or roles
• Versioning: Track query versions or migration information

Example with multiple metadata fields:

-- name: complex-analytics-query
-- description: Daily revenue calculation for reporting dashboard
-- author: analytics-team
-- version: 2.1
-- tags: reporting, revenue, daily
-- max-execution-time: 5s
-- cache-ttl: 3600
SELECT
    date_trunc('day', created_at) as day,
    sum(amount) as revenue
FROM orders
WHERE created_at >= :start_date
GROUP BY day
ORDER BY day DESC

Credits

The queries library is heavily influenced (and in some cases re-uses part of the logic) by

• regresql
• yesql

Documentation

Overview

Credits to https://github.com/gchaincl/dotsql

Index

• type Query
  • func NewQuery(name, path, query string, metadata map[string]string) (*Query, error)
  • func (q *Query) GetMetadata(key string) (string, bool)
  • func (q *Query) HasSection(tag string) bool
  • func (q *Query) Prepare(args map[string]interface{}) []interface{}
  • func (q *Query) Query() string
  • func (q *Query) RawQuery() string
  • func (q *Query) Section(tag string) string
  • func (q *Query) SectionNames() []string
  • func (q *Query) SectionQuery(tag string) *Query
• type QueryStore
  • func NewQueryStore() *QueryStore
  • func (s *QueryStore) LoadFromDir(path string) error
  • func (qs *QueryStore) LoadFromEmbed(sqlFS embed.FS, path string) error
  • func (s *QueryStore) LoadFromFile(fileName string) (err error)
  • func (s *QueryStore) MustHaveQuery(name string) *Query
  • func (s *QueryStore) Queries() map[string]*Query
  • func (s *QueryStore) Query(name string) (*Query, error)
  • func (s *QueryStore) QueryNames() []string
• type ScannedQuery
• type Scanner
  • func (s *Scanner) Run(fileName string, io *bufio.Scanner) map[string]*ScannedQuery

Types

type Query

type Query struct {
	Name         string
	Path         string
	Raw          string
	OrdinalQuery string
	Mapping      map[string]int
	Args         []string
	NamedArgs    []sql.NamedArg
	Metadata     map[string]string
	// contains filtered or unexported fields
}

func NewQuery

func NewQuery(name, path, query string, metadata map[string]string) (*Query, error)

func (*Query) GetMetadata

func (q *Query) GetMetadata(key string) (string, bool)

GetMetadata retrieves a metadata value by key

func (*Query) HasSection

func (q *Query) HasSection(tag string) bool

HasSection reports whether the query contains a non-empty @tag section.

func (*Query) Prepare

func (q *Query) Prepare(args map[string]interface{}) []interface{}

Prepare the arguments for the ordinal query. Missing arguments will be returned as nil

func (*Query) Query

func (q *Query) Query() string

Query returns ordinal query

func (*Query) RawQuery

func (q *Query) RawQuery() string

func (*Query) Section

func (q *Query) Section(tag string) string

Section returns raw content for a @tag section.

func (*Query) SectionNames

func (q *Query) SectionNames() []string

SectionNames returns @tag section names in order of appearance.

func (*Query) SectionQuery

func (q *Query) SectionQuery(tag string) *Query

SectionQuery returns a parsed Query for a @tag section, with its own Args and OrdinalQuery.

type QueryStore

type QueryStore struct {
	// contains filtered or unexported fields
}

func NewQueryStore

func NewQueryStore() *QueryStore

NewQueryStore setups new query store

func (*QueryStore) LoadFromDir

func (s *QueryStore) LoadFromDir(path string) error

func (*QueryStore) LoadFromEmbed

func (qs *QueryStore) LoadFromEmbed(sqlFS embed.FS, path string) error

func (*QueryStore) LoadFromFile

func (s *QueryStore) LoadFromFile(fileName string) (err error)

LoadFromFile loads query/queries from specified file

func (*QueryStore) MustHaveQuery

func (s *QueryStore) MustHaveQuery(name string) *Query

MustHaveQuery returns query or panics on error

func (*QueryStore) Queries

func (s *QueryStore) Queries() map[string]*Query

Queries returns all queries in the store as a map

func (*QueryStore) Query

func (s *QueryStore) Query(name string) (*Query, error)

Query retrieve query by given name

func (*QueryStore) QueryNames

func (s *QueryStore) QueryNames() []string

QueryNames returns a sorted list of all query names in the store

type ScannedQuery

type ScannedQuery struct {
	Query    string
	Metadata map[string]string
}

type Scanner

type Scanner struct {
	// contains filtered or unexported fields
}

func (*Scanner) Run

func (s *Scanner) Run(fileName string, io *bufio.Scanner) map[string]*ScannedQuery