Query authoring for MongoDB and PostgreSQL needs real autocomplete, dialect data, and diagnostics

[MinhQuang28]

Problem

Writing queries in TablePro is painful for MongoDB and PostgreSQL. The editor renders and executes fine, but the authoring layer (autocomplete, dialect data, diagnostics, formatting) is built for generic SQL only, so both of these get a degraded experience.

---

MongoDB: autocomplete is effectively dead

SQLCompletionAdapter is the only completion delegate wired into the editor (SQLEditorView.swift:67), and it never branches on PluginManager.editorLanguage(for:). MQL goes through the SQL pipeline unchanged.

The visible result: typing a dot in MQL produces nothing. SQLCompletionProvider.swift:128-149:

if let dotPrefix = context.dotPrefix {
    guard let schemaProvider else { return [] }
    if let derived = context.tableReferences.first(where: {
        $0.isDerived && $0.identifier.caseInsensitiveCompare(dotPrefix) == .orderedSame
    }), let columns = derived.derivedColumns, !columns.isEmpty {
        return columns.map { SQLCompletionItem.column($0, dataType: nil, tableName: derived.identifier) }
    }
    if let tableName = await schemaProvider.resolveAlias(dotPrefix, in: context.tableReferences) {
        ...
    }
    if items.isEmpty {
        if await schemaProvider.isKnownSchema(dotPrefix) { ... }
        else if await schemaProvider.isKnownDatabase(dotPrefix) { ... }
    }
    return items          // <- empty for every MQL dot
}

For db. the dot prefix is db. For db.users. it is users. Neither is a SQL alias, schema, or database, so both fall through to an empty array.

What is left is 23 static snippets in MongoDBPlugin.swift:110 (db., .find, .aggregate, .updateOne, ...), plus sqlDialect = nil. So there is no completion for:

• collection names
• field names inside a filter or update document
• $ operators ($match, $gte, $in, $lookup, $unwind, $set, $inc)
• aggregation pipeline stages, which need a different operator set than a query filter

Trigger characters are [".", " "] only (SQLCompletionAdapter.swift:73-75), so $ and { never open the popup at all.

MongoShellParser also has gaps that surface only after pressing Run:

• Chained methods are applied to .find only (MongoShellParser.swift:344-348). db.c.aggregate([...]).limit(10) drops .limit(10) silently, so the query runs and returns the wrong row count with no error. This one is a correctness bug, not just ergonomics.
• updateOne / updateMany / replaceOne / findOneAndUpdate go through parseTwoArgs, so a third options argument ({upsert: true}, arrayFilters) is rejected.
• parseChainedOptions (MongoShellParser.swift:353) understands sort, limit, skip, projection and silently breaks on anything else.
• No .explain(), .distinct(), .bulkWrite(), use <db>, or variable assignment.

Cmd+Shift+F on an MQL tab runs the SQL formatter: SQLEditorCoordinator.performFormatSQL() calls SQLFormatterService with databaseType ?? .mysql and never checks the editor language.

---

PostgreSQL: the analyzer is good, the dialect data is thin

SQLContextAnalyzer is solid (30 clause types, CTE names, derived tables, alias resolution). The problem is what it has to work with.

PostgreSQLPlugin.swift:82-127 declares roughly 60 keywords and 33 functions. Missing from functions:

Group	Missing
Window	ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, FIRST_VALUE, LAST_VALUE, PERCENT_RANK, CUME_DIST
Regex	REGEXP_REPLACE, REGEXP_MATCHES, REGEXP_SPLIT_TO_TABLE, REGEXP_SPLIT_TO_ARRAY, REGEXP_COUNT
jsonb	JSONB_SET, JSONB_PATH_QUERY, JSONB_PATH_EXISTS, JSONB_ARRAY_ELEMENTS, JSONB_ARRAY_ELEMENTS_TEXT, JSONB_EACH, JSONB_OBJECT_KEYS, JSONB_STRIP_NULLS, JSONB_PRETTY, TO_JSONB, ROW_TO_JSON
Array	UNNEST, ARRAY_AGG, ARRAY_LENGTH, ARRAY_POSITION, ARRAY_REMOVE, ARRAY_APPEND, CARDINALITY
String	FORMAT, INITCAP, LPAD, RPAD, POSITION, OVERLAY, SPLIT_PART, STRING_TO_ARRAY, STRING_AGG, TRANSLATE
Misc	GENERATE_SERIES, COALESCE, NULLIF, GREATEST, LEAST, WIDTH_BUCKET, AGE, DATE_TRUNC, DATE_PART, MAKE_INTERVAL, GEN_RANDOM_UUID

Keywords are missing ON CONFLICT, DO UPDATE SET, DO NOTHING, DISTINCT ON, LATERAL, TABLESAMPLE, GENERATED, MATERIALIZED, FILTER, EXCLUDE, WINDOW, RANGE, GROUPS.

Bigger gap: no PostgreSQL operator is known anywhere in the autocomplete stack. SQLDialectDescriptor has no operators field, and a grep for ::, ->, ->>, #>, @>, &&, ~* across Core/Autocomplete/ returns nothing. So:

• col:: offers no data types
• data-> offers no JSON keys and no hint that -> returns json while ->> returns text
• tags @> gets no help at all

extractPrefix at SQLContextAnalyzer.swift:682 treats . as the only qualifier separator, so :: is not even recognized as a completion boundary.

Also missing on the PG side: enum value completion, user-defined function completion, sequence names, search_path awareness, and snippets for ON CONFLICT DO UPDATE SET, RETURNING, WITH ... AS MATERIALIZED, and window frame clauses.

---

Both: no inline diagnostics

There is no diagnostic surface in the editor. A grep for diagnostic, errorLine, squiggl, or underline across Views/Editor/ and Core/Autocomplete/ returns nothing. Every syntax error, including the ones MongoShellParser already detects with a precise reason, is only reported after the query is executed.

---

Proposed solution

Three tiers, ordered by cost. Tier 1 needs no PluginKit ABI bump and no plugin re-release.

Tier 1: dialect data and trigger characters (about 1 day)

1.1 Add operators to SQLDialectDescriptor

Per the ABI rules in CLAUDE.md, adding a parameter to the existing public init would replace its mangled symbol and break every shipped plugin (this already happened once in 0.49.0 with PluginQueryResult.columnMeta). So add a new init overload and demote the current one:

// SQLDialectDescriptor.swift
public let operators: Set<String>

// Mark the CURRENT 14-parameter init @_disfavoredOverload and have it
// delegate with `operators: []`. Keep its signature byte-identical.
@_disfavoredOverload
public init(
    identifierQuote: String,
    ...
    caseFoldFunction: String = SQLDialectDescriptor.defaultCaseFoldFunction
) {
    self.init(identifierQuote: identifierQuote, ..., caseFoldFunction: caseFoldFunction, operators: [])
}

// New full init
public init(
    identifierQuote: String,
    ...
    caseFoldFunction: String = SQLDialectDescriptor.defaultCaseFoldFunction,
    operators: Set<String> = []
) { ... }

The existing @_disfavoredOverload 12-parameter init at line 76 keeps delegating as it does today, so it needs no change beyond passing through. withCaseSensitivityStyle must be updated to carry operators forward or it will silently drop them.

Run scripts/check-pluginkit-abi.sh against the merge base and confirm the diff is additive (no symbol disappears). No currentPluginKitVersion bump.

1.2 Fill out the PostgreSQL dialect

Straight data edit in PostgreSQLPlugin.swift:82. Functions from 33 to roughly 200 using the table above, plus the missing keywords, plus:

operators: [
    "::", "->", "->>", "#>", "#>>", "@>", "<@", "?", "?|", "?&",
    "||", "&&", "@@", "~", "~*", "!~", "!~*", "<->", "|/", "@",
    "#-", "IS DISTINCT FROM", "IS NOT DISTINCT FROM"
]

MySQL, SQLite, ClickHouse and the rest keep operators: [] and behave exactly as today.

1.3 Language-aware trigger characters

SQLCompletionAdapter.swift:73:

func completionTriggerCharacters() -> Set<String> {
    switch editorLanguage {
    case .javascript: return [".", "$", "{", "\"", "'"]   // MQL
    default:          return [".", " ", ":", "(", ","]
    }
}

editorLanguage comes from PluginManager.shared.editorLanguage(for:), resolved in makeEngine alongside dialect and stored on the adapter.

1.4 Teach the analyzer about :: and ->

In extractPrefix (SQLContextAnalyzer.swift:682), recognize :: as a qualifier separator the same way . is handled today, and add two clause types to SQLClauseType:

• .castTarget when the text before the cursor ends in :: → return dialect.dataTypes as SQLCompletionItem.keyword items
• .jsonPath when it ends in -> or ->> → return nothing useful yet, but reserve the shape so a later change can pull keys from a sampled jsonb column

Then in SQLCompletionProvider, when context.clauseType == .castTarget, return data types instead of falling into the dot-prefix branch. Operators from 1.2 join the general keyword pool with kind: .operator, which SQLCompletionKind already supports (SQLCompletionItem.swift:20, icon and colour already defined).

1.5 Stop the MQL dot path dead-ending

Minimal safety net until Tier 2 lands. In SQLCompletionProvider.swift:128, when all three resolutions miss, fall back to the plugin's statement completions filtered by prefix instead of returning []. One-line behaviour change, makes db.users. at least list the 20 driver methods.

---

Tier 2: a real MQL completion path (about 3 to 4 days)

2.1 Make the delegate polymorphic

Introduce a small protocol so the editor is not hardcoded to SQL:

@MainActor
protocol QueryCompletionProviding {
    func completions(text: String, cursor: Int) async -> CompletionContext?
    func triggerCharacters() -> Set<String>
}

SQLCompletionAdapter keeps its CodeSuggestionDelegate conformance and all its existing machinery (50 ms debounce, 30 ms refilter, 5000-char window, SQLSuggestionEntry bridging). It just picks its inner provider:

private static func makeProvider(...) -> QueryCompletionProviding {
    switch PluginManager.shared.editorLanguage(for: databaseType ?? .mysql) {
    case .javascript: return MongoCompletionProvider(schemaProvider: schemaProvider, ...)
    default:          return CompletionEngine(schemaProvider: schemaProvider, ...)
    }
}

Nothing in SQLEditorView changes. CompletionEngine gains the conformance for free since it already has getCompletions(text:cursorPosition:).

2.2 MongoContextAnalyzer

New file under Core/Autocomplete/. Scans backwards from the cursor and classifies into one of five positions. This is a small state machine over the same NSString-based scanning SQLContextAnalyzer already uses (keep character(at:), not String.Index, per the performance rules in CLAUDE.md).

Position	Trigger	Completions
.dbRoot	after db.	collection names + getCollectionNames(), createCollection(), stats(), runCommand()
.collectionMethod	after db.<name>.	the 21 methods MongoOperation supports, with signature snippets
.filterDocument	inside { } in arg 1 of find / updateOne / deleteMany / countDocuments	field names, then $ query operators
.updateDocument	inside { } in arg 2 of update* / findOneAndUpdate	$ update operators ($set, $unset, $inc, $push, $pull, $addToSet, $rename, $currentDate) then field names
.pipelineStage	inside [ ] of aggregate	$ stage operators ($match, $group, $project, $lookup, $unwind, $sort, $limit, $skip, $facet, $addFields, $replaceRoot, $count, $bucket, $graphLookup, $merge, $out)

The three $ sets are genuinely distinct. Offering $match inside a filter document or $gte as a pipeline stage is worse than offering nothing, so keep them separate from the start.

Brace and bracket depth tracking has to skip string literals. MongoShellParser.readStringLiteral already handles ", ' and backtick and can be reused if it is made non-private, or mirrored.

2.3 Field name source

BsonDocumentFlattener (Plugins/MongoDBDriverPlugin/BsonDocumentFlattener.swift) already infers a flat field list from documents, and has tests. Wire it to completion:

1. Add a sampleFields(collection:limit:) method to the Mongo driver that runs find({}).limit(50), flattens, and returns the union of field paths with their inferred BSON type.
2. Expose it through the existing metadataSource abstraction on SQLSchemaProvider rather than building a second cache. SQLSchemaProvider is already an actor with in-flight loadTask coalescing, eager column preloading, and a fetchColumns hook. Collections map to "tables" and fields map to "columns" with almost no impedance mismatch, and the coalescing invariant in CLAUDE.md (never a boolean isLoading guard that returns without data) is already respected there.
3. Cache per collection with the same lifetime as the column cache.

This is the single highest-value item in the whole issue. Field completion inside a filter is what makes MQL editing feel supported.

2.4 Route the formatter by language

SQLEditorCoordinator.performFormatSQL():

switch PluginManager.shared.editorLanguage(for: databaseType ?? .mysql) {
case .javascript: formatted = MongoShellFormatter.format(text)
default:          formatted = SQLFormatterService.format(text, dialect: databaseType ?? .mysql)
}

MongoShellFormatter can be small: reuse the brace and bracket depth scanner from 2.2, indent by depth, one field per line above a length threshold.

---

Tier 3: inline diagnostics and parser gaps (about 2 to 3 days)

3.1 Diagnostics

MongoShellParser already throws MongoShellParseError with four distinct reasons and localized messages. Run it on a debounce (reuse the 50 ms debounceNanoseconds value from the adapter) and surface the result:

1. Extend MongoShellParseError with a range: NSRange? so the error can point at the offending token instead of the whole statement. Additive, all cases keep their current payload.
2. Draw an underline through CodeEditSourceEditor's text attribute layer and a marker in the gutter.
3. For PostgreSQL, a statement splitter plus an unbalanced-paren and unterminated-string check covers most of what people actually hit while typing. A full PG grammar is out of scope.

3.2 Fix the parser gaps

• Move the chained-options call out of the case .find binding at MongoShellParser.swift:344 so it applies to aggregate, findOne, distinct, and countDocuments too. Add limit, skip, sort, allowDiskUse, maxTimeMS, hint, collation, explain to parseChainedOptions, and replace the default: break with a thrown unsupportedMethod so nothing is dropped silently again.
• Add a parseThreeArgs path for the options argument on updateOne / updateMany / replaceOne / findOneAndUpdate (upsert, arrayFilters, hint).
• Add .explain(), .distinct(), .bulkWrite(), use <db>.
• Tests: TableProTests/Core/MongoDB/ already has BsonDocumentFlattenerTests, so the pattern exists. The aggregate().limit() drop should get a regression test first, since it is a wrong-results bug.

---

Suggested order for a fast landing

1. 1.2 alone (pure data edit in one plugin file, no ABI surface) already fixes most of the PostgreSQL complaints. Half a day, shippable on its own.
2. 1.5 (three-line fallback) stops MQL returning an empty popup.
3. 3.2 first bullet, the aggregate().limit() drop, because it produces wrong results today.
4. 1.1 + 1.3 + 1.4 together, since they share the operators plumbing.
5. 2.1 → 2.3 as one branch. 2.3 is the payoff, 2.1 and 2.2 are the scaffolding it needs.
6. 2.4 and 3.1 last.

Steps 1 to 4 are independent of each other and can go in parallel with no file conflicts: PostgreSQLPlugin.swift, SQLCompletionProvider.swift, MongoShellParser.swift, and SQLDialectDescriptor.swift + SQLContextAnalyzer.swift respectively.

Alternatives considered

• Only expanding the static CompletionEntry lists per plugin. Cheap, and worth doing for PostgreSQL as part of Tier 1, but it cannot fix MongoDB. Static snippets do not know collection names or document fields, and the dot path still returns nothing.
• Adopting an LSP per language. Too heavy for a plugin architecture where each driver ships as a .tableplugin bundle, and there is no maintained MQL language server to point at.
• Making MQL editing depend on the AI assistant. Needs network and costs a round trip per keystroke context. Autocomplete has to be local and instant.

Related database type

PostgreSQL and MongoDB (both).

Notes for triage

The MongoDB dot dead-end (SQLCompletionProvider.swift:128-149) and the aggregate().limit() silent drop (MongoShellParser.swift:344) are arguably bugs rather than missing features, and could be split into their own issues if that helps scheduling. The rest is feature work.