[Docs] Does yugabyte have any plans for sqlc support ?

[psankar]

Description

sqlc.dev is gaining popularity among Go developers for adding type safety checks to Go + SQL code. Are there any plans for supporting or endorsing sqlc officially ? sqlc already works with vanilla postgres and pgx driver. But there are certain things that may be specific to yugabyte (say distributed transactions or so) which may not be covered by the vanilla pgx driver (may not be precise, just giving a representative example).

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[ddorian]

Hi @psankar

It should work with YugabyteDB. Can you try it and report if you hit any issues (either here or a new issue)?
We've only enhanced drivers to be more cluster aware, but they should work by default.

Looking at their docs, you should be able to use pgx or our pgx-smart-driver.

[psankar]

I created a sample yugabyte+sqlc program and it failed to compile.

cannot use s.pool (variable of type *pgxpool.Pool) as db.DBTX value in argument to db.New: *pgxpool.Pool does not implement db.DBTX (wrong type for method Exec)
		have Exec(context.Context, string, ...any) ("github.com/yugabyte/pgx/v5/pgconn".CommandTag, error)
		want Exec(context.Context, string, ...interface{}) ("github.com/jackc/pgx/v5/pgconn".CommandTag, error)compiler

Possibly related to the obsolete pgx fork that you maintain and the issue #28935

On the long run, I believe that if you want to natively support sqlc, you must submit patches to sqlc and make yugabyte/pgx/v<X> as a valid value on the sqlc.yaml like below:

version: "2"
sql:
  - engine: "postgresql"
    queries: "queries"
    schema: "migrations"
    gen:
      go:
        package: "db"
        out: "db"
        sql_package: "yugabyte/pgx/v5"

Currently the above fails to even sqlc generate and the code generated with pgx/v5 driver fails to even compile with yugabyte library. I believe that this issue should be reopened.

[ddorian]

Possibly related to the obsolete pgx fork that you maintain and the issue #28935

Can you try it with plain pgx and not with our fork?

Can you mention your usecase on #28935 ?

[psankar]

With plain pgx the code will of course work against any normal postgres compatible database.

But the reason why I am evaluating yugabyte is for the features like geographical partitioning and cluster-aware load balancing etc.

	databaseURL = "postgres://yugabyte:yugabyte@yugabytedb-node1:5433,yugabytedb-node2:5433,yugabytedb-node3:5433,yugabytedb-node4:5433/yugabyte"

	// Enable YugabyteDB smart driver features for geo-partitioning
	// load_balance=true enables cluster-aware load balancing
	// yb_servers_refresh_interval refreshes the server list periodically
	databaseURL += "?load_balance=true&yb_servers_refresh_interval=300"

[ddorian]

But the reason why I am evaluating yugabyte is for the features like geographical partitioning and cluster-aware load balancing etc.

These features are unrelated to the code generation process I believe. So I believe we just need to pull enough changes from upstream to be compatible.

[psankar]

These features are unrelated to the code generation.

But until the newer yuygabyte/pgx library is released, we will not be able to use sqlc at all. It will result in build failure. So that needs to be either documented (so that people like me need not try that experiment) or the release of the new driver be pushed as soon as possible.

On the long run, even with the new driver, I am not sure if things like TABLESPACE will cause issues with sqlc. I will test and open issues if needed, after the new yugabyte/pgx driver release.

[ddorian]

The features it provides are constrained so it should be rare to cause issues.

[psankar]

This bug should be reopened because sqlc generated code now even fails to compile. We need a new version of pgx release.

[ddorian]

cc @ashetkar

[ashetkar]

The YugabyteDB fork of the pgx driver is now updated to version 5.7.6. #28935