Query templates

[kyleconroy]

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
}

[inconshreveable]

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:

const columns = `
 concat('prefix_', id) as "id",
 type,
 account_id,
 created_at,
 description,
 metadata,
 ...

[daaku]

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]

I haven't made any progress on this design, so I'm going to close out this issue for now.