What you really want from your database client is pretty simple:
Fortunately for you, emacs is your editor, so all this is super simple.
There are other guides that demonstrate the basics of connecting. To recap: start by adding something like this to your
(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.
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.
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
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.
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.