SQLly logoSQLly

IntelliSense that writes JOIN ON from foreign keys

Schema-aware SQL completion in SQLly: JOIN ON clauses from real foreign keys, stable aliases, real column values, and an F2 loop that turns misses into tests.

By

Most SQL completion is a dictionary with good manners. It knows the keywords, it knows the table names, and when you type JOIN it offers every table in the database in alphabetical order. The part you actually wanted help with, the ON clause, is still yours to type from memory, usually after a detour to look up which column the foreign key really uses.

SQLly's IntelliSense is built from one model of your actual database: its tables, columns, routines and, above all, its keys. That model is what lets it write the ON clause for you, and label it honestly when it is guessing.

It starts with a model, not a word list

Schema awareness means SQLly holds a snapshot of:

  • tables and views, with their columns, types and nullability;
  • stored procedures and functions, with their parameters;
  • declared foreign keys, fetched in bulk rather than one object at a time, which is what makes join reasoning fast enough to run while you type;
  • name-inferred relationships where no formal key exists, marked as inferences rather than presented as constraints.

Completion reads an immutable snapshot, so suggestions are instant (no round trip between a keystroke and the menu) and consistent (every suggestion in one popup comes from the same view of the schema). The snapshot is cached on disk per connection and database, so a relaunch starts warm and revalidates in the background. Where part of the schema failed to load, the model marks that region as incomplete rather than empty, so SQLly stays quiet instead of telling you a table does not exist when the truth is that it could not look.

The model is also scope-aware: it knows whether the cursor is in a FROM, a WHERE, a JOIN … ON, or an EXEC parameter list, and offers what belongs there.

JOIN ON, written from real foreign keys

Start typing a join after a table and SQLly can offer the whole thing: the join type, the table, an alias, and the ON clause. It follows a deterministic path through the model:

  1. Declared foreign keys first. When a foreign key connects the tables, SQLly uses that exact column mapping.
  2. Name-inferred relationships next. With no foreign key, it can match conventions such as CustomerId to Customer.Id, and labels the suggestion as inferred.
  3. Only tables in scope. The ON clause only references tables already in the current FROM/JOIN set, so it resolves in the query you actually have.

Each suggestion carries a confidence signal, so a fact and an educated guess never look the same. A declared-key join has an exact mapping. An inferred one is often right too, but it is worth a glance before it meets production data.

The join type follows what you type: i leads with INNER JOIN, l with LEFT JOIN, r with RIGHT JOIN, o with FULL OUTER JOIN, and with nothing typed you get a plain JOIN. Accept it and you get something shaped like this (table names here are just an illustration): 

SELECT *
FROM Sales.Orders o
    LEFT JOIN Sales.Customer c ON c.CustomerId = o.CustomerId

Aliases that stay put

Auto alias gives a table a short, readable alias based on its name instead of a parade of t1/t2 labels. The same table gets the same alias throughout a session, aliases avoid collisions, and once one exists, column completion uses it: type c. and you get the customer's columns. The join suggestion uses these aliases when it writes the ON clause, so the whole query reads consistently.

When the values matter too

The schema tells SQLly what a column is. Data awareness helps with what it contains. Put the cursor after WHERE Status = and press Ctrl+Shift+Space, and SQLly offers the distinct values the column really holds. Is it shipped, SHIPPED, or Shipped? Now you know, without running a scratch query first.

Reading data is different from reading schema, so this plays by stricter rules:

  • You ask for it. SQLly never queries your data because you paused while typing.
  • Distinct and capped. It runs a SELECT DISTINCT capped at 100 values, in your engine's own syntax (TOP, LIMIT, or FETCH FIRST).
  • Cached and bounded. Results are cached per connection, database, table and column with least-recently-used eviction, so asking twice does not query twice.
  • Local. It is a query against your database, run from your machine. Sampled values stay on your device.

Planned, not shipped: foreign key / name interchangeability, where you type a customer's name and SQLly inserts the matching id by following the foreign key, is on the roadmap planned. Value introspection, which it builds on, works today.

It catches the GROUP BY mistake, too

The same model powers checks, not just suggestions. Select a column that is neither grouped nor aggregated in a grouped query and SQLly underlines it as you type, explains why, and offers a one-click fix that adds it to the GROUP BY. Inside a GROUP BY list, the columns the query still needs come first, marked required. How strict the check is depends on the engine: an error on SQL Server, PostgreSQL and Oracle, a warning on MySQL and MariaDB (it depends on ONLY_FULL_GROUP_BY), and silence on SQLite, which allows it.

Press F2 when it gets it wrong

No completion engine is right about every schema on earth. What matters is what happens when it misses. With the completion popup open, press F2 and SQLly captures what happened: the statement, the cursor context, the suggestions it offered, and the slice of catalog needed to reproduce the case. A small dialog asks what you expected instead. The improvement process then goes like this:

  1. You choose what is shared. The dialog shows a searchable tree of your data model with a checkbox on every node. By default it includes only the objects the flagged statement references; sharing the whole catalog is an explicit opt-in.
  2. It is scrubbed. String and binary literals, comment bodies, and password- or secret-style values are masked before upload.
  3. It is sent in the background. Submit closes the dialog right away, and a failed upload is retried on a slowing schedule for up to five hours, surviving restarts, without ever double-posting.
  4. It becomes a test. Accepted reports become cases in a dedicated verification suite that runs on the build system: a real test that fails until completion is right, and keeps the fix from quietly regressing later.

That last step is the point. A bug report that turns into a regression test is a fix that stays fixed.

After the query runs

The model keeps working once you have results. SQLly tracks column provenance: which table and column each result column came from. Hover PermissionName and it tells you the source is p.Name. Right-click a cell and choose WHERE = this value, and it writes WHERE p.[Name] = 'Export', a predicate that actually runs, rather than one against the alias that SQL Server would reject. When attribution is not certain, it says nothing rather than guessing.

And if your schema lives partly in local DDL files, the multi-modal schema lets completion know about the table that so far exists only in your branch.

Completion should feel like a quiet assist, not a slot machine. Grounding it in real keys, labelling guesses as guesses, and turning every miss into a test is how SQLly tries to earn that.