fix: make the SQLite Data Catalog's Describe and Show DDL actions work by MaxFreedomPollard · Pull Request #1195 · tconbeer/harlequin

MaxFreedomPollard · 2026-10-23T00:52:03Z

There is no open issue for this. I found it reading src/harlequin_sqlite/interactions.py, so the CHANGELOG entry references none.

Two of the SQLite adapter's Data Catalog actions (right-click, or ., on a table or view) build the wrong SQL:

Describe inserts pragma table_info("main"."orders"). SQLite rejects that with near ".": syntax error for every table and view in every database, because a pragma's argument is a bare name; the database has to qualify the pragma itself, as in pragma "main".table_info("orders").
Show DDL reads an unqualified sqlite_schema, which is main's. For a table or view in an attached (or temp) database it inserts nothing, or, when main has a table with the same name, main's DDL instead of the one you clicked. It also put the label into a string literal unescaped, so for a view called Bob's orders it raised near "s": syntax error.

To reproduce on main:

sqlite3 sales.db "create table orders (id bigint)"
sqlite3 archive.db "create table orders (id bigint, total real)"
harlequin -a sqlite sales.db archive.db

In the Data Catalog, open the context menu on archive > orders. Describe opens a buffer with pragma table_info("archive"."orders"), and running it shows near ".": syntax error. Show DDL opens CREATE TABLE orders (id bigint), which is main.orders.

What are the key elements of this solution?

Both functions now take the database from the relation's parent item, which is already there (show_relation_ddl was guarding on it but not using it):

Describe inserts pragma "<database>".table_info("<relation>").
Show DDL selects from "<database>".sqlite_schema where name = '<relation>'.

Identifiers are double-quoted with any " doubled, which is how search_catalog() already quotes a database name. In Show DDL's string literal, any ' in the label is doubled.

Why did you design your solution this way? Did you assess any alternatives? Are there tradeoffs?

Describe keeps the pragma statement it always meant to insert, so the buffer reads the same, just valid. select * from pragma_table_info('orders', 'archive') also works, but it changes what users see for no gain.

Show DDL now matches on name rather than tbl_name, since tbl_name also matches a table's indexes and triggers. With limit 1 and no order by, the table's own row only came first because of rowid order. HarlequinSqliteConnection.execute() takes no parameters, so the label is escaped into the literal rather than bound.

The DuckDB adapter's versions of these actions are unaffected: describe "db"."schema"."table" is valid DuckDB, and its show_relation_ddl already filters on database and schema.

Does this PR require a change to Harlequin's docs?

No.
Yes, and I have opened a PR at tconbeer/harlequin-web.
Yes; I haven't opened a PR, but the gist of the change is: ...

Did you add or update tests for this change?

Yes.
No, I believe tests aren't necessary.
No, I need help with testing this change.

Please complete the following checklist:

I have added an entry to CHANGELOG.md, under the [Unreleased] section heading. That entry references the issue closed by this PR.
I acknowledge Harlequin's MIT license. I do not own my contribution.

There are two new tests in tests/adapter_tests/test_sqlite.py. Both use a two_databases fixture: main and an attached archive, each with a table called orders, plus a view in archive called Bob's orders.

test_describe_inserts_a_pragma_that_runs runs the text Describe inserts for every relation and checks that it lists that relation's own columns. On main it fails with sqlite3.OperationalError: near ".": syntax error.
test_show_ddl_reads_the_database_the_relation_is_in checks the DDL that Show DDL inserts for every relation. On main it fails with HarlequinQueryError: near "s": syntax error from the quoted view. With the view left out, the diff shows "archive"."orders" getting main's CREATE TABLE orders (id bigint).

Testing was done on macOS arm64 with Python 3.10.15.

uv run pytest tests/adapter_tests/test_sqlite.py -n0 gives 1 failed, 23 passed, 2 skipped. With interactions.py reverted to main and the new tests kept, it gives 3 failed, 21 passed, 2 skipped. The failure common to both runs is test_load_extension, which fails the same way on unmodified main here, because the committed hello0.dylib fixture is x86_64 and dlopen wants arm64e on this machine.

uv run pytest -m "not online" gives 9 failed, 2039 passed, 9 skipped, 112 errors. Running the same test files on unmodified main here fails or errors on exactly the same 121 node IDs. Apart from test_load_extension, they need things this environment denies:

unix sockets under /tmp, for hsql's warm-session tests (test_hsql_equivalence.py, test_hsql_serve.py and test_hsql_session.py)
/tmp/harlequin, for test_keys_app
sysctlbyname, for pyarrow's ORC writer (TestOrcOptions::test_compression)
named semaphores, for the two multi-process tests in test_query_log.py

On Python 3.12.15, tests/adapter_tests/test_sqlite.py gives 1 failed, 24 passed, 1 skipped, where the one failure is again test_load_extension.

ruff format --check . reports 214 files already formatted, ruff check . reports All checks passed!, mypy reports Success: no issues found in 165 source files, and lint-imports reports Contracts: 5 kept, 0 broken.

🤖 Generated with Claude Code

tconbeer

I reviewed this in four separate passes: correctness, reuse/simplification, security, and comments/changelog. The fix is right. Describe and Show DDL now work on main, on an attached database and on temp. They also work with names that contain ", ', spaces, unicode or mixed case, and on WITHOUT ROWID tables, FTS5 tables and their shadow tables. The quoting can't be escaped by a hostile name, and the old tbl_name = '{label}' was injectable, so this is strictly better than before. Both new tests fail when interactions.py is reverted to main and pass on this head.

Should fix (inline)

Show DDL can return a trigger's DDL when a trigger has the same name as the table. Fix: add and type in ('table', 'view').
_quote_identifier copies quoting the adapter already does in search_catalog. Suggest one shared helper.

Pre-existing, not introduced here (suggest a follow-up issue)

catalog.py wraps names in " without escaping embedded quotes.
- Confirmed Drop Table on a table named victim" -- runs drop table "main"."victim" --" and drops victim. The confirm dialog shows the crafted name.
- Preview Data on a similar name produces a multi-statement buffer.
- The shared helper from (2) fixes both.
_get_relations and _get_columns (adapter.py:340, :347) don't escape names either, and _get_columns doesn't quote the database at all. Expanding a table named has'sq fails with near "sq": syntax error, so Describe now works on names whose catalog node won't expand.
HarlequinSqliteCursor.fetchone() returns (None,) rather than None when nothing matches. As a result, Show DDL on a relation dropped since the catalog loaded inserts None into a new buffer.

Not checked

SQLite older than 3.33, which is when sqlite_schema appeared. main already depends on it in three places, so this PR adds no new requirement.
The inserted pragma running through the app's editor, statement splitter and HarlequinSqliteConnection.execute(). I ran it on the raw sqlite3 connection only, not through pilot.
Show DDL in manual transaction mode, where connection.execute() may issue begin.
The DuckDB adapter's interactions.
Windows/macOS. tests/adapter_tests/test_sqlite.py was run on Linux with Python 3.10 only; I did not run the full suite, mypy or lint-imports.
Whether an issue exists for the CHANGELOG entry to reference.

Generated by Claude Code

tconbeer · 2026-10-23T03:13:18Z

-        from sqlite_schema
-        where tbl_name = '{item.label}'
+        from {_quote_identifier(item.parent.label)}.sqlite_schema
+        where name = {_quote_literal(item.label)}


Should fix — narrow regression. In SQLite a trigger can share a table's name, because triggers have their own namespace. With no type filter here, where name = … returns whichever matching row comes first:

create table base(a); create trigger samename after insert on base begin select 1; end; create table samename(x int);

Here, Show DDL on main.samename inserts CREATE TRIGGER samename …, while main's tbl_name filter returns CREATE TABLE samename(x int). Tables, views and indexes can't share a name with each other, so filtering on type is enough:

Suggested change

where name = {_quote_literal(item.label)}

where name = {_quote_literal(item.label)} and type in ('table', 'view')

A trigger in two_databases with the same name as one of its relations would pin this.

Generated by Claude Code

tconbeer · 2026-10-23T03:13:23Z

+def _quote_identifier(name: str) -> str:
+    return '"' + name.replace('"', '""') + '"'
+
+
+def _quote_literal(text: str) -> str:
+    return "'" + text.replace("'", "''") + "'"


Should fix — reuse. _quote_identifier is the same expression search_catalog() already writes inline (adapter.py:251). That makes this the adapter's second copy of identifier quoting, and every other place in the adapter quotes without escaping:

catalog.py builds qualified_identifier/query_name as f'"{label}"' (lines 32-33, 85-86, 110-111, 130). That's why this PR couldn't reuse item.parent.qualified_identifier.

_get_relations and _get_columns (adapter.py:340, :347).

Suggest putting the helper in one small stdlib-only module in harlequin_sqlite, imported by both interactions.py and adapter.py, and using it in search_catalog too. Applying it to the catalog constructors can be a follow-up (see the review summary).

Optional: _quote_literal can go if the lookup binds the label instead. For example, a HarlequinSqliteConnection._get_ddl(db_name, relation_name) next to _get_relations/_get_columns that runs where name = ? on self.conn, the way search_catalog binds its params.

Generated by Claude Code

tconbeer · 2026-10-23T03:13:25Z

 ) -> None:
+    if item.parent is None:
+        return
+    # a pragma's argument is a bare name; the database qualifies the pragma itself


Nit: "bare name" isn't quite accurate. The argument on the next line is quoted, and SQLite accepts that. What it rejects is a schema-qualified argument: pragma table_info("main"."t") fails with near ".": syntax error. SQLite also calls that prefix the schema, not the database.

Suggested change

# a pragma's argument is a bare name; the database qualifies the pragma itself

# a pragma's argument can't be schema-qualified; the schema prefixes the pragma

Generated by Claude Code

tconbeer · 2026-10-23T03:13:28Z

+
+@pytest.fixture
+def two_databases(tmp_path: Path) -> HarlequinSqliteConnection:
+    """main and an attached database, each with a table called orders."""


Nit: the docstring leaves out the Bob's orders view, which is what both tests rely on for the quoting case.

Suggested change

"""main and an attached database, each with a table called orders."""

"""main and an attached archive, each with a table called orders; archive

also has a view with a quote in its name."""

Generated by Claude Code

Describe inserted `pragma table_info("main"."orders")`, which SQLite rejects for every relation: a pragma's argument is a bare name, and the database qualifies the pragma itself. Show DDL read main's sqlite_schema whatever database the relation was in, so it inserted nothing for a relation in an attached or temp database, or main's DDL when main had a relation of the same name; an unescaped quote in the label also broke its query. Both now take the database from the relation's parent item. Identifiers are quoted the way search_catalog() quotes a database name, and a quote in the label is escaped in Show DDL's string literal. Co-Authored-By: Claude Opus 5.5 <[email protected]>

A trigger can have the same name as a table, because triggers have their own namespace, so matching sqlite_schema on name alone could return the trigger's row. Only tables and views are in the Data Catalog, and they can't share a name with each other or with an index, so filtering on type leaves the one row Show DDL wants. The two_databases fixture now has a trigger called orders in archive, created before archive's table, and its docstring mentions the view the quoting case relies on. Co-Authored-By: Claude Opus 5.5 <[email protected]>

search_catalog() and the Describe and Show DDL interactions each quoted an identifier with their own copy of the same expression. Both now call quote_identifier() from a new stdlib-only harlequin_sqlite.quoting module. Co-Authored-By: Claude Opus 5.5 <[email protected]>

A quoted argument is fine; a schema-qualified one is what SQLite rejects, and SQLite calls that prefix the schema. Co-Authored-By: Claude Opus 5.5 <[email protected]>

tconbeer · 2026-10-27T05:00:40Z

Thank you for your PR!

tconbeer reviewed Oct 23, 2026

View reviewed changes

tconbeer mentioned this pull request Oct 26, 2026

Formalize identifier quoting everywhere #1209

Open

MaxFreedomPollard and others added 4 commits October 26, 2026 16:37

docs: say what SQLite rejects in a pragma argument

f20de91

A quoted argument is fine; a schema-qualified one is what SQLite rejects, and SQLite calls that prefix the schema. Co-Authored-By: Claude Opus 5.5 <[email protected]>

MaxFreedomPollard force-pushed the fix/sqlite-describe-and-ddl branch from cd90d41 to f20de91 Compare October 26, 2026 23:41

tconbeer mentioned this pull request Oct 27, 2026

Fix flaky test_a_query_submitted_while_one_runs_interrupts_it #1212

Merged

3 of 8 tasks

Merge branch 'main' into fix/sqlite-describe-and-ddl

c3b2d13

tconbeer enabled auto-merge (squash) October 27, 2026 04:59

tconbeer approved these changes Oct 27, 2026

View reviewed changes

tconbeer merged commit 381b44d into tconbeer:main Oct 27, 2026
30 of 32 checks passed

Navigation Menu

Repository navigation

fix: make the SQLite Data Catalog's Describe and Show DDL actions work - #1195

fix: make the SQLite Data Catalog's Describe and Show DDL actions work#1195
tconbeer merged 5 commits into
tconbeer:mainfrom
MaxFreedomPollard:fix/sqlite-describe-and-ddl

MaxFreedomPollard commented Oct 23, 2026

Uh oh!

tconbeer left a comment

Uh oh!

tconbeer Oct 23, 2026

Uh oh!

tconbeer Oct 23, 2026

Uh oh!

tconbeer Oct 23, 2026

Uh oh!

tconbeer Oct 23, 2026

Uh oh!

tconbeer commented Oct 27, 2026

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

	where name = {_quote_literal(item.label)}
	where name = {_quote_literal(item.label)} and type in ('table', 'view')

	# a pragma's argument is a bare name; the database qualifies the pragma itself
	# a pragma's argument can't be schema-qualified; the schema prefixes the pragma

	"""main and an attached database, each with a table called orders."""
	"""main and an attached archive, each with a table called orders; archive
	also has a view with a quote in its name."""

Conversation

MaxFreedomPollard commented Oct 23, 2026

Uh oh!

tconbeer left a comment

Choose a reason for hiding this comment

Not checked

Uh oh!

tconbeer Oct 23, 2026

Choose a reason for hiding this comment

Uh oh!

tconbeer Oct 23, 2026

Choose a reason for hiding this comment

Uh oh!

tconbeer Oct 23, 2026

Choose a reason for hiding this comment

Uh oh!

tconbeer Oct 23, 2026

Choose a reason for hiding this comment

Uh oh!

tconbeer commented Oct 27, 2026

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants