FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

feat: add JSON-shaped result directives for pgx/v5 · sqlc-dev/sqlc@fe89bbd · GitHub

/ sqlc Public

Commit fe89bbd

Browse files
m-ali-akbay
and
claude
committed
feat: add JSON-shaped result directives for pgx/v5
Add two directives that return JSON-encoded results decoded directly into Go structs by pgx/v5, including nested objects and arrays from a single query: - sqlc.jsonb_build_object."Name"(key, value, ...) builds an arbitrary named struct from key/value pairs. - sqlc.embed.jsonb(table) builds a struct mirroring a whole row, as a shorthand that composes inside ARRAY(...) for slices of rows. Both are rewritten to real Postgres functions (jsonb_build_object / to_jsonb) before analysis; pgx/v5 decodes the jsonb value into a plain struct (or slice) with no Scan/Value plumbing. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
1 parent e209d86 commit fe89bbd

41 files changed

Lines changed: 9080 additions & 2734 deletions

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎docs/howto/embedding.md‎

Lines changed: 133 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -58,4 +58,136 @@ type ScoreAndTestsRow struct {
5858
Student Student
5959
TestScore TestScore
6060
}
61-
```
61+
```
62+
63+
#### Nested JSON objects and arrays
64+
65+
`sqlc.jsonb_build_object."Name"(key, value, key, value, ...)` builds a named
66+
Go struct from an inline JSON shape — a typed wrapper around Postgres's
67+
`jsonb_build_object`, so keys must be string literals. This is most useful
68+
for pulling a one-to-many relationship into a single query as a slice
69+
field, instead of running a separate query per parent row. It's **pgx/v5
70+
only**: `sqlc generate` fails with a clear error for any other
71+
`sql_package`.
72+
73+
`"Name"` is required and must be a double-quoted identifier in that
74+
position, not an argument — Postgres parses this as a 3-part qualified
75+
function name (`catalog.schema.name`), and sqlc reads the struct name
76+
directly off of it.
77+
78+
```sql
79+
CREATE TABLE authors (
80+
id bigserial PRIMARY KEY,
81+
name text NOT NULL
82+
);
83+
84+
CREATE TABLE books (
85+
id bigserial PRIMARY KEY,
86+
author_id bigint NOT NULL REFERENCES authors (id),
87+
title text NOT NULL
88+
);
89+
```
90+
91+
```sql
92+
-- name: GetAuthors :many
93+
SELECT
94+
sqlc.embed(authors),
95+
ARRAY(
96+
SELECT sqlc.jsonb_build_object."Book"('id', books.id, 'title', books.title)
97+
FROM books
98+
WHERE books.author_id = authors.id
99+
) AS books
100+
FROM authors;
101+
```
102+
103+
```go
104+
type GetAuthorsRow struct {
105+
Author Author
106+
Books []Book
107+
}
108+
109+
type Book struct {
110+
ID int64 `json:"id"`
111+
Title string `json:"title"`
112+
}
113+
```
114+
115+
No custom `Scan`/`Value` methods, no wrapper type — `Books` is a plain
116+
`[]Book`; pgx v5 decodes the `jsonb[]` column into it directly. A lone
117+
`sqlc.jsonb_build_object."Name"(...)` (not wrapped in `ARRAY(...)`) works
118+
the same way and produces a plain struct field instead of a slice:
119+
120+
```sql
121+
-- name: GetAuthorSummary :one
122+
SELECT sqlc.jsonb_build_object."AuthorSummary"('name', name) AS summary FROM authors LIMIT 1;
123+
```
124+
125+
```go
126+
type GetAuthorSummaryRow struct {
127+
Summary AuthorSummary
128+
}
129+
```
130+
131+
Two queries that use the same explicit name reuse the same Go type, as long
132+
as their shapes match (this works even mixing scalar and `ARRAY(...)` uses
133+
of the same name). A shape mismatch, or a name that collides with an
134+
existing model/enum type, fails generation with an error instead of
135+
emitting Go code that won't compile.
136+
137+
##### Overriding generated names
138+
139+
The struct name and individual field names can be overridden via the
140+
`rename` option:
141+
142+
```json
143+
{
144+
"rename": {
145+
"Book": "BookSummary",
146+
"Book.id": "BookID"
147+
}
148+
}
149+
```
150+
151+
`"Book"` renames the type; `"Book.id"` renames just the `id` field within
152+
it, without affecting other types that also have an `id` key. Use the plain
153+
key (`"id"`) instead to rename that field everywhere.
154+
155+
##### Embedding a whole row as JSON
156+
157+
Listing every column by hand is tedious when you just want the whole row.
158+
`sqlc.embed.jsonb(table)` builds a JSON object from all of a table's columns,
159+
the same way `sqlc.embed(table)` gives you the table's model — but as a
160+
single JSON value, so it can be nested inside `ARRAY(...)` to return a slice
161+
of rows from one query:
162+
163+
```sql
164+
-- name: GetAuthorsWithBooks :many
165+
SELECT
166+
authors.id,
167+
ARRAY(SELECT sqlc.embed.jsonb(books) FROM books WHERE books.author_id = authors.id) AS books
168+
FROM authors;
169+
```
170+
171+
```go
172+
type GetAuthorsWithBooksRow struct {
173+
ID int64 `json:"id"`
174+
Books []Book `json:"books"`
175+
}
176+
177+
type Book struct {
178+
ID int64 `json:"id"`
179+
AuthorID int64 `json:"author_id"`
180+
Title string `json:"title"`
181+
}
182+
```
183+
184+
The generated struct is named from the result alias — singularized for the
185+
`ARRAY(...)` case (`AS books` → `Book`), or used as-is for a scalar
186+
`sqlc.embed.jsonb(table) AS author` (→ `Author`). Fields, types and JSON keys
187+
come from the table's columns, so the object always decodes cleanly. Under
188+
the hood the call is rewritten to `to_jsonb(table)`, which you'll see in
189+
`EXPLAIN` output and logs.
190+
191+
Like `sqlc.jsonb_build_object`, this is pgx/v5 only, and the generated name
192+
must not collide with a model or another JSON type; use the `rename` option
193+
if it does.

‎internal/cmd/shim.go‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -183,6 +183,7 @@ func pluginQueryColumn(c *compiler.Column) *plugin.Column {
183183
IsNamedParam: c.IsNamedParam,
184184
IsFuncCall: c.IsFuncCall,
185185
IsSqlcSlice: c.IsSqlcSlice,
186+
JsonName: c.JSONName,
186187
}
187188

188189
if c.Type != nil {
@@ -213,6 +214,10 @@ func pluginQueryColumn(c *compiler.Column) *plugin.Column {
213214
}
214215
}
215216

217+
for _, field := range c.JSONFields {
218+
out.JsonFields = append(out.JsonFields, pluginQueryColumn(field))
219+
}
220+
216221
return out
217222
}
218223

‎internal/codegen/golang/gen.go‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -212,6 +212,10 @@ func generate(req *plugin.GenerateRequest, options *opts.Options, enums []Enum,
212212
return nil, errors.New(":batch* commands are only supported by pgx")
213213
}
214214

215+
if usesJSON(queries) && tctx.SQLDriver != opts.SQLDriverPGXV5 {
216+
return nil, errors.New("sqlc.jsonb_build_object(...) and sqlc.embed.jsonb(...) are only supported by pgx/v5")
217+
}
218+
215219
funcMap := template.FuncMap{
216220
"lowerTitle": sdk.LowerTitle,
217221
"comment": sdk.DoubleSlashComment,
@@ -356,6 +360,15 @@ func usesBatch(queries []Query) bool {
356360
return false
357361
}
358362

363+
func usesJSON(queries []Query) bool {
364+
for _, q := range queries {
365+
if len(q.JSONTypes) > 0 {
366+
return true
367+
}
368+
}
369+
return false
370+
}
371+
359372
func checkNoTimesForMySQLCopyFrom(queries []Query) error {
360373
for _, q := range queries {
361374
if q.Cmd != metadata.CmdCopyFrom {

‎internal/codegen/golang/imports.go‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -350,6 +350,14 @@ func (i *importer) queryImports(filename string) fileImports {
350350
return true
351351
}
352352
}
353+
// JSONTypes live outside Ret/Arg, so check them separately.
354+
for _, t := range q.JSONTypes {
355+
for _, f := range t.Fields {
356+
if hasPrefixIgnoringSliceAndPointerPrefix(f.Type, name) {
357+
return true
358+
}
359+
}
360+
}
353361
}
354362
return false
355363
})

‎internal/codegen/golang/json_type.go‎

Lines changed: 96 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,96 @@
1+
package golang
2+
3+
import (
4+
"fmt"
5+
6+
"github.com/sqlc-dev/sqlc/internal/codegen/golang/opts"
7+
"github.com/sqlc-dev/sqlc/internal/plugin"
8+
)
9+
10+
// JSONType is a plain Go struct synthesized from a JSON directive. No
11+
// Scan/Value methods are needed: pgx v5 decodes the jsonb value directly into
12+
// a bare JSONType (or []JSONType for an array) via reflection.
13+
type JSONType struct {
14+
Name string
15+
Fields []Field
16+
}
17+
18+
// newGoJSONColumn resolves the Go type for a JSON column ([]Name for arrays,
19+
// Name otherwise) and collects the JSONType declarations it needs, including
20+
// nested ones. col.JsonName is used as-is so two queries can share a type.
21+
func newGoJSONColumn(req *plugin.GenerateRequest, options *opts.Options, col *plugin.Column, models modelTypeSet, qualifier string) (string, []JSONType) {
22+
structName := StructName(col.JsonName, options)
23+
24+
var fields []Field
25+
var types []JSONType
26+
for _, f := range col.JsonFields {
27+
tags := map[string]string{"json": f.Name}
28+
addExtraGoStructTags(tags, req, options, f)
29+
30+
var fieldType string
31+
if len(f.JsonFields) > 0 {
32+
var nested []JSONType
33+
fieldType, nested = newGoJSONColumn(req, options, f, models, qualifier)
34+
types = append(types, nested...)
35+
} else {
36+
fieldType = qualifyType(goType(req, options, f), models, qualifier)
37+
}
38+
39+
// A namespaced rename ("Name.fieldKey") wins over the global one.
40+
fieldName, ok := options.Rename[col.JsonName+"."+f.Name]
41+
if !ok {
42+
fieldName = StructName(f.Name, options)
43+
}
44+
45+
fields = append(fields, Field{
46+
Name: fieldName,
47+
DBName: f.Name,
48+
Type: fieldType,
49+
Tags: tags,
50+
Column: f,
51+
})
52+
}
53+
54+
types = append(types, JSONType{Name: structName, Fields: fields})
55+
56+
if col.IsArray {
57+
return "[]" + structName, types
58+
}
59+
return structName, types
60+
}
61+
62+
// internJSONTypes deduplicates types against seen (package-wide, keyed by
63+
// name, mutated in place), returning only those still needing to be emitted.
64+
// A name that collides with a model/enum, or with an earlier type of a
65+
// different shape, is an error. Scalar and array uses share one declaration.
66+
func internJSONTypes(seen map[string]JSONType, models modelTypeSet, types []JSONType) ([]JSONType, error) {
67+
var toEmit []JSONType
68+
for _, t := range types {
69+
if _, ok := models[t.Name]; ok {
70+
return nil, fmt.Errorf("sqlc.jsonb_build_object.%q(...) collides with an existing model or enum type; give it a different name", t.Name)
71+
}
72+
prev, ok := seen[t.Name]
73+
if !ok {
74+
seen[t.Name] = t
75+
toEmit = append(toEmit, t)
76+
continue
77+
}
78+
if !sameJSONShape(prev, t) {
79+
return nil, fmt.Errorf("sqlc.jsonb_build_object.%q(...) is used with conflicting shapes across queries; give one of them a different name", t.Name)
80+
}
81+
}
82+
return toEmit, nil
83+
}
84+
85+
func sameJSONShape(a, b JSONType) bool {
86+
if len(a.Fields) != len(b.Fields) {
87+
return false
88+
}
89+
for i, f := range a.Fields {
90+
g := b.Fields[i]
91+
if f.Name != g.Name || f.Type != g.Type || f.Tag() != g.Tag() {
92+
return false
93+
}
94+
}
95+
return true
96+
}

0 commit comments

Comments
 (0)
Back | FazBrowse Home | New Git URL