sqlc generate fails if configured with a URI for sqlc vet, but if a DB is not running

[alecthomas]

Version

1.24.0

What happened?

Hello there,

Big fan of sqlc.

I've configured a database.uri in the config in order to run sqlc vet, but when I run sqlc generate it fails with:

🐚 ~/dev/ftl $ sqlc generate --experimental
# package sql
backend/controller/sql/queries.sql:1:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:8:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
...etc.

If I remove the URI from the config it succeeds without issue. I'm not sure why it's trying to connect but I would expect either:

1. It not to require the DB during generate at all or...
2. There be a flag I could use to disable this, if it used for some reason I'm unaware of.

The use case for this is that I wish to regenerate all source in CI, then build it to verify that everything rebuilds correctly. I don't need or want the DB up and running for this.

Relevant log output

🐚 ~/dev/ftl $ sqlc generate --no-remote                                                                                                                                               aat/fixes-for-sqlc-upgrade
# package sql
backend/controller/sql/queries.sql:1:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:8:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:13:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:18:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:23:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:29:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:36:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:41:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:45:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:57:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:64:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:76:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:82:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:110:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:119:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:128:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:144:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:152:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:164:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:171:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:177:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:186:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:200:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:217:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:222:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:236:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:245:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:251:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:258:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:270:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:286:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:299:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:312:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:333:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:337:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:346:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:355:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:362:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:367:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:375:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)
backend/controller/sql/queries.sql:383:1: failed to connect to `host=localhost user=postgres database=ftl`: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)

### Database schema

```sql
N/A

SQL queries

N/A

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "backend/controller/sql/queries.sql"
    schema: "backend/controller/sql/schema"
    database:
      uri: postgres://localhost/ftl?sslmode=disable&user=postgres&password=secret
    gen:
      go:
        package: "sql"
        sql_package: "pgx/v5"
        out: "backend/controller/sql"
        emit_interface: true
        query_parameter_limit: 3
        overrides:
          - db_type: "timestamptz"
            go_type: "time.Time"
          - db_type: "pg_catalog.interval"
            go_type: "time.Duration"
          - db_type: "pg_catalog.interval"
            nullable: true
            go_type:
              type: "NullDuration"
          - db_type: "timestamptz"
            nullable: true
            go_type:
              type:  "NullTime"
          - db_type: "uuid"
            go_type:
              type:  "Key"
          - db_type: "uuid"
            nullable: true
            go_type:
              type:  "NullKey"
          - db_type: "pg_catalog.varchar"
            nullable: true
            go_type: "github.com/alecthomas/types.Option[string]"
          - db_type: "text"
            go_type: "string"
          - db_type: "text"
            nullable: true
            go_type: "github.com/alecthomas/types.Option[string]"
          - db_type: "pg_catalog.int8"
            nullable: true
            go_type: "github.com/alecthomas/types.Option[int64]"
          - db_type: "bigint"
            nullable: true
            go_type: "github.com/alecthomas/types.Option[int64]"
          - db_type: "int"
            nullable: true
            go_type: "github.com/alecthomas/types.Option[int32]"
          - db_type: "bool"
            nullable: true
            go_type: "github.com/alecthomas/types.Option[bool]"
          # Can't use until https://github.com/sqlc-dev/sqlc/issues/2632 is fixed, if it ever is...
#          - column: "ingress_requests.key"
#            go_type: "github.com/TBD54566975/ftl/backend/common/model.RequestName"
#          - column: "ingress_requests.key"
#            nullable: true
#            go_type: "github.com/TBD54566975/ftl/backend/common/model.NullRequestName"
#          - column: "runners.key"
#            go_type: "github.com/TBD54566975/ftl/backend/common/model.RunnerKey"
          - column: "controller.key"
            go_type: "github.com/TBD54566975/ftl/backend/common/model.ControllerKey"
          - column: "deployments.name"
            go_type: "github.com/TBD54566975/ftl/backend/common/model.DeploymentName"
          - column: "events.payload"
            go_type: "encoding/json.RawMessage"
    rules:
      - sqlc/db-prepare
      # - postgresql-query-too-costly
      - postgresql-no-seq-scan
rules:
- name: postgresql-query-too-costly
  message: "Query cost estimate is too high"
  rule: "postgresql.explain.plan.total_cost > 500.0"
- name: postgresql-no-seq-scan
  message: "Query plan results in a sequential scan"
  rule: "postgresql.explain.plan.node_type == 'Seq Scan'"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[kyleconroy]

In 1.23 we added the database-based query analyzer. This is turned on if you have a database URI set.

There be a flag I could use to disable this, if it used for some reason I'm unaware of.

The analyzer.database configuration option is what you're looking for. Here's how to set it.

version: "2"
sql:
  - engine: "postgresql"
    queries: "backend/controller/sql/queries.sql"
    schema: "backend/controller/sql/schema"
    database:
      uri: postgres://localhost/ftl?sslmode=disable&user=postgres&password=secret
    analyzer:
      database: false
    gen:
      go:
        package: "sql"
        sql_package: "pgx/v5"
        out: "backend/controller/sql"

[alecthomas]

Ah my apologies, and thanks for the help.

[PatrLind]

I just had a similar issue. My config looks like this:

---

version: 2
sql:
  - schema: "./migrations/"
    queries: "./queries/"
    engine: "postgresql"
    database:
      uri: ${SQLC_VET_DB_URI}
    rules:
      - sqlc/db-prepare
    gen:
      go:
        sql_package: "pgx/v4"
        emit_interface: true
        emit_result_struct_pointers: true
        out: "sqlc"
        package: "sqlc"
        overrides:
        - db_type: "uuid"
          go_type: "github.com/gofrs/uuid.UUID"
          nullable: false
        - db_type: "uuid"
          go_type: "github.com/gofrs/uuid.NullUUID"
          nullable: true

I run the command as export SQLC_VET_DB_URI="" && sqlc generate -f ./internal/something/db/postgres/sqlc.yaml but I still get errors that it cannot connect. Should I do this in some other way?

I have several DBs I can run vet against, but when generating the sqlc definitions I don't use one...

v1.25.0