supabase/e2e/studio/features/sql-editor.spec.ts

import fs from 'fs'
import { expect, Page } from '@playwright/test'

import { env } from '../env.config.js'
import { expectClipboardValue } from '../utils/clipboard.js'
import { dropTable, query } from '../utils/db/index.js'
import { isCLI } from '../utils/is-cli.js'
import { resetLocalStorage } from '../utils/reset-local-storage.js'
import { test } from '../utils/test.js'
import { toUrl } from '../utils/to-url.js'
import { waitForApiResponseWithTimeout } from '../utils/wait-for-response-with-timeout.js'
import { waitForApiResponse } from '../utils/wait-for-response.js'

const sqlSnippetName = 'pw_sql_snippet'
const sqlSnippetNameDuplicate = 'pw_sql_snippet (Duplicate)'
const sqlSnippetNameFolder = 'pw_sql_snippet_folder'
const sqlSnippetNameFavorite = 'pw_sql_snippet_favorite'
const sqlSnippetNameShare = 'pw_sql_snippet_share'
const sqlFolderName = 'pw_sql_folder'
const sqlFolderNameUpdated = 'pw_sql_folder_updated'
const newSqlSnippetName = 'Untitled query'

/**
 * Due to how sql editor is created, it's very annoying to test SQL editor in staging, I've created various workarounds to help mitigate flaky tests as much as possible.
 *
 * List of problems:
 * 1. The connection string loading is very intermitten which leads to results not showing on the results tab. Sometimes it loads and sometimes it doesn't.
 *    > I've created a workaround by waiting for the api call which loads the connection string, and also ignore the error if the API call after 3 seconds. (Assuming that the connection string is already loaded)
 * 2. The only way to access actions in the sidebar, is by right clicking unlike the table editor. This might cause issues as keyboard and mouse click actions are not consistent enough.
 *    > The best way to mitigate this, is clear all SQL snippets before and after each tests.
 * 3. There would random have these errors "Sorry, An unexpected errors has occurred." when sharing sql snippet.
 *    > Have not figured out why this is happening. My guess is that when we click too fast things are not loaded properly and it's causing errors.
 *    > Full error: Cannot read properties of undefined (reading 'type')
 *
 */

const deleteSqlSnippet = async (page: Page, ref: string, sqlSnippetName: string) => {
  const privateSnippet = page.getByLabel('private-snippets')
  await privateSnippet.getByText(sqlSnippetName).last().click({ button: 'right' })
  await page.getByRole('menuitem', { name: 'Delete query' }).click()
  await expect(page.getByRole('heading', { name: 'Confirm to delete query' })).toBeVisible()
  await page.getByRole('button', { name: 'Delete 1 query' }).click()
  await waitForApiResponse(page, 'projects', ref, 'content', { method: 'DELETE' })
  await page.waitForTimeout(500)
}

const deleteFolder = async (page: Page, ref: string, folderName: string) => {
  await page.getByText(folderName, { exact: true }).click({ button: 'right' })
  await page.getByRole('menuitem', { name: 'Delete folder' }).click()
  await page.getByRole('button', { name: 'Delete folder' }).click()
  await waitForApiResponse(page, 'projects', ref, 'content/folders', {
    method: 'DELETE',
  })
}

test.describe('SQL Editor', () => {
  test.skip(
    env.IS_PLATFORM,
    'This test does not work in hosted environments. Self hosted mode is supported.'
  )

  let page: Page

  test.beforeAll(async ({ browser, ref }) => {
    test.setTimeout(60000)

    // Create a new table for the tests
    page = await browser.newPage()
    await page.goto(toUrl(`/project/${ref}/sql/new?skip=true`))

    await resetLocalStorage(page, ref)

    // intercept AI title generation to prevent flaky tests
    await page.route('**/dashboard/api/ai/sql/title-v2', async (route) => {
      await route.abort()
    })
  })

  test.beforeEach(async ({ ref }) => {
    test.setTimeout(60000)

    await page.goto(toUrl(`/project/${ref}/sql/new?skip=true`))
    // this is required to load the connection string
    if (!isCLI()) {
      await waitForApiResponseWithTimeout(
        page,
        (response) => response.url().includes('profile/permissions'),
        3000
      )
      await waitForApiResponseWithTimeout(
        page,
        (response) => response.url().includes('profile'),
        3000
      )
    }
  })

  test.afterAll(async ({ ref }) => {
    if ((await page.getByLabel('private-snippets').count()) === 0) {
      return
    }

    if (isCLI()) {
      //  In self-hosted environments, we don't have access to the supabase platform, reloading would clear/reset all the sql snippets.
      await page.reload()
      return
    }

    // remove sql snippets for "Untitled query" and "pw_sql_snippet"
    const privateSnippet = page.getByLabel('private-snippets')
    let privateSnippetText = await privateSnippet.textContent()
    while (privateSnippetText?.includes(newSqlSnippetName)) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
      privateSnippetText =
        (await page.getByLabel('private-snippets').count()) > 0
          ? await privateSnippet.textContent()
          : ''
    }

    while (privateSnippetText?.includes(sqlSnippetName)) {
      await deleteSqlSnippet(page, ref, sqlSnippetName)
      privateSnippetText =
        (await page.getByLabel('private-snippets').count()) > 0
          ? await privateSnippet.textContent()
          : ''
    }
  })

  test('should check if SQL editor is working as expected', async ({ ref }) => {
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`select 'hello world';`)

    const sqlMutationPromise = waitForApiResponse(page, 'pg-meta', ref, 'query?key=', {
      method: 'POST',
    })
    await page.getByTestId('sql-run-button').click()
    await sqlMutationPromise

    // verify the result
    await expect(page.getByRole('gridcell', { name: 'hello world' })).toBeVisible()

    // SQL written in the editor should not be the previous query.
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`select length('hello');`)
    await page.getByTestId('sql-run-button').click()

    // verify the result is updated.
    await waitForApiResponse(page, 'pg-meta', ref, 'query?key=', { method: 'POST' })
    await expect(page.getByRole('gridcell', { name: '5' })).toBeVisible()

    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`delete table 'test';`)
    await page.getByTestId('sql-run-button').click()

    // verify warning modal is visible
    await expect(page.getByRole('heading', { name: 'Potential issue detected' })).toBeVisible()
    await expect(page.getByText('This query includes destructive operations')).toBeVisible()
    await page.getByRole('button', { name: 'Cancel' }).click()

    // clear SQL snippet
    if (!isCLI()) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
    } else {
      await page.reload()
    }
  })

  test('should block execution for alter database connection limit 0', async ({ ref }) => {
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`alter database postgres connection limit 0;`)

    // Track whether the SQL editor dispatches this specific query to pg-meta
    let queryDispatched = false
    const listener = (request: any) => {
      if (
        request.url().includes('query?key=') &&
        request.method() === 'POST' &&
        request.postData()?.includes('connection limit 0')
      ) {
        queryDispatched = true
      }
    }
    page.on('request', listener)

    await page.getByTestId('sql-run-button').click()

    // verify warning modal blocks execution
    await expect(page.getByRole('heading', { name: 'Potential issue detected' })).toBeVisible()
    await expect(page.getByText('This query may prevent new database connections')).toBeVisible()
    expect(queryDispatched).toBe(false)

    // cancel should dismiss without executing
    await page.getByRole('button', { name: 'Cancel' }).click()
    expect(queryDispatched).toBe(false)

    page.removeListener('request', listener)

    // clear SQL snippet
    if (!isCLI()) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
    } else {
      await page.reload()
    }
  })

  test('should block execution for alter database allow_connections false', async ({ ref }) => {
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`ALTER DATABASE postgres ALLOW_CONNECTIONS false;`)

    // Track whether the SQL editor dispatches this specific query to pg-meta
    let queryDispatched = false
    const listener = (request: any) => {
      if (
        request.url().includes('query?key=') &&
        request.method() === 'POST' &&
        request.postData()?.includes('ALLOW_CONNECTIONS false')
      ) {
        queryDispatched = true
      }
    }
    page.on('request', listener)

    await page.getByTestId('sql-run-button').click()

    // verify warning modal blocks execution
    await expect(page.getByRole('heading', { name: 'Potential issue detected' })).toBeVisible()
    await expect(page.getByText('This query may prevent new database connections')).toBeVisible()
    expect(queryDispatched).toBe(false)

    // cancel should dismiss without executing
    await page.getByRole('button', { name: 'Cancel' }).click()
    expect(queryDispatched).toBe(false)

    page.removeListener('request', listener)

    // clear SQL snippet
    if (!isCLI()) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
    } else {
      await page.reload()
    }
  })

  test('should block execution for update without where clause', async ({ ref }) => {
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`update countries set name = 'test';`)

    // Track whether the SQL editor dispatches this specific query to pg-meta
    let queryDispatched = false
    const listener = (request: any) => {
      if (
        request.url().includes('query?key=') &&
        request.method() === 'POST' &&
        request.postData()?.includes("set name = 'test'")
      ) {
        queryDispatched = true
      }
    }
    page.on('request', listener)

    await page.getByTestId('sql-run-button').click()

    // verify warning modal blocks execution
    await expect(page.getByRole('heading', { name: 'Potential issue detected' })).toBeVisible()
    await expect(page.getByText(/This query runs an UPDATE without a WHERE clause/)).toBeVisible()
    expect(queryDispatched).toBe(false)

    // cancel should dismiss without executing
    await page.getByRole('button', { name: 'Cancel' }).click()
    expect(queryDispatched).toBe(false)

    page.removeListener('request', listener)

    // clear SQL snippet
    if (!isCLI()) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
    } else {
      await page.reload()
    }
  })

  test('warns on CREATE TABLE without RLS and "Run and enable RLS" enables it', async ({ ref }) => {
    // Suffix with parallel worker index so parallel workers don't collide
    // on the same table name — when they do, one worker's `dropTable`
    // races another's "Run and enable RLS" and the post-action query
    // sometimes finds the table missing.
    const tableName = `pw_rls_smoke_test_${test.info().parallelIndex}`

    // Drop any leftover table from a previous failed run, and ensure cleanup
    // after the test regardless of pass/fail.
    await dropTable(tableName)

    try {
      await expect(page.getByText('Loading...')).not.toBeVisible()
      await page.locator('.view-lines').click()
      await page.keyboard.press('ControlOrMeta+KeyA')
      await page.keyboard.type(`create table ${tableName} (id int8 primary key);`)

      await page.getByTestId('sql-run-button').click()

      // Modal appears with the RLS warning
      await expect(
        page.getByRole('heading', { name: 'Potential issue detected' }),
        'Warning modal should appear when CREATE TABLE has no RLS'
      ).toBeVisible()
      await expect(
        page.getByText('This query creates a table without enabling Row Level Security'),
        'Modal should mention Row Level Security'
      ).toBeVisible()

      // Click "Run and enable RLS" — query runs with appended ALTER
      const sqlMutationPromise = waitForApiResponse(page, 'pg-meta', ref, 'query?key=', {
        method: 'POST',
      })
      await page.getByRole('button', { name: 'Run and enable RLS' }).click()
      await sqlMutationPromise

      // Verify the table was created with RLS enabled
      const rows = await query<{ relrowsecurity: boolean }>(
        `select c.relrowsecurity
         from pg_class c
         join pg_namespace n on n.oid = c.relnamespace
         where n.nspname = 'public' and c.relname = $1`,
        [tableName]
      )
      expect(rows[0]?.relrowsecurity, 'Table should exist and have RLS enabled').toBe(true)
    } finally {
      await dropTable(tableName)

      // clear SQL snippet
      if (!isCLI()) {
        await deleteSqlSnippet(page, ref, newSqlSnippetName)
      } else {
        await page.reload()
      }
    }
  })

  test('does not warn on CREATE FUNCTION with plpgsql SELECT..INTO variable assignment', async ({
    ref,
  }) => {
    // Regression for the parser false-positive where `select ... into var`
    // inside a $$...$$ plpgsql body was mistaken for SELECT..INTO creating
    // a new table, firing the CREATE-TABLE-without-RLS warning modal.
    const fnName = 'pw_rls_regression_fn'

    // Clean up any leftover function from a previous run
    await query(`drop function if exists public.${fnName}()`)

    try {
      await expect(page.getByText('Loading...')).not.toBeVisible()
      await page.locator('.view-lines').click()
      await page.keyboard.press('ControlOrMeta+KeyA')
      await page.keyboard.type(
        `create or replace function ${fnName}() returns jsonb language plpgsql as $$ declare ret jsonb; begin select jsonb_build_object('ok', true) into ret; return ret; end; $$;`
      )

      const sqlMutationPromise = waitForApiResponse(page, 'pg-meta', ref, 'query?key=', {
        method: 'POST',
      })
      await page.getByTestId('sql-run-button').click()
      await sqlMutationPromise

      // If the warning modal had fired, the query would have been blocked and
      // the waiter above would have timed out. Belt-and-braces check that the
      // modal is not visible.
      await expect(
        page.getByRole('heading', { name: /Potential issues? detected/ }),
        'RLS warning should not fire on CREATE FUNCTION with plpgsql SELECT..INTO'
      ).not.toBeVisible()

      // Confirm the function was actually created
      const rows = await query<{ exists: boolean }>(
        `select exists (
           select 1 from pg_proc p
           join pg_namespace n on n.oid = p.pronamespace
           where n.nspname = 'public' and p.proname = $1
         ) as exists`,
        [fnName]
      )
      expect(rows[0]?.exists, 'Function should have been created').toBe(true)
    } finally {
      await query(`drop function if exists public.${fnName}()`)

      // clear SQL snippet
      if (!isCLI()) {
        await deleteSqlSnippet(page, ref, newSqlSnippetName)
      } else {
        await page.reload()
      }
    }
  })

  test('should not show warning modal for safe alter database statement', async ({ ref }) => {
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`alter database postgres set statement_timeout = 60000;`)

    const sqlMutationPromise = waitForApiResponse(page, 'pg-meta', ref, 'query?key=', {
      method: 'POST',
    })
    await page.getByTestId('sql-run-button').click()
    await sqlMutationPromise

    // verify warning modal is NOT visible - query should execute directly
    await expect(
      page.getByRole('heading', { name: /Potential issues? detected/ })
    ).not.toBeVisible()

    // clear SQL snippet
    if (!isCLI()) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
    } else {
      await page.reload()
    }
  })

  test('exporting works as expected', async ({ ref }) => {
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`select 'hello world';`)
    await page.getByTestId('sql-run-button').click()

    // export as Markdown
    await page.getByRole('button', { name: 'Export' }).click()
    await page.getByRole('menuitem', { name: 'Copy as Markdown' }).click()
    // Make sure the dropdown has closed otherwise it would make the other assertions unstable
    await expect(page.getByRole('menuitem', { name: 'Copy as Markdown' })).not.toBeVisible()
    await expectClipboardValue({
      page,
      value: `| ?column?    |
| ----------- |
| hello world |`,
      exact: true,
    })

    // export as JSON
    await page.getByRole('button', { name: 'Export' }).click()
    await page.getByRole('menuitem', { name: 'Copy as JSON' }).click()
    await expect(page.getByRole('menuitem', { name: 'Copy as JSON' })).not.toBeVisible()
    await expectClipboardValue({
      page,
      value: `[
  {
    "?column?": "hello world"
  }
]`,
      exact: true,
    })

    // export as CSV
    const downloadPromise = page.waitForEvent('download')
    await page.getByRole('button', { name: 'Export' }).click()
    await page.getByRole('menuitem', { name: 'Download CSV' }).click()
    await expect(page.getByRole('menuitem', { name: 'Download CSV' })).not.toBeVisible()
    const download = await downloadPromise
    expect(download.suggestedFilename()).toContain('.csv')
    const downloadPath = await download.path()
    const csvContent = fs.readFileSync(downloadPath, 'utf-8').replace(/\r?\n/g, '\n')
    expect(csvContent).toBe(`?column?
hello world`)
    fs.unlinkSync(downloadPath)

    // clear SQL snippet
    if (!isCLI()) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
    } else {
      await page.reload()
    }
  })

  test('snippet favourite works as expected', async ({ ref }) => {
    test.skip(isCLI(), 'This test does not work in self-hosted environments.')

    // clean up private snippets and snippets shared with the team
    await waitForApiResponseWithTimeout(
      page,
      (response) => response.url().includes('query?key=table-columns'),
      3000
    )
    const privateSnippetSection = page.getByLabel('private-snippets')
    if ((await privateSnippetSection.getByText(newSqlSnippetName, { exact: true }).count()) > 0) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
    }

    if (
      (await privateSnippetSection.getByText(sqlSnippetNameFavorite, { exact: true }).count()) > 0
    ) {
      await deleteSqlSnippet(page, ref, sqlSnippetNameFavorite)
    }

    // create sql snippet
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`select 'hello world';`)
    await page.getByTestId('sql-run-button').click()

    // rename snippet
    await privateSnippetSection.getByText(newSqlSnippetName).click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Rename query', exact: true }).click()
    await expect(page.getByRole('heading', { name: 'Rename' })).toBeVisible()
    await page.getByRole('textbox', { name: 'Name' }).fill(sqlSnippetNameFavorite)
    await page.getByRole('button', { name: 'Rename query', exact: true }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    await expect(
      privateSnippetSection.getByText(sqlSnippetNameFavorite, { exact: true })
    ).toBeVisible()
    await page.waitForTimeout(2000) // wait for sql snippets cache to invalidate.

    // open up shared and favourites sections
    await page.getByRole('button', { name: 'Favorites' }).click()

    // favourite snippets
    await page.getByTestId('sql-editor-utility-actions').click()
    await page.getByRole('menuitem', { name: 'Add to favorites', exact: true }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    const favouriteSnippetsSection = page.getByLabel('favorite-snippets')
    await expect(
      favouriteSnippetsSection.getByText(sqlSnippetNameFavorite, { exact: true })
    ).toBeVisible()

    // unfavorite snippets
    await page.getByTestId('sql-editor-utility-actions').click()
    await page.getByRole('menuitem', { name: 'Remove from favorites' }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    await expect(
      favouriteSnippetsSection.getByText(sqlSnippetNameFavorite, { exact: true })
    ).not.toBeVisible()

    // clear SQL snippet
    if (!isCLI()) {
      await deleteSqlSnippet(page, ref, sqlSnippetNameFavorite)
    } else {
      await page.reload()
    }
  })

  test('share with team works as expected', async ({ ref }) => {
    test.skip(isCLI(), 'Sharing and unsharing SQL snippet has issues in staging')

    // clean up private snippets and snippets shared with the team
    await waitForApiResponseWithTimeout(
      page,
      (response) => response.url().includes('query?key=table-columns'),
      3000
    )
    const privateSnippetSection = page.getByLabel('private-snippets')
    if ((await privateSnippetSection.getByText(newSqlSnippetName, { exact: true }).count()) > 0) {
      await deleteSqlSnippet(page, ref, newSqlSnippetName)
    }

    if ((await privateSnippetSection.getByText(sqlSnippetNameShare, { exact: true }).count()) > 0) {
      // this would delete snippets from both favorite and private snippets sections
      await deleteSqlSnippet(page, ref, sqlSnippetNameShare)
    }

    if ((await page.getByRole('button', { name: 'Shared' })?.textContent())?.includes('(')) {
      const sharedSnippetSection = page.getByLabel('project-level-snippets')
      await page.getByRole('button', { name: 'Shared' }).click()

      let sharedSnippetText = await sharedSnippetSection.textContent()
      while (sharedSnippetText?.includes(sqlSnippetNameShare)) {
        await sharedSnippetSection.getByText(sqlSnippetName).last().click({ button: 'right' })
        await page.getByRole('menuitem', { name: 'Delete query' }).click()
        await expect(page.getByRole('heading', { name: 'Confirm to delete query' })).toBeVisible()
        await page.getByRole('button', { name: 'Delete 1 query' }).click()
        await waitForApiResponse(page, 'projects', ref, 'content', { method: 'DELETE' })
        await page.waitForTimeout(500)
        sharedSnippetText =
          (await page.getByLabel('project-level-snippets').count()) > 0
            ? await sharedSnippetSection.textContent()
            : ''
      }
      await page.getByRole('button', { name: 'Shared' }).click()
    }

    // create sql snippet
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`select 'hello world';`)
    await page.getByTestId('sql-run-button').click()

    // rename snippet
    await privateSnippetSection.getByText(newSqlSnippetName).click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Rename query', exact: true }).click()
    await expect(page.getByRole('heading', { name: 'Rename' })).toBeVisible()
    await page.getByRole('textbox', { name: 'Name' }).fill(sqlSnippetNameShare)
    await page.getByRole('button', { name: 'Rename query', exact: true }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    await expect(
      privateSnippetSection.getByText(sqlSnippetNameShare, { exact: true })
    ).toBeVisible()
    await page.waitForTimeout(2000) // wait for sql snippets cache to invalidate.

    // open up shared and favourites sections
    await page.getByRole('button', { name: 'Shared' }).click()

    // share with a team
    const snippet = privateSnippetSection.getByText(sqlSnippetNameShare, { exact: true })
    await snippet.click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Share query with team' }).click()
    await expect(page.getByRole('heading', { name: 'Confirm to share query' })).toBeVisible()
    await page.waitForTimeout(1000)
    await page.getByRole('button', { name: 'Share query', exact: true }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    const sharedSnippet = page.getByLabel('project-level-snippets')
    await expect(sharedSnippet.getByText(sqlSnippetNameShare, { exact: true })).toBeVisible({
      timeout: 5000,
    })

    // unshare a snippet
    await sharedSnippet.getByText(sqlSnippetNameShare).click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Unshare query with team' }).click()
    await expect(page.getByRole('heading', { name: 'Confirm to unshare query:' })).toBeVisible()

    const unsharePromise = waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    await page.getByRole('button', { name: 'Unshare query', exact: true }).click()
    await unsharePromise
    await expect(page.getByTestId('confirm-unshare-snippet-modal')).not.toBeVisible()
    await expect(sharedSnippet.getByText(sqlSnippetNameShare, { exact: true })).not.toBeVisible()

    // clear SQL snippet
    if (!isCLI()) {
      await deleteSqlSnippet(page, ref, sqlSnippetNameShare)
    } else {
      await page.reload()
    }
  })

  test('folders works as expected', async ({ ref }) => {
    test.skip(isCLI(), 'This test does not work in self-hosted environments.')
    // clean up folders and snippets
    await waitForApiResponseWithTimeout(
      page,
      (response) => response.url().includes('query?key=table-columns'),
      3000
    )
    const privateSnippetSection = page.getByLabel('private-snippets')
    if ((await privateSnippetSection.getByText(sqlFolderName, { exact: true }).count()) > 0) {
      await deleteFolder(page, ref, sqlFolderName)
    }
    if (
      (await privateSnippetSection.getByText(sqlFolderNameUpdated, { exact: true }).count()) > 0
    ) {
      await deleteFolder(page, ref, sqlFolderNameUpdated)
    }

    // create sql snippet
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`select 'hello world';`)
    await page.getByTestId('sql-run-button').click()

    // rename snippet
    await privateSnippetSection.getByText(newSqlSnippetName).click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Rename query', exact: true }).click()
    await expect(page.getByRole('heading', { name: 'Rename' })).toBeVisible()
    await page.getByRole('textbox', { name: 'Name' }).fill(sqlSnippetNameFolder)
    await page.getByRole('button', { name: 'Rename query', exact: true }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    await expect(
      privateSnippetSection.getByText(sqlSnippetNameFolder, { exact: true })
    ).toBeVisible()
    await page.waitForTimeout(2000) // wait for sql snippets cache to invalidate.

    // create a folder
    await page.getByTestId('sql-editor-new-query-button').click()
    await page.getByRole('menuitem', { name: 'Create a new folder' }).click()
    await page.getByRole('tree', { name: 'private-snippets' }).getByRole('textbox').click()
    await page
      .getByRole('tree', { name: 'private-snippets' })
      .getByRole('textbox')
      .fill(sqlFolderName)
    await page.waitForTimeout(500)
    await page.locator('.view-lines').click() // blur input and renames folder
    await waitForApiResponse(page, 'projects', ref, 'content/folders', { method: 'POST' })
    await expect(page.getByText('Successfully created folder')).toBeVisible()

    // rename a folder
    await privateSnippetSection.getByText(sqlFolderName).click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Rename folder' }).click()
    await page
      .getByRole('treeitem', { name: sqlFolderName })
      .getByRole('textbox')
      .fill(sqlFolderNameUpdated)
    await page.waitForTimeout(500)
    await page.locator('.view-lines').click() // blur input and renames folder
    await waitForApiResponse(page, 'projects', ref, 'content/folders', { method: 'PATCH' })

    // move sql snippet into folder
    await privateSnippetSection.getByText(sqlSnippetNameFolder).click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Move query' }).click()
    await page.getByRole('button', { name: 'Root of the editor (Current)' }).click()
    await page.getByRole('option', { name: sqlFolderNameUpdated, exact: true }).click()
    await page.getByRole('button', { name: 'Move file' }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    await expect(page.getByText('Successfully moved')).toBeVisible({
      timeout: 5000,
    })

    // delete a folder + deleting a folder would also remove the SQL snippets within
    await privateSnippetSection
      .getByText(sqlFolderNameUpdated, { exact: true })
      .click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Delete folder' }).click()
    await expect(page.getByRole('heading', { name: 'Confirm to delete folder' })).toBeVisible()
    await page.getByRole('button', { name: 'Delete folder' }).click()
    await waitForApiResponse(page, 'projects', ref, 'content/folders', {
      method: 'DELETE',
    })
    await expect(page.getByText('Successfully deleted folder', { exact: true })).toBeVisible({
      timeout: 5000,
    })
    await expect(privateSnippetSection.getByText(sqlFolderNameUpdated)).not.toBeVisible()
    await expect(privateSnippetSection.getByText(sqlSnippetNameFolder)).not.toBeVisible()
  })

  test('other SQL snippets actions work as expected', async ({ ref }) => {
    test.skip(isCLI(), 'This test does not work in self-hosted environments.')
    // clean up 'Untitled query', 'pw_sql_snippet' and 'pw_sql_snippet (Duplicate)' snippets if exists
    await waitForApiResponseWithTimeout(
      page,
      (response) => response.url().includes('query?key=table-columns'),
      3000
    )
    const privateSnippet = page.getByLabel('private-snippets')
    if ((await privateSnippet.getByText(newSqlSnippetName).count()) > 0) {
      deleteSqlSnippet(page, ref, newSqlSnippetName)
    }
    if ((await privateSnippet.getByText(sqlSnippetNameDuplicate, { exact: true }).count()) > 0) {
      await deleteSqlSnippet(page, ref, sqlSnippetNameDuplicate)
    }
    if ((await privateSnippet.getByText(sqlSnippetName, { exact: true }).count()) > 0) {
      await deleteSqlSnippet(page, ref, sqlSnippetName)
    }

    // create sql snippet
    await expect(page.getByText('Loading...')).not.toBeVisible()
    await page.locator('.view-lines').click()
    await page.keyboard.press('ControlOrMeta+KeyA')
    await page.keyboard.type(`select 'hello world';`)
    await page.getByTestId('sql-run-button').click()

    // rename snippet
    const privateSnippetSection = page.getByLabel('private-snippets')
    await privateSnippetSection.getByText(newSqlSnippetName).click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Rename query', exact: true }).click()
    await expect(page.getByRole('heading', { name: 'Rename' })).toBeVisible()
    await page.getByRole('textbox', { name: 'Name' }).fill(sqlSnippetName)
    await page.getByRole('button', { name: 'Rename query', exact: true }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    await expect(privateSnippetSection.getByText(sqlSnippetName, { exact: true })).toBeVisible()
    await page.waitForTimeout(2000) // wait for sql snippets cache to invalidate.

    // duplicate SQL snippet
    await privateSnippetSection
      .getByTitle(sqlSnippetName, { exact: true })
      .click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Duplicate query' }).click()
    await waitForApiResponse(page, 'projects', ref, 'content', { method: 'PUT' })
    await expect(
      privateSnippetSection.getByText(sqlSnippetNameDuplicate, { exact: true })
    ).toBeVisible()

    // filter SQL snippets
    const searchBar = page.getByRole('textbox', { name: 'Search queries...' })
    await searchBar.fill('Duplicate')
    await expect(page.getByText(sqlSnippetName, { exact: true })).not.toBeVisible()
    await expect(page.getByTitle(sqlSnippetNameDuplicate, { exact: true })).toBeVisible()
    await expect(page.getByText('result found')).toBeVisible()
    await searchBar.fill('') // clear search bar

    // export query as migration file
    await privateSnippetSection
      .getByTitle(sqlSnippetName, { exact: true })
      .click({ button: 'right' })
    await page.getByRole('menuitem', { name: 'Export query' }).click()
    await expect(page.getByText('supabase migration new')).toBeVisible()
    await page.getByRole('button', { name: 'Close' }).click()

    // delete all files used in this test
    await deleteSqlSnippet(page, ref, sqlSnippetNameDuplicate)
    await deleteSqlSnippet(page, ref, sqlSnippetName)
  })
})