Repository navigation
Query templates #112
Description
A sqlc user asked how it would be possible to share a set of select expressions across queries. There isn't a way to do this in the SQL standard, so I came up with the following idea. A given query would be marked as a template. This query must be a certain shape, specifically a select from a single table with no additional clauses. You could then refer to that template from subsequent queries to that same table.
Here's an example using the setup from the README
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);
-- template: named_author
SELECT id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
FROM authors;
-- name: GetAuthor :one
SELECT sqlc.named_author.* FROM authors
WHERE id = $1 LIMIT 1;
-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
$1, $2
)
RETURNING sqlc.named_author.*;package db
import (
"context"
"database/sql"
)
type NamedAuthor struct {
ID int64
Fullname string
BioDefault string
One int64
}
const createAuthor = `-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
$1, $2
)
RETURNING id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
`
type CreateAuthorParams struct {
Name string
Bio sql.NullString
}
func (q *Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (NamedAuthor, error) {
row := q.db.QueryRowContext(ctx, createAuthor, arg.Name, arg.Bio)
var i NamedAuthor
err := row.Scan(&i.ID, &i.Fullname, &i.BioDefault, &i.One)
return i, err
}
const getAuthor = `-- name: GetAuthor :one
SELECT id, lower(name) as fullname, coalese(bio, 'none') as bio_default, 1 as one
FROM authors
WHERE id = $1 LIMIT 1
`
func (q *Queries) GetAuthor(ctx context.Context, id int64) (NamedAuthor, error) {
row := q.db.QueryRowContext(ctx, getAuthor, id)
var i NamedAuthor
err := row.Scan(&i.ID, &i.Fullname, &i.BioDefault, &i.One)
return i, err
}
Activity
inconshreveable commentedon Oct 25, 2019
An example of what this looks like for us are queries with computed columns. One real example is where a table has many columns and a single one with a prefix appended to it:
daaku commentedon Dec 13, 2019
IMHO, there are two aspects to this.
First is to DRY-up the SQL so the select expressions don't need to be repeated (and additionally kept consistent across queries). This obviously has implications, a big one being that the queries are no longer real.
Second is the ability to share the return types on the generated Go code side. By this I mean that if I have two queries with the same select expression, only generate one shared return type for them. Using some sort of hashing of the select expression and table name should be able to allow this independent of the first concern and would be a great improvement in itself. The question of what/how to name this type is not obvious though.
kyleconroy commentedon Aug 17, 2021
I haven't made any progress on this design, so I'm going to close out this issue for now.