Bug: Autocomplete inserts double-quoted identifiers and incorrectly alters keyword casing

[AustralianRaven]

Description

Two related editor behaviors are causing friction when writing queries:

1. Table/column names are wrapped in "double quotes" on autocomplete selection — even for normal, plain names that don't need quoting.
2. SQL keywords (SELECT, WHERE, FROM, etc.) have their casing changed when the formatter runs or when autocomplete inserts them.

These make it harder to write clean SQL quickly and produce unexpected results, especially when queries are shared across tools that may handle quoted identifiers differently.

---

Steps to Reproduce

Bug 1 — Double-quoting on autocomplete

1. Connect to a PostgreSQL database.
2. Begin typing a query: SELECT * FROM
3. Trigger autocomplete and pick a table name (e.g. MyTable or any table with mixed case or special characters).
4. Observed: The name is inserted as "MyTable" instead of MyTable.

Bug 2 — Keyword casing changes

1. Type (or paste) a query with uppercase keywords: SELECT id FROM users WHERE id = 1
2. Use the Format Query shortcut (Shift+Ctrl+F / Shift+Cmd+F), or switch formatter presets.
3. Observed: Keywords may be lower-cased or otherwise re-cased depending on the active formatter preset.

---

Expected Behaviour

• Autocomplete should insert plain identifiers without wrapping them in quotes unless the name actually requires quoting (e.g. reserved words, names starting with digits, names containing spaces or special characters).
• The formatter should respect the user's chosen keyword-case preset and should not silently override it.

---

Relevant Code Locations

Bug 1 — Auto-quoting logic

apps/studio/src/lib/editor/CodeMirrorPlugins.ts:8–36 — autoquoteHandler

// Line 22: only skips quoting if the text IS a SQL keyword
if (origin === "complete" && keywords[text[0].toLowerCase()] != true) {
  const names = text[0]
    .match(/("[^"]*"|[^.]+)/g)
    .map((n) => (/^\d/.test(n) && n !== alias ? `"${n}"` : n))
    .map((n) =>
      /[^a-z0-9_]/.test(n) && !/"/.test(n) && n !== alias ? `"${n}"` : n  // line 30
    )
    .join(".");
  changeObj.update(from, to, [names]);
}

The regex /[^a-z0-9_]/ at line 30 matches any character that is not a lowercase letter, digit, or underscore — this includes uppercase letters. So a perfectly valid table name like UserAccounts will be inserted as "UserAccounts" because it contains uppercase characters. The intended guard is probably only for names that contain spaces, hyphens, or reserved characters, not for camelCase or PascalCase names.

This handler is only registered for PostgreSQL mode (guarded at line 13: !mode.includes("pgsql")).

apps/studio/src/lib/editor/CodeMirrorDefinitions.ts:4–14 — sets identifierQuote: '"' for the text/x-pgsql MIME type, which is what autoquoteHandler uses to decide which quote character to wrap with.

apps/studio/src/vendor/sql-hint/index.js:111–123 — insertIdentifierQuotes — a second quoting path in the hint provider that wraps each dot-separated name segment in identifier quotes when the user is already inside a quoted context.

apps/studio/src/vendor/sql-hint/index.js:35–39 — getIdentifierQuote — resolves the quote character from the CodeMirror mode; defaults to ` but returns " for PostgreSQL.

---

Bug 2 — Keyword casing

apps/studio/src/components/common/texteditor/SQLTextEditor.vue:95–99 — manual formatSql() (keyboard shortcut Shift+Ctrl+F)

formatSql() {
  const formatted = format(this.value, {
    language: FormatterDialect(dialectFor(this.queryDialect)),
    // No keywordCase passed — relies on sql-formatter library default
  });
  this.$emit("input", formatted);
}

No keywordCase option is forwarded here, so the result depends on the sql-formatter library's own default rather than the user's stored preset.

apps/ui-kit/lib/components/sql-text-editor/SqlTextEditor.vue:45–47 — the UI-kit editor auto-formats on every formatterConfig change:

watch: {
  formatterConfig() {
    this.formatSql()   // triggered whenever preset changes
  }
}

This means switching presets immediately reformats the open query, which can unexpectedly change keyword casing.

apps/studio/src/migration/20250831_populate_formatter_presets.js:7–9 — built-in presets and their keyword-case settings:

Preset	keywordCase	dataTypeCase
bk-default	preserve	preserve
pgFormatter	upper	lower
prettier-sql	upper	lower

The default preset preserves case, but pgFormatter and prettier-sql uppercase keywords. If a user switches presets (or if a preset is applied on startup), keywords will be forcibly recased.

apps/studio/src/common/transport/index.ts:83–96 — FormatterPresetConfig type definition (where keywordCase, dataTypeCase, functionCase are defined as "preserve" | "upper" | "lower").

---

Suggested Fixes

Bug 1

Change the regex in CodeMirrorPlugins.ts:30 to only quote identifiers that genuinely need it — i.e., names containing whitespace, hyphens, reserved characters, or that start with a digit — rather than quoting anything with an uppercase letter:

// Before (quotes anything with a non-lowercase char, including CamelCase)
/[^a-z0-9_]/.test(n)

// After (only quote names with characters that actually require quoting in SQL)
/[^a-zA-Z0-9_]/.test(n) || /^\d/.test(n)

Or, alternatively, add a user-facing setting to disable auto-quoting entirely.

Bug 2

• In SQLTextEditor.vue:formatSql(), pass the active formatter preset's keywordCase option so the manual format command respects user preferences.
• Consider adding a confirmation or undo step before the formatterConfig watcher auto-reformats the current query, to avoid silent case changes.