feat: add text format to explain()

[steve-chavez]

json format for explain was added in #293 but it's hard to read. This PR adds a text format so it looks like:

postgrest.from('users').select().explain({format: 'text'}).then(({ data, error }) => console.log(data, error))

Aggregate  (cost=17.65..17.68 rows=1 width=112)
  ->  Seq Scan on users  (cost=0.00..15.10 rows=510 width=132)

[kiwicopple]

I'm wondering how the developer workflow for this will be?

If I was building an app, I would probably want to do something like:

createClient('URL', 'KEY', {
  db: {
    withQueryAnalyzer: process.env.ENVIRONMENT == 'test' ? 'explain' : null // leave room for "explain analyze"?
  }
})

const { data, error, plan } = await supabase.from('table').select()

console.log('plan', plan)

I think the key is that developers won't want to edit their code a lot to get both the plan and the data, but it looks like PostgREST returns either an explain or a request? (which would make this workflow difficult)

Another workflow that they could adopt is

const query = supabase.from('table').select()

const { data, error } = await query

if (process.env.ENVIRONMENT != 'production') {
  const { data } = await query.explain()
  console.log('query plan', data)
}

Not sure what the best practice would be here. This is a really awesome feature, and I think it will be very powerful if we can build a simple workflow around it

[steve-chavez]

Main idea is to allow users to check how their RLS policies are affecting JS queries, from the JS client itself. Otherwise this requires some extra steps on the SQL editor; doable as shown on the slip blog post but doing it from JS provides quicker feedback.

I'm wondering how the developer workflow for this will be?

I thought basically:

const { user, session, error } = await supabase.auth.signIn({
  email: 'example@email.com',
  password: 'example-password',
})

const { data, error } = await supabase.from('table').select().explain({analyze: true})

console.log(data)
// check the plan, if the query takes too long

'explain' : null // leave room for "explain analyze"?

Note that this is already possible with explain({analyze: true}). Can also be paired with rollback.

const { data, error, plan } = await supabase.from('table').select

Using a different key for the plan seems like a good idea, like what we do for count.

but it looks like PostgREST returns either an explain or a request? (which would make this workflow difficult)

Some issues with that interface:

• PostgREST would need to execute 2 queries for the request and that would break the promise of "1 request = 1 query" thus notably reducing performance.
• Doing EXPLAIN ANALYZE would mean executing the same query twice which would be surprising behavior.
• The EXPLAIN output would be too large(in JSON format specially) to send in a response header.

So for this feature I believe it's better to stick to PostgreSQL behavior: a query cannot get data when using EXPLAIN.

Not sure what the best practice would be here. This is a really awesome feature, and I think it will be very powerful if we can build a simple workflow around it

Not sure either. I think it as the first step towards better observability for executions plans, so definitely not final.

[steve-chavez]

I'm wondering how the developer workflow for this will be?

For me, the ideal workflow would involve using supabase-js in the browser console. There I'd be able to pretty print the data with console.table, copy the execution plan with just the copy(plan) function and perhaps paste it into a plan visualizer like https://explain.depesz.com/ or https://explain.dalibo.com/.

I've been trying to do that but seems I need browserify and additional stuff for getting a minified js library.

It would be great if supabase-js could be included when visiting app.supabase.com/project/_/api.

[steve-chavez]

const { data, error, plan } = await supabase.from('table').select
Using a different key for the plan seems like a good idea, like what we do for count.

I'll move the plan from data to plan in another PR.

Edit: checking on the implementation I'm not sure if it's worth it as the users won't be able to get data and plan at the same time.

[github-actions]

🎉 This PR is included in version 1.0.0-next.8 🎉

The release is available on:

• GitHub release
• npm package (@next dist-tag)

Your semantic-release bot 📦🚀

[github-actions]

🎉 This PR is included in version 1.0.0-rc.2 🎉

The release is available on:

• GitHub release
• npm package (@rc dist-tag)

Your semantic-release bot 📦🚀

[github-actions]

🎉 This PR is included in version 1.0.0 🎉

The release is available on:

• GitHub release
• npm package (@latest dist-tag)

Your semantic-release bot 📦🚀