sqlc-gen

README

sqlc-gen

sqlc-gen generates queries and supporting code for keyset pagination. Keyset pagination is generally much faster than the traditional offset/limit approach at the cost of more complicated queries. We require one query per keyset combination per direction and depending on whether we are on the first page or a subsequent page. This quickly spirals out of control and this is where people often reach for dynamic query builders or ORMs.

Unlike dynamic query builders or ORMs sqlc-gen approaches this problem through generated queries that are known at compile time. This has several nice properties:

• The generated queries are not a runtime surprise; and the one thing you don't want to surprise is the query planner.
• You'll only get the queries you see. Nothing else. With dynamic query builders or ORMs the space of possible queries is unbounded. With sqlc, and sqlc-gen by extension, it is limited to exactly what you see.
• Because we know the types of structs and their fields involved, we can generate supporting code to describe paginators and parsing and serializing paginators from or to URLs. There's no empty interfaces, no pointers, no type assertions. This makes working with the generated structs generally very pleasant.

Features

• Generates keyset pagination queries to be parsed by sqlc
• Generates code that safely parses URL queries into paginators or serializes them into query strings
• Provides structs that describe how a specific entity can be paginated allowing for easy rendering of UI controls
• Adds functions that take paginators and automatically select the correct query depending on the field to order by.

Assumptions

sqlc-gen assumes the following:

• The columns that need to be sorted by are known at compile time.
• Each table has a primary key of type bigint (go int64)
• The queries provided load only one entity; the returned rows are meant to be enriched through other queries.

Supported types

• timestamptz/time.Time
• bigint/int64
• text/string

Configuration

sqlc-gen requires a configuration file that tells it what queries to generate. The configuration consists of the following sections:

• project: high level settings, such as the module of the project or the root directory (if not specified the directory that has the configuration file is used).
• queries: settings regarding the generated queries
• paginators: where to place generated paginators and what module to use
• entities: elements to paginate

---
project:
  module: git.sr.ht/~ilikeorangutans/bks
paginators:
  package: model
  file: pkg/model/generated_paginators.go
queries: 
  file: db/generated_queries.sql
  package: pkg/dbmodel
entities:
  - name: Libraries
    table: libraries
    sqlc_type: dbmodel.Library
    joins:
      - user_libraries on libraries.id = user_libraries.library_id
    where:
      - user_libraries.user_id = sqlc.arg(user_id)
    default_sort_column: name
    default_direction: asc
    primary_key: id
    sort_keys:
      - name: name
        title: Name
        default_direction: asc
        type: string
    provided_values:
      - name: userID
        type: int64
  - name: Books
    table: books
    sqlc_type: dbmodel.Book
    primary_key: id
    joins:
      - user_libraries ul on ul.library_id = books.library_id
    where:
      - ul.library_id = ANY(sqlc.arg(library_ids)::bigint[])
    default_sort_column: sort
    default_direction: asc
    sort_keys:
      - name: sort
        title: Title
        default_direction: asc
        type: string
      - name: created_at
        title: Added
        default_direction: desc
        type: time.Time
        prefix: books
    provided_values:
      - name: libraryIDs
        type: '[]int64'
      - name: userID
        type: int64
        not_in_query: true

Pagination Types

Keyset pagination requires the system to provide a concept of where in the table it should look. This is called a paginator; it embeds the column to sort by, the order direction, how many rows to fetch, and the values needed to build the keyset. sqlc-gen generates a Paginator for each type which consists only of the fields needed. Further, it provides methods to safely parse Paginators from requests and functions that allow customizing paginators.

Usage