Skip to content

Latest commit

 

History

History
579 lines (459 loc) · 17.8 KB

README.md

File metadata and controls

579 lines (459 loc) · 17.8 KB

SQL CLI Neovim Plugin

A Neovim plugin for the SQL CLI tool that provides seamless integration for executing SQL queries directly from your editor.

Features

  • 🚀 Execute SQL queries/scripts directly from Neovim
  • 📊 Split pane output window with results
  • 🔄 Dynamic split orientation toggle (vertical/horizontal)
  • 🎯 Execute query at cursor (smart SELECT to GO/semicolon detection)
  • 📝 Visual selection execution
  • 📄 View data files directly in buffer
  • 🔍 Auto-detect CSV files and data hints
  • ⌨️ Customizable keymaps
  • 🎯 Query plan visualization
  • 🔄 Async execution with live output
  • 🎨 Syntax highlighting for output (tables, numbers, errors, success messages)
  • 📚 Built-in function help system (LSP-like documentation)
  • 🔎 Function search and discovery
  • 🔤 Intelligent autocompletion for column names, SQL functions, and keywords
  • ⚡ Generator function discovery and help (FIBONACCI, GENERATE_PRIMES, etc.)

Installation

Using lazy.nvim

IMPORTANT: Since the plugin is in a subdirectory of the main repo, you need to use one of these approaches:

Option 1 - Clone and use locally (Recommended):

{
  dir = vim.fn.expand("~/dev/sql-cli/nvim-plugin"),
  name = "sql-cli.nvim",
  lazy = false,
  config = function()
    require('sql-cli').setup({
      command = vim.fn.expand("~/dev/sql-cli/target/release/sql-cli"),
      output_format = "table",
    })
  end,
}

Option 2 - Manual installation:

# Clone the repo
git clone https://github.com/TimelordUK/sql-cli.git ~/sql-cli-temp

# Copy plugin files to your Neovim config
cp -r ~/sql-cli-temp/nvim-plugin/* ~/.config/nvim/

# Then in your lazy.nvim config, just require it:
require('sql-cli').setup({
  command = "sql-cli",  -- Or full path to executable
})

Option 3 - Symlink approach:

# Create a symlink in your local lazy.nvim directory
ln -s ~/dev/sql-cli/nvim-plugin ~/.local/share/nvim/lazy/sql-cli.nvim

Then configure normally:

{
  "sql-cli.nvim",
  config = function()
    require('sql-cli').setup()
  end,
}

Using packer.nvim

use {
  '/path/to/sql-cli/nvim-plugin',
  config = function()
    require('sql-cli').setup()
  end
}

Configuration

require('sql-cli').setup({
  -- Path to sql-cli executable
  -- If formatting doesn't work, try setting the full path:
  -- command = "/home/user/sql-cli/target/release/sql-cli",
  command = "sql-cli",
  
  -- Split configuration
  split = {
    direction = "vertical", -- "vertical" or "horizontal"
    size = 0.5,            -- Size as fraction (0.5 = 50%)
  },
  
  -- Default output format
  output_format = "table",

  -- Table output settings (when output_format = "table")
  table_output = {
    max_col_width = 50,      -- Maximum column width (0 = unlimited, default: 50)
    col_sample_rows = 100,   -- Rows to sample for width (0 = all rows, default: 100)
    auto_hide_empty = false, -- Hide columns that are all NULL/empty (default: false)
  },

  -- SQL Formatting preferences (NEW!)
  format = {
    lowercase = false,     -- Use lowercase keywords
    compact = false,       -- Compact formatting
    tabs = false,         -- Use tabs instead of spaces
  }, -- "table", "csv", "json", "tsv"
  
  -- Auto-detect features
  auto_detect = {
    csv_files = true,      -- Auto-use CSV files as data source
    data_hints = true,     -- Auto-detect -- #!data: hints
  },
  
  -- Keymaps (set to false to disable)
  keymaps = {
    execute = "<leader>sq",         -- Execute entire buffer
    execute_selection = "<leader>ss", -- Execute visual selection
    execute_at_cursor = "<leader>sx", -- Execute query at cursor
    toggle_output = "<leader>so",   -- Toggle output window
    toggle_orientation = "<leader>st", -- Toggle split orientation
    set_data_file = "<leader>sd",   -- Set data file
    clear_data_file = "<leader>sc", -- Clear data file
    show_plan = "<leader>sp",       -- Show query plan
    open_data_file = "<leader>sv",  -- View data file in buffer
    function_help = "K",            -- Show function/generator help at cursor (Shift+K)
    list_functions = "<leader>sL",  -- List all SQL functions
    list_generators = "<leader>sG", -- List all generator functions
  },
  
  -- Output window settings
  output = {
    focus_on_run = false,  -- Focus output after execution
    clear_on_run = true,   -- Clear output before each run
    wrap = false,          -- Line wrap in output
    number = false,        -- Show line numbers
  }
})

Usage

Function and Generator Help

The plugin provides intelligent help for both SQL functions and generator functions:

  • Shift+K on any function or generator name shows detailed help in a floating window
  • Works with both regular functions (MEDIAN, SQRT, etc.) and generators (FIBONACCI, GENERATE_PRIMES, etc.)
  • Provides signature, description, examples, and argument information
  • If the word under cursor isn't found, suggests similar functions/generators

Example:

-- Place cursor on FIBONACCI and press Shift+K to see help
SELECT * FROM FIBONACCI(20);

-- Place cursor on MEDIAN and press Shift+K to see help
SELECT MEDIAN(salary) FROM employees;

Basic Commands

  • :SqlCliExecute - Execute current buffer as SQL
  • :SqlCliSetData <file> - Set data file for queries
  • :SqlCliClearData - Clear data file setting
  • :SqlCliShowPlan - Show query execution plan
  • :SqlCliToggleOutput - Toggle output window
  • :SqlCliCopyQuery - Copy query at cursor to clipboard
  • :SqlCliFormatQuery - Format/prettify SQL query at cursor

Default Keymaps

Query Execution

  • <leader>sq - Execute SQL query (normal mode)
  • <leader>ss - Execute selected SQL (visual mode)
  • <leader>sx - Execute query at cursor (from SELECT to GO/semicolon)
  • <leader>sp - Show query plan
  • <leader>sy - Copy query at cursor to clipboard
  • <leader>sY - Copy query as shell command (formats as sql-cli -q "query" with proper escaping)
  • <leader>sf - Format/prettify query at cursor (uses AST-based formatter)

Navigation

  • ]q - Jump to next query
  • [q - Jump to previous query
  • <leader>s/ - Toggle comment for query at cursor

Output Management

  • <leader>so - Toggle output window
  • <leader>st - Toggle split orientation (vertical/horizontal)
  • <leader>sw - Save results to CSV file
  • <leader>sb - Open results in new buffer

Data Files

  • <leader>sd - Set data file
  • <leader>sc - Clear data file
  • <leader>sv - View data file in buffer

Function Help (LSP-like features)

  • K - Show help for SQL function under cursor
  • <leader>sL - List all available SQL functions
  • <leader>sF - Search SQL functions interactively

Schema & Column Information

  • <leader>sh - Show table schema (columns and types)
  • <leader>sk - Smart detection at cursor (shows column info or function help)

Multi-Table Navigation

  • ]t - Jump to next result table
  • [t - Jump to previous result table
  • <leader>s1 - Jump to first result table
  • <leader>s2 - Jump to second result table
  • <leader>s3 - Jump to third result table
  • <leader>sI - Show current table info (debug modal)

SQL Refactoring & CASE Generation

  • <leader>sD - Show distinct values for column under cursor (preview in floating window)
  • <leader>sC - Generate CASE statement for column under cursor (auto-detects data file)
  • <leader>sr - Generate CASE for numeric range (e.g., for RANGE() function)
  • <leader>sd - Generate CASE from data file with column picker
  • <leader>sb - Generate banding CASE statement
  • <leader>sp - Pivot/conditional aggregation builder

Window Function Helpers

  • <leader>sw - Window function wizard (interactive menu)
  • <leader>swr - Add ROW_NUMBER() ranking with partition
  • <leader>swl - Add LAG/LEAD for delta calculations
  • <leader>swa - Add windowed aggregates (running totals, moving averages)
  • <leader>swk - Add RANK functions (RANK, DENSE_RANK, PERCENT_RANK, NTILE)
  • <leader>swv - Add FIRST_VALUE/LAST_VALUE

Autocompletion

  • <C-Space> - Trigger SQL autocompletion (in INSERT mode)
    • Completes column names from current data file
    • Suggests SQL functions with descriptions
    • Includes SQL keywords
    • Sorted by relevance: columns → functions → keywords
  • <C-x><C-o> - Alternative way to trigger completion (Vim's standard omnicompletion)

Data File Hints

Add hints in your SQL files to specify the data source:

-- #!data: ../data/sales.csv
SELECT * FROM sales WHERE amount > 1000;

The plugin will automatically detect and use these hints.

Script Execution

Scripts with GO separators are automatically detected and executed properly:

-- #!data: data/test.csv

SELECT * FROM test WHERE id < 10;
GO

SELECT COUNT(*) FROM test;
GO

Working with CSV Files

When you open a CSV file, it's automatically set as the data source. You can then write queries in another buffer and execute them against this CSV.

Window Function Helpers

The plugin provides interactive wizards to quickly generate window functions, which are often complex to write:

Quick Access with <leader>sw

Press <leader>sw to open an interactive menu with all window function patterns:

  • ROW_NUMBER() for ranking
  • LAG/LEAD for delta calculations
  • Windowed aggregates (running totals, moving averages)
  • RANK functions (RANK, DENSE_RANK, PERCENT_RANK, NTILE)
  • FIRST/LAST VALUE for boundary values

Common Patterns

Add Ranking (<leader>swr)

Place cursor on a column and press <leader>swr to add ROW_NUMBER():

-- Before: cursor on 'region'
SELECT region, salesperson, sales_amount FROM test;

-- After: adds ranking partitioned by region
SELECT region, salesperson, sales_amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY sales_amount DESC) as region_rank
FROM test;

Calculate Deltas (<leader>swl)

Place cursor on a numeric column and press <leader>swl to add LAG/LEAD:

-- Before: cursor on 'sales_amount'
SELECT salesperson, month, sales_amount FROM test;

-- After: adds previous value and change calculation
SELECT salesperson, month, sales_amount,
    LAG(sales_amount, 1) OVER (ORDER BY month) as prev_sales_amount,
    sales_amount - LAG(sales_amount, 1) OVER (ORDER BY month) as sales_amount_change
FROM test;

Running Totals (<leader>swa)

Add cumulative or moving window aggregates:

-- Running total example
SELECT month, sales_amount,
    SUM(sales_amount) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) as running_sum
FROM test;

-- Moving average example (3-row window)
SELECT month, sales_amount,
    AVG(sales_amount) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg
FROM test;

Interactive Prompts

Each wizard guides you through:

  1. Column selection - Which column to analyze
  2. Partitioning - Group calculations by specific columns
  3. Ordering - Sort order for window calculations
  4. Aliases - Custom names for result columns

The functions are inserted inline at your cursor position with proper SQL formatting.

SQL Refactoring & Code Generation

The plugin includes powerful SQL refactoring tools to speed up query development:

CASE Statement Generation

Explore Column Values (<leader>sD)

Place your cursor on any column name and press <leader>sD to see distinct values:

  • Shows top 20 distinct values by frequency
  • Displays total distinct count
  • Helps understand data distribution before writing CASE statements
  • Works with any data file (CSV, or file CTEs)

Generate CASE from Column (<leader>sC)

Place cursor on a column name and press <leader>sC to automatically generate a CASE statement:

  • Auto-detects data file from context
  • For categorical columns (≤20 distinct values): Creates CASE with exact values
  • For numeric columns (>20 values): Generates smart range-based bands
  • Inserts inline after the column in SELECT statements

Example:

-- Before: cursor on ocean_proximity
SELECT ocean_proximity FROM house_prices;

-- After pressing \sC:
SELECT ocean_proximity,
    CASE
        WHEN ocean_proximity = '<1H OCEAN' THEN '<1H OCEAN'
        WHEN ocean_proximity = 'INLAND' THEN 'INLAND'
        WHEN ocean_proximity = 'ISLAND' THEN 'ISLAND'
        WHEN ocean_proximity = 'NEAR BAY' THEN 'NEAR BAY'
        WHEN ocean_proximity = 'NEAR OCEAN' THEN 'NEAR OCEAN'
        ELSE 'Other'
    END AS ocean_proximity_category
FROM house_prices;

Generate CASE for Ranges (<leader>sr)

Perfect for synthetic data from RANGE() or numeric columns:

  • Prompts for column name, range (e.g., 1-100), and number of bands
  • Offers preset labels (Very Low/Low/Medium/High/Very High, Level 1-5, etc.)
  • Generates equal-width bands automatically

Example with RANGE():

-- Before:
SELECT * FROM RANGE(1, 100);

-- After \sr with 5 bands:
SELECT *,
    CASE
        WHEN value <= 20 THEN 'Level 1'
        WHEN value > 20 AND value <= 40 THEN 'Level 2'
        WHEN value > 40 AND value <= 60 THEN 'Level 3'
        WHEN value > 60 AND value <= 80 THEN 'Level 4'
        WHEN value > 80 THEN 'Level 5'
    END AS value_band
FROM RANGE(1, 100);

Generate CASE from Data File (<leader>sd)

Interactive CASE generation with column picker:

  • Shows all columns with their data types
  • Automatically determines best CASE style (values vs ranges)
  • Smart data file detection from hints or current context

Window Functions & Aggregations

Window Function Wizard (<leader>sw)

Interactive builder for window functions:

  • ROW_NUMBER(), RANK(), DENSE_RANK()
  • LAG/LEAD for change detection
  • Running totals and moving averages
  • Guides through PARTITION BY and ORDER BY setup

Conditional Aggregation Builder (<leader>sp)

Generate pivot-style conditional aggregations:

  • Transforms rows to columns
  • Creates SUM(CASE WHEN...) patterns
  • Useful for pivot tables and cross-tabs

Multi-Table Navigation

When executing SQL scripts with multiple queries separated by GO statements, the plugin now supports navigation between result tables:

Navigation Keys

  • ]t - Jump to next result table
  • [t - Jump to previous result table
  • <leader>s1 - Jump directly to first table
  • <leader>s2 - Jump directly to second table
  • <leader>s3 - Jump directly to third table
  • <leader>sI - Show current table info (debug modal) (e.g., "Table 2/4 (10 rows)")

Example Workflow

  1. Open a multi-query SQL file: :e examples/chemistry.sql
  2. Execute with <leader>sx to run all queries
  3. Use ]t to navigate forward through result tables
  4. Use [t to navigate backward through result tables
  5. Use <leader>s1 to jump back to the first table
  6. Use <leader>sI to see which table you're currently viewing

The navigation wraps around - pressing ]t on the last table takes you to the first, and [t on the first table takes you to the last.

Examples

Quick Analysis Workflow

  1. Open a CSV file: :e data/sales.csv
  2. Open a new SQL buffer: :vnew query.sql
  3. Write your query: 
    SELECT 
  region,
  COUNT(*) as sales_count,
  SUM(amount) as total
FROM sales
GROUP BY region
  4. Execute with <leader>sq

Visual Selection Execution

  1. Write multiple queries
  2. Visually select the one you want
  3. Press <leader>ss to execute only the selection

Using Query Plans

  1. Write a complex query
  2. Press <leader>sp to see the execution plan
  3. Optimize based on the plan output

Token Management (JWT Auto-Refresh)

For APIs with short-lived tokens (e.g., 15-minute expiry), the plugin can automatically refresh tokens:

Configuration

require('sql-cli').setup({
  -- ... other config ...

  token = {
    -- Your token endpoint URL
    token_endpoint = 'http://localhost:5000/token',

    -- Auto-refresh before expiry
    auto_refresh = true,

    -- Refresh every 14 minutes (for 15-min tokens)
    refresh_interval = 14 * 60,

    -- JSON path to extract token
    token_path = 'token',  -- For {"token": "xxx"}
  }
})

Commands

  • :TokenRefresh - Manually refresh token
  • :TokenShow - Display current token (truncated)
  • :TokenConfig <url> - Set token endpoint

Usage in Macros

-- MACRO: CONFIG
-- API_TOKEN = ${JWT_TOKEN}  -- Auto-uses fresh token
-- END MACRO

The token refreshes automatically, no need to exit Neovim!

Tips

  • The plugin remembers your last query, so toggling the output window will re-run it
  • Use :SqlCliSetData with tab completion to quickly switch data files
  • Add -- #!data: hints to your SQL files for reproducible queries
  • The output window is a regular buffer, so you can search, copy, etc.
  • Syntax highlighting is automatically applied to output for better readability
  • SQL files get additional buffer-local keymaps (<LocalLeader>r to run, <LocalLeader>p for plan)
  • Autocompletion works best after setting a data file - it reads the schema automatically
  • Use <C-Space> in INSERT mode to trigger intelligent SQL completion

Statusline Integration

Add to your statusline to show the current data file:

-- For lualine
{
  function() return require('sql-cli').statusline() end,
  cond = function() return vim.bo.filetype == 'sql' end,
}

-- For custom statusline
vim.o.statusline = vim.o.statusline .. ' %{luaeval("require(\"sql-cli\").statusline()")}'

Troubleshooting

Plugin not loading

If you get attempt to call field 'setup' (a nil value), ensure:

  1. The plugin is properly installed:

    :Lazy show sql-cli

  2. Check plugin health:

    :checkhealth sql-cli

  3. Verify the Lua module can be loaded:

    :lua print(vim.inspect(require('sql-cli')))

  4. For local development, ensure the path is correct:

    {
  dir = vim.fn.expand("~/dev/sql-cli/nvim-plugin"),
  name = "sql-cli.nvim",
  lazy = false,  -- Load immediately
  config = function()
    require('sql-cli').setup({
      command = vim.fn.expand("~/dev/sql-cli/target/release/sql-cli"),
    })
  end,
}

License

Same as SQL CLI - MIT