Using Emacs as a Database Client
Most people that use Emacs are programmers - that’s a fact! Most programmers have to deal with relational databases - that’s also a fact!
Programmers often interact with the database they have to use via some dedicated
client - be it a console or a GUI one. One fairly little known fact about Emacs
is that it features a mode that wraps around terminal database clients, called
sql-interactive-mode). The mode allows you to interact with a
relational database from the comfort of Emacs. Sure, you can run a terminal
psql directly from
ansi-term (for instance) as well - but if
you’re using a client with no
readline support (like the default clients for
Oracle and DB2) you’ll certainly appreciate
Let’s play a bit with
SQLi. To create a PostgreSQL connection start
M-x sql-postgres. You’ll be prompted for username, database,
password and host and then you’ll be dropped in a buffer dedicated to the
connection you’ve specified. Apart from being able to run all sorts of
SQL in that buffer you’ll also be able to send to it SQL from
files you’re editing in Emacs.
Let’s see how you can do that in a bit more detail. First you have to
associate an SQL file1 with a connection. While in some
M-x sql-set-product and type
postgres. Afterwards do
sql-set-sqli-buffer and select the name of the connection buffer you
want to use (it’s probably called
*SQL* if you have only one
connection buffer). Now you’ll be able to use commands like
C-c C-r) from the
.sql buffer and the code from
the region will be executed in the associated connection buffer.
If you’re connecting to the same database most of the time you can specify the login params in your configuration like this:
(setq sql-postgres-login-params '((user :default "postgres") (database :default "postgres") (server :default "localhost") (port :default 5432)))
Note that there are similar variables for every supported database (e.g.
Alternatively, you can specify a list of databases that you typically connect to and use the command
sql-connect to select a database from the list. Here’s an example:
(setq sql-connection-alist '((pgsql-prod (sql-product 'postgres) (sql-port 5432) (sql-server "db.prod.com") (sql-user "user") (sql-password "password") (sql-database "my-app")) (pgsql-staging (sql-product 'postgres) (sql-port 5432) (sql-server "db.staging.com") (sql-user "user") (sql-password "password") (sql-database "my-app")) (mysql-dev (sql-product 'mysql) (sql-port 5432) (sql-server "localhost") (sql-user "user") (sql-password "password") (sql-database "some-app"))))
Once this has been evaluated, you can run
M-x sql-connect and pick the database to
connect to. It’s a good habit to rename the
SQLi buffers manually to something
more meaningful if you have several of those - after all names like
*SQL<1>*, etc can be pretty confusing. You can also create some simple wrapper commands that
target some particular connection to help with this:
(defun sql-connect-to-pqsql-prod () (interactive) (sql-connect 'pqsql-prod "*pgsql-prod*"))
Obviously, you can make such wrapper functions as sophisticated as you need them to be. This one is super basic and will only connect to a particular database and set the name of the resulting
SQLi buffer accordingly.
A word of caution - make sure you don’t commit important credentials in plain text alongside your Emacs configuration. Normally in such cases you’d be using either encrypted credentials or you’ll be getting the credentials from env variables.
There’s a lot more to be said about using Emacs as a database client, but that’s beyond the scope of this introduction article. Have a look at the documentation of
sql-interaction-mode for further details. Also - don’t forget that
sql-interaction-mode is implemented on top of the standard
comint package, so it supports all the common
comint commands and configuration options.
That’s all I have for you today. Keep hacking!
Technically speaking - a buffer visiting an SQL file. ↩