SQL tables are never linked to the application code that queries them — the whole data layer lands as degree-1 orphans

[andou-san]

SQL tables are never linked to the application code that queries them — the whole data layer lands as degree-1 orphans

Version: graphify 0.9.46 (graphifyy via pipx), macOS, Python 3.14.3
Corpus: Next.js/TypeScript + MySQL app — 1,109 files (768 code: 298 .ts, 224 .tsx, 41 .sql), schema split across db/schema.sql + 38 numbered migrations.

Summary

tree-sitter-sql extracts table declarations correctly, and tree-sitter-typescript extracts the application code correctly. But nothing connects the two: a table declared in db/schema.sql has no edge to the .ts file whose query reads SELECT … FROM that_table. Every table lands connected only to the file that declares it (contains, degree 1) and nothing else.

The result is that the data layer floats free of the application. On my corpus that is 65 orphaned SQL symbol nodes, and db/ never bridges a single community despite being queried from ~100 route files.

The practical cost: the graph cannot answer "what breaks if I change this table?" — which is the question a schema-heavy project most wants from a knowledge graph, and the one a migration-heavy history makes load-bearing.

Reproduce

Any repo where SQL lives in .sql files and queries are string literals in application code:

db/schema.sql                    CREATE TABLE volunteer_assignments (…)
db/migrations/035_volunteers.sql CREATE TABLE volunteer_assignments (…)
src/app/api/partner/volunteers/assignments/route.ts
                                 await query(`SELECT … FROM volunteer_assignments WHERE …`)
                                 await query(`UPDATE volunteer_assignments SET …`)
src/app/api/partner/volunteers/roster/route.ts
                                 await query(`INSERT INTO volunteer_assignments …`)

graphify .

Observed: volunteer_assignments has degree 1 (only contains, from its declaring file). No edge to any of the three route files that query it.

Expected: an edge of some kind between the table and each file whose SQL text references it.

To confirm the tables are genuinely used rather than dead, I grepped nine of the orphans against src/ — every one is referenced in 1–4 files:

role_change_audit             db/schema.sql:L42        → 1 src file
event_updates                 db/migrations/032_…:L22  → 4 src files
organization_module_contacts  db/schema.sql:L700       → 2 src files
maintenance_work_order_photos db/migrations/020_…:L3   → 3 src files
asset_documents               db/migrations/024_…:L83  → 3 src files
volunteer_roster_slots        db/migrations/035_…:L123 → 3 src files
volunteer_assignments         db/schema.sql:L1108      → 3 src files
user_email_preferences        db/schema.sql:L731       → 2 src files
account_status_audit          db/schema.sql:L54        → 1 src file

Root cause

There is no resolver for table name in SQL → same name inside a string literal in another language. resolver_registry.py has language-specific resolvers (pascal_resolution.py, ruby_resolution.py, …), but the SQL↔host-language direction is a text-embedding problem, not an AST one: the table name appears inside a template literal that the TypeScript grammar sees only as a string.

I want to be explicit that the existing guardrail is correct and should stay. references/extraction-spec.md says:

calls edges MUST stay within one language … cross-language call edges are phantom artifacts, never emit them.

That is right for calls. But it means schema↔code coupling is invisible by construction, and references is a different relation with different semantics — "this file's SQL names this table" is a factual, checkable claim, not an inferred call.

What I did as a local workaround, and the precision trap in it

I wrote a post-hoc linker and got 921 edges across 75 of 80 tables, dropping db/ orphans from 65 → 4 and growing the largest connected component from 2,764 → 2,962 nodes.

The part worth passing upstream is how easy it is to get this wrong. My first attempt matched table names on a word boundary and applied the SQL-context test file-wide — i.e. "does this file contain SQL anywhere, and does this table name appear anywhere in it". That produced 1,489 edges and was badly wrong: common table names collided with ordinary identifiers.

table	naive matcher	strict matcher
events	157 files	27
users	98	78
listings	95	32
facilities	94	44
total edges	1,489	921

The 130 phantom events edges were all JavaScript variables named events. Any implementation of this feature will hit the same trap — table names like users, events, sales, assets, notifications are extremely common identifiers.

What worked was requiring the table to sit in a real SQL position in the same match, not merely in the same file:

KEYWORDS = r'(?:FROM|JOIN|INTO|UPDATE|REFERENCES|TABLE(?:\s+IF\s+NOT\s+EXISTS)?)'
rx = re.compile(KEYWORDS + r'\s+`?' + re.escape(table) + r'`?\b', re.I)

Run over the whole file text rather than line-by-line, so multi-line template literals still match. I hand-checked 12 sampled hits and all 12 were genuine (INTO role_change_audit, UPDATE volunteer_assignments, FROM asset_documents, …). Backtick handling matters for MySQL; presumably "…" for Postgres and […] for T-SQL (cf. #2712).

A useful side effect: the 5 tables with no SQL-position reference anywhere were all explicable — a backup table, a temp table inside a migration, one queried only from shell scripts, one likely a mis-extracted CTE/column, and one genuinely unused. That last category makes this a dead-schema detector too.

Suggested shape

• A resolver that, after per-file extraction, scans non-SQL source text for declared table names in SQL keyword positions and emits references edges (file → table), EXTRACTED, confidence_score 1.0.
• Link to every declaration site (both schema.sql and the migration that created the table), so tracing a table also reaches its migration history.
• Keep it lexical and free — no LLM needed; this ran over 537 files in a couple of seconds.
• Probably wants an opt-out flag, since a repo with an ORM rather than raw SQL would get little from it.

Scope / non-goals

• File-level only. Resolving to the specific function holding the query needs the enclosing-scope span, which I did not attempt.
• Raw-SQL-in-string-literal only. ORM-generated queries (Prisma, Drizzle, ActiveRecord) name tables via model classes and would need separate handling.
• Related but distinct: Feature: extract table-to-table dependencies from INSERT/CTAS/MERGE, not just CREATE VIEW #1572 (table→table via INSERT/CTAS/MERGE) and SQL FK-reference edges use referencing-file stem instead of resolving to the defining file, producing dangling edges across multi-migration schemas #2442 (FK edges resolving to the referencing file's stem) are both within SQL. This issue is SQL↔host-language.

Happy to open a PR against resolver_registry.py if this shape sounds right.