5 minute read

Here’s how you can connect to databases and execute SQLs in Spacemacs (Emacs).

Configuration

Prerequisites:

  • Ruby
  • Go

Make sure these are already installed on your system.

Configuration:

  1. Enable sql layer

    Add sql to the existing dotspacemacs-configuration-layers list in your init file.

  2. Install external dependencies

    1. Syntax Checking: Install the sqlint gem.

      gem install sqlint

    2. Formatting: Install sqlfmt and move it to your $PATH.

      brew install sqlfmt
sqlfmt -v

    3. LSP supporting: Use sqls (Go implementation)

      1. Install sqls

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

        This installs sqls under go env GOPATH (e.g., ~/go/bin). Check:

        ~/go/bin/sqls --version

        Ensure Emacs can find the bin by either adding it to $PATH:

        export PATH=$PATH:$HOME/go/bin

        Or set the variable lsp-sqls-server to its absolute path:

        (with-eval-after-load 'lsp-sqls (setq lsp-sqls-server "~/go/bin/sqls"))

      2. Set the variable sql-backend to 'lsp, and the variable sql-lsp-sqls-workspace-config-path to 'workspace or 'root.

        (setq-default
 dotspacemacs-configuration-layers
 '((sql :variables
        sql-backend 'lsp
        sql-lsp-sqls-workspace-config-path 'workspace)))

        The difference between 'workspace and 'root depends on whether you are working in a simple project or a multi-module project (monorepo). For a simple project, they are usually the same. The distinction matters when your project has nested folders.

        • 'root (Project Root):

          • This is the top-level directory of your entire project, usually determined by the version control system (e.g., where the .git/ folder lives).

          • Use Case: You want one single configuration file to apply to the entire repository, regardless of which sub-folder you are working in.

        • 'workspace (LSP Workspace Folder):

          • This is the specific directory that lsp-mode considers the “root” for the current server instance. In a monorepo, this is often a subdirectory of the Git root.

          • Use Case: You have a monorepo with multiple distinct services (e.g., backend/ and frontend/), and you want a different SQL configuration for each one.

      3. Put .sqls/config.json files under those directories, and lsp-sqls will automatically connect DB when a .sqls file is first opened.

        {
    "sqls": {
        "connections": [
            {
                "driver": "mysql",
                "dataSourceName": "user1:password1@tcp(localhost:3306)/sample_db"
            },
            {
                "driver": "sqlite3",
                "dataSourceName": "/path/to/file.db",
                "alias": "sqlite-local"
            }
        ]
    }
}

        To add multiple connections of the same driver (e.g., two different SQLite databases or two PostgreSQL environments), you simply add another object to the connections array in your configuration file. Each connection entry is independent, even if they share the same “driver”. You can use the “alias” field to give them unique names.

        By default, sqls automatically selects the first connection listed in your connections array in .sqls/config.json. If you open a .sql file and do not manually switch connections, the LSP server will attempt to provide completions and run queries against that top-most entry. There is no explicit "default": true property in the sqls configuration schema. To change the default connection, you simply reorder the list in your configuration file.

      4. Another way to connect DB is through the variable lsp-sqls-connections.

        (setq lsp-sqls-connections
      '(((driver . "mysql")
         (dataSourceName . "<user>:<password>@tcp(localhost:3306)/<database>"))
        ((driver . "mssql")
         (dataSourceName . "Server=localhost;Database=sammy;User Id=yyoncho;Password=hunter2;"))
        ((driver . "postgresql")
         (dataSourceName . "host=127.0.0.1 port=5432 user=yyoncho password=123456 dbname=sammy sslmode=disable"))))

Notes on the difference between sqls and sql-ls

I want to enable and use the sql layer in Spacemacs. I’ve installed sqls as per the doc. But when I opened a .sql file it’s the language server sql-language-server (sql-ls) that is installed and started automatically.

Under the lsp-mode installation directory I find two files related to SQL support: lsp-sql.el and lsp-sqls.el.

It seems the first one is an implementation using the language server sql-ls (written in TypeScript) and the second is another implementation using the language server sqls (written in Go).

How do you use the Go version only? — You can disable sql-ls with:

(add-to-list 'lsp-disabled-clients 'sql-ls)

Connecting DB and Executing SQLs

You can connect to a database by opening an interactive REPL buffer (SQLi) using any of these commands:

  • sql-show-sqli-buffer (C-c C-z, M-m m b b)
  • sql-product-interactive (C-c TAB)
  • spacemacs/sql-start (M-m m ', M-m m s i)

They ask for the connection parameters from user input and create a new session if there isn’t an existing SQLi session.

Another method is using sql-connect and sql-connection-alist:

  1. Set an alist of connection parameters in variable sql-connection-alist.

    (setq sql-connection-alist
      '(("mysql-local"                  ; Used in the SQLi buffer name
         (sql-product 'mysql)           ; See sql-add-product
         (sql-server "localhost")
         (sql-port 3306)
         (sql-user "root")
         (sql-password "123456")
         (sql-database "mybase"))))

    Connections defined here appear in the submenu SQL->Start… for making new SQLi sessions.

  2. Start a SQLi session by calling sql-connect (M-m m b c), which asks for the above configured connection name.

    (sql-connect "mysql-local")

Once a connection is created you can execute SQLs in the SQLi buffer directly. You can also send the SQLs from elsewhere to the SQLi buffer. For example, from a sql-mode buffer you can send SQLs in the current line or region with sql-send-line-and-next (C-c C-n) or sql-send-region (C-c C-r) among others.

The SQLs are sent to the last created SQLi buffer by default. You can change the associated SQLi buffer for a sql-mode buffer or the globally default SQLi buffer using sql-set-sqli-buffer.

Commands in sql-mode

  • Change SQL dialect for the current buffer: spacemacs/sql-highlight (M-m m h k)

  • Capitalize keywords in region: sqlup-capitalize-keywords-in-region (M-m m c)

  • Format codes

    • in buffer: sqlfmt-buffer (M-m m = =)
    • in region: lsp-format-region (M-m m = r)
  • Connections
    • LSP (sqls)
      • Show all connection: lsp-sql-show-connections
      • Switch to another connection: lsp-sql-switch-connection
    • Connect DB in a SQLi buffer
      • sql-show-sqli-buffer (C-c C-z, M-m m b b)
      • sql-product-interactive (C-c TAB)
      • spacemacs/sql-start (M-m m ‘, M-m m s i)

  • Databases:

    • Show all: lsp-sql-show-databases
    • Switch: lsp-sql-switch-database

  • Tables

    • Show all tables in the current database:
      • sql-list-all (C-c C-l a, M-m m l a)
      • lsp-sql-show-tables, lsp-execute-code-action (M-m m a a)
    • Show all fields in a table:
      • sql-list-table (C-c C-l t, M-m m l t)
      • lsp-describe-thing-at-point (C-.)

  • Switch associated SQLi buffer to another: sql-set-sqli-buffer, sql-set-sqli-buffer-generally

  • Execute SQLs in the current:

    • line:
      • sql-send-line-and-next (C-c C-n)
      • spacemacs/sql-send-line-and-next (M-m m s l)
      • spacemacs/sql-send-line-and-next-and-focus (M-m m s L)
    • region:
      • sql-send-region (C-c C-r)
      • spacemacs/sql-send-region (M-m m s r)
      • spacemacs/sql-send-region-and-focus (M-m m s R)
      • lsp-sql-execute-query
    • paragraph:
      • sql-send-paragraph (C-c C-c)
      • spacemacs/sql-send-paragraph (M-m m s f)
      • spacemacs/sql-send-paragraph-and-focus (M-m m s F)
      • lsp-sql-execute-paragraph
    • buffer:
      • sql-send-buffer (C-c C-b)
      • spacemacs/sql-send-buffer (M-m m s b)
      • spacemacs/sql-send-buffer-and-focus (M-m m s B)
      • lsp-sql-execute-query
    • minibuffer:
      • sql-send-string (C-c C-s)
      • spacemacs/sql-send-string (M-m m s q)
      • spacemacs/sql-send-string-and-focus (M-m m s Q)

Commands in sql-interactive-mode

  • Rename buffer
    • by appending a number: sql-rename-buffer (M-m m b r)
    • to another name: Pass a universal-argument to the above
  • Save the connection information of the current session to sql-connection-alist if it wasn’t started with a connection name: sql-save-connection (M-m m b S)

References

Comments

You May Also Enjoy

Loading Local Images in Minimal Mistakes

4 minute read

I want to be able to load local images when the site is served locally. However, the URL format file:///Users/<username>/archive/image/20260218184820.p...