querygen: CLI + tiny library for readable SQL queries

querygen enables writing parameterized SQL queries using string interpolation-like syntax. Since Go lacks features like macros, quasiquotes or native string interpolation, we resort to heinous crimes codegen.

Usage

Write a SQL query in your Go code using interpolation-like syntax:

package cakes // cakes.go

const partyAttendeesQuery = `
SELECT person_name
FROM party_attendees
WHERE party = {{partyId : int}}
`

const bestChoiceCakeQuery = `
WITH attendees AS (` + partyAttendeesQuery + `)

SELECT fave_cakes.cake_type
FROM attendees JOIN fave_cakes ON attendees.person_name = fave_cakes.person_name
-- Need to allow host to exclude one cake they don't like
WHERE fave_cake.cake_type != {{excludedCakeType : string}}
GROUP BY fave_cake.cake_type 
ORDER BY COUNT(fave_cake.cake_type) DESC
LIMIT 1
`

Get the querygen CLI and run it on the package.

go install github.com/sourcegraph/querygen/cmd/querygen@latest
querygen ./...

This will generate a file next to the original file:

// Code generated by querygen.
// You may only edit import statements.
package cakes // cakes_query_gen.go

import (
	"github.com/sourcegraph/querygen/lib/interpolate"
)

type partyAttendeesQueryVars struct {
	partyId int
}

var _ interpolate.QueryVars = &partyAttendeesQueryVars{}

// methods omitted...

type bestChoiceCakeQueryVars struct {
	partyId          int
	excludedCakeType string
}

var _ interpolate.QueryVars = &bestChoiceCakeQueryVars{}

// methods omitted...

You can use these structs with interpolate.Do(myQuery, &myQueryVars{...}) function to generate a *sqlf.Query which can then be executed. The interpolate.Do function replaces the sqlf.Sprintf function.

For more complex usage, see Reference.md.

Motivation and Comparison

The Sourcegraph monorepo largely uses the sqlf library for constructing SQL queries. querygen sits on top of sqlf to enable:

1. Incrementally improving the readability of existing queries
2. Add compile-time checking for names and number of arguments

without having to switch to an alternate SQL driver.

Tool/Library	sqlf	querygen	pgx	sqlc
Operation	Run-time	~Compile-time	Run-time	~Compile-time
Named parameters in query syntax	❌	✅	✅	✅
Static parameter name checking	❌	✅	❌	✅
Static binding var count checking (†)	❌	✅	❌	✅
Arbitrary dynamic query fragments	✅	✅	✅	❌
Static query validation	❌	❌	❌	✅
Statically checked row scanning	❌	❌	❌	✅

(†) Caveat: Static binding var count checking requires using the exhaustruct linter or similar for the types generated for query variables.

We could probably use sqlc for common cases, but the Sourcegraph codebase has some complex dynamically assembled queries which cannot be statically analyzed by sqlc. So those would need to be either re-written to fit into sqlc's model, or you'd need some sophisticated abstract interpretation to extract all possible queries and analyze them individually.

Contributing

See Development.md for build instructions etc.

At the moment, this is made primarily for Sourcegraph's internal use. Usage outside Sourcegraph is not supported.