Using Emacs as a MySQL Client

What you really want from your database client is pretty simple:

To store multiple sets of connection credentials: dev, staging, maybe read-only access for production?, etc.
To connect, write and run queries, and view their results, all in your editor, which is the best program you know.
To write queries once—to not have to retype them all the time.

Fortunately for you, emacs is your editor, so all this is super simple.

Connecting

There are other guides that demonstrate the basics of connecting. To recap: start by adding something like this to your .emacs:

(setq sql-connection-alist
      '((db-dev (sql-product 'mysql)
                (sql-server "db.site.com")
                (sql-user "username")
                (sql-password "password")
                (sql-database "devDB")
                (sql-port 3306))
        (db-stg (sql-product 'mysql)
                (sql-server "db.site.com")
                (sql-user "username")
                (sql-password "password")
                (sql-database "stagingDB")
                (sql-port 3306))
        (db-prod (sql-product 'mysql)
                 (sql-server "db.site.com")
                 (sql-user "username")
                 (sql-password "password")
                 (sql-database "productionDB")
                 (sql-port 3306))))

Then add a connection function for each database:

(defun connect-to-database (label)
  "Connect to the database associated with the given `label`."
  (interactive)
  (let ((product (car (cdr (assoc label sql-connection-alist)))))
    (setq sql-product product)
    (sql-connect label)))

(defun mysql-db-dev ()
  (interactive)
  (connect-to-database 'db-dev))

(defun mysql-db-stg ()
  (interactive)
  (connect-to-database 'db-stg))

(defun mysql-db-prod ()
  (interactive)
  (connect-to-database 'db-prod))

Now you can run, e.g., M-x mysql-db-dev to connect. And if you’re in a sql-mode buffer, that buffer is linked with the mysql process, as you like it.

Which gets you two-thirds of what you want.

Queries

So what about all the queries you want to write once and never again? Well, clearly you want to put those in a file. And you want to associate that file with a connection in your sql-connection-alist. And you want to be able to open that file and run a command to connect to the database it’s associated with.

So download dbcnx-mode, put it somewhere in your load-path, and make it useful:

(require 'dbcnx-mode)
(add-to-list 'auto-mode-alist '("\\.dbcnx" . dbcnx-mode))

Now, create a file named according to a key in your sql-connection-alist, and give it the extension dbcnx—e.g., db-dev.dbcnx.

Opening that file should load dbcnx-mode (remember to eval the mode and the additions to your .emacs). You can now C-c C-l to connect to the db-dev database. Then, since dbcnx-mode derives from sql-mode, you can C-c C-c and C-c C-r all you like.

Credentials

One possible improvement to this setup is in how the credentials are accessed. Storing them in your .emacs isn’t ideal—for example, if you’re already storing them in your project’s .env, it’d be nice not to have to duplicate them, but to run a process that greps for the credentials from the .env and builds the sql-connection-alist entry from that. But that’s a project for another day.

contents ⁂ index