Configure a custom dialer

[tobiassjosten]

I'm using GoogleCloudPlatform/cloud-sql-go-connector for my application, to connect to Cloud SQL both locally and from Cloud Run. For the driver, I'm using pgx (pgxpool), with a regular DSN string (user=asdf password=qwer dbname=zxcv sslmode=disabled) and the connection happens through a custom dialer:

config, err := pgxpool.ParseConfig(os.Getenv("CLOUD_SQL_DSN"))
if err != nil {
	log.Fatalf("Unable to parse config: %v\n", err)
}

d, err := cloudsqlconn.NewDialer(ctx)
if err != nil {
	log.Fatalf("Unable to create dialer: %v\n", err)
}

config.ConnConfig.DialFunc = func(ctx context.Context, _ string, instance string) (net.Conn, error) {
	return d.Dial(ctx, os.Getenv("CLOUD_SQL_ICN"))
}

pool, err := pgxpool.NewWithConfig(ctx, config)
if err != nil {
	log.Fatalf("Unable to connect to database: %v\n", err)
}

Is there a way to achieve this with sqlc? I've looked around the code but can't find a way to "hook in" to the configuration and set my custom dialer.

Any help or pointers would be appreciated!

[kyleconroy]

When configured with sql_package=pgx/v5, the generated New function takes a pgxpool.

package db

import (
	"context"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
)

type DBTX interface {
	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
	QueryRow(context.Context, string, ...interface{}) pgx.Row
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db",
        "sql_package": "pgx/v5"
      }
    }
  }]
}

Taking your sample code, the following code should work

config, err := pgxpool.ParseConfig(os.Getenv("CLOUD_SQL_DSN"))
if err != nil {
	log.Fatalf("Unable to parse config: %v\n", err)
}

d, err := cloudsqlconn.NewDialer(ctx)
if err != nil {
	log.Fatalf("Unable to create dialer: %v\n", err)
}

config.ConnConfig.DialFunc = func(ctx context.Context, _ string, instance string) (net.Conn, error) {
	return d.Dial(ctx, os.Getenv("CLOUD_SQL_ICN"))
}

pool, err := pgxpool.NewWithConfig(ctx, config)
if err != nil {
	log.Fatalf("Unable to connect to database: %v\n", err)
}

queries := db.New(pool)

[tobiassjosten]

Thanks, @kyleconroy! That looks promising but my problem right now is that the CLI runner won't be configured with this dialer, so I'm unable to even run sqlc generate.

Is there perhaps a way I can run sqlc commands as a library, from within my own application, so that I can control the dialer?

This is the error I'm getting with sqlc generate:

# package storage
storage/queries/companies.sql:1:1: failed to connect to `user=myusername database=mydatabase`: /private/tmp/.s.PGSQL.5432 (/private/tmp): dial error: dial unix /private/tmp/.s.PGSQL.5432: connect: no such file or directory

Using configuration:

version: "2"
servers:
  - engine: postgresql
    # Didn't work:
    # uri: host=/cloudsql/${CLOUD_SQL_ICN} ${CLOUD_SQL_DSN}
    # Also doesn't work:
    uri: postgresql:///mydatabase?host=/cloudsql/${CLOUD_SQL_ICN}&user=myusername&password=mypassword&sslmode=disable
sql:
  - name: "storage"
    engine: "postgresql"
    queries: "storage/queries/"
    schema: "storage/migrations/"
    database:
      managed: true
    gen:
      go:
        package: "storage"
        out: "storage"
        sql_package: "pgx/v5"
        overrides:
          - db_type: "pg_catalog.timestamp"
            go_type: "time.Time"
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
    rules:
      - sqlc/db-prepare
      - postgresql-query-too-costly
rules:
  - name: postgresql-query-too-costly
    message: "Query cost estimate is too high"
    rule: "postgresql.explain.plan.total_cost > 100.0"

[kyleconroy]

Sorry, I misunderstood your original question. You're not trying to connect using the generated codes; instead, you want to use a custom dialer to connect to a database when running sqlc generate. Is that right?

[tobiassjosten]

I could have made that more clear. :) But, yes, that's exactly right.

[tobiassjosten]

Do you think this is at all possible, @kyleconroy? Any pointers would be greatly appreciated!

[tobiassjosten]

For anyone else struggling with this in the future, it's only a problem if you're trying to enable enhanced analysis with managed databases. Remove the servers and sql.database configuration and code generation will work just fine.

I didn't find a way to configure the dialer for sqlc's CLI. An alternative way is to use Cloud SQL Auth Proxy to create a local tunnel and then have sqlc connect through that.