sqleibniz

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

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

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

from source

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

via make

this builds the project with cargo and moves the resulting binary to /usr/bin/.

git clone https://github.com/xnacly/sqleibniz
make

Uninstall via:

make uninstall

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").

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 systemwide installation beforehand via make install

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

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

Contribution

Contributions are always welcome <3, but remember to test all features you contribute.

Local Dev env

git clone git@github.com:xNaCly/sqleibniz.git
cargo run example/*

Debugging the parser

Run sqleibniz via cargo with --features trace to enable the log of each Parser.<stmt_type>_stmt function as well as the resulting ast nodes. This allows for a deeper insight for deadlocks etc.

EXPLAIN VACUUM;
EXPLAIN QUERY PLAN VACUUM my_big_schema INTO 'repacked.db';

For instance, run the checked-in trace example:

cargo run --features trace -- -i example/trace_example.sql

That prints the parser callstack and resulting AST before the normal diagnostic summary:

============================== CALLSTACK ===============================
↳ parse | Keyword(EXPLAIN)
 ↳ sql_stmt_list | Keyword(EXPLAIN)
  ↳ sql_stmt_prefix | Keyword(EXPLAIN)
   ↳ sql_stmt | Keyword(VACUUM)
    ↳ vacuum_stmt | Keyword(VACUUM)
   ↳ sql_stmt_prefix | Keyword(EXPLAIN)
    ↳ sql_stmt | Keyword(VACUUM)
     ↳ vacuum_stmt | Keyword(VACUUM)
================================= AST ==================================
- Explain(...) [child=Vacuum { ... }]
- Explain(...) [child=Vacuum { ... }]
took: [...]
=============================== Summary ================================
[+] example/trace_example.sql:
    0 Diagnostic(s) detected
    0 Diagnostic(s) ignored

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

There is also --ast and --ast-json, both enabling ast introspection:

[
  {
    "child": {
      "filename": null,
      "schema_name": null,
      "type": "Vacuum"
    },
    "type": "Explain"
  },
  {
    "child": {
      "filename": {
        "String": "repacked.db"
      },
      "schema_name": {
        "Ident": "my_big_schema"
      },
      "type": "Vacuum"
    },
    "type": "Explain"
  }
]