GitHub - xnacly/sqleibniz: Static analysis and LSP for SQL in Rust

Static analysis and LSP for SQL in Rust. Check for valid syntax, semantics and perform dynamic analysis.

For a standalone SQLite lexer, parser, and AST without sqleibniz added layers, use sqleibniz-ast.

Warning

Sqleibniz is in development, please keep this in mind before creating issues.

Features

Sqleibniz is a command line tool to analyse sql statements by checking for their static and dynamic correctness. See below for a list of currently implemented features.

Supported features

static analysis (syntax and semantic analysis)
- syntax analysis - sqleibniz aims to implement the syntax sqlite understands
- warn for sqlites quirks
- do the used tables exist / were they created beforehand
- do the used columns exist / were they created beforehand
- do the used functions exist / were they created beforehand
- are all used types compatible
dynamic analysis (runtime analysis via embedded sqlite)
- assertions via @sqleibniz::assert
- were all tables and their columns created correctly (with correct storage classes)
- were all stmts executed successfully
pretty errors
- faulty code display with line numbers
- link to sqlite documentation for each diagnostic
- ability to omit specific errors depending on their group (Rule)
- highlighting the error in the faulty code snippet
- explanation why the specific error was ommitted based on its Rule
- syntax highlighting in terminal errors
- possible fix suggestions
- suggestions for unknown and possible misspelled keywords
language server protocol
- diagnostics for full sqleibniz analysis
- apply disabled diagnostics from leibniz.lua
- snippets
- intelligent completions
lua scripting
- configure sqleibniz with lua
- scripting to hook into node analysis for custom diagnostics
- execute hooks when encountering the defined node while analysing

Supported Sql statements

`sqlite` specification	syntax analysis	semantic analysis	Example
`explain-stmt`	✅	❌	`EXPLAIN QUERY PLAN VACUUM;`
`alter-table-stmt`	✅	✅	`ALTER TABLE schema.table_name ADD new_column_name TEXT;`
`analyze-stmt`	✅	❌	`ANALYZE my_table;`
`attach-stmt`	✅	❌	`ATTACH DATABASE 'users.db' AS users;`
`begin-stmt`	✅	❌	`BEGIN DEFERRED TRANSACTION;`
`commit-stmt`	✅	❌	`END TRANSACTION;`
`create-index-stmt`	✅	❌	`CREATE INDEX idx_users_id ON users (id);`
`create-table-stmt`	✅	✅	`CREATE TABLE users (id INTEGER) STRICT;`
`create-trigger-stmt`	✅	❌	`CREATE TRIGGER user_ai AFTER INSERT ON users BEGIN SELECT 1; END;`
`create-view-stmt`	✅	✅	`CREATE VIEW active_users AS SELECT id FROM users;`
`create-virtual-table-stmt`	✅	✅	`CREATE VIRTUAL TABLE docs USING fts5(content);`
`delete-stmt`	✅	✅	`DELETE FROM users WHERE id = 1 RETURNING *;`
`detach-stmt`	✅	❌	`DETACH DATABASE my_database;`
`drop-index-stmt`	✅	❌	`DROP INDEX my_index;`
`drop-table-stmt`	✅	❌	`DROP TABLE my_table;`
`drop-trigger-stmt`	✅	❌	`DROP TRIGGER my_trigger;`
`drop-view-stmt`	✅	❌	`DROP VIEW my_view;`
`insert-stmt`	✅	✅	`INSERT INTO users (id) VALUES (1) RETURNING *;`
`pragma-stmt`	✅	✅	`PRAGMA schema.optimize(0xfffe);`
`reindex-stmt`	✅	❌	`REINDEX my_schema.my_table;`
`release-stmt`	✅	❌	`RELEASE SAVEPOINT latest_savepoint;`
`rollback-stmt`	✅	❌	`ROLLBACK TO latest_savepoint;`
`savepoint-stmt`	✅	❌	`SAVEPOINT latest_savepoint;`
`select-stmt`	✅	✅	`SELECT id FROM users WHERE active = true;`
`update-stmt`	✅	✅	`UPDATE users SET name = 'Ada' WHERE id = 1;`
`vacuum-stmt`	✅	❌	`VACUUM INTO 'repacked.db';`

See example/stmt.sql for the executable statement support matrix used by the examples.

Installation

cargo

from source

git clone https://github.com/xnacly/sqleibniz
cd sqleibniz
cargo install --path sqleibniz

Uninstall via:

cargo uninstall sqleibniz

Command line interface usage

sqleibniz [OPTIONS] [PATHS]...

Run sqleibniz --help for the current CLI reference, including all flags and diagnostic rules accepted by -D.

Configuration

Sqleibniz can be configured via a leibniz.lua file. By default, the CLI reads ./leibniz.lua; pass --config <path> to use another file or --ignore-config to skip configuration entirely.

The language server reads disabled_rules from the workspace leibniz.lua. Lua hooks are only executed in LSP diagnostics when --lsp-enable-hooks is passed explicitly.

See leibniz.lua for the canonical example configuration, including disabled rules and Lua hook examples. That file includes the current SQLite-specific rules such as sqlite/unknown-pragma.

Each hook runs for contexts matching every field in match. Token hooks can use node.content for the token text and report a diagnostic by calling sqleibniz.diagnostic(node, "message").

Each hook invocation is limited to 10 ms by default, preventing an accidental infinite Lua loop from blocking analysis. Set max_hook_runtime in the configuration to a number of milliseconds (for example, max_hook_runtime = 50) or a string such as "50ms". Pass --max-hook-runtime 50ms to override the configuration for one CLI or language-server invocation.

sqleibniz instructions

A sqleibniz instruction is prefixed with @sqleibniz:: and written inside of a sql single line comment.

`expect`

In a similar fashion to ignoring diagnostics via the configuration in leibniz.lua, sqleibniz allows the user to expect diagnostics in the source file and omit them on a statement by statement basis. To do so, a comment containing a sqleibniz instruction has to be issued:

-- will not cause a diagnostic
-- @sqleibniz::expect <explanation for instruction usage here>
-- incorrect, because EXPLAIN wants a sql stmt
EXPLAIN 25;

-- will not cause a diagnostic
-- @sqleibniz::expect <explanation for instruction usage here>
-- incorrect, because 'unknown_table' does not exist
SELECT * FROM unknown_table;

-- will cause a diagnostic
-- incorrect, because EXPLAIN wants a sql stmt, not a literal
EXPLAIN QUERY PLAN 25;

Passing the above file to sqleibniz:

======================== example/sqleibniz.sql =========================
sql/syntax: Unexpected Literal
 -> /home/teo/programming/sqleibniz/example/sqleibniz.sql:11:20
 09 |
 10 | -- will not cause a diagnostic
 11 | EXPLAIN QUERY PLAN 25;
    |                    ~~ error occurs here.
    |
    ~ note: Literal Number(25.0) can not start a statement
    ~ docs: https://www.sqlite.org/syntax/sql-stmt.html
 * sql/syntax: The source file contains a structure with incorrect syntax
=============================== Summary ================================
[-] example/sqleibniz.sql:
    1 Diagnostic(s) detected
    0 Diagnostic(s) ignored

=> 0/1 Files verified successfully, 1 verification failed.

@sqleibniz::expect is implemented by inserting a token with the type Type::InstructionExpect. The parser encounters this token and consumes all token until a token with the type Type::Semicolon is found. Thus sqleibniz is skipping the analysis of the statement directly after the sqleibniz instruction. A statement is terminated via ;. @sqleibniz::expect therefore supports ignoring diagnostics for statements spanning either a single line or multiple lines.

Language Server Protocol (lsp)

Sqleibniz has an LSP provider included, with in-editor diagnostics, hover info and other dx helpers. The language server loads leibniz.lua from the first workspace folder, then from the LSP rootUri, then from the current working directory. Only disabled_rules are applied in LSP mode by default. Lua hooks are ignored unless the server is started with --lsp-enable-hooks.

Setup in Neovim

requires installation beforehand via cargo install

As simple as adding the following to the neovim lua config:

vim.lsp.config.sqleibniz = {
    cmd = { 'sqleibniz', '--lsp' },
    filetypes = { "sql" },
    root_markers = { "leibniz.lua" }
}
vim.lsp.enable('sqleibniz')