Raw queries

[iorlas]

Just a quick question/suggestion. Well, I have an app which uses go-pg mainly. It looks nice and sound, but I have to do some raw queries with variable results. The use-case could be described like this: "advanced version of raw SQL API" (hasura or any existing BI or ETL solution did not fit the needs damn).

So, input is predictable in one way or another, the query will be quite complex (CTEs, window-functions, sub-queries, complex joins). The output (columns) is kind of predictable, but not really.

In general, it is easy to implement with low-level drivers by making the iteration and scanning each row into the map, interface{} or just a slice of strings. It will allow to stream the data, make less noise in the code, great flexibility.

It is easily doable with database/sql, but might not be that easy with go-pg. I would like to avoid keeping two separate database drivers, keeping 2 db pools. So, here are the pitfalls I found:

• Query.ForEach is the only way to iterate w/o scanning all the data into memory. But it needs query object. I need to supply raw query, but both Exec and Query return Result instead, making ForEach non-applicable
• I need to unmarshal a variable num of columns, so I would expect to pass [][]string as a model. Obviously, it does not work. I've tried to use pg.Strings, but it did not work either (the issue with unmarshalling, it might be my mistake somewhere, will take a look on weekends, too overwhelmed).

Well, not a requirement, just another usecase, it would be awesome to have some flexibility. I believe go-pg is quite flexible right now, but it might not be as obvious as should be.

Anyway, wanted to give you some feedback asap, hope it helps you, might help me if you will suggest the preferred approach :)

Btw, we redid the otel tracing hook almost from the zero, to be compliant with the specs, will send you PR on weekends as well. There are a couple of issues and some optimizations still to be done, but the current otel tracing hook has some critical issues and missing essential data, we'll see

[elliotcourant]

Query.ForEach is the only way to iterate w/o scanning all the data into memory. But it needs query object. I need to supply raw query, but both Exec and Query return Result instead, making ForEach non-applicable

Not to say that this is the best solution, but it might be a solution. What about making a model for that query and then making the query a view in SQL? Then go-pg would simply select from that view as if it were a table allowing you to use your ForEach normally.

[iorlas]

To create a model and a view, we need to know the column names beforehand. But the point is to provide pool-ppl-BI to make some simple queries with different columns.

It is an option to convert the columns into json and expect that json column in the model, but yeah.. painful.

[vmihailenco]

Questions like this is one of the reasons I've created Bun. It works on top of database/sql so sql.Rows is always available.

[iorlas]

Diving a little bit deeper into the docs I realized the Bun is a replacement for the go-pg with the intent to deprecate the go-pg. Am I right?

I guess we will evaluate the Bun as a migration step to use the same connection pool for raw and ORM load. Initially, I thought the Bun is just a simple query builder, but now it looks much better! Thank you for your efforts!

Sorry for the offtopic, but do you plan to deprecate go-pg in favor of the Bun? Any timelines? Does it make sense to invest any time in go-pg right now or you would recommend starting with Bun right away?

[vmihailenco]

Diving a little bit deeper into the docs I realized the Bun is a replacement for the go-pg with the intent to deprecate the go-pg. Am I right?

Yes.

Practically nothing drastic happened. Bun is just pg v11 with a different core based on database/sql interfaces. pg v10 will only receive security updates and important bug fixes. The same happened with pg v9.

New projects should use Bun.

[iorlas]

Sweet! I suggest highlighting the project status a bit more, with big letters and red colors, you know. I see this info in the readme, but one of our project just started to use go-pg, feels like this info is easily missed.

Again, sorry for the offtopic, but do you plan to provide the soft-delete capability in the Bun? I didn't find it in the models or deletion docs. We use it greatly in our projects, it might not be as important as other stuff, but it would be awesome to know the status.

[vmihailenco]

Yes, soft deletes are supported - https://github.com/uptrace/bun/blob/master/internal/dbtest/soft_delete_test.go#L56-L87

with big letters and red colors

I will add something like that when Bun reaches v1.0 (July or August I guess).

[iorlas]

Aah, didn't spot it right away. I guess the docs have some gaps, but it is fine for now. Thank you for your work!

To the next ppl looking into the issue: migrate off the go-pg to the Bun, which could be used as a ORM and Raw SQL executer since it is built on top of the database/sql package.