Using Emacs With PostgreSQL

I like to stay inside of Emacs as much as possible, and it's amazing that I have put off this quality of life improvement for so long. After reading this post about using Emacs as a database client, I decided to try and make this more convenient for my setup.

The Macro

I built it as a macro, which just makes all this convenient to wrap up. The macro takes the connection information as a &rest parameter, so that I may specify several at once. Each one needs to be added to the list of connections. Then, I'd like to automatically define an interactive function for the connection using its name.

(defvar sql-connection-alist nil)

(defmacro sql-specify-connections (&rest connections)
  "Set the sql-connection-alist from CONNECTIONS.
Generates respective interactive functions to establish each
connection."
  `(progn
     ,@(mapcar (lambda (conn)
		 `(add-to-list 'sql-connection-alist ',conn))
	       connections)
     ,@(mapcar (lambda (conn)
		  (let* ((varname (car conn))
			 (fn-name (intern (format "sql-connect-to-%s" varname)))
			 (buf-name (format "*%s*" varname)))
		    `(defun ,fn-name ,'()
		       (interactive)
		       (sql-connect ',varname ,buf-name))))
		connections)))

This is just part of my general Emacs configuration.

Using the Macro

In my init.el I have a last step to optionally load some work-specific functionality. I added work.el to my .gitignore file so that I don't have to worry about committing any sensitive information.

(when (file-exists-p "~/.emacs.d/work.el")
  (load-file "~/.emacs.d/work.el"))

Then, inside my work.el file I use the macro:

(sql-specify-connections
 (mytest-pgsql-dev (sql-product 'postgres)
	     (sql-port 5432)
	     (sql-server "localhost")
	     (sql-user "postgres")
	     (sql-password "password")
	     (sql-database "myapp_development"))
 (mytest-pgsql-test (sql-product 'postgres)
	      (sql-port 5432)
	      (sql-server "localhost")
	      (sql-user "postgres")
	      (sql-password "password")
	      (sql-database "myapp_test")))

Expand the Macro

You can look at what this expands to by running M-x macrostep-expand, but I have included it here:

(progn
  (add-to-list 'sql-connection-alist
	       '(mytest-pgsql-dev
		 (sql-product 'postgres)
		 (sql-port 5432)
		 (sql-server "localhost")
		 (sql-user "postgres")
		 (sql-password "password")
		 (sql-database "myapp_development")))
  (add-to-list 'sql-connection-alist
	       '(mytest-pgsql-test
		 (sql-product 'postgres)
		 (sql-port 5432)
		 (sql-server "localhost")
		 (sql-user "postgres")
		 (sql-password "password")
		 (sql-database "myapp_test")))
  (defun sql-connect-to-mytest-pgsql-dev nil
    (interactive)
    (sql-connect 'mytest-pgsql-dev "*mytest-pgsql-dev*"))
  (defun sql-connect-to-mytest-pgsql-test nil
    (interactive)
    (sql-connect 'mytest-pgsql-test "*mytest-pgsql-test*")))

Now, as I'm bopping in and out of .sql files in my project, I can use these interactive functions to share a connection to a database.

Extra

While I'm talking about my postgres development setup, it'd be worth including a couple of lines from my .psqlrc file:

\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%\n'
\set PROMPT2 ''

These lines get picked up when Emacs starts the psql process, and ensures that the client writes more useful values for prompts. This allows me to ensure that the header row of my query results are properly aligned.

Another extra thing I use is C-c C-s to send things like \x to toggle vertical/horizontal display of query results.