Emacs as SQL client with LSP
Tags: sql, emacs, lsp, database
Published: 2021-03-22 00:54:40


Hello there!

In this post, I show you how I configured my Emacs to be used as a SQL client using the [LSP - Language Server Protocol](https://langserver.org/) :)

{% youtube D97vDu_BhwA %}

<small> yes I'm a light-moder now, you can judge me </small>

If this is not your first time here, you probably know that I'm a [Spacemacs]() user, so it will be the focus of this tutorial, although with small adaptations you will be able to use on another "Emacs distro" or vanilla Emacs :)

# Third party installs

<img src="https://i.pinimg.com/originals/74/76/d9/7476d96990bc2c3d97a23493246107c3.gif"/>

We need to install the LSP support, the SQL linter and the SQL formatter, to do that we first need to install [Go](https://golang.org/) and [Ruby](https://www.ruby-lang.org/en/).

In macOS, you can do that by:

```shell
brew install ruby
brew install go
```

You need to export gopath on your `.bashrc` or similar, in my case I use [Oh My Zsh](https://ohmyz.sh/):

```shell
# add this line to your rc file
export PATH="$HOME/go/bin:$PATH"
# or
echo 'export PATH="$HOME/go/bin:$PATH"' >> .zshrc 
```

Now we install the `SQLS`, `sqlint` and `sqlfmt`:
```shell
gem install sqlint
go get github.com/lighttiger2505/sqls
wget -q -O - https://github.com/mjibson/sqlfmt/releases/download/v0.4.0/sqlfmt_0.4.0_darwin_amd64.tar.gz | tar -xpvzf - --directory "$HOME/go/bin"
# for other distros you can check out the releases
# here: https://github.com/mjibson/sqlfmt/releases
# remember to extract the files to your go path
```
At this point if you `ls` your `go/bin` you should see this:
```shell
ls $HOME/go/bin                                               
> sqlfmt sqls
```

And, of course, install `mysql` and `psql`:

```shell
brew install mysql
brew install libpq
brew link --force libpq
```

# Emacs installs
For Spacemacs users, you must add to you `dotspacemacs-configuration-layers` the lsp and the SQL layers:

```lisp
( ;; ...
  dotspacemacs-configuration-layers
    '( ;; ...
      lsp
      (sql :variables
           sql-backend 'lsp
           lsp-sqls-workspace-config-path nil)
      ;; ...
```
Obs.:

I will explain the `lsp-sqls-workspace-config-path` later on this text.

This will work on the most recent branch of Spacemacs.

**For non-spacemacs users**, you must install `sql` and `sql-indent` and of course the `lsp`, [this link might be of your interest](https://www.emacswiki.org/emacs/SqlMode).

# Final configs

Well, now we must configure our emacs, *i.e.* add our databases to some variables inside emacs.

Your LSP will try to reach a config file on your root or project, to avoid that we set the `lsp-sqls-workspace-config-path` to `nil`, so we can configure it on `elisp` only, but if you prefer you can do it as 
[this doc exemplifies](https://develop.spacemacs.org/layers/+lang/sql/README.html#external-dependencies).

We have two variables now to deal: `lsp-sqls-connections` and `sql-connection-alist`. The first one is to give access to LSP to your db, the second to your emacs.

They have the following shape:

```lisp
(setq lsp-sqls-connections
    '(((driver . "mysql") (dataSourceName . "yyoncho:local@tcp(localhost:3306)/foo"))
      ((driver . "postgresql") (dataSourceName . "host=127.0.0.1 port=5432 user=yyoncho password=local dbname=sammy sslmode=disable"))))
```
<small> [font](https://emacs-lsp.github.io/lsp-mode/page/lsp-sqls/) </small>

and

```lisp
(setq sql-connection-alist
      '((pool-a
         (sql-product 'mysql)
         (sql-server "1.2.3.4")
         (sql-user "me")
         (sql-password "mypassword")
         (sql-database "thedb")
         (sql-port 3306))))
```
<small> [font](https://stackoverflow.com/questions/5734965/how-can-i-get-emacs-sql-mode-to-use-the-mysql-config-file-my-cnf) </small>

Buuuuut there are some issues. 

<img src="https://media.tenor.com/images/b902b70c387295173375fc54f22b5d0f/tenor.gif"/>

You might have noticed that the form of `dataSourceName` is different if you are using `mysql` or `postgres` and there is a strange issue that emacs always asks for password on `postgres` databases even when you pass it to `sql-connection-alist`, the solution is to pass the full uri to the database variable.

But don't worry, I've got your back.

<img src="https://media3.giphy.com/media/iGHbjYrUftITS/giphy.gif?cid=82a1493b2j6tz0v1kvq81a6nxqezcjo96f8cknfug16e4ygu&rid=giphy.gif"/>

# Macros making your life easier

What if we could do this?

```lisp
(sql-add-mysql-db
 your-db-name
 :port 1234
 :user "username"
 :host "dbhost"
 :database "dbname"
 :password "mysql")

(sql-add-postgress-db
 your-db-name
 :port 1234
 :user "username"
 :host "dbhost"
 :database "dbname"
 :password "mysql")
```

Add these next functions and macros to your emacs config and you will be able to do that ;D

```lisp
;;;###autoload
(defmacro any-nil? (&rest args)
  `(not (and ,@args)))

;;;###autoload
(defmacro throw-if (condition &optional error-description)
  "if condition is true, thrown an error"
  `(if ,condition (error (or ,error-description ""))))

;; Variables related to sql configs
(setq lsp-sqls-connections nil)
(setq sql-connection-alist nil)

;;;###autoload
(defun format-postgres-sqls (host port user password db)
  (format "host=%s port=%s user=%s password=%s dbname=%s"
          host port user password db))

;;;###autoload
(defun format-mysql-sqls (host port user password db)
  (format "%s:%s@tcp(%s:%s)/%s" user password host port db))

;;;###autoload
(defun format-postgres-uri (host port user password db)
  (format "postgresql://%s:%s@%s:%s/%s" user password host port db))


;;;###autoload
(defun add-to-sqls-connections (db-type data-src-name)
  (add-to-list 'lsp-sqls-connections
               (list (cons 'driver db-type)
                     (cons 'dataSourceName data-src-name))))

;;;###autoload
(defmacro add-to-sql-conection-alist (db-type name host port user password db)
  `(add-to-list 'sql-connection-alist
                (list (quote ,name)
                     (list 'sql-product (quote ,db-type))
                     (list 'sql-user ,user)
                     (list 'sql-server ,host)
                     (list 'sql-port ,port)
                     (list 'sql-password ,password)
                     (list 'sql-database ,db))))

;;;###autoload
(defmacro sql-add-postgres-db (name &rest db-info)
  "Adds a mysql database to emacs and lsp
   This macro expects a name to the database and a p-list of parameters
   :port, :user, :password, :database, :host
   The only optional is :port, its default value is 5432
   e.g.:
   (sql-add-postgres-db
        my-db-name ;; notice that there are no quotes here
        :port 1234
        :user \"username\"
        :host \"my-host\"
        :database \"my-db\"
        :password \"mypassword\")"
  `(let ((port (or ,(plist-get db-info :port) 5432))
         (user ,(plist-get db-info :user))
         (password ,(plist-get db-info :password))
         (host ,(plist-get db-info :host))
         (db ,(plist-get db-info :database)))
     (throw-if (any-nil? user password host db (quote ,name)) "there are info missing!")
     (let ((full-uri (format-postgres-uri host port user password db))
           (data-src-name (format-postgres-sqls host port user password db)))
       (add-to-sqls-connections "postgresql" data-src-name)
       (add-to-sql-conection-alist 'postgres ,name host port user password full-uri))))

;;;###autoload
(defmacro sql-add-mysql-db (name &rest db-info)
  "Adds a mysql database to emacs and lsp
   This macro expects a name to the database and a p-list of parameters
   :port, :user, :password, :database, :host
   The only optional is :port, its default value is 3306
   e.g.:
   (sql-add-mysql-db
        my-db-name ;; notice that there are no quotes here
        :port 1234
        :user \"username\"
        :host \"my-host\"
        :database \"my-db\"
        :password \"mypassword\")"
  `(let ((port (or ,(plist-get db-info :port) 3306))
         (user ,(plist-get db-info :user))
         (password ,(plist-get db-info :password))
         (host ,(plist-get db-info :host))
         (db ,(plist-get db-info :database)))
     (throw-if (any-nil? user password host db (quote ,name)) "there are info missing!")
     (add-to-sqls-connections "mysql" (format-mysql-sqls host port user password db))
     (add-to-sql-conection-alist 'mysql ,name host port user password db)))
```

Those are in my [elisp repo](https://github.com/Viglioni/laurisp)

And that's it!
With all these configs you can now use your Emacs as a SQL client with a linter, a backend and a formatter :))

Stay safe, use masks and use Emacs
xoxo

<img src="https://i.imgur.com/HL9QRRz.gif"/>

# edits
- there was an error on `sql-add-postgres-db`:
```lisp
;; wrong
 (data-src-name (format-postgres-uri host port user password db))
;; correct
 (data-src-name (format-postgres-sqls host port user password db))
```