sqls-server/sqls

SQL language server written in Go.

sqls: SQL Language Server

An implementation of the Language Server Protocol for SQL.

Note

This project is currently under development and there is no stable release. Therefore, destructive interface changes and configuration changes are expected.

Features

sqls aims to provide advanced intelligence for you to edit sql in your own editor.

Support RDBMS

• MySQL(Go-MySQL-Driver)
• PostgreSQL(pgx)
• SQLite3(go-sqlite3)
• MSSQL(go-mssqldb)
• H2(pgx)
• Vertica(vertica-sql-go)

Language Server Features

Auto Completion

• DML(Data Manipulation Language)
  • SELECT
    • Sub Query
  • INSERT
  • UPDATE
  • DELETE
• DDL(Data Definition Language)
  • CREATE TABLE
  • ALTER TABLE

Join completion

If the tables are connected with a foreign key sqls can complete JOIN statements

CodeAction

• Execute SQL
• Explain SQL
• Switch Connection(Selected Database Connection)
• Switch Database

Hover

Signature Help

Document Formatting

Installation

go install github.com/sqls-server/sqls@latest

Editor Plugins

• sqls.vim
• vscode-sqls
• sqls.nvim
• Emacs LSP mode

DB Configuration

The connection to the RDBMS is essential to take advantage of the functionality provided by sqls. You need to set the connection to the RDBMS.

Configuration Methods

There are the following methods for RDBMS connection settings, and they are prioritized in order from the top. Whichever method you choose, the settings you make will remain the same.

1. Configuration file specified by the -config flag
2. workspace/configuration set to LSP client
3. Configuration file located in the following location
   • $XDG_CONFIG_HOME/sqls/config.yml ("$HOME/.config" is used instead of $XDG_CONFIG_HOME if it's not set)

Configuration file sample

# Set to true to use lowercase keywords instead of uppercase.
lowercaseKeywords: false
connections:
  - alias: dsn_mysql
    driver: mysql
    dataSourceName: root:root@tcp(127.0.0.1:13306)/world
  - alias: individual_mysql
    driver: mysql
    proto: tcp
    user: root
    passwd: root
    host: 127.0.0.1
    port: 13306
    dbName: world
    params:
      autocommit: "true"
      tls: skip-verify
  - alias: mysql_via_ssh
    driver: mysql
    proto: tcp
    user: admin
    passwd: Q+ACgv12ABx/
    host: 192.168.121.163
    port: 3306
    dbName: world
    sshConfig:
      host: 192.168.121.168
      port: 22
      user: sshuser
      passPhrase: ssspass
      privateKey: /home/sqls-server/.ssh/id_rsa
  - alias: dsn_vertica
    driver: vertica
    dataSourceName: vertica://user:pass@host:5433/dbname

Workspace configuration Sample

• setting example with vim-lsp.

if executable('sqls')
    augroup LspSqls
        autocmd!
        autocmd User lsp_setup call lsp#register_server({
        \   'name': 'sqls',
        \   'cmd': {server_info->['sqls']},
        \   'whitelist': ['sql'],
        \   'workspace_config': {
        \     'sqls': {
        \       'connections': [
        \         {
        \           'driver': 'mysql',
        \           'dataSourceName': 'root:root@tcp(127.0.0.1:13306)/world',
        \         },
        \         {
        \           'driver': 'postgresql',
        \           'dataSourceName': 'host=127.0.0.1 port=15432 user=postgres password=mysecretpassword1234 dbname=dvdrental sslmode=disable',
        \         },
        \       ],
        \     },
        \   },
        \ })
    augroup END
endif

• setting example with coc.nvim.

In coc-settings.json opened by :CocConfig

{
    "languageserver": {
        "sql": {
            "command": "sqls",
            "args": ["-config", "$HOME/.config/sqls/config.yml"],
            "filetypes": ["sql"],
            "shell": true
        }
    }
}

• setting example with nvim-lspconfig.

require'lspconfig'.sqls.setup{
  on_attach = function(client, bufnr)
    require('sqls').on_attach(client, bufnr) -- require sqls.nvim
  end
  settings = {
    sqls = {
      connections = {
        {
          driver = 'mysql',
          dataSourceName = 'root:root@tcp(127.0.0.1:13306)/world',
        },
        {
          driver = 'postgresql',
          dataSourceName = 'host=127.0.0.1 port=15432 user=postgres password=mysecretpassword1234 dbname=dvdrental sslmode=disable',
        },
      },
    },
  },
}

• Setting example for Sublime Text 4

  Install the LSP Client by Opening the command palette and run Package Control: Install Package, then select LSP.

  Open Preferences > Package Settings > LSP > Settings and add the "sqls" client configuration to the "clients":

{
    "show_diagnostics_count_in_view_status": true,
    "clients": {
        "sqls": {
            "enabled": true,
            "command": ["/path/to/sqls binary"],
            "selector": "source.sql"
        }
    }
}

I'm sorry. Please wait a little longer for other editor settings.

Configuration Parameters

The first setting in connections is the default connection.

Key	Description
connections	Database connections

connections

dataSourceName takes precedence over the value set in proto, user, passwd, host, port, dbName, params.

Key	Description
alias	Connection alias name. Optional.
driver	mysql, postgresql, sqlite3, mssql, h2. Required.
dataSourceName	Data source name.
proto	tcp, udp, unix.
user	User name
passwd	Password
host	Host
port	Port
path	unix socket path
dbName	Database name
params	Option params. Optional.
sshConfig	ssh config. Optional.

sshConfig

Key	Description
host	ssh host. Required.
port	ssh port. Required.
user	ssh user. Optional.
privateKey	private key path. Required.
passPhrase	passPhrase. Optional.

DSN (Data Source Name)

See also.

• https://github.com/go-sql-driver/mysql#dsn-data-source-name
• https://pkg.go.dev/github.com/jackc/pgx/v4
• https://github.com/mattn/go-sqlite3#connection-string

Contributors

This project exists thanks to all the people who contribute.

Inspired

I created sqls inspired by the following OSS.

• dbcli Tools
  • mycli
  • pgcli
  • litecli
• non-validating SQL parser
  • sqlparse

sqls's Issues

Version Don't Match

# internal/race
compile: version "go1.18.3" does not match go tool version "go1.18.4"
# internal/goarch
compile: version "go1.18.3" does not match go tool version "go1.18.4"
# encoding
compile: version "go1.18.3" does not match go tool version "go1.18.4"
# internal/unsafeheader
compile: version "go1.18.3" does not match go tool version "go1.18.4"
......

Spawning language server with cmd failed

Describe the bug
When opening any .sql file, it automatically attempts to start the language server but produces an error message
"Spawning language server with cmd: sqls failed. The language server is either not installed, missing from PATH, or not executable"

To Reproduce
Steps to reproduce the behavior:

1. Download and install sqls using go install github.com/lighttiger2505/sqls@latest
2. Setup the language server as below

require'lspconfig'.sqls.setup{
on_attach = on_attach,
flags = {
debounce_text_changes = 150
},
cmd = {"sqls", "-config", "/home/xllvr/.config/sqls/config.yml"},
capabilities = require('cmp_nvim_lsp').update_capabilities(vim.lsp.protocol.make_client_capabilities())
}

3. Open a .sql file

Expected behavior
Language server to spawn and begin functioning

Versions (please complete the following information):

• OS Version: Arch Linux (Linux 5.16.15-arch1-1)
• sqls Version: Latest

Additional context
Using cmp as the auto-completion, as well as having the native lsp setup (other language servers, namely R, tsserver and Pyright are functioning)

Cannot complete columns if table name is quoted

Assume we have order and logs table, both with id column and query buffer

SELECT *
FROM `order` o
JOIN logs l
WHERE 
-- cursor here

and we want to see possible columns after l, logs, o, and order. Currently, I see only column list for l and logs. After quoting logs the server returns an empty completion list.

sqls Version:v0.1.0, Revision:f955831, Debian sid, tested on MariaDB

Postgres completions don't work for tables outside of the "public" schema

Describe the bug

It seems like sqls has trouble with code completions and hover when dealing with a table outside of the public schema in Postgres. Both actions work fine for me for tables in the public schema, but return no results for tables in other schemas.

To Reproduce

Create a table in the default public schema, and one in another schema:

CREATE TABLE cat (
    age          integer,
    owner_name   varchar(40)
);

CREATE SCHEMA my_schema;
CREATE TABLE my_schema.owner (
    age    integer,
    name   varchar(40)
);

Write a query involving the two tables and experiment with completion on each table through sqls:

SELECT
   cat.age AS cat_age,
   owner.age AS owner_age 
FROM
   cat 
   JOIN
      my_schema.owner AS owner 
    ON cat.owner_name = owner.name;

In particular, try to complete from owner. or my_schema.owner.. Also try to perform a hover action over owner.

Expected behavior

I would expect completion to work on both the cat and owner tables. Instead, it only works on cat. This is true regardless of whether the full my_schema.owner. prefix is used or the owner alias. Similarly, I would expect textDocument/hover to work for both cat and owner, but it only works for cat.

Versions (please complete the following information):

• OS Version: Linux 5.17.1
• sqls Version: 0.2.22

Wrong formatting with comments.

-- comment 111
SELECT * from tb1;
-- comment 222
SELECT * from tb2;

Formatting Request:

{
    "jsonrpc": "2.0",
    "method": "textDocument/formatting",
    "params": {
        "textDocument": {
            "uri": "file:///workspace/mysql.sql"
        },
        "options": {
            "tabSize": 4,
            "insertSpaces": true
        }
    },
    "id": 1703824210
}

Original code:

lsp-server result:

{
    "id": 1703824210,
    "result": [
        {
            "range": {
                "start": {
                    "line": 0,
                    "character": 14
                },
                "end": {
                    "line": 3,
                    "character": 18
                }
            },
            "newText": "SELECT\n    *\nFROM\n    tb1;SELECT\n        *\n    FROM\n        tb2;"
        }
    ],
    "jsonrpc": "2.0"
}

Formatted code:

Problem:

1. Comments were removed.
2. The second SELECT is on wrong line.

Server/client connection

Hi,

I would like to use the library with Monaco Editor and I was trying to understand how the client and server are communicating. However I cannot understand on which port is the server running?
In the config files I could only find the database connection configs but nothing related to the client/server connection.
I also checked out the sourcegraph/jsonrpc2 library and Gorilla WebSocket (which is used by jsonrpc2) but I still couldn't find anything useful. The only thing I can assume is that client and server are communicating through WebSockets.

Maybe I am missing something, can I get some help on this?

Kerberos (gssapi) authentication support

I am connecting to our company postgresql server using kerberos ticket, but it seems to be not possible at the moment with current sqls. I see in my emacs session log this message:

LSP :: pq: kerberos error: no GSSAPI provider registered (import github.com/lib/pq/auth/kerberos if you need Kerberos support)

Would it be possible to add the support for this feature?

Added sqls to scoop

Is your feature request related to a problem? Please describe.
I've added sqls to the windows package manager, Scoop. Just add my bucket.
https://github.com/alextwothousand/scoop-bucket

Describe the solution you'd like
N/A

Describe alternatives you've considered
N/A

Additional context
N/A

ld: warning: directory not found for option '-L/Users/nshv/homebrew/lib'

I try to install with go install github.com/lighttiger2505/sqls@latest
Getting this - ld: warning: directory not found for option '-L/Users/nshv/homebrew/lib'

Is there any way to fix that?
MacOS Monterey 12.6 (M1pro)

Formatter breaks T-SQL

The database connections work very well with Microsoft SQL Server. However, the formatter is unusable. Try formatting a query like SELECT TOP 100 * FROM Table and it doesn't seem to know what to do with TOP.

Is the answer just to disable the formatter in the lsp config, or are there other formatter configuration parameters I have missed?

Deprecated install via `go get`

Describe the bug
Installing via go get gives the message go get: installing executables with 'go get' in module mode is deprecated.

To Reproduce
Steps to reproduce the behavior:

1. go get github.com/lighttiger2505/sqls

Expected behavior
No deprecated install message

Versions (please complete the following information):

• OS Version: Microsoft Windows 10 Pro Version 10.0.19043 Build 19043
• sqls Version 413ed89
• go version go1.17.2 windows/amd64

Install Messages

 % /c/Program\ Files/Go/bin/go get github.com/lighttiger2505/sqls
go: downloading github.com/lighttiger2505/sqls v0.2.19
go: downloading github.com/sourcegraph/jsonrpc2 v0.1.0
go: downloading gopkg.in/yaml.v2 v2.4.0
go: downloading github.com/go-sql-driver/mysql v1.6.0
go: downloading github.com/jackc/pgx/v4 v4.12.0
go: downloading github.com/mattn/go-sqlite3 v1.14.8
go: downloading golang.org/x/crypto v0.0.0-20210711020723-a769d52b0f97
go: downloading github.com/olekukonko/tablewriter v0.0.5
go: downloading github.com/mattn/go-runewidth v0.0.9
go: downloading github.com/jackc/pgtype v1.8.0
go: downloading github.com/jackc/pgconn v1.9.0
go: downloading github.com/jackc/pgproto3/v2 v2.1.1
go: downloading github.com/jackc/pgio v1.0.0
go: downloading github.com/jackc/pgservicefile v0.0.0-20200714003250-2b9c44734f2b
go: downloading github.com/jackc/pgpassfile v1.0.0
go: downloading github.com/jackc/chunkreader/v2 v2.0.1
go: downloading golang.org/x/text v0.3.6
go: downloading github.com/jackc/pgproto3 v1.1.0
go: downloading github.com/jackc/chunkreader v1.0.0
go: downloading golang.org/x/sys v0.0.0-20210615035016-665e8c7367d1
go get: installing executables with 'go get' in module mode is deprecated.
        Use 'go install pkg@version' instead.
        For more information, see https://golang.org/doc/go-get-install-deprecation
        or run 'go help get' or 'go help install'.

Formatter issues

Describe the bug
The formatter seems to have issues in certain cases.

To Reproduce
Steps to reproduce the behavior:
See before and after pictures for two different cases:
Before 1:

After 1:

Before 2:

After 2:

Expected behavior
The formatter wouldn't remove line breaks and merge lines together when it isn't appropriate.

Versions (please complete the following information):

• OS Version: Linux 5.17.1-arch1-1 x86_64 GNU/Linux
• sqls Version: "sqls version Version:, Revision:"

Per project connection

Is there way to setup per project connection, that will set the default database per project?

LSP not working with simple select getting "Not found statement"

LSP appears to register and be running in the background, but when typing "select" etc LSP does not kick in. When debugging enabled I see "Not found statement" errors from sqls.

To Reproduce

1. Enable debugging in vim
2. Edit sql file containing "select * from table;"
3. Start to type sql but LSP does not do anything.
4. See this in the log:

/opt/nvim-linux64/share/nvim/runtime/lua/vim/lsp/rpc.lua:457 ] "rpc" "/home/user/go/bin/sqls" "stderr"
'2021/06/15 07:07:00 error serving, Not found statement, Node: "select * from table;\n", Position: (3, 2):\n github.com/lighttiger2505/sqls/parser/parseutil.extractFocusedStatement\n
/home/user/go/pkg/mod/github.com/lighttiger2505/[email protected]/parser/parseutil/parseutil.go:62\n'

Expected behavior
LSP completes/suggests as I type.

Versions:

• Ubuntu : 20.04.2
• nanotee/sqls.nvim : Upto commit f2cc9c1 (i.e. latest)
• lighttiger2505/sqls : Latest
• $ ps -ef | grep sqls | grep go

user 36036 36031 0 09:44 pts/3 00:00:00 /home/user/go/bin/sqls

• LspInfo:

Configured servers: html, css, elixirls, angularls, typescript, lua, clangd, graphql, vim, vuels, pyright, gopls, latex, elmls,
bash, intelephense, kotlin_language_server, sumneko_lua, terraformls, bashls, dockerls, efm, java, solargraph, cmake,
dockerfile, dartls, yamlls, sqls, texlab, python, yaml, svelte, jsonls, cssls, emmet_ls, vimls, json, tsserver, rust_analyzer
Neovim logs at: /home/jim/.cache/ranger/nvim/lsp.log

1 client(s) attached to this buffer: sqls

Client: sqls (id 2)
root: /home/user/workspace/seymour-database-schema
filetypes: sql, mysql
cmd: /home/user/go/bin/sqls

1 active client(s):

Client: sqls (id 2)
root: /home/jim/workspace/seymour-database-schema
filetypes: sql, mysql
cmd: /home/jim/go/bin/sqls

Additional context
I'm using LunarVim and other LSP's work.

support for function/procedures/other schemas for mssql

I don't get the info for functions and procedures, and tables not in dbo have less info.

Describe the solution you'd like
Support, for functions/procedurs all schemas.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
I'm using mssql server 2016, neovim 0.8, and nanotee/sqls.nvim

"no database connection" on start when configuring by workspace/configuration

Describe the bug
A clear and concise description of what the bug is.

I currently use workspace/configuration instead of configuration files and the server is always emitting the "no database connection" error message on start, but the server does works regardless.

To Reproduce
Steps to reproduce the behavior:

1. Use a workspace/configuration config. An example one I use to set coc.nvim in neovim 0.5 is:

   vim.fn['coc#config']('languageserver.sqls', {
       command = 'sqls',
       filetypes = { 'sql' },
       settings = {
           sqls = {
               connections = {
                   {
                       driver = 'postgresql',
                       dataSourceName = 'postgres://postgres@localhost/database?sslmode=disable'
                   }
               }
           }
       }
   })

Expected behavior
A clear and concise description of what you expected to happen.

I don't expect this message to show up.

Versions (please complete the following information):

• OS Version: Arch Linux
• sqls Version sqls Version:0.2.18, Revision:db8733f

Additional context
Add any other context about the problem here.

DATABASE_URL env variable as source of database connection info

Is your feature request related to a problem? Please describe.
The DATABASE_URL environment variable is standard in many environments for specifying how to connect to the database. It would be nice if sqls could use, reducing the need for manual configuration.

Describe the solution you'd like
Check for the environment variable, if it's there use it to connect to the database.

Describe alternatives you've considered
Manual configuration.

Additional context
I don't know how standardized the format is. Possibly you'd need to do more work to support multiple variations.

Run without a database connection

Is your feature request related to a problem? Please describe.
I would like to use this language server to provide generic SQL autocompletion in an environment where I cannot guarantee the existence of a live database instance.

Describe the solution you'd like
It would be great if not having a configuration file meant that the language server would simply autocomplete generic SQL keywords. What currently happens is that the language server refuses to serve any completions, with the following error message:

Error: database cache not found

I believe this used to be the behavior of sqls, but it changed some releases ago.

Is it possible to see the type information for column in sqls?

Many language server can get document information such as variables and functions, so that I can check function arguments and variable type information.

Is the same possible with sqls?
I want to get colum type info and more...

Add support for Snowflake go driver

Is your feature request related to a problem? Please describe.
I was looking to get some autocomplete functionality when editing SQL in Emacs with lsp-mode.

I can run lsp mode and it connects to sqls and shows my configured connection string. I get a snowflake driver not found.

Describe the solution you'd like
I was looking to get some autocomplete functionality when editing SQL in Emacs with lsp-mode.

Describe alternatives you've considered
not using lsp-mode

Additional context
https://pkg.go.dev/github.com/snowflakedb/gosnowflake

Biquery Connection

Are there any plans to add a bigquery connection? What steps would be needed to do so? I used to use the Google IDE, but the new editor tabs have non-sensical autocompletion and are completely unusable for me.

Where does the config go?

Describe the bug
I'm trying to configure this package with spacemacs. I don't know what config file I need to create in order to create a connection.

To Reproduce
I've created ~/.config/sqls/config.yml and $PROJECT/.sqls/config.json. Neither of these appear to be getting picked up from sqls

I've run

sqls -trace -config ./.sqls/config.json
sqls -trace -config ~/.config/sqls/config.yml

But can't see anything that might tell me what's going on.

Expected behavior
An error message telling me what files are trying to be read (or not being found). More explicit documentation to help me configure correctly

Versions (please complete the following information):

• OS Version: ubuntu
• sqls Version: Unstalled with go get github.com/lighttiger2505/sqls today (2021-01-24)

$ sqls -version
sqls Version:, Revision:

Additional context
Add any other context about the problem here.

Please make a new release

I am not familiar with golang and suspect that the version installed by go get still have a bug wiith completion quoted table names. Is that true?

Please additionally add a hint how to build master version of sqls.

Server shutting down when using CASE keyword

Bug Description
When I attempt to use CASE in a query, the server shuts down right after I type the word and no more suggestions are made. If I press esc to return to normal mode I am shown a message Client 1 quit with exit code 2 and signal 0.

To Reproduce
Example reproduction:

1. With sqls active, type SELECT CASE W
2. Press esc to return to normal mode

Expected behavior
After typing SELECT CASE W, there should be suggestions for keywords which begin with 'W' and the server should not shut down unexepectedly.

Versions:

• OS Version: macOS 12
• sqls Version: latest

mssql connection fail for case sensitive databases.

Describe the bug
If databse is configured as case sensitive
then connection fails.

To Reproduce
Steps to reproduce the behavior:

1. Make sure Collation column for database is set to 'CS'
   SELECT DATABASEPROPERTYEX('<DATABSE_NAME>', 'Collation') SQLCollation;
   example: SQL_Latin1_General_CP1_CS_AS
2. configure database connection in neovim using sqls.nvim
3. try to connect.
4. See error in log file.
   :lua vim.cmd('e'..vim.lsp.get_log_path())
   example:
   [ERROR][2022-01-18 00:40:40] .../vim/lsp/rpc.lua:420 "rpc" "sqls" "stderr" "2022/01/18 00:40:40 mssql: Invalid object name 'information_schema.schemata'.\n"

Expected behavior
connection should be successful

Versions (please complete the following information):

• OS Version: macOS 12.1
• sqls Version: version that is installed by lsp-installer.
  sqls --version responses with sqls Version:, Revision:
• neovim Version: 0.6.1
• mssql dbms version: Microsoft SQL Server 2017 (RTM-CU28) (KB5008084) - 14.0.3430.2 (X64) 


Support for the SELECT query with a new line immediately after `SELECT`

Hello, thanks for the great project.

One thing I noticed using sqls is that SELECT statement with the syntax like below does not return
the results:

SELECT
   select_list
FROM
   table_name;

It returns the number of rows affected.
Would be nice if this type of SELECT also be supported.

Thanks.

Support for jupyterlab-lsp

Jupyter and JupyterLab are able to talk to a language server. It supports editing files (.sql) or editing in a jupyter cell (for instance is cell with a magic of %%sql or %%sparksql).

It would be great if sqls could be a language server for jupyter.

https://jupyterlab-lsp.readthedocs.io/en/latest/index.html

Is this something that is being considered?

change lib/pq to jackc/pgx

Completion error occurs.

It is used in Windows + gVim + vim-lsp + PostgreSQL.
sqls is the latest version.

The environment is as follows.

Windows10 64bit 1909
VIM - Vi IMproved 8.2 MS-Windows 64-bit GUI version Included patches: 1-735
go version go1.13.6 windows/amd64
PostgreSQL 12.2, compiled by Visual C++ build 1914, 64-bit

The following is the error log of vim-lsp.

panic serving textDocument/completion:
runtime error: invalid memory address or nil pointer dereference
goroutine 18 [running]:
github.com/lighttiger2505/sqls/internal/handler.panicf(0x89e180, 0x834df0, 0x8fead5, 0x2, 0xc0000c76b8, 0x1, 0x1, 0x8b62c0, 0x8cc040)
C:/Users/scope/go/src/github.com/lighttiger2505/sqls/internal/handler/handler.go:52 +0x93
github.com/lighttiger2505/sqls/internal/handler.(*Server).Handle.func1(0xc00008caf0, 0xc0000c7d98)
C:/Users/scope/go/src/github.com/lighttiger2505/sqls/internal/handler/handler.go:63 +0xcb
panic(0x89e180, 0x834df0)
C:/hirano/001.InsDir/Go/src/runtime/panic.go:679 +0x1c0
github.com/lighttiger2505/sqls/internal/completer.(*Completer).columnCandidates(0x0, 0xc000169580, 0xe, 0x10, 0x835720, 0x0, 0x0, 0x0)
C:/Users/scope/go/src/github.com/lighttiger2505/sqls/internal/completer/completer.go:423 +0x93
github.com/lighttiger2505/sqls/internal/completer.(*Completer).Complete(0x0, 0xc0002d8000, 0x5b96, 0xc000453fa0, 0x1f, 0x2, 0x10, 0x0, 0x0, 0x8, ...)
C:/Users/scope/go/src/github.com/lighttiger2505/sqls/internal/completer/completer.go:237 +0x9af
github.com/lighttiger2505/sqls/internal/handler.(*Server).handleTextDocumentCompletion(0xc0000803c0, 0x9857e0, 0xc00000a0c8, 0xc0000f6000, 0xc00008caf0, 0x16, 0x0, 0x0, 0x2)
C:/Users/scope/go/src/github.com/lighttiger2505/sqls/internal/handler/completion.go:27 +0x171
github.com/lighttiger2505/sqls/internal/handler.(*Server).handle(0xc0000803c0, 0x9857e0, 0xc00000a0c8, 0xc0000f6000, 0xc00008caf0, 0x0, 0xc000162240, 0x16, 0xa)
C:/Users/scope/go/src/github.com/lighttiger2505/sqls/internal/handler/handler.go:90 +0x9c1
github.com/lighttiger2505/sqls/internal/handler.(*Server).Handle(0xc0000803c0, 0x9857e0, 0xc00000a0c8, 0xc0000f6000, 0xc00008caf0, 0x0, 0x0, 0x0, 0x0)
C:/Users/scope/go/src/github.com/lighttiger2505/sqls/internal/handler/handler.go:67 +0xd8
github.com/sourcegraph/jsonrpc2.(*HandlerWithErrorConfigurer).Handle(0xc000036d30, 0x9857e0, 0xc00000a0c8, 0xc0000f6000, 0xc00008caf0)
C:/Users/scope/go/pkg/mod/github.com/sourcegraph/[email protected]/handler_with_error.go:21 +0x7a
github.com/sourcegraph/jsonrpc2.(*Conn).readMessages(0xc0000f6000, 0x9857e0, 0xc00000a0c8)
C:/Users/scope/go/pkg/mod/github.com/sourcegraph/[email protected]/jsonrpc2.go:522 +0x571
created by github.com/sourcegraph/jsonrpc2.NewConn
C:/Users/scope/go/pkg/mod/github.com/sourcegraph/[email protected]/jsonrpc2.go:334 +0x1e0

The connection information of .vimrc is as follows.

autocmd User lsp_setup call lsp#register_server({
\   'name': 'sqls',
\   'priority': 15,
\   'cmd': {server_info->['sqls']},
\   'whitelist': ['sql'],
\   'initialization_options': {
\   'sqls': {
\     'connections': [
\     {
\       'driver': 'postgresql',
\       'dataSourceName': 'host=LOCAL-IP port=5432 dbname=GTX1 user=postgres password=PASSWORD sslmode=disable',
\     },
\   ],}},
\ })

Thank you.

Add json1 support for sqlite driver

It seems there's no support for the json1 extension of sqlite.

select json_array(1, 2);

no such function: json_array

And when I checked it seems the sqlite3 was not compiled with json1 extension.

PRAGMA compile_options;

+----------------------------+
| COMPILE OPTIONS |
+----------------------------+
| COMPILER=gcc-11.1.0 |
| DEFAULT_WAL_SYNCHRONOUS=1 |
| ENABLE_FTS3 |
| ENABLE_FTS3_PARENTHESIS |
| ENABLE_RTREE |
| ENABLE_UPDATE_DELETE_LIMIT |
| OMIT_DEPRECATED |
| SYSTEM_MALLOC |
| THREADSAFE=1 |
+----------------------------+

I'm on: Emacs 28.0.50 on Manjaro Linux

So far I'm using sqlite inferior process to send queries instead of lsp.

If there's documentation on how to manually update the drivers, sorry. I think it only needs to be built with json1 tag.

Release binaries do not work with sqlite3

Describe the bug
I get the following error from sqls when using the sqlite3 backend:

Binary was compiled with 'CGO_ENABLED=0', go-sqlite3 requires cgo to work. This is a stub

To Reproduce
Download https://github.com/lighttiger2505/sqls/releases/download/v0.2.6/sqls_0.2.6_Linux_x86_64.tar.gz
Use following configuration file:

connections:
  - driver: sqlite3
    dataSourceName: db.sqlite3

Expected behavior
I would expect the language server to work with sqlite3 as documented in the README rather than printing a message saying it doesn't.

Versions (please complete the following information):

• OS Version: Ubuntu 20.10 in Docker
• sqls Version:0.2.6, Revision:888860b

Error when running code actions

Describe the bug

While trying to run any code action I am receiving an error:

To Reproduce
Steps to reproduce the behavior:

1. Setup a db connection
2. Open an SQL and write any query
3. Select the "Execute Query" code action

Actual behaviour

Error: 1RPC[Error] code_name = MethodNotFound, message = "method workspace/executeCommand is not supported by any of the servers registered for the current buffer"

Expected behavior

Query will be executed.

Versions (please complete the following information):

• OS Version: Ubuntu 20.04.2
• sqls Version: sqls Version:0.2.18, Revision:db8733f
• Editor: NVIM v0.5.0-dev+1233-g82ac44d01

Additional info

• LspInfo shows that sqls is up and running
• code completion works

Multiple Database Support?

I have the a mysql like this:

SELECT *
FROM db1.tb1 tb1_alias
JOIN db2.tb2 tb2_alias ON tb1_alias.<cursor_1> = tb2_alias.<cursor_2>

<cursor_1> will autocomplete, but cursor_2 not.

db1 and db2 in a same mysql server instance.

config:

# Set to true to use lowercase keywords instead of uppercase.
lowercaseKeywords: false
connections:
    -   alias: mysql1
        driver: mysql
        dataSourceName: myuser:mypwd@tcp(127.0.0.1:3306)/db1

If I add db2 in the yml file, still not work:

# Set to true to use lowercase keywords instead of uppercase.
lowercaseKeywords: false
connections:
    -   alias: mysql1
        driver: mysql
        dataSourceName: myuser:mypwd@tcp(127.0.0.1:3306)/db1

    -   alias: mysql2
        driver: mysql
        dataSourceName: myuser:mypwd@tcp(127.0.0.1:3306)/db2

How can auto-complete all databases and tables in one sql file?

Add Simplified Chinese support

execute sql
select * from sys_menu where name = '中文';

sqls: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''中�' at line 1

Subquery columns suggestions not working properly

Describe the bug
The columns suggestions for subqueries are not always given properly.

To Reproduce
Steps to reproduce the behavior:

WORKING CASE:

1. Create a sql statement such as:

SELECT * FROM 
    (SELECT sub1.listid, sub1.venuecity FROM 
        (SELECT v.venuecity, l.listid FROM venue v, listing l) sub1 
    ) sub2, users u WHERE u.userid = sub2.venuecity AND u.userid = sub2.listid;

2. When changing '*' with sub2. the column names listid and venuecity are suggested.

INCORRECT CASE:

1. Create a sql statement such as:

SELECT * FROM users u,
    (SELECT sub1.listid, sub1.venuecity FROM 
        (SELECT v.venuecity, l.listid FROM venue v, listing l) sub1 
    ) sub2 WHERE u.userid = sub2.venuecity AND u.userid = sub2.listid;

2. When changing '*' with sub2. the column name listid is suggested, venuecity is not being suggested.

Additional context
It seems like this issue happens when we first add a table in the FROM clause and then a subquery. If we add them the other way around this issue doesn't appear.

showDatabases for Postgresql does not return databases but schemas

I think this is because the showDatabases command uses NewMySQLDBRepository.

This MySQL repository executes the query select SCHEMA_NAME from information_schema.SCHEMATA; which, in postgres, returns list of schema.

test=# select version();
                                                      version                                                      
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.4 on x86_64-apple-darwin19.5.0, compiled by Apple clang version 11.0.3 (clang-1103.0.32.62), 64-bit
(1 row)

test=# select SCHEMA_NAME from information_schema.SCHEMATA;
    schema_name     
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 public
 information_schema
(6 rows)

Maybe it can use driver specific repository?
Sorry if I misunderstand something, Thanks.

Document config for connecting to Windows domain server with integrated authentication

I have a microsoft sql server I need to connect to using Windows authentication.
With the MS sql server plugin in VSCode, this works by specifying ServerName\DBinstance and specifying "integrated" for the authentication type.

The mssql go driver explicitly supports using windows authentication

However, I cannot tell how to configure sqls to take advantage of that. Using the above as the dataSourceName results in a call to localhost for the server, which is incorrect.

It would be great to get more clarity on how to do this please.

ODBC Supoort

May databases and data-lakes have a generic odbc driver. Would it be possible to support (linked against unixODBC)? There are a couple compatible libraries with database/sql: https://github.com/alexbrainman/odbc

Support MSSQL

Describe the solution you'd like
I've just started to use sqls and it is very nice. I would love to be able to use it for all the DBMS I encounter. Currently the main DBMS I am missing support for is MSSQL. Please add support for this.

Describe alternatives you've considered
Not using sqls.

Additional context
There appears to be a golang MSSQL driver that is actively developed at: https://github.com/denisenkom/go-mssqldb
It supports the database/sql package.

For testing, you can run MSSQL via docker FROM mcr.microsoft.com/mssql/server

Do you have developer-level docs on what is involved in adding support for a new DBMS?

Oracle support

I am trying to enable oracle support by adding a file oracle.go to
/internal/database/

I use docker with oracle xe to boot of a instance of oracle to test.

but I have some question:

1. [Solved]I download the project but could not compile, I could unit test with oracle_test.go
2. The test case seem not complete. [in progress]
3. Seem keyword.go need to be update. Any other file is related to db connection? [Working on the branch]
4. does the password is required to coded in the config? [Hard code as a temp solution]

Are there any related document about this?

Update readme to suggest adding the golang PPA

The 18.04 repo has an outdated version of go which doesn't support os.GetUserHomeDir. Could you please consider adding the golang PPA to the README?

Update readme to suggest adding the golang PPA

sudo add-apt-repository ppa:longsleep/golang-backports
sudo apt-get update
sudo apt-get install golang-go

Thanks!

Add Clickhouse support

Hello,

First of all , thanks for the project, it looks great !

I would like to know if you would welcome clickhouse support in sqls.
I could look at the code and see how I could make that happen. In addition, given that clickhouse is open source, we could integrate CI tests in your current workflow.

Would you welcome such PR ? If so, could you point me to the right direction ?

I was planing to use the official go driver for clickhouse

Thanks
Again, great work already !
Thank you for taking the time to build this LSP server.

Lack of 'verbose' option makes debugging miserable

Describe the bug

I'm attempting to use lspconfig in neovim to connect to a database.

The database string is postgresql:///lattice -- a unix-domain connection to postgres using the standard format -- and I get 'No database connection' from sqls.

When I inspect ~/.cache/nvim/lsp.log there is not enough information available to diagnose what is occurring:

[ERROR][2021-12-30 12:15:23] .../vim/lsp/rpc.lua:417	"rpc"	"/nix/store/xcy7s66xpf5vz8gxry212r8af3jgr1r8-sqls-0.2.20/bin/sqls"	"stderr"	"2021/12/30 12:15:23 sqls: reading on stdin, writing on stdout\n2021/12/30 12:15:23 db worker: start\n2021/12/30 12:15:23 send err no database connection\n2021/12/30 12:15:23 Send Message: no database connection\n"
[ERROR][2021-12-30 12:15:23] .../vim/lsp/rpc.lua:417	"rpc"	"/nix/store/xcy7s66xpf5vz8gxry212r8af3jgr1r8-sqls-0.2.20/bin/sqls"	"stderr"	"2021/12/30 12:15:23 db worker: Update db chache primary complete\n"
[ERROR][2021-12-30 12:15:23] .../vim/lsp/rpc.lua:417	"rpc"	"/nix/store/xcy7s66xpf5vz8gxry212r8af3jgr1r8-sqls-0.2.20/bin/sqls"	"stderr"	"2021/12/30 12:15:23 db worker: Update db chache secondary complete\n"
[ERROR][2021-12-30 12:16:12] .../vim/lsp/rpc.lua:417	"rpc"	"/nix/store/xcy7s66xpf5vz8gxry212r8af3jgr1r8-sqls-0.2.20/bin/sqls"	"stderr"	"2021/12/30 12:16:12 db worker: done\n"

Moreover, there does not appear to be a way of instructing sqls to be more verbose. Which in turn means that i cannot determine if there is an issue with how sqls handles unix-domain passwordless postgres connections :)

I'm so excited to use sqls and it looks awesome and I'm grateful for all your work, but if you could give us a --debug flag or a -v flag, I'd be unblocked!

PS you also misspelled 'cache', but english spelling is dumb, I'd file the bug against English not sqls ;)

Help!!There are many grammatical problems

1.Loss of double quotes in strings after formatting
2.These codes cannot be formatted，such as 'select * from my_mysql where id is not null'
3.Function hints without parentheses
...

Redundant grave accent appears after completion quoted name

Reproduction
Assume that logs is the only table with name beginning with l, then write

SELECT * from `l---cursor here

After completion I get

SELECT * FROM ``logs`

Expected behavior

SELECT * FROM `logs`

• Debian sid
• sqls Version:v0.2.0, Revision:6f977d8
  Additional context
• CoC nvim

Can you reproduce this bug?

Formatting breaks PostgreSQL code

I'm using neovim with nanotee/sqls.nvim plugin but formatting doesn't seem to work properly.

For example, this code:

CREATE TABLE
  users (
    id BIGSERIAL PRIMARY KEY,
    username TEXT UNIQUE NOT NULL CHECK > 8,
    password TEXT NOT NULL
  );

Will be formatted to:

CREATETABLEusers(
  id BIGSERIALPRIMARYKEY,
  usernameTEXTUNIQUENOTNULLCHECK>8,
  passwordTEXTNOTNULL
);

Installation fails

Describe the bug
I am trying to install sqls using go install, but it fails because of godror .

To Reproduce
Steps to reproduce the behavior:

1. run

go install github.com/lighttiger2505/sqls@latest

2. See error

# github.com/godror/godror
../pkg/mod/github.com/godror/[email protected]/orahlp.go:524:19: undefined: VersionInfo
../pkg/mod/github.com/godror/[email protected]/orahlp.go:525:19: undefined: VersionInfo
../pkg/mod/github.com/godror/[email protected]/orahlp.go:526:10: undefined: StartupMode
../pkg/mod/github.com/godror/[email protected]/orahlp.go:527:11: undefined: ShutdownMode
../pkg/mod/github.com/godror/[email protected]/orahlp.go:529:31: undefined: Event
../pkg/mod/github.com/godror/[email protected]/orahlp.go:529:42: undefined: SubscriptionOption
../pkg/mod/github.com/godror/[email protected]/orahlp.go:529:64: undefined: Subscription
../pkg/mod/github.com/godror/[email protected]/orahlp.go:530:31: undefined: ObjectType
../pkg/mod/github.com/godror/[email protected]/orahlp.go:531:59: undefined: Data
../pkg/mod/github.com/godror/[email protected]/orahlp.go:532:28: undefined: DirectLob
../pkg/mod/github.com/godror/[email protected]/orahlp.go:532:28: too many errors

Expected behavior
Installation success.

Versions (please complete the following information):

• OS Version: manjaro linux
• sqls Version @latest \ 0.2.22

Additional context
I ran

go clean -cache -modcache

and tried deleting cache manually to no effect.

Install fails on M1 mac

I am getting the below error despite having done go install github.com/lighttiger2505/sqls@latest
strangely enough the process worked in my intel mac but not on the M1 mac. Is there an additional step I might be missing ?

Additional context ->
my sqls file resides in /Users/username/go/bin which the the case for my intel mac as well.
However I had to run export GOPROXY=direct on M1 mac before running go install github.com/lighttiger2505/sqls@latest

• OS Version: Mac OS Monterey 12.6.1, M1 pro

MySQL - INSERT statement formatting bug

Describe the bug
INSERT statement format to invalid syntax statement

To Reproduce

1. Create file example.sql
2. Type this code INSERT INTO users (NAME, email, password,phone_number) VALUES ('john doe', '[email protected]', '123', '312324')
3. Call LSP format
4. Get result
   INSERT INTOusers( NAME, email, password, phone_number ) VALUES( 'john doe', '[email protected]', '123', '312324' )

Expected behavior
INTOusers should be INTO users

Versions (please complete the following information):

• OS Version: Arch Linux
• sqls Version: 0.2.20

Additional context
Add any other context about the problem here.

Invalid SQL leads to stack overflow

Describe the bug
Parsing an invalid SQL like select (case a when 'a' then b) leads to stack overflow.

To Reproduce
Run parsed, err := parser.Parse("select (case a when 'a' then b)")

Expected behavior
A graceful failure.

Versions (please complete the following information):

• OS Version: MacOS 12.6
• sqls Version 0.2.20