Home

Awesome

SQL Language Server

build-and-test

The SQL Language Server offers autocompletion, error/warning notifications, and other editor support. It includes a linting feature, an SQL parser, and a Visual Studio Code extension. It supports MySQL, PostgreSQL, and SQLite3 databases.

completion

Packages

Supported DB

Installation & How to setup

Visual Studio Code

Install the VSC extension.

Other Editors

npm i -g sql-language-server

Neovim

LanguageClient-neovim

Add the following to the init.vim file (.vimrc):

let g:LanguageClient_serverCommands = {
    \ 'sql': ['sql-language-server', 'up', '--method', 'stdio'],
    \ }
nvim-lsp

Run the following command and reference the nvim-lsp documentation for more information.

:LspInstall sqlls

Monaco Editor (monaco-languageclient)

See the example to use the Monaco Editor to develop sql-language-server.

Follow the development section section to check Mocaco Editor working.

Usage

CLI

$ sql-language-server up [options]        run sql-language-server

Options

  --version      Show version number                                   [boolean]
  --help         Show help                                             [boolean]
  --method, -m  What use to communicate with sql language server
                   [string] [choices: "stdio", "node-ipc"] [default: "node-ipc"]
  --debug, -d    Enable debug logging                 [boolean] [default: false]
$ sql-language-server up --method stdio

Configuration

There are two ways to use configuration files.

Example for personal configuration file

{
  "connections": [
    {
      "name": "sql-language-server",
      "adapter": "mysql",
      "host": "localhost",
      "port": 3307,
      "user": "username",
      "password": "password",
      "database": "mysql-development",
      "projectPaths": ["/Users/joe-re/src/sql-language-server"],
      "ssh": {
        "user": "ubuntu",
        "remoteHost": "ec2-xxx-xxx-xxx-xxx.ap-southeast-1.compute.amazonaws.com",
        "dbHost": "127.0.0.1",
        "port": 3306,
        "identityFile": "~/.ssh/id_rsa",
        "passphrase": "123456"
      }
    },
    {
      "name": "postgres-project",
      "adapter": "postgres",
      "host": "localhost",
      "port": 5432,
      "user": "postgres",
      "password": "pg_pass",
      "database": "pg_test",
      "projectPaths": ["/Users/joe-re/src/postgres_project"]
    },
    {
      "name": "sqlite3-project",
      "adapter": "sqlite3",
      "filename": "/Users/joe-re/src/sql-language-server/packages/server/test.sqlite3",
      "projectPaths": ["/Users/joe-re/src/sqlite2_project"]
    }
  ]
}

Please restart sql-language-server process after creating .sqllsrc.json.

Connection parameters

KeyDescriptionvaluerequireddefault
nameConnection name(free-form text)true
adapterDatabase type"mysql" or "postgres" or "sqlite3" or "bigquery"true
hostDatabase hoststringfalse
portDatabase portstringfalsemysql:3306, postgres:5432
userDatabase userstringfalsemysql:"root", postgres:"postgres"
passwordDatabase passwordstringfalse
databaseDatabase namestringfalse
filenameDatabase filename(only for sqlite3)stringfalse
projectPathsProject path that you want to apply(if you don't set it configuration will not apply automatically when lsp's started up)string[]false[]
sshSettings for port fowarding*see below SSH sectionfalse
SSH
KeyDescriptionvaluerequireddefault
remoteHostThe host address you want to connect tostringtrue
remotePortPort number of the server for sshnumberfalse22
userUser name on the serverstringfalse
dbHostDatabase host on the serverstringfalse127.0.0.1
dbPortDatabase port on the servernumberfalsemysql:3306, postgres:5432
identityFileIdentity file for sshstringfalse~/.ssh/config/id_rsa
passphrasePassphrase to allow to use identity filestringfalse

Personal configuration file

The personal configuration file is located at ~/.config/sql-language-server/.sqllsrc.json. When the SQL Language Server starts, it will try to read this file.

Project configuration file

The project configuration file is located at ${YOUR_PROJECT_ROOT}/.sqllsrc.json. This file has the same settings as the personal configuration file, with a few exceptions:

Here is an example project configuration file for a PostgreSQL database:

{
  "name": "postgres-project",
  "adapter": "postgres",
  "host": "localhost",
  "port": 5432,
  "user": "postgres",
  "database": "pg_test"
}

If you have also set a personal configuration, the project configuration and personal configure will be merged if they have the same name.

Personal configuration example:

{
  "connections": [{
    "name": "postgres-project",
    "password": "password",
    "ssh": {
      "user": "ubuntu",
      "remoteHost": "ec2-xxx-xxx-xxx-xxx.ap-southeast-1.compute.amazonaws.com",
      "dbHost": "127.0.0.1",
      "port": 5432,
      "identityFile": "~/.ssh/id_rsa",
      "passphrase": "123456"
    }
  }]
}

It will merge them as follows:

{
  "name": "postgres-project",
  "adapter": "postgres",
  "host": "localhost",
  "port": 5432,
  "user": "postgres",
  "database": "pg_test",
  "password": "password",
  "ssh": {
    "user": "ubuntu",
    "remoteHost": "ec2-xxx-xxx-xxx-xxx.ap-southeast-1.compute.amazonaws.com",
    "dbHost": "127.0.0.1",
    "port": 5432,
    "identityFile": "~/.ssh/id_rsa",
    "passphrase": "123456"
  }
}

Workspace configuration for sql-language-server

Parameters of workspace configuration
Example of workspace configuration

~/.config/nvim/coc-settings.json

{
  "languageserver": {
    "sql": {
      "command": "sql-language-server",
      "args": ["up", "--method", "stdio"],
      "filetypes": ["sql"],
      "settings": {
        "sqlLanguageServer": {
          "connections": [
            {
              "name": "mysql_project",
              "adapter": "mysql",
              "host": "127.0.0.1",
              "port": 3306,
              "user": "sqlls",
              "password": "sqlls",
              "database": "mysql_db",
              "projectPaths": ["/Users/joe_re/src/MysqlProject"],
              "ssh": {
                "user": "ubuntu",
                "remoteHost": "xxx-xx-xxx-xxx-xxx.ap-southeast-1.compute.amazonaws.com",
                "dbHost": "127.0.0.1",
                "port": 3306
              }
            }
          ],
          "lint": {
            "rules": {
              "align-column-to-the-first": "error",
              "column-new-line": "error",
              "linebreak-after-clause-keyword": "off",
              "reserved-word-case": ["error", "upper"],
              "space-surrounding-operators": "error",
              "where-clause-new-line": "error",
              "align-where-clause-to-the-first": "error"
            }
          }
        }
      }
    }
  }
}
"settings": {
  "sqlLanguageServer.connections": [
    {
      "name": "mysql_project",
      "adapter": "mysql",
      "host": "127.0.0.1",
      "port": 3306,
      "user": "sqlls",
      "password": "sqlls",
      "database": "mysql_db",
      "projectPaths": ["/Users/joe_re/src/MysqlProject"],
      "ssh": {
        "user": "ubuntu",
        "remoteHost": "xxx-xx-xxx-xxx-xxx.ap-southeast-1.compute.amazonaws.com",
        "dbHost": "127.0.0.1",
        "port": 3306
      }
    }
  ],
  "sqlLanguageServer.lint": {
    "rules": {
      "align-column-to-the-first": "off",
      "column-new-line": "error",
      "linebreak-after-clause-keyword": "error",
      "reserved-word-case": ["error", "upper"],
      "space-surrounding-operators": "error",
      "where-clause-new-line": "error",
      "align-where-clause-to-the-first": "error",
    }
  }
}

Inject environment variables

${env:VARIABLE_NAME} syntax allows you to replace configuration value with an environment variable. This is useful if you don't want to store the value in the configuration file.

Example
{
  "adapter": "mysql",
  "host": "localhost",
  "port": 3307,
  "user": "username",
  "password": "${env:DB_PASSWORD}",
  "database": "mysql-development",
  "ssh": {
    "user": "ubuntu",
    "remoteHost": "ec2-xxx-xxx-xxx-xxx.ap-southeast-1.compute.amazonaws.com",
    "dbHost": "127.0.0.1",
    "port": 3306,
    "identityFile": "~/.ssh/id_rsa",
    "passphrase": "${env:SSH_PASSPHRASE}"
  }
}

Switch database connection

If you have multiple connection entries in your personal config file, you can switch the database connection.

2020-05-25_15-23-01

VSC extension provides Switch database connection command.

Raw RPC param:

method: workspace/executeCommand
command: switchDataBaseConnection
arguments: string(project name)

SQLite3 Notes

If you get error when you use sqlite3 connection, you may need to rebuild sqlite3 to your environment.

VSC extension provides the command to rebuild it.(Name: Rebuild SQLite3 Client) image

If you're using sql-language-server directly, go to the install directory and run npm rebuild sqlite to rebuild it.

Linting

You can use lint rules provided by sqlint to ensure your SQL code follows best practices and avoid potential errors. Refer to the sqlint configuraton documentation to learn how to use and configure the linter to match your use case.

sqlint-on-editor

You can also use sqlint to automatically fix any problems it can identify in your code.

2020-06-18_08-24-03

Raw RPC param:

method: workspace/executeCommand
command: fixAllFixableProblems
arguments: string(document uri)

Contributing to sql-language-server

Bug Reports and Feature Requests

If you have any questions, problems or suggestions for improvements, feel free to create a new issue on GitHub Issues. You can also start a discussion there about new rules for SQLint.

Development

Code contributions are always appreciated, so feel free to fork the repo and submit pull requests.

Development environment

You can start developing sql-language-server using Docker Compose. To begin the development process in your Docker container, run the following command:

$ docker compose up

Open http://localhost:3000 on your browser.

Migrating the Database

To migrate the database, follow these steps:

  1. Login into development Docker container
$ docker compose exec assets bash
  1. Migrate the database
$ cd example/monaco_editor
$ yarn migrate:postgres # postgres
$ yarn migrate:mysql    # mysql
$ yarn migrate:sqlite   # sqlite3