Feature request: show SQL query in terminal(log-level=debug)

[sbayeta]

Hi guys, congratulations on this excellent software!

Could you please consider adding a configuration option to output the SQL query sent to the db with each request? I was curious about the generated SQL by a nested embedded resource request. I was able to do it by enabling statement logging in the database, but I thought it would be nice to be able to see it in the console running postgREST.

Thanks for your terrific work!

Cheers! Santiago

[swuecho]

You might find this issue helpful. https://github.com/PostgREST/postgrest/issues/1573

[steve-chavez]

@sbayeta Thanks!

We've always recommended looking at the pg logs because they are more complete, but I can see how printing the SQL ourselves might give better UX.

I think this would be a "debug" logging level. This level would only show the main SQL query and not our SET LOCAL stuff. We'll still recommend looking at the pg logs for more information.

Related to https://github.com/PostgREST/postgrest/issues/540.

[sbayeta]

Hi Steve, thank you for your excellent support!

I read the issue you mentioned, as well as swuecho's, and I get there's a lot of nuances regarding this.

I'm not sure how valuable this feature would be for others, specially when weighing the details mentioned in the linked issues. For me, getting the pg logs to work was a bit of a hassle since I haven't done that before, but I guess it's something one has to learn in order to maintain a pb db propperly. Now I know how to do it, but it's still cumbersome to enable and (not forget to) disable logging, and look for the specific entries of interest (in my case, pgadmin4 floods the logs so they were greater than 1MB with only one pgrest request).

So if there's a way to catch at least the simple use case of seeing the generated SQL right in the console by setting the postgrest log level, I would probably be a nice addition.

Thanks again for your support and congratulations.

Cheers, Santiago

[wolfgangwalther]

I think this would be a "debug" logging level. This level would only show the main SQL query and not our SET LOCAL stuff. We'll still recommend looking at the pg logs for more information.

Maybe we can make this orthogonal to the logging level? If this was another logging setting, that would enable the logging of queries, it could be a bit easier to find the correct query.

So, e.g. assume that I only care about the troublesome stuff, so I set the logging level to error. It would be good, if I could now output the query only in that case as well, without suddenly dumping ALL requests to the log via logging level debug.

So basically the logging level decides when to log and the other setting decides what to log.

Thoughts?

[steve-chavez]

@wolfgangwalther I actually was thinking that we should always print the query on 5xx responses(error level). Some users want to trace the error without going to the db logs. What do you think?

[wolfgangwalther]

I would not print the query all the time. This could be default setting, but there should be an option. And I would like to be able to print the query for others as well.

So:

• log-level = crit | error | warn | info
• log-query = true | false

The default can of course be error / true. But the log-query option should affect every log-level. Of course the query is only logged when the request is logged with it. When the request is not logged because of the log-level, the query is not logged as well.

Does that make sense?

[steve-chavez]

If we output the queries with their placeholders($1, $2), that could offer a nice way to test them with the pg 16 generic plan: https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/

Additionally it would be nice to output the queries as an http response:

• Using Accept: application/sql
• Enabled with db-plan-enabled

That would allow us to have SQL snippets on all the API docs. With some work those could be autogenerated too.

[steve-chavez]

robx shared a way to render the Snippet type to Bytestring: https://github.com/PostgREST/postgrest/compare/main...robx:postgrest:show-snippet

With that, we can render our generated query on terminal or as a response. For adding this we need to refactor and make the Query module pure.