codemirror-sql-fk

Foreign-key-aware SQL autocompletion for CodeMirror 6. Suggests JOIN targets, generates ON conditions, and completes column names — all based on your database's foreign key relationships.

Features

• JOIN table suggestions — after typing JOIN, shows only tables related via foreign keys to tables already in the query
• ON clause generation — after JOIN orders ON, suggests the full join condition (orders.customer_id = c.id)
• Column completion — after c., lists columns for the resolved table
• Alias-aware — respects table aliases throughout
• Composite FK support — handles multi-column foreign keys
• Non-intrusive — returns null for positions it doesn't handle, so built-in SQL completions still work

Install

npm install codemirror-sql-fk

Usage

Quick setup (single package)

import { sql, PostgreSQL } from "@codemirror/lang-sql";
import { sqlFkCompletion } from "codemirror-sql-fk";

const state = EditorState.create({
  extensions: [
    sql({ dialect: PostgreSQL, schema: mySchemaMap }),
    sqlFkCompletion({ schema: myFkSchema, dialect: PostgreSQL }),
  ],
});

Manual setup (recommended for bundlers / monorepos)

If your bundler might resolve different copies of @codemirror/lang-sql, use createFkCompletionSource directly to ensure you use the same PostgreSQL instance:

import { sql, PostgreSQL } from "@codemirror/lang-sql";
import { autocompletion } from "@codemirror/autocomplete";
import { createFkCompletionSource } from "codemirror-sql-fk";

const fkSource = createFkCompletionSource(myFkSchema);

const state = EditorState.create({
  extensions: [
    autocompletion(),
    sql({ dialect: PostgreSQL, schema: mySchemaMap }),
    PostgreSQL.language.data.of({ autocomplete: fkSource }),
  ],
});

Schema format

import type { SqlFkSchema } from "codemirror-sql-fk";

const schema: SqlFkSchema = {
  tables: {
    customers: {
      columns: ["id", "name", "email"],
    },
    orders: {
      columns: ["id", "customer_id", "total"],
      foreignKeys: [
        { columns: ["customer_id"], refTable: "customers", refColumns: ["id"] },
      ],
    },
  },
};

Loading schema from PostgreSQL

Query column metadata and foreign keys at runtime:

-- Columns
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

-- Foreign keys (use pg_constraint for reliability)
SELECT
  cl.relname AS table_name,
  att.attname AS column_name,
  cl2.relname AS ref_table,
  att2.attname AS ref_column
FROM pg_constraint con
JOIN pg_class cl ON con.conrelid = cl.oid
JOIN pg_namespace ns ON cl.relnamespace = ns.oid
JOIN pg_class cl2 ON con.confrelid = cl2.oid
JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey)
JOIN pg_attribute att2 ON att2.attrelid = con.confrelid AND att2.attnum = ANY(con.confkey)
WHERE con.contype = 'f' AND ns.nspname = 'public'
ORDER BY cl.relname, con.conname;

Then build the SqlFkSchema object from the results.

How it works

1. Context detection — parses the SQL text before the cursor using regex to detect whether you're after JOIN, after ON, or after table.
2. Table extraction — finds all FROM and JOIN table references with their aliases
3. FK lookup — normalizes the schema into source/target indexes for fast relation lookup
4. Suggestion generation — based on context, suggests related tables, join conditions, or columns

The completion source returns null for any position it doesn't recognize, allowing CodeMirror's built-in SQL completions to handle everything else.

Debugging

Pass debug: true to enable console logging:

sqlFkCompletion({ schema, dialect: PostgreSQL, debug: true });
// or
createFkCompletionSource(schema, { debug: true });

This logs schema stats on init and position detection on every keystroke — helpful for diagnosing why completions aren't appearing.

API

sqlFkCompletion(config)

Returns a CodeMirror Extension. Pass the dialect you're using (defaults to StandardSQL). Set debug: true for console logging.

createFkCompletionSource(schema, options?)

Returns a raw CompletionSource function. Use this when you need full control over how the source is attached to the editor. Pass { debug: true } for console logging.

Types

interface SqlFkSchema {
  tables: { [tableName: string]: TableDef };
}

interface TableDef {
  columns: string[];
  foreignKeys?: ForeignKey[];
}

interface ForeignKey {
  columns: string[];
  refTable: string;
  refColumns: string[];
}

License

MIT