Generating named queries and interfaces

[digitive]

With large project there will be huge amount of functions. It will be much better if we support named queries and interfaces. For example

-- name: Author.Get :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

-- name: Author.List :many
SELECT * FROM authors
ORDER BY name;

-- name: Book.ListByAuthor :many
SELECT * FROM books 
WHERE authorId = $1;

Will generate:

type AuthorQuerier interface {
    Get(ctx context.Context, id int) error
    List(ctx context.Context) ([]Author, error)
}
var _ AuthorQuerier = (*AuthorQueries)(nil)

type BookQuerier interface {
    ListByAuthor(ctx context.Context, authorId int) ([]Book, error)
}
var _ BookQuerier = (*BookQueries)(nil)
...
func NewAuthorQueries(db DBTX) *AuthorQueries {
    return &AuthorQueries{db: db}
}

type AuthorQueries struct {
    db DBTX
}
...

type BookQueries struct {
    db DBTX
}

func NewBookQueries(db DBTX) *BookQueries {...}

With this named Queries and interfaces support, I don't need to split queries into different packages like #710
in order to categorise queries.

[kyleconroy]

Can you explain why the different package approach doesn't work for you? I'd rather not more complexity to query names, especially features that may not map directly to other programming languages.

[digitive]

The main reason is that each generated package will contain a models.go, which will duplicate the same structure across different packages. for example, if there are 3 tables: authors, books and orders, it may end up 9 types Author type: authors.Author, books.Author, orders.Author Book type: author.Book, books.Book, orders.Book Order type: authors.Order, books.Order, orders.Order

[digitive]

Another reason is the current approach will easily generate one big interface with too many methods in it, which will be a pain to mock.

[fleipold]

A slightly different tack would be to have separate files with queries, where each file would result in a separate interface. The mapping could either be defined in the SQL source code with a meta-data comment "at file level" or as a mapping in the sqlc.yaml.

The generic interface name Queries indicates that this dimension isn't well used. So far we have gone for the multiple package approach and lived with the duplication.