sqlc-gen-queries

A CLI tool that generates sqlc-compatible SQL queries from your database schema catalog. Point it at a schema catalog and a configuration file, and it produces ready-to-use query files for sqlc.

Features

• Generate CRUD queries (SELECT, INSERT, UPDATE, DELETE) from a database schema catalog
• Primary key CRUD and FK-based List operations generated by default — no configuration needed
• Fine-grained control over which queries and tables are generated via include/exclude lists
• Configurable via YAML — shares the same sqlc.yaml configuration file
• Works as a standalone CLI or as part of a CI/CD pipeline
• Supports custom query templates

Installation

go install github.com/sqlc-contrib/sqlc-gen-queries/cmd/sqlc-gen-queries@latest

Schema Catalog

The --catalog-file (schema.json by default) is a JSON representation of your database schema. Generate it using Atlas, an open-source schema management tool:

atlas schema inspect \
  --url "postgres://user:pass@localhost:5432/mydb?sslmode=disable" \
  --format '{{ json . }}' > schema.json

This produces a JSON catalog containing tables, columns, indexes, primary keys, foreign keys, and other schema metadata that sqlc-gen-queries uses to generate queries.

Configuration

sqlc-gen-queries reads configuration from the same sqlc.yaml file used by sqlc. To share the config file without sqlc rejecting unknown keys, register a dummy plugin with true as the command:

version: "2"
plugins:
  - name: gen-queries
    process:
      cmd: "true"
sql:
  - schema: "schema/migration"
    queries: "ent/query"
    engine: "postgresql"
    codegen:
      - plugin: gen-queries
        out: "ent/query"
        options:
          tables:
            include:
              - "users"
              - "auth.accounts"
            exclude:
              - "audit_logs"
              - "auth.sessions"
          queries:
            include:
              - "CopyUsers"
            exclude:
              - "DeleteUser"

Use options.tables to control which tables get query files. Entries may be table names (audit_logs) or schema-qualified table names (auth.sessions).

• include is an allow-list: when non-empty, only the listed tables are generated. When omitted or empty, every table is generated.
• exclude is a deny-list that always takes precedence over include, so a table present in both lists is skipped.

Use options.queries to control which queries are generated per table. Entries are query names (e.g. GetUser, ListPostsByTitle).

• The default query set (below) is always generated unless a query is excluded.
• include adds opt-in queries (see Opt-in queries) on top of the default set.
• exclude removes queries from what would otherwise be generated — including defaults (e.g. dropping DeleteUser) — and always takes precedence over include.

Note: options.queries is an object (include/exclude). The older flat list form (queries: ["CopyUsers"]) is no longer supported — move those entries under queries.include.

Default queries (always generated)

Primary key CRUD operations, List queries (including FK-index-based list queries), and their Exec/Batch variants are generated automatically for every table:

Query	Description
Get<Table>	Select a row by primary key
BatchGet<Tables>	Batch select rows by primary key
List<Tables>	Paginated list with filtering
List<Tables>By<Columns>	Paginated list by foreign key index
Insert<Table>	Insert a row
ExecInsert<Table>	Insert a row (exec, returns affected rows)
BatchInsert<Tables>	Batch insert rows
BatchExecInsert<Tables>	Batch insert rows (exec)
Update<Table>	Update a row by primary key
ExecUpdate<Table>	Update a row by primary key (exec)
BatchUpdate<Tables>	Batch update rows by primary key
BatchExecUpdate<Tables>	Batch update rows by primary key (exec)
Delete<Table>	Delete a row by primary key
ExecDelete<Table>	Delete a row by primary key (exec)
BatchDelete<Tables>	Batch delete rows by primary key
BatchExecDelete<Tables>	Batch delete rows by primary key (exec)

Opt-in queries

These queries are not part of the default set — they are only generated when explicitly listed in options.queries.include:

Query	Description
Copy<Tables>	Bulk insert via PostgreSQL COPY protocol
Get<Table>With<Related>	Select with FK join
BatchGet<Tables>With<Related>	Batch select with FK join
Get<Table>By<Columns>	Select by non-PK unique index
List<Tables>By<Columns>	Paginated list by non-FK non-unique index
Update<Tables>By<Columns>	Update by non-unique index
Delete<Tables>By<Columns>	Delete by non-unique index

All opt-in queries also have their Exec/Batch/BatchExec variants available.

Usage

Run sqlc-gen-queries before sqlc generate so that the generated .sql query files are available for sqlc to produce Go code:

sqlc-gen-queries --config-file sqlc.yaml --catalog-file schema.json
sqlc generate

Flag	Environment Variable	Default	Description
--config-file	SQLC_CONFIG_FILE	sqlc.yaml	Path to the sqlc configuration file
--catalog-file	SQLC_CATALOG_FILE	schema.json	Path to the catalog file

Contributing

Contributions are welcome! Please open an issue or pull request.

To set up a development environment with Nix:

nix develop
go test ./...

License

MIT