DOCSEARCHbyAlgolia
Submit Docs

sqls Language Server

repository·master·Indexed 23 days ago

https://github.com/sqls-server/sqls

An implementation of the Language Server Protocol (LSP) for SQL that provides auto-completion, join completion, code actions, and formatting. It supports multiple RDBMS including MySQL, PostgreSQL, SQLite3, MSSQL, H2, and Vertica.

Tokens
3.7K
Snippets
12
Records
18
Agent score
80%

What's inside sqls

  1. Supported RDBMS for sqls

    master

    sqls provides advanced intelligence for several relational database management systems:

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

  2. Configure sqls RDBMS connections

    master

    To use sqls features, you must configure a connection to an RDBMS. Configuration is applied using the following priority order:

    1. A configuration file specified via the -config flag.
    2. workspace/configuration settings provided by your LSP client.
    3. A configuration file located at $XDG_CONFIG_HOME/sqls/config.yml (defaults to $HOME/.config/sqls/config.yml).

    Note: The first connection defined in the connections list is treated as the default connection.

  5. Set up MySQL test databases

    master

    To set up the MySQL test databases for different versions (5.6, 5.7, and 8), download the world.sql dataset and import it into the respective running instances. Note that each version is mapped to a specific port:

    • MySQL 5.6: port 13305
    • MySQL 5.7: port 13306
    • MySQL 8: port 13307

    The default user is root with password root.

    wget https://downloads.mysql.com/docs/world.sql.gz
gzip world.sql.gz

# MySQL 5.6
mysql -u root -proot -h 127.0.0.1 -P 13305 < world.sql
# MySQL 5.7
mysql -u root -proot -h 127.0.0.1 -P 13306 < world.sql
# MySQL 8
mysql -u root -proot -h 127.0.0.1 -P 13307 < world.sql
rm world.sql

  6. Run the sqls language server

    master
    The sqls binary implements the Language Server Protocol (LSP) for SQL. By default, it reads from stdin and writes to stdout. You can start the server by running the binary without arguments or by providing specific flags to control logging and configuration.
    sqls

  7. Reference the sqls configuration file schema

    master

    The config.yml file allows you to define multiple database connections.

    Global Settings

    • lowercaseKeywords: (boolean) Set to true to use lowercase keywords instead of uppercase.

    Connection Object Keys

    dataSourceName takes precedence over individual fields like proto, user, passwd, host, port, and dbName.

    KeyDescription
    aliasConnection alias name. Optional.
    drivermysql, postgresql, sqlite3, mssql, h2, vertica. Required.
    dataSourceNameData source name (DSN).
    prototcp, udp, unix.
    userUser name
    passwdPassword
    hostHost
    portPort
    pathunix socket path
    dbNameDatabase name
    paramsOption params. Optional.
    sshConfigSSH configuration object. Optional.

    sshConfig Keys

    KeyDescription
    hostssh host. Required.
    portssh port. Required.
    userssh user. Optional.
    privateKeyprivate key path. Required.
    passPhrasepassPhrase. Optional.
    # 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

  8. Run supported RDBMS via Docker Compose

    master

    The docker-compose.yml file provides pre-configured services for various Relational Database Management Systems (RDBMS) to facilitate testing or development with sqls. You can spin up these databases using docker-compose up.

    services:
  mysql8:
    image: mysql:8
    container_name: sqls_mysql8
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: world
      MYSQL_USER: docker
      MYSQL_PASSWORD: docker
      TZ: 'Asia/Tokyo'
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
    - ./.docker/mysql/data:/var/lib/mysql8
    ports:
    - 13307:3306

  postgres12:
    image: postgres:12-alpine
    container_name: sqls_postgres12
    ports:
      - "15432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=mysecretpassword1234
      - PGPASSWORD=mysecretpassword1234
      - POSTGRES_DB=dvdrental
      - DATABASE_HOST=localhost
    volumes:
      - ./.docker/postgres/data:/var/lib/postgresql/data

  mssql2019:
    image: mcr.microsoft.com/mssql/server:2019-latest
    container_name: sqls_mssql2019
    ports:
      - "11433:1433"
    environment:
      SA_PASSWORD: Passw0rd
      ACCEPT_EULA: Y
    volumes:
      - ./.docker/mssql/data:/var/opt/mssql/data

  9. Configure sqls for Sublime Text 4

    master
    1. Install the LSP package via Package Control.
    2. Open Preferences > Package Settings > LSP > Settings.
    3. Add the sqls client configuration to the clients object.
    {
    "show_diagnostics_count_in_view_status": true,
    "clients": {
        "sqls": {
            "enabled": true,
            "command": ["/path/to/sqls binary"],
            "selector": "source.sql"
        }
    }
}

  10. Export keyword and function lists from MySQL

    master

    You can export the SQL keyword categories, keyword lists, and function lists from the MySQL test databases into text files. This is done by running specific .sql scripts against the mysql database for each version.

    Ports used:

    • 5.6: 13305
    • 5.7: 13306
    • 8: 13307
    # Export keyword & function list categories
mysql -u root -proot -h 127.0.0.1 -P 13305 -D mysql < help_categories.sql > ./export/help_categories_mysql56.txt
mysql -u root -proot -h 127.0.0.1 -P 13306 -D mysql < help_categories.sql > ./export/help_categories_mysql57.txt
mysql -u root -proot -h 127.0.0.1 -P 13307 -D mysql < help_categories.sql > ./export/help_categories_mysql8.txt

# Export keyword list
mysql -u root -proot -h 127.0.0.1 -P 13305 -D mysql < help_keywords_mysql56.sql > ./export/help_keywords_mysql56.txt
mysql -u root -proot -h 127.0.0.1 -P 13306 -D mysql < help_keywords_mysql57.sql > ./export/help_keywords_mysql57.txt
mysql -u root -proot -h 127.0.0.1 -P 13307 -D mysql < help_keywords_mysql8.sql  > ./export/help_keywords_mysql8.txt

# Export function list
mysql -u root -proot -h 127.0.0.1 -P 13305 -D mysql < help_functions_mysql56.sql > ./export/help_functions_mysql56.txt
mysql -u root -proot -h 127.0.0.1 -P 13306 -D mysql < help_functions_mysql57.sql > ./export/help_functions_mysql57.txt
mysql -u root -proot -h 127.0.0.1 -P 13307 -D mysql < help_functions_mysql8.sql  > ./export/help_functions_mysql8.txt

  11. Configure sqls for Neovim (nvim-lspconfig)

    master

    If using nvim-lspconfig, use the following setup in your Lua configuration. This example includes the sqls.nvim plugin for enhanced on_attach functionality.

    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',
        },
      },
    },
  },
}

  12. Configure sqls for VS Code (coc.nvim)

    master

    Add the following to your coc-settings.json (accessible via :CocConfig in Vim/Neovim) to point to your specific configuration file.

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