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 :)
yes I'm a light-moder now, you can judge me
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
We need to install the LSP support, the SQL linter and the SQL formatter, to do that we first need to install Go and Ruby.
In macOS, you can do that by:
brew install ruby
brew install go
You need to export gopath on your .bashrc or similar, in my case I use Oh My Zsh:
# 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:
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:
ls $HOME/go/bin
> sqlfmt sqls
And, of course, install mysql and psql:
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:
( ;; ...
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.
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.
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:
(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"))))
and
(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))))
Buuuuut there are some issues.
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.
Macros making your life easier
What if we could do this?
(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
;;;###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))
```
Top comments (1)
Thank you so much, I can not think that it is possible to use emacs as a sql client, thank you so much, I really appreciate that, keep up the good word :)