purcell / sqlint Goto Github PK

View Code? Open in Web Editor NEW

404.0 10.0 30.0 64 KB

Simple SQL linter supporting ANSI and PostgreSQL syntaxes

License: MIT License

Ruby 69.27% Makefile 1.39% Nix 29.34%

sqlint's Introduction

SQLint - a simple SQL linter

About

SQLint is a simple command-line linter which reads your SQL files and reports any syntax errors or warnings it finds.

At this stage, SQLint checks SQL against the ANSI syntax, and uses the PostgreSQL SQL parser to achieve this. SQLint does not have support for non-standard SQL variants (e.g. MySQL), but contributions are welcome.

Installation

SQLint is currently provided as a ruby gem: you can install it using the following command:

gem install sqlint

Usage

To check the syntax of a file containing SQL, simply pass the filename to sqlint on the command line:

sqlint filename.sql

In the absence of a filename, sqlint reads from standard input.

Editor plugins

Support for sqlint is provided for the following editors:

Emacs, via Flycheck
VIM, via Syntastic, Neomake or ALE
SublimeText, via SublimeLinter

Using with pre-commit

Add this to your .pre-commit-hooks.yaml:

-   repo: https://github.com/purcell/sqlint
    rev: master
    hooks:
    -   id: sqlint

Authors

This software was written by Steve Purcell and Kieran Trezona-le Comte.

License and copyright

💝 Support this project and my other Open Source work via Patreon

💼 LinkedIn profile

✍ sanityinc.com

🐦 @sanityinc

sqlint's People

Contributors

Stargazers

Watchers

sqlint's Issues

Installation fails

Installing this ($ sudo gem install sqlint) fails for me with:

current directory: /var/lib/gems/2.5.0/gems/pg_query-1.1.0/ext/pg_query        
/usr/bin/ruby2.5 -r ./siteconf20190115-18131-tleqnp.rb extconf.rb              
mkmf.rb can't find header files for ruby at /usr/lib/ruby/include/ruby.h       
                                                                               
extconf failed, exit code 1

I'm running ruby 2.5.1p57on Ubuntu 18.10, if that is useful.

macOS/Linux package for Nix Packages collection

Thanks for creating this tool -- it's useful!

I wrote a macOS/Linux package for it to add to the Nix Packages collection. I listed myself as the maintainer, but since it's your library, I'm happy to hand it over to you. Just let me know.

If you're interested, you can add yourself to the maintainer list and edit the files in pkgs/development/tools/sqlint. (Additional details regarding Nix and Ruby.)

Inconsistency between sqlint.gemspec and Gemfile.lock?

It appears these two files refer to different versions of pgquery (0.13.5 vs. 1.0.0):
https://github.com/purcell/sqlint/blob/master/sqlint.gemspec#L39
https://github.com/purcell/sqlint/blob/master/Gemfile.lock#L6

Cannot get working in travis build

not sure if this is an issue with sqlint or pq_query

/home/travis/.rvm/rubies/ruby-2.4.1/lib/ruby/site_ruby/2.4.0/rubygems/core_ext/kernel_require.rb:55:in `require': incompatible library version - /home/travis/.rvm/gems/ruby-2.4.1/gems/pg_query-1.0.2/lib/pg_query/pg_query.so (LoadError)
	from /home/travis/.rvm/rubies/ruby-2.4.1/lib/ruby/site_ruby/2.4.0/rubygems/core_ext/kernel_require.rb:55:in `require'
	from /home/travis/.rvm/gems/ruby-2.4.1/gems/pg_query-1.0.2/lib/pg_query.rb:4:in `<top (required)>'
	from /home/travis/.rvm/rubies/ruby-2.4.1/lib/ruby/site_ruby/2.4.0/rubygems/core_ext/kernel_require.rb:55:in `require'
	from /home/travis/.rvm/rubies/ruby-2.4.1/lib/ruby/site_ruby/2.4.0/rubygems/core_ext/kernel_require.rb:55:in `require'
	from /home/travis/.rvm/gems/ruby-2.4.1/gems/sqlint-0.1.8/bin/sqlint:4:in `<top (required)>'
	from /home/travis/.rvm/gems/ruby-2.4.1/bin/sqlint:23:in `load'
	from /home/travis/.rvm/gems/ruby-2.4.1/bin/sqlint:23:in `<main>'
	from /home/travis/.rvm/gems/ruby-2.4.1/bin/ruby_executable_hooks2.4:15:in `eval'
	from /home/travis/.rvm/gems/ruby-2.4.1/bin/ruby_executable_hooks2.4:15:in `<main>'

which is the output from travis (trusty linux) with the below .travis.yml

language: python
python:
  - "3.6"
install:
  - yes "" | sudo apt-add-repository ppa:brightbox/ruby-ng
  - sudo apt-get update
  - wget https://repo.continuum.io/miniconda/Miniconda3-latest-Linux-x86_64.sh -O miniconda.sh
  - bash miniconda.sh -b -p $HOME/miniconda
  - export PATH="$HOME/miniconda/bin:$PATH"
  - hash -r
  - conda config --set always_yes yes --set changeps1 no
  - conda update -q conda
  # Useful for debugging any issues with conda
  - conda info -a
  - conda env create --name event-completeness --file environment.yml
  - source activate event-completeness
  - sudo apt-get install ruby2.4 ruby2.4-dev
  - sudo gem install pg
  - sudo gem pristine --all
  - sudo gem install pg_query
  - sudo gem install sqlint
  - sqlint *.sql
script:
  - flake8 .

works fine locally on ubuntu 16.04

sudo apt install ruby-full
sudo gem install sqlint
sqlint <sqlfile>

Any pointers appreciated!

Feature request: support for mysql

First of all thanks a lot for creating this linter!

It would be great if there would be support for the mysql syntax as well. Is there any chance this could be added? Thanks!

sqllint installation fails for pre-commit

pre-commit log below:

version information

pre-commit version: 2.17.0
git --version: git version 2.17.1
sys.version:
    3.9.5 (default, Dec  1 2021, 10:34:33) 
    [GCC 7.5.0]
sys.executable: /home/saurav/.pyenv/versions/3.9.5/bin/python3.9
os.name: posix
sys.platform: linux

error information

An unexpected error has occurred: CalledProcessError: command: ('/home/saurav/.rvm/rubies/ruby-3.0.0-cruby/bin/ruby', '/home/saurav/.rvm/rubies/ruby-3.0.0-cruby/bin/gem', 'build', 'sqlint.gemspec')
return code: 1
expected return code: 0
stdout: (none)
stderr:
    Invalid gemspec in [sqlint.gemspec]: cannot load such file -- pg_query
    ERROR:  Error loading gemspec. Aborting.

Traceback (most recent call last):
  File "/home/saurav/.pyenv/versions/3.9.5/lib/python3.9/site-packages/pre_commit/error_handler.py", line 70, in error_handler
    yield
  File "/home/saurav/.pyenv/versions/3.9.5/lib/python3.9/site-packages/pre_commit/main.py", line 396, in main
    return run(args.config, store, args)
  File "/home/saurav/.pyenv/versions/3.9.5/lib/python3.9/site-packages/pre_commit/commands/run.py", line 416, in run
    install_hook_envs(to_install, store)
  File "/home/saurav/.pyenv/versions/3.9.5/lib/python3.9/site-packages/pre_commit/repository.py", line 224, in install_hook_envs
    _hook_install(hook)
  File "/home/saurav/.pyenv/versions/3.9.5/lib/python3.9/site-packages/pre_commit/repository.py", line 82, in _hook_install
    lang.install_environment(
  File "/home/saurav/.pyenv/versions/3.9.5/lib/python3.9/site-packages/pre_commit/languages/ruby.py", line 132, in install_environment
    helpers.run_setup_cmd(
  File "/home/saurav/.pyenv/versions/3.9.5/lib/python3.9/site-packages/pre_commit/languages/helpers.py", line 52, in run_setup_cmd
    cmd_output_b(*cmd, cwd=prefix.prefix_dir, **kwargs)
  File "/home/saurav/.pyenv/versions/3.9.5/lib/python3.9/site-packages/pre_commit/util.py", line 154, in cmd_output_b
    raise CalledProcessError(returncode, cmd, retcode, stdout_b, stderr_b)
pre_commit.util.CalledProcessError: command: ('/home/saurav/.rvm/rubies/ruby-3.0.0-cruby/bin/ruby', '/home/saurav/.rvm/rubies/ruby-3.0.0-cruby/bin/gem', 'build', 'sqlint.gemspec')
return code: 1
expected return code: 0
stdout: (none)
stderr:
    Invalid gemspec in [sqlint.gemspec]: cannot load such file -- pg_query
    ERROR:  Error loading gemspec. Aborting.

Support to check Primary Keys and Column Format

Does SQL lint only checks the Syntax of the SQL file or it does support checking whether there are any primary keys existing for a table or not and whether the column formats as expected or not?

Somehow, hooks yaml is not cloned

Pre-commit fails to install the hooks, claiming it's not present in the cache folder used for cloning

Generated column syntax from postgresql 12 detected as error

docs: https://www.postgresql.org/docs/12/ddl-generated-columns.html

Example (from the docs above):

CREATE TABLE people (
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

$ sqlint test.sql
test.sql:3:43:ERROR syntax error at or near "("

It is curious that this is happening because I thought sqlint used psql as the parser.

Failed to build gem native extension

Hello, please forgive me in advance if I'm doing a mistake as I'm quite new to using vim. I'm trying to install sqlint on windows 10. Installation on Fedora 23 on another machine worked fine but I can't install on Windows 10 for use with gvim 7.4.17.

I installed ruby and the ruby dev environment, as required. As far as I understood, the issue lies with this part of the file "extconf.rb" (located here for me: C:\Ruby23-x64\lib\ruby\gems\2.3.0\gems\pg_query-0.9.0\ext\pg_query):
unless File.exist?("#{workdir}/libpg_query.tar.gz") File.open("#{workdir}/libpg_query.tar.gz", 'wb') do |target_file| open('https://codeload.github.com/lfittl/libpg_query/tar.gz/' + LIB_PG_QUERY_TAG, 'rb') do |read_file| target_file.write(read_file.read) end end end

I can successfully download the tar.gz file manually (recreating the url with LIB_PG_QUERY_TAG = '9.5-1.1.0'). But when I run "gem install sqlint", I get the following error message and this repository (C:\Ruby23-x64\lib\ruby\gems\2.3.0\gems\pg_query-0.9.0\ext\pg_query) contains an empty file ((libpg_query.tar.gz => 0KB).

`C:\Users\I079276>gem install sqlint
Temporarily enhancing PATH to include DevKit...
Building native extensions. This could take a while...
ERROR: Error installing sqlint:
ERROR: Failed to build gem native extension.

current directory: C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/pg_query-0.9.0/ext/pg_query

C:/Ruby23-x64/bin/ruby.exe -r ./siteconf20160419-8976-1lc4v5i.rb extconf.rb
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers. Check the mkmf.log file for more details. You may
need configuration options.

Provided configuration options:
--with-opt-dir
--without-opt-dir
--with-opt-include
--without-opt-include=${opt-dir}/include
--with-opt-lib
--without-opt-lib=${opt-dir}/lib
--with-make-prog
--without-make-prog
--srcdir=.
--curdir
--ruby=C:/Ruby23-x64/bin/$(RUBY_BASE_NAME)
C:/Ruby23-x64/lib/ruby/2.3.0/net/http.rb:933:in connect_nonblock': SSL_connect returned=1 errno=0 state=SSLv3 read server certificate B: certificate verify failed (OpenSSL::SSL::SSLError) from C:/Ruby23-x64/lib/ruby/2.3.0/net/http.rb:933:inconnect'
from C:/Ruby23-x64/lib/ruby/2.3.0/net/http.rb:863:in do_start' from C:/Ruby23-x64/lib/ruby/2.3.0/net/http.rb:852:instart'
from C:/Ruby23-x64/lib/ruby/2.3.0/open-uri.rb:319:in open_http' from C:/Ruby23-x64/lib/ruby/2.3.0/open-uri.rb:737:inbuffer_open'
from C:/Ruby23-x64/lib/ruby/2.3.0/open-uri.rb:212:in block in open_loop' from C:/Ruby23-x64/lib/ruby/2.3.0/open-uri.rb:210:incatch'
from C:/Ruby23-x64/lib/ruby/2.3.0/open-uri.rb:210:in open_loop' from C:/Ruby23-x64/lib/ruby/2.3.0/open-uri.rb:151:inopen_uri'
from C:/Ruby23-x64/lib/ruby/2.3.0/open-uri.rb:717:in open' from C:/Ruby23-x64/lib/ruby/2.3.0/open-uri.rb:35:inopen'
from extconf.rb:15:in block in <main>' from extconf.rb:14:inopen'
from extconf.rb:14:in `

extconf failed, exit code 1

Gem files will remain installed in C:/Ruby23-x64/lib/ruby/gems/2.3.0/gems/pg_query-0.9.0 for inspection.
Results logged to C:/Ruby23-x64/lib/ruby/gems/2.3.0/extensions/x64-mingw32/2.3.0/pg_query-0.9.0/gem_make.out`

The last double-dashed line (beginning with ruby=C:/...) contains what looks like an error related to a certificate: state=SSLv3 read server certificate B: certificate verify failed (OpenSSL::SSL::SSLError)
Is there a link with the fact SSLv3 is now considered insecure and generally blocked ?

I hope it's not a bad setup on my side that I would not be aware of and that I'm not simply make you lose your time...

Thanks guys, Donatien

Where gets sqlint the SQL-keywords?

When I was typing in the SQL-buffer in pair with sqlint, I got an error from sqlint that the USE-keyword would not be a valid SQL-word, like

  USE database;

But it's really a normal SQL-statement, see also this source.

So it led me to wondering where sqlint got the list of valid SQL-keywords?

support for vscode

Might you able to provide support for vscode.

Checker definition probably flawed?

I'm using Emacs 25.05 on Ubuntu 15.10. I tried to install sqlint, but it seems it's using Ruby. I got some error with installing:

gem install sqlint

It told me the following:

mkmf.rb can't find header files for ruby at /usr/lib/ruby/include/ruby.h

I got it solved from this with the command:

sudo apt-get install ruby2.1-dev

Then proceed with

gem install sqlint

In Emacs, M-x flycheck-verify-setup confirmed this with this:

Syntax checkers for buffer test.sql in sql-mode:

sql-sqlint
    - predicate:  t
    - executable: Found at /usr/local/bin/sqlint

Flycheck Mode is enabled.

Then I typed some words, but I see an error:

Suspicious state from syntax checker sql-sqlint: Checker sql-sqlint returned non-zero exit code 1, but no errors from output: /usr/lib/ruby/2.1.0/rubygems/core_ext/kernel_require.rb:135:in `require': cannot load such file -- sqlint (LoadError)
    from /usr/lib/ruby/2.1.0/rubygems/core_ext/kernel_require.rb:135:in `rescue in require'
    from /usr/lib/ruby/2.1.0/rubygems/core_ext/kernel_require.rb:144:in `require'
    from /var/lib/gems/2.1.0/gems/sqlint-0.1.2/bin/sqlint:5:in `<top (required)>'
    from /usr/local/bin/sqlint:23:in `load'
    from /usr/local/bin/sqlint:23:in `<main>'

Checker definition probably flawed.

Googling around gave me no usable advice. How to proceed further? Any help would be appreciated, since I was trying to solving it on my own.

Cant support create db if not exists command

CREATE DATABASE IF NOT EXISTS somedd;
Tool throwing ERROR syntax error at or near "NOT"

Fails to install and run on M1 Macs because of pg_query version

Hi,

Unfortunately, I'm not savvy with ruby to understand how to update the pg_query version used in this linter myself.

Since we updated to M1 macbooks we had trouble running the linter

Could not find pg_query-1.2.0 in any of the sources
Run `bundle install` to install missing gems.

When trying to install we get a lot of errors.

 Building native extensions. This could take a while...
ERROR:  Error installing pg_query:
        ERROR: Failed to build gem native extension.

    current directory: /Library/Ruby/Gems/2.6.0/gems/pg_query-1.2.0/ext/pg_query
/System/Library/Frameworks/Ruby.framework/Versions/2.6/usr/bin/ruby -I /System/Library/Frameworks/Ruby.framework/Versions/2.6/usr/lib/ruby/2.6.0 -r ./siteconf20220822-64024-sb0a0l.rb extconf.rb
compiling src/pg_query.c
compiling src/pg_query_fingerprint.c
compiling src/pg_query_json.c
compiling src/pg_query_json_plpgsql.c
In file included from src/pg_query_json_plpgsql.c:2:
In file included from src/pg_query_json_plpgsql.h:5:
In file included from ./src/postgres/include/plpgsql.h:21:
In file included from ./src/postgres/include/commands/trigger.h:17:
In file included from ./src/postgres/include/nodes/execnodes.h:17:
In file included from ./src/postgres/include/access/genam.h:19:
In file included from ./src/postgres/include/nodes/tidbitmap.h:26:
In file included from ./src/postgres/include/utils/dsa.h:17:
./src/postgres/include/port/atomics.h:68:10: fatal error: 'port/atomics/arch-arm.h' file not found
#include "port/atomics/arch-arm.h"
         ^~~~~~~~~~~~~~~~~~~~~~~~~
1 error generated.
make: *** [src/pg_query_json_plpgsql.o] Err

Turns out M1 Macs are compatible with pg_query 1.3.0 or later.

pganalyze/pg_query#210

Is there a way to update the pg_query version that this linter uses?

Thanks

Syntax error on postgresql backslash command

A book on PostgreSQL contains the following example code where the backslash command is causing sqlint to fail. I'm not experienced enough in SQL to say if this error is appropriate or not but PostgreSQL runs it fine.

BEGIN;


CREATE TABLE factbook (
  year int,
  date date,
  shares text,
  trades text,
  dollars text
);

\copy factbook FROM 'factbook.csv' WITH delimiter '\t' null ''

ALTER TABLE factbook
  ALTER shares
   TYPE bigint
  USING replace(shares, ',', '')::bigint,

  ALTER trades
   TYPE bigint
  USING replace(shares, ',', '')::bigint,

  ALTER dollars
   TYPE bigint
  USING substring(replace(dollars, ',', '') from 2)::numeric;

COMMIT;

12:1:ERROR syntax error at or near "\"

Backticks are considered a syntax error

e.g.

SELECT * FROM `my_table`

purcell / sqlint Goto Github PK

sqlint's Introduction

sqlint's People

Contributors

Stargazers

Watchers

Forkers

sqlint's Issues

version information

error information

Recommend Projects

Recommend Topics

Recommend Org