How can I get emacs sql-mode to use the mysql config file (.my.cnf)?

Question

When I type mysql dbname at the bash prompt, I automatically connect to the database dbname with the username, password, and host information included in my .my.cnf file.

When I use M-x sql-mysql in emacs, I am asked for all of this information again.

Is there a way that I can get emacs sql mode to use the information in my .my.cnf file?

Answer 1

I don't think that this is possible but you can set such stuff in the mode configuration itself:

(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))
(pool-b
(sql-product 'mysql)
(sql-server "1.2.3.4")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3307))))

(defun sql-connect-preset (name)
  "Connect to a predefined SQL connection listed in `sql-connection-alist'"
  (eval `(let ,(cdr (assoc name sql-connection-alist))
    (flet ((sql-get-login (&rest what)))
      (sql-product-interactive sql-product)))))

(defun sql-pool-a ()
  (interactive)
  (sql-connect-preset 'pool-a))

Have a look at this article for more info.

Answer 2

Sure it's possible. It's quite involved though.

Roughly, the steps are:

1. Reading and parsing the ini file using ini.el.
2. Merging the different hosts config with the clients config because the latter is applied globally.
3. Transforming each host to a suitable format for sql-connection-alist
4. Populating sql-connection-alist
5. Using M-x sql-connect (which auto-completes!)

The following code also includes a .pgpass parser, just in case. You'll notice the implementation is simpler.

;;; .pgpass parser
(defun read-file (file)
  "Returns file as list of lines."
  (with-temp-buffer
    (insert-file-contents file)
    (split-string (buffer-string) "\n" t)))

(defun pgpass-to-sql-connection (config)
  "Returns a suitable list for sql-connection-alist from a pgpass file."
          (let ((server (lambda (host port db user _pass)
                          (list
                           (concat db ":" user ":" port ":" host)
                           (list 'sql-product ''postgres)
                           (list 'sql-server host)
                           (list 'sql-user user)
                           (list 'sql-port (string-to-number port))
                           (list 'sql-database db))))
                (pgpass-line (lambda (line)
                               (apply server (split-string line ":" t)))))
            (mapcar pgpass-line config)))

;;; .my.cnf parser
;;; Copied verbatim from https://github.com/daniel-ness/ini.el/blob/master/ini.el
(defun ini-decode (ini_text) 
  ;; text -> alist
  (interactive)
  (if (not (stringp ini_text))
      (error "Must be a string"))
  (let ((lines (split-string ini_text "\n"))
    (section)
    (section-list)
    (alist))
    (dolist (l lines)
      ;; skip comments
      (unless (or (string-match "^;" l)
          (string-match "^[ \t]$" l))
    ;; catch sections
    (if (string-match "^\\[\\(.*\\)\\]$" l)
        (progn 
          (if section
          ;; add as sub-list
          (setq alist (cons `(,section . ,section-list) alist))
        (setq alist section-list))
          (setq section (match-string 1 l))
          (setq section-list nil)))
          ;; catch properties
          (if (string-match "^\\([^\s\t]+\\)[\s\t]*=[\s\t]*\\(.+\\)$" l)
          (let ((property (match-string 1 l))
            (value (match-string 2 l)))
            (progn 
              (setq section-list (cons `(,property . ,value) section-list)))))))
    (if section
    ;; add as sub-list
    (setq alist (cons `(,section . ,section-list) alist))
      (setq alist section-list))
    alist))

(defun read-ini (file)
  "Returns ini file as alist."
  (with-temp-buffer
    (insert-file-contents file)
    (ini-decode (buffer-string))))

(defun filter-alist (wanted-members alist)
  "Returns a copy of given alist, with only fields from wanted-members."
  (let ((result nil)
        (add-if-member (lambda (elt)
                         (when (member (car elt) wanted-members)
                           (add-to-list 'result elt t)))))
    (mapc add-if-member alist)
    result))

(defun merge-alist (original override)
  "Returns a union of original and override alist. On key conflict, the latter wins."
  (let ((result (copy-alist override))
        (add (lambda (elt)
               (setq result (add-to-list
                             'result elt t
                             (lambda (left right) (equal (car left) (car right))))))))
    (mapc add original)
    result))
    
(defun parse-mycnf-hosts (file-path)
  "Returns list of hosts with clients' section applied to all hosts."
  (let ((hosts nil)
        (global nil)
        (fields '("user" "host" "database" "password" "port"))
        (section-parse (lambda(section)
                         (if (equal (car section) "client")
                             (setq global (filter-alist fields (cdr section)))
                           (let ((host (car section))
                                 (config (filter-alist fields (cdr section))))
                             (when config (add-to-list 'hosts (cons host config) t))))))
        (merge-host-with-global (lambda (host)
                                  (cons (car host) (merge-alist global (cdr host))))))
    (mapc section-parse (read-ini file-path))
    (mapcar merge-host-with-global hosts)))

(defun mycnf-to-sql-connection (config)
  (let ((add-sql-product
     (lambda (config)
       (let ((head (car config))
             (tail (cdr config)))
         (cons head (append tail (list (list 'sql-product ''mysql)))))))
    (parse-keys-and-values
     (lambda (config)
       (let ((head (car config))
             (tail (cdr config)))
         (cons
          head
          (mapcar
           (lambda (element)
            (let ((key (car element))
                  (value (cdr element)))
              (cond ((equal key "host") (list 'sql-server value))
                    ((equal key "port") (list 'sql-port (string-to-number value)))
                    ((equal key "user") (list 'sql-user value))
                    ((equal key "password") (list 'sql-password value))
                    ((equal key "database") (list 'sql-database value))
                    (t (error (format "Unknown key %s" key))))))
           tail))))))
  (mapcar add-sql-product (mapcar parse-keys-and-values config))))

;;; Actually populating sql-connection-alist
(setq sql-connection-alist
      (append
       (mycnf-to-sql-connection (parse-mycnf-hosts "~/.my.cnf"))
       (pgpass-to-sql-connection (read-file "~/.pgpass"))
       ))

With the following .my.cnf:

[client]
user=me

[host1]
database=db1
host=db.host1.com

[host2]
database=db2
user=notme
host=db.host2.com

Executing the expression (mycnf-to-sql-connection (parse-mycnf-hosts "~/.my.cnf")) gives me (pretty printed by hand):

(("host2" ((sql-server "db.host2.com")
           (sql-user "notme")
           (sql-database "db2")))
 ("host1" ((sql-server "db.host1.com")
           (sql-database "db1")
           (sql-user "me"))))

Finally, instead of using M-x sql-mysql, use M-x sql-connect and you'll be able to connect using the alias, with auto-completion.

Answer 3

To do this we need to ""trick"" sql-mode into running mysql --login-path=some-connection-name some-db-name (--login-path being the parameter that sql-mode does not pass by default.)

Create your named connections on the sql side via (by default are stored in .mylogin.cnf):

mysql_config_editor set --login-path=wow --host=127.0.0.1 --port=3306 --user=root --password

Then label the connections on the emacs side as so (in your init.el):

(setq sql-connection-alist
  '((wow-local
     (sql-product 'mysql)
     (sql-mysql-options '("--login-path=wow")) ; Note: use the login-path specified earlier
     (sql-server "") ; Note: All of these empty string parameters prevent being prompted for these values and are ignored.
     (sql-user "")
     (sql-password "")
     (sql-database "wowza"))
    (wow-local-test
     (sql-product 'mysql)
     (sql-mysql-options '("--login-path=wow")) ; Note: You can have multiple connections using the same login-path just with different parameters
     (sql-server "")
     (sql-user "")
     (sql-password "")
     (sql-database "wowza-test"))
    (production
     (sql-product 'mysql)
     (sql-mysql-options '("--login-path=production"))
     (sql-server "")
     (sql-port 0) ; Note: 0 ignores, anything else overrides your .cnf
     (sql-user "")
     (sql-password "")
     (sql-database "wowza_prod"))))

Finally with our sql connections created, and our connection labeled on the emacs side, we can run M-x sql-connect which should then prompt you to select one of your lovely connections.

Note:

• setting the emacs variable sql-mysql-options allows you to use your connections as defined in the .mylogin.cnf file (YMMV on other .cnf files) (you may also use this to pass in whatever mysql parameters you'd like to)
• setting sql-server,sql-user, etc... to empty string prevents a prompt for said value
• setting sql-server, sql-user, etc... to anything other than empty string will override the values set in the .cnf
• See M-x (describe-variable sql-mysql-options), and M-x (describe-function sql-comint-mysql) in the sql.el file for further details regarding logic around ignored parameters.

TLDR: use sql-mysql-options and --login-path=<your-login-path> magic

Answer 4

Just hit return, it's picked up by default.