Migrate client.sql() off deprecated v1 SQL-runner endpoints to the v2 query/sql flow

[jpetey75]

Problem

client.sql() (the SQL Runner) calls Lightdash API endpoints that have been deprecated and are past their sunset date ("may be removed at any time"). If Lightdash removes them, client.sql() breaks.

This is independent of query execution (.execute()), which already uses the current v2 async flow and is unaffected.

Deprecated endpoints in use

lightdash/sql_runner.py currently calls:

SDK call	Lightdash status
POST /api/v1/projects/{uuid}/sqlRunner/run	@deprecated → POST /api/v2/projects/{uuid}/query/sql
GET /api/v1/projects/{uuid}/sqlRunner/results/{...}	@deprecated → GET /api/v2/projects/{uuid}/query/{queryUuid}/results
GET /api/v1/schedulers/job/{jobId}/status	(part of the old job-polling flow)

Source: packages/backend/src/controllers/sqlRunnerController.ts — the run route is annotated @deprecated Use POST /api/v2/projects/{projectUuid}/query/sql instead, and the results route @deprecated Use GET /api/v2/projects/{projectUuid}/query/{queryUuid}/results instead. These are part of the batch deprecated 2025-02-17 with sunset 2025-05-17.

The Lightdash CLI has already migrated to v2 (packages/cli/src/handlers/sql.ts → /api/v2/projects/${projectUuid}/query/sql), so the replacement path is proven.

Proposed solution

Migrate client.sql() to the v2 async query flow:

1. POST /api/v2/projects/{uuid}/query/sql with the SQL → returns a queryUuid
2. Poll GET /api/v2/projects/{uuid}/query/{queryUuid} until ready, then page results

This mirrors the flow the SDK already implements for metric queries in _QueryExecutor (query.py) — submit → poll → paginate — so much of that logic can be reused/shared rather than written from scratch.

Open questions for the team

• Introspection endpoints: sqlRunner/tables and sqlRunner/fields (used by client.sql_runner.tables()/fields()) do not appear to carry @deprecated markers. Leave them on v1, or migrate everything for consistency?
• Response shape: does v2 /query/sql + /query/{queryUuid}/results return the same row/column shape SqlResult expects, or does the result transform need adjusting?
• Shared executor: is it worth refactoring _QueryExecutor so both metric-query and SQL-query share the submit/poll/paginate machinery?
• Priority: the sunset date has already passed — how urgent is this relative to other work?

Scope note

.execute() / metric queries, .compile() (#5), explores metadata, and /health are all on current, non-deprecated endpoints — this issue is specifically the SQL Runner.

[linear-code]

PROD-8540