Skip to content

Commit ec2912c

Browse files
kyleconroykyleconroy
authored
feat(analyzer): Analyze queries using a running PostgreSQL database (sqlc-dev#2805)
* feat(analyzer): Analyze queries using a running PostgreSQL database 94 of the open issues on sqlc are related to the analyzer. There are many cases where the current analyzer produces false positives or false negatives. sqlx and some other projects have proven that it's possible to extract query metadata from a running database. This approach is a bit different, in that the database analysis is layered on top of the existing query analyzer. We use the new analysis to provide better type information and support a wider set of cases when the existing analyzer fails. * test(analyzer): Update endtoend tests for new analyzer Add a `contexts` key to exec.json to opt certain tests into or out of database-backed analysis. Fix many incorrect test cases that didn't run against an actual database.
1 parent 775f80b commit ec2912c

478 files changed

Lines changed: 4422 additions & 996 deletions

File tree

Some content is hidden

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

‎examples/authors/sqlc.yaml‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,8 @@ sql:
77
engine: postgresql
88
database:
99
managed: true
10+
analyzer:
11+
database: false
1012
rules:
1113
- sqlc/db-prepare
1214
- postgresql-query-too-costly

‎examples/booktest/sqlc.json‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,9 @@
1313
"database": {
1414
"managed": true
1515
},
16+
"analyzer": {
17+
"database": false
18+
},
1619
"rules": [
1720
"sqlc/db-prepare"
1821
]

‎examples/jets/sqlc.json‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,9 @@
1313
"database": {
1414
"managed": true
1515
},
16+
"analyzer": {
17+
"database": false
18+
},
1619
"rules": [
1720
"sqlc/db-prepare"
1821
]

‎examples/ondeck/sqlc.json‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,9 @@
1313
"database": {
1414
"managed": true
1515
},
16+
"analyzer": {
17+
"database": false
18+
},
1619
"rules": [
1720
"sqlc/db-prepare"
1821
],
@@ -21,7 +24,7 @@
2124
"emit_interface": true
2225
},
2326
{
24-
"path": "mysql",
27+
"path": "mysql",
2528
"name": "ondeck",
2629
"schema": "mysql/schema",
2730
"queries": "mysql/query",

‎go.mod‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@ require (
2929

3030
require (
3131
github.com/benbjohnson/clock v1.3.5 // indirect
32+
github.com/jackc/puddle/v2 v2.2.1 // indirect
3233
github.com/pingcap/failpoint v0.0.0-20220801062533-2eaa32854a6c // indirect
3334
github.com/xeipuuv/gojsonpointer v0.0.0-20180127040702-4e3ac2762d5f // indirect
3435
github.com/xeipuuv/gojsonreference v0.0.0-20180127040603-bd5ef7bd5415 // indirect

‎go.sum‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -100,7 +100,10 @@ github.com/jackc/pgx/v5 v5.4.3/go.mod h1:Ig06C2Vu0t5qXC60W8sqIthScaEnFvojjj9dSlj
100100
github.com/jackc/puddle v0.0.0-20190413234325-e4ced69a3a2b/go.mod h1:m4B5Dj62Y0fbyuIc15OsIqK0+JU8nkqQjsgx7dvjSWk=
101101
github.com/jackc/puddle v0.0.0-20190608224051-11cab39313c9/go.mod h1:m4B5Dj62Y0fbyuIc15OsIqK0+JU8nkqQjsgx7dvjSWk=
102102
github.com/jackc/puddle v1.1.3/go.mod h1:m4B5Dj62Y0fbyuIc15OsIqK0+JU8nkqQjsgx7dvjSWk=
103+
github.com/jackc/puddle v1.3.0 h1:eHK/5clGOatcjX3oWGBO/MpxpbHzSwud5EWTSCI+MX0=
103104
github.com/jackc/puddle v1.3.0/go.mod h1:m4B5Dj62Y0fbyuIc15OsIqK0+JU8nkqQjsgx7dvjSWk=
105+
github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk=
106+
github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
104107
github.com/jinzhu/inflection v1.0.0 h1:K317FqzuhWc8YvSVlFMCCUb36O/S9MCKRDI7QkRKD/E=
105108
github.com/jinzhu/inflection v1.0.0/go.mod h1:h+uFLlag+Qp1Va5pdKtLDYj+kHp5pxUVkryuEj+Srlc=
106109
github.com/kisielk/gotool v1.0.0/go.mod h1:XhKaO+MFFWcvkIS/tQcRk01m1F5IRFswLeQ+oQHNcck=

‎internal/analyzer/analyzer.go‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
package analyzer
2+
3+
import (
4+
"context"
5+
6+
"github.com/sqlc-dev/sqlc/internal/sql/ast"
7+
"github.com/sqlc-dev/sqlc/internal/sql/named"
8+
)
9+
10+
type Column struct {
11+
Name string
12+
OriginalName string
13+
DataType string
14+
NotNull bool
15+
Unsigned bool
16+
IsArray bool
17+
ArrayDims int
18+
Comment string
19+
Length *int
20+
IsNamedParam bool
21+
IsFuncCall bool
22+
23+
// XXX: Figure out what PostgreSQL calls `foo.id`
24+
Scope string
25+
Table *ast.TableName
26+
TableAlias string
27+
Type *ast.TypeName
28+
EmbedTable *ast.TableName
29+
30+
IsSqlcSlice bool // is this sqlc.slice()
31+
}
32+
33+
type Parameter struct {
34+
Number int
35+
Column *Column
36+
}
37+
38+
type Analysis struct {
39+
Columns []Column
40+
Params []Parameter
41+
}
42+
43+
type Analyzer interface {
44+
Analyze(context.Context, ast.Node, string, []string, *named.ParamSet) (*Analysis, error)
45+
Close(context.Context) error
46+
}

‎internal/cmd/cmd.go‎

Lines changed: 19 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -197,7 +197,10 @@ var genCmd = &cobra.Command{
197197
defer trace.StartRegion(cmd.Context(), "generate").End()
198198
stderr := cmd.ErrOrStderr()
199199
dir, name := getConfigPath(stderr, cmd.Flag("file"))
200-
output, err := Generate(cmd.Context(), ParseEnv(cmd), dir, name, stderr)
200+
output, err := Generate(cmd.Context(), dir, name, &Options{
201+
Env: ParseEnv(cmd),
202+
Stderr: stderr,
203+
})
201204
if err != nil {
202205
os.Exit(1)
203206
}
@@ -219,7 +222,11 @@ var uploadCmd = &cobra.Command{
219222
RunE: func(cmd *cobra.Command, args []string) error {
220223
stderr := cmd.ErrOrStderr()
221224
dir, name := getConfigPath(stderr, cmd.Flag("file"))
222-
if err := createPkg(cmd.Context(), ParseEnv(cmd), dir, name, stderr); err != nil {
225+
opts := &Options{
226+
Env: ParseEnv(cmd),
227+
Stderr: stderr,
228+
}
229+
if err := createPkg(cmd.Context(), dir, name, opts); err != nil {
223230
fmt.Fprintf(stderr, "error uploading: %s\n", err)
224231
os.Exit(1)
225232
}
@@ -234,7 +241,11 @@ var checkCmd = &cobra.Command{
234241
defer trace.StartRegion(cmd.Context(), "compile").End()
235242
stderr := cmd.ErrOrStderr()
236243
dir, name := getConfigPath(stderr, cmd.Flag("file"))
237-
if _, err := Generate(cmd.Context(), ParseEnv(cmd), dir, name, stderr); err != nil {
244+
_, err := Generate(cmd.Context(), dir, name, &Options{
245+
Env: ParseEnv(cmd),
246+
Stderr: stderr,
247+
})
248+
if err != nil {
238249
os.Exit(1)
239250
}
240251
return nil
@@ -277,7 +288,11 @@ var diffCmd = &cobra.Command{
277288
defer trace.StartRegion(cmd.Context(), "diff").End()
278289
stderr := cmd.ErrOrStderr()
279290
dir, name := getConfigPath(stderr, cmd.Flag("file"))
280-
if err := Diff(cmd.Context(), ParseEnv(cmd), dir, name, stderr); err != nil {
291+
opts := &Options{
292+
Env: ParseEnv(cmd),
293+
Stderr: stderr,
294+
}
295+
if err := Diff(cmd.Context(), dir, name, opts); err != nil {
281296
os.Exit(1)
282297
}
283298
return nil

‎internal/cmd/diff.go‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,6 @@ import (
44
"context"
55
"errors"
66
"fmt"
7-
"io"
87
"os"
98
"runtime/trace"
109
"sort"
@@ -13,8 +12,9 @@ import (
1312
"github.com/cubicdaiya/gonp"
1413
)
1514

16-
func Diff(ctx context.Context, e Env, dir, name string, stderr io.Writer) error {
17-
output, err := Generate(ctx, e, dir, name, stderr)
15+
func Diff(ctx context.Context, dir, name string, opts *Options) error {
16+
stderr := opts.Stderr
17+
output, err := Generate(ctx, dir, name, opts)
1818
if err != nil {
1919
return err
2020
}

‎internal/cmd/generate.go‎

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -139,8 +139,11 @@ func readConfig(stderr io.Writer, dir, filename string) (string, *config.Config,
139139
return configPath, &conf, nil
140140
}
141141

142-
func Generate(ctx context.Context, e Env, dir, filename string, stderr io.Writer) (map[string]string, error) {
143-
configPath, conf, err := readConfig(stderr, dir, filename)
142+
func Generate(ctx context.Context, dir, filename string, o *Options) (map[string]string, error) {
143+
e := o.Env
144+
stderr := o.Stderr
145+
146+
configPath, conf, err := o.ReadConfig(dir, filename)
144147
if err != nil {
145148
return nil, err
146149
}
@@ -343,7 +346,12 @@ func remoteGenerate(ctx context.Context, configPath string, conf *config.Config,
343346

344347
func parse(ctx context.Context, name, dir string, sql config.SQL, combo config.CombinedSettings, parserOpts opts.Parser, stderr io.Writer) (*compiler.Result, bool) {
345348
defer trace.StartRegion(ctx, "parse").End()
346-
c := compiler.NewCompiler(sql, combo)
349+
c, err := compiler.NewCompiler(sql, combo)
350+
defer c.Close(ctx)
351+
if err != nil {
352+
fmt.Fprintf(stderr, "error creating compiler: %s\n", err)
353+
return nil, true
354+
}
347355
if err := c.ParseCatalog(sql.Schema); err != nil {
348356
fmt.Fprintf(stderr, "# package %s\n", name)
349357
if parserErr, ok := err.(*multierr.Error); ok {

0 commit comments

Comments
 (0)