How can I inspect the SQL statements generated by PostgREST?

[awei82]

Is it possible to inspect the raw SQL statements that postgREST is generating for requests?

For example, for a request like this:

curl "http://localhost:3000/people?age=gte.18&student=is.true"

I'd like to be able to review the generated SQL:

SELECT *
FROM people
WHERE age > 18
AND student IS TRUE;

Thanks!

[jenstroeger]

Hah! Funny you ask because I had the same challenge yesterday. Here’s what I did. First, enable statement logging in the db (see also the -c option in the docs):

# Add this to Docker compose, or however you run the Postgres server.
postgres-db:
  image: postgres/postgres:17-alpine3.21@sha256:f325a29ec9deb7039c5f07761d77d79d537dac836ecd99f982f6ca5476724604 
  command: [postgres, -c, log_statement=all]
  ...

and then you’ll see output like this:

postgres-db-1  | 2025-06-14 06:07:22.662 UTC [83] LOG:  execute 12: BEGIN ISOLATION LEVEL READ COMMITTED READ WRITE
postgres-db-1  | 2025-06-14 06:07:22.663 UTC [83] LOG:  execute 13: select set_config('search_path', $1, true), set_config('role', $2, true), set_config('request.jwt.claims', $3, true), set_config('request.method', $4, true), set_config('request.path', $5, true), set_config('request.headers', $6, true), set_config('request.cookies', $7, true)
postgres-db-1  | 2025-06-14 06:07:22.663 UTC [83] DETAIL:  Parameters: $1 = '"api", "public"', $2 = 'anonymous', $3 = '{"role":"anonymous"}', $4 = 'POST', $5 = '/rpc/signup', $6 = '{"accept-encoding":"gzip, deflate","user-agent":"python-requests/2.32.3","content-length":"64","content-type":"application/x-www-form-urlencoded","connection":"keep-alive","host":"localhost:3000","accept":"*/*"}', $7 = '{}'
postgres-db-1  | 2025-06-14 06:07:22.663 UTC [83] LOG:  execute 14: WITH pgrst_source AS (SELECT pgrst_call.pgrst_scalar FROM (SELECT "api"."signup"("email" := $1::text, "password" := $2::text) pgrst_scalar) pgrst_call) SELECT null::bigint AS total_result_set, 1 AS page_total, coalesce(json_agg(_postgrest_t.pgrst_scalar)->0, 'null') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM (SELECT "signup".* FROM "pgrst_source" AS "signup"   ) _postgrest_t
postgres-db-1  | 2025-06-14 06:07:22.663 UTC [83] DETAIL:  Parameters: $1 = '[email protected]', $2 = 'g%#H9aW(#h'
postgres-db-1  | 2025-06-14 06:07:22.668 UTC [83] LOG:  execute 2: COMMIT

You have both, the setup for the endpoint statement (i.e. setting role and configs) and the actual endpoint implementation. A little tedious to rummage through, and maybe there are better ways/filters, but it’s a start I suppose.

Notice the:

(SELECT "api"."signup"("email" := $1::text, "password" := $2::text) pgrst_scalar)

in the execute 14 line which is the endpoint call I made to the /rpc/signup endpoint.

[laurenceisla]

The answer above is the most complete option right now. However, In PostgREST v13 there's another way to log the SQL query (see this section in the docs); for now, it only logs the main query and does not show the parameter values.

[awei82]

I missed that part in the docs - thanks!