go: expose DBTX inside Queries (via flag)

[toqueteos]

What do you want to change?

Currently the autogenerated db.go file contains this two types:

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
}

// [..]

type Queries struct {
    db DBTX
}

Sometimes, because of ✨ questionable business reasons ✨ we would like to use the DBTX inside a *Queries to write some raw SQL but currently that's not possible.

An escape hatch behind a flag, something like emit_public_dbtx, to allow this would be very welcome.

Options for the changes required:

• A) Embed DBTX inside Queries

  type Queries struct {
  DBTX
  }

• B) Make db public by renaming it to DB

  type Queries struct {
  DB DBTX
  }

• C) Add a new method to Queries which exposes the DBTX (this option requires the least changes)

  // There might be better names than Conn, I'm open to suggestions
  func (q *Queries) Conn() DBTX {
  return q.db
  }

What database engines need to be changed?

None

What programming language backends need to be changed?

Go

[francesconi]

Why don't you add a receiver function in a new file inside the generated package? We do something similar when we have to deal with transactions outside the generated package.

func (q *Queries) Pool() *pgxpool.Pool {
    return q.db.(*pgxpool.Pool)
}

We also add receiver functions for raw queries that are currently not being able to be interpreted by sqlc.

[toqueteos]

Why don't you add a receiver function in a new file inside the generated package? We do something similar when we have to deal with transactions outside the generated package.

func (q *Queries) Pool() *pgxpool.Pool {
  return q.db.(*pgxpool.Pool)
}

We also add receiver functions for raw queries that are currently not being able to be interpreted by sqlc.

That's my current workaround, at the cost of sqlc's "out" folder no longer being 100% autogenerated thus it can't be blindly deleted anymore.

[francesconi]

Have you also considered using emit_methods_with_db_argument?

[toqueteos]

Have you also considered using emit_methods_with_db_argument?

I don't think so, I'll give it a try this afternoon.

EDIT: Tried, that flag does "the opposite" of what I want. It requires a DBTX on every query.

I just need the underlying DBTX to be accessible so I can call Exec, Query and/or QueryRow.

[Waishnav]

EDIT: Tried, that flag does "the opposite" of what I want. It requires a DBTX on every query.

I just need the underlying DBTX to be accessible so I can call Exec, Query and/or QueryRow.

@toqueteos, this is ideal way, suppose you have to work in application where database per tenant architecture is followed. suppose you are working turso, in such case, you have to find the pool connection according to the user and attach it to queries in runtime, instead of being tightly coupled with Queries

Even though, in certain edge case like you are facing, there is need for making this DBTX instance public, I agree that there should be configuration to have this option where we can make the db connection pool as public struct attached to Queries instance.

@kyleconroy @francesconi, Should I raise the PR for this issue by adding new configuration key for it?