pgfence 0.3: VS Code Extension, LSP Server, and 5 New Rules

pgfence 0.3 ships three things: a VS Code extension, the LSP server that powers it, and five new detection rules.

The VS Code extension

Install it from the VS Code Marketplace or search “pgfence” in the Extensions panel.

The extension is a thin client. It discovers the @flvmnt/pgfence package from your project’s node_modules (or a global install) and launches the LSP server over stdio. You don’t install a second copy of pgfence: the same package that powers your CLI now powers your editor.

What it does

Inline diagnostics. Every dangerous pattern gets an underline with the lock mode, risk level, and rule ID. You see the problems as you type, not after you push.

Quick fixes. On supported flagged statements with executable rewrites, pgfence can replace the original statement with a safer sequence. CREATE INDEX becomes CREATE INDEX CONCURRENTLY IF NOT EXISTS. For more complex rollout patterns, the editor shows the guidance in diagnostics and hover without pretending the whole sequence is a one-click edit.

Hover info. Hover over any flagged statement to see exactly what lock mode it acquires, what operations it blocks (reads, writes, DDL), and the safe alternative.

Status bar. A shield icon in the status bar shows the error/warning count for the current file. It only appears on SQL and migration files.

Architecture

The extension ships zero analysis logic. All intelligence lives in the @flvmnt/pgfence npm package, specifically in dist/lsp/server.js. The extension’s job is to find that file, launch it as a child process, and pipe JSON-RPC messages back and forth.

This means:

1. One source of truth. CLI and editor use the same rules, the same parser, and the same safe rewrite metadata.
2. Instant updates. npm update @flvmnt/pgfence in your project and the extension picks up new rules on next restart. No separate extension update needed.
3. No bundled binaries. The extension client stays small. All the heavy lifting (libpg_query C bindings, rule engine, policy checks) lives in the npm package you already have.

The server discovers project-level configuration from pgfence.config.ts, workspace settings, and pgfence-stats.json files. The core config you use for the CLI carries over to the editor automatically.

New rules in 0.3

ban-char-field

Flags char(n) and character(n) columns. These types pad values with spaces, waste storage, and changing the length later requires an ACCESS EXCLUSIVE lock with a full table rewrite. Use text instead.

prefer-identity

Flags serial, bigserial, and smallserial columns. These create implicit sequences with ownership quirks and permission issues. GENERATED BY DEFAULT AS IDENTITY is the standard replacement with cleaner semantics.

drop-database

Flags DROP DATABASE as CRITICAL risk. Irreversible, destroys the entire database and all its data. Includes DROP DATABASE IF EXISTS.

ban-alter-domain-add-constraint

Flags ALTER DOMAIN ... ADD CONSTRAINT. This validates the constraint against every column using the domain, blocking writes on all those tables. Domains with constraints have poor support for online migrations.

ban-create-domain-with-constraint

Flags CREATE DOMAIN ... CHECK (...). A heads-up that domains with constraints will be painful to modify later. Use table-level CHECK constraints instead.

Parser improvements

The SQL parser now exposes character-level source offsets (startOffset, endOffset) on every parsed statement, with correct byte-to-character conversion for multi-byte UTF-8 content. This is what enables the LSP server to map diagnostics to exact source ranges in your editor.

Policy violations now carry a statementIndex field, so the LSP can position policy warnings (missing lock_timeout, CONCURRENTLY inside transaction, etc.) at the specific statement that triggered them rather than at the top of the file.

Snapshot

• 40+ checks across 4 risk levels
• 300+ tests
• 8 lock modes mapped
• Built-in support for TypeORM, Prisma, Knex, Drizzle, Sequelize, and raw SQL migrations

Get started

# Install the CLI
npm install -D @flvmnt/pgfence

# Install the VS Code extension
code --install-extension flvmnt.pgfence

The extension discovers the CLI automatically. Open any .sql file or migration and you’ll see diagnostics immediately.

Full extension docs: pgfence.com/docs/editor