Convert an orm.Query to a String

[nickretallack]

It would be helpful if there were a way to convert an orm.Query object to a string so I can inspect it in tests or alter it before sending it to the database. I'm surprised that I can't find such a feature in this library since it would be useful when generating documentation.

[elliotcourant]

It might be possible in the future to do something like this, but I believe it was an intentional design decision. But I do not know the exact reason.

If you would like to see the queries though as well as other useful information about what go-pg is doing with the database; you can use Wireshark. Wireshark will let you see the queries being sent to the database as well as the responses from the database in real time independent of your code.

[nickretallack]

I want to build queries that go-pg isn't capable of building. For example, in a test I want to add explain analyze to the start of the query so I can check if it uses a certain index. And in production I want to take two queries built with go-pg and union them.

[g14a]

It would be helpful if there were a way to convert an orm.Query object to a string so I can inspect it in tests or alter it before sending it to the database. I'm surprised that I can't find such a feature in this library since it would be useful when generating documentation.

You can a query hook as soon as you connect to your DB and you can automatically print the formatted query.

type dblogger struct{}

func (d dblogger) AfterQuery(c context.Context, q *pg.QueryEvent) error {
        fmt.Println(q.FormattedQuery)
        return nil
}

and add db.AddQueryHook(dbLogger{}) after connecting to your database. It prints any query hitting the db. @nickretallack

[nickretallack]

That's very awkward. It would be nice if there was a more straightforward way to do this.

[scorsi]

The correct code is:

type dbLogger struct{}

func (d dbLogger) BeforeQuery(c context.Context, q *pg.QueryEvent) (context.Context, error) {
	return c, nil
}

func (d dbLogger) AfterQuery(c context.Context, q *pg.QueryEvent) error {
	fq, _ := q.FormattedQuery()
	fmt.Println(string(fq))
	return nil
}

// later in your code

db := pg.Connect(opt)
db.AddQueryHook(dbLogger{})

It's awkward but it works like a charm.

[nickretallack]

My specific use case: I want to write a test that checks whether or not a database index is used by a query. To do this, I get the query as a string, add explain to the start of it, and search the output for mentions of the index. This works, but it requires me to save the query text in a global variable temporarily. This prevents tests from working concurrently, right?

[scorsi]

Effectively, it does prevent perfect concurrency.

In pseudo code:

• Run query and store the SQL into a global variable (and potentially make the query exits by returning not nil in the return).
• Wait for the query to finish, launch a goroutine with a copy of the global variable.
• Repeat.

In fact, if you want to test if you must declares indexes, you don't requires that sort of things, you should be able to understand what go-pg does exactly under the hoods.

You should make indexes where you put DISTINCT, WHERE, ORDER BY or GROUP BY clauses.

The best way to test if you need indexes is :

• don't add it at the beginning
• look for the time your queries took
• create two database by dumping a part of your database (warning about GDPR !)
• create the index in one of the both db
• run the same query in both db to look if its better with the index
• if it's the case, look at the impact size the index adds

Faster don't means better in all cases. The size of your database is also important. I'm working with Big Data constraints.

[nickretallack]

I'm not testing if I need indexes. I'm testing whether or not indexes were used by a query. This is to prevent queries from drifting away from the indexes that were created for them. If a big query ceases to use its indexes, it can cause a huge performance problem.

I'm sure there are other reasons people would want to get the query as a string in code in a more sensible way. Please consider implementing this feature.

[vmihailenco]

orm.Query does not have a String representation since the same Query can represent SELECT, UPDATE, DELETE etc. In v10 you can use following code to get string representation:

query := db.Model(...)
sel := orm.NewSelectQuery(query)
fmt.Println(sel.String())

The API above is not stable and may be changed at any time. Use at your own risk.