Request for comments: SQL to Go code generator quite similar to sqlc

[jschaf]

Hi! I'm working on sqld, a code generator that takes SQL queries and outputs type-safe Go code using pgx. I'm a big fan of sqlc (hence the name) and if you have some time, I'd love to get your thoughts on the RFC, specifically:

1. Is it possible or beneficial to merge the approaches used in sqlc and sqld? I created a new project because one of sqlc's value propositions is that it must work without running Postgres. For sqld, I wanted to avoid implementing parsing logic by leaning more heavily on Postgres. Additionally, I'd prefer to only support pgx instead of the sql package.

2. Are you okay with the name sqld? I thought it was mildly clever but I'm open to changing it.

3. Possible collaboration on a future ClientTrace API similar to the Go stdlib http tracing: https://blog.golang.org/http-tracing. There's a ton of useful metrics to surface (DNS resolution for host names, response parse time, pool queue time). I've created an initial pgx-focused ClientTrace.go that I use in the generated code.

4. Overall API design.

• Is it worth supporting options when building a Querier?
• How many inline parameters before switching to a struct: FindAuthors(ctx, "name") vs FindAuthors(ctx, FindAuthorsParams{Name: "name"}). Looks like sqlc switches after 1 parameter.

RFC: sqld: Go code generation for Postgres (Google doc)

To summarize the main differences between sqld and sqlc:

1. sqld requires a running Postgres instance to get type information by executing queries.
2. sqld only supports pgx.

I've started a similar discussion on the pgx repo: jackc/pgx#915. Feel free to close this whenever.

[jschaf]

Started a repo: https://github.com/jschaf/sqld. Leaning toward changing the name to pggen.

[kyleconroy]

@jschaf Thanks for reaching out about your new project. I've read through the RFC.

Is it possible or beneficial to merge the approaches used in sqlc and sqld?

It's most certainly possible, but I'm not sure it's a good idea. I've attempted to import a catalog from a running database a few times (#657, #550, #659), but haven't attempted to make it work across all supported databases. There's also an open issue for pgx support (#472).

The biggest blocker here is the decision to rely on the running PostgreSQL instance for determining input and output types. We'd have to make some pretty big internal changes to make that work, but it wouldn't be insurmountable.

Are you okay with the name sqld? I thought it was mildly clever but I'm open to changing it.

I'm personally not a huge fan of the name. I agree that it's clever, but I think it will just cause confusion. You're free to name your project whatever you like, but I'd prefer you use the name pggen.

Possible collaboration on a future ClientTrace API similar to the Go stdlib http tracing

Very interested in exploring what a shared interface could look like. I've pointed people to https://github.com/ngrok/sqlmw in the past if they're looking to instrument all database queries.

Overall API design

This one is a bit more challenging to change. I don't want to change the default generator, as it would cause significant breaking changes for the individuals and companies already using sqlc. What I'd really like to do is make it easy for people to write their own code generators so that can pick the API design they want to use.

Good luck with your project!

[jschaf]

The biggest blocker here is the decision to rely on the running PostgreSQL instance for determining input and output types.

Yes, this seems to be the biggest difference and it reflects different value props of both projects. I'm happy to keep the projects separate.

I'd prefer you use the name pggen.

No worries, done. https://github.com/jschaf/pggen

I've pointed people to https://github.com/ngrok/sqlmw

Oh, neat, I hadn't run across that before. I'll get codegen off the ground before taking a look at it. I'll loop you in once I get there.

I don't want to change the default generator.

Totally, no reason to break the world by changing sqlc. I was asking from the angle: if you could start afresh would you change the interface of the generated code?

Good luck with your project!

Thanks, and thank you for creating a sqlc. I can't emphasize how nice it is to have generated code with the full functionality of Postgres.

[jschaf]

As an interesting observation, I discovered a feature of sqlc that will prove hard to replicate: nullability tracking. It's useful to know if an output can be null or if it's the value is always not null like SELECT coalesce(id, 'some_value') from author. It's useful because non-null values can be simpler: string vs sql.NullString.

As far as I'm aware, the only approach to determine if query output is null is to mimic sqlc and build up a type inference engine that tracks nullability.