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

Answered by kyleconroy

alecthomas asked this question in Q&A

@alecthomas alecthomas

Nov 24, 2023

· 2 comments · 1 reply

Answered by kyleconroy Return to top

alecthomas
Nov 24, 2023

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:

It not to require the DB during generate at all or...
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?

Answered by kyleconroy

Nov 24, 2023

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"

View full answer

Replies: 2 comments 1 reply

kyleconroy
Nov 24, 2023
Maintainer

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"

1 reply

@alecthomas

alecthomas Nov 25, 2023
Author

Ah my apologies, and thanks for the help.

Answer selected by kyleconroy

PatrLind
Feb 2, 2024

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

0 replies

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

Uh oh!

Uh oh!

alecthomas Nov 24, 2023

Version

What happened?

Relevant log output

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Replies: 2 comments · 1 reply

Uh oh!

Uh oh!

kyleconroy Nov 24, 2023 Maintainer

Uh oh!

alecthomas Nov 25, 2023 Author

Uh oh!

Uh oh!

PatrLind Feb 2, 2024

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

alecthomas
Nov 24, 2023

Replies: 2 comments 1 reply

kyleconroy
Nov 24, 2023
Maintainer

alecthomas Nov 25, 2023
Author

PatrLind
Feb 2, 2024