VS Code¶

The sqlty extension shows what sqlty check reports while you type, each diagnostic under the name, row type or query it is about, with the fix of sqlty check --fix as a quick fix.

schema.sql:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

[tool.sqlty]
schema = "schema.sql"

from sqlty import SQL

# error[45:49]: [unknown-column] "column `nmae` does not exist in `users`"
q = SQL[tuple[int, str]]("--sql\nSELECT id, nmae FROM users")

The editor underlines nmae, and hovering it shows the message and a link to the rule's page.

Install¶

Install sqlty from the Visual Studio Marketplace, or from Open VSX in Cursor, VSCodium and other editors built on VS Code:

code --install-extension jirikuncar.sqlty

The extension runs sqlty server from your project's environment, so add sqlty there:

uv add --dev sqlty

How the server is found¶

The first sqlty of version 0.3.0 or newer runs:

The extension restarts the server when one of these changes, or when you run sqlty: Restart Server. Saving sqlty.toml, pyproject.toml, Cargo.toml or a .sql file reloads the project's settings and schema without a restart.

Fix on save¶

source.fixAll.sqlty is sqlty check --fix on the file: a stale SQL[T] is rewritten, an inline --sql comment is split onto its own line, and so on. Run it on every save:

{
  "[python]": {
    "editor.codeActionsOnSave": {
      "source.fixAll.sqlty": "explicit"
    }
  }
}

Format on save¶

source.format.sqlty is sqlty format on the file: it lays out the query strings with sqruff. It is a code action, not a document formatter, so Ruff or Black still formats the Python code. Run both on every save:

{
  "[python]": {
    "editor.codeActionsOnSave": {
      "source.fixAll.sqlty": "explicit",
      "source.format.sqlty": "explicit"
    }
  }
}

Completion¶

Inside a query string, the editor completes table names after FROM and JOIN, and the columns of the tables in scope after alias. and in SELECT, WHERE, ON, GROUP BY and ORDER BY. Each column shows its table or alias, and its type and nullability once selected. Typing . opens the list. After JOIN, the tables a foreign key links to one in scope come first, with the ON clause that joins them: orders ON orders.user_id = u.id.

A string completes exactly when sqlty check checks it, by the same rules:

from sqlty import SQL

q = SQL[tuple[int, str]]("--sql\nSELECT id, <CURSOR> FROM users")
# completions: id column users
# completions: name column users

A string passed to a query helper, with no marker:

from typing import Annotated, LiteralString
from sqlty import SQL

def fetch_all[T](query: Annotated[LiteralString, SQL], form: type[T]) -> list[T]: ...

fetch_all("SELECT u.<CURSOR> FROM users u", tuple[int, str])
# completions: id column u
# completions: name column u

A method whose receiver has an evident class, as in check:

from typing import Annotated, LiteralString
from sqlty import SQL

class Db:
    def fetch_one[T](self, query: Annotated[LiteralString, SQL], form: type[T]) -> T: ...

db = Db()
db.fetch_one("SELECT name FROM users WHERE <CURSOR> = 1", str)
# completions: id column users
# completions: name column users

Adjacent literals are one query, so a table name completes in the last of them:

q = (
    "--sql\n"
    "SELECT count(*) "
    "FROM us<CURSOR>"
)
# completions: users table public

A function registered in [[tool.sqlty.functions]] completes too. What check does not check does not complete: a call to anything else, an f-string, a variable.

def run(query: str) -> None: ...

run("SELECT <CURSOR> FROM users")
# completions: None

Turn it off with "sqlty.completion.enable": false; the server then does no completion work. It needs sqlty 0.4.0 or newer, and with an older one the output channel says so.

Hover¶

Hover a word of a query string to see what it is. A column shows its SQL type, whether it can be NULL and where it comes from; a table or view lists its columns; a word at the top level of a select item shows the result column, with the inference rule that typed it, as sqlty check --explain does:

[tool.sqlty]
schema = "schema.sql"

schema.sql:

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, total INTEGER);

q = "--sql\nSELECT user_id, SUM(total) AS spe<CURSOR>nt FROM orders GROUP BY user_id"
# hover: Result column `spent`: `int | None` (`BIGINT`)
# hover:
# hover: Typed by `postgres.aggregate.sum-integers`: Summing small integers widens to bigint so the sum fits.
# hover:
# hover: PostgreSQL 17 documentation, 9.21, Table 9.60 General-Purpose Aggregate Functions (sum ( smallint ) → bigint, sum ( integer ) → bigint)

VS Code shows the hovers of every extension together, so sqlty's sits next to Pylance's.

Highlighting¶

A string marked --sql or -- sql is highlighted as SQL: keywords, strings, numbers, comments, operators and placeholders. Other strings stay Python strings, and so do f-strings, raw strings and bytes:

q = "--sql\nSELECT name FROM users WHERE id = %(id)s"  # SQL
plain = "SELECT name FROM users"  # a Python string

The extension adds a TextMate grammar to Python, so highlighting needs no server. A grammar sees the text of one string only, so a string checked without a marker stays a Python string: the query of an annotated helper or a [[tool.sqlty.functions]] function, and SQL[T]("SELECT ..."). A query split across adjacent literals is highlighted in its first literal only.

Breaking changes¶

Set sqlty.breaking.against to a baseline, and the editor also shows what sqlty breaking reports against it:

{
  "sqlty.breaking.against": "main"
}

sqlty.breaking.direction picks one; the default is both. A finding's source is sqlty breaking (forward) or sqlty breaking (backward), and it has no quick fix: the fix belongs to the migration or the code, depending on which ships first.

Settings¶

Restricted Mode¶

In Restricted Mode, the extension highlights the SQL of strings marked --sql and runs nothing. Diagnostics, quick fixes and completion start once you trust the folder, without a reload.

Other editors¶

sqlty server runs in any LSP client: see Neovim, Vim, Helix and Emacs for their setup, the server's options and SQL highlighting.