How can I execute a SQL query in emacs lisp?

Question

I want to execute an SQL query and get its result in elisp:

(let ((results (do-sql-query "SELECT * FROM a_table")))
     (do-something-with results))

I'm using Postgres, and I already know all of my connection information (host, username, password, db et al) I just want to execute the query and get the result back, synchronously.

Answer 1

I haven't tried it, but pg.el looks promising.

Answer 2

I ran into this recently in relation to SQLite.. Haven't used it yet, but it looks like an approach.

Answer 3

I ended up calling out to psql using this form:

(shell-command-to-string
   (concat sql-postgres-program
                 " -h DBHOST -t"
                 " -c \"SELECT db FROM defines.databases WHERE role = '" tier "' AND db ~ '^" string "';\""
                 " -U USERNAME DBNAME"))

It seems to have worked all right. I have to chomp it (using the function Paul linked to) and split the results, but I get what I need out of it.

Answer 4

sql.el is built in to emacs and has two modes; you should be able to work with that to send mysql commands from elisp but it doesn't seem to make it obvious how you do it.

I'm not sure this works entirely from elisp without user interaction, or being in sql-mode. It does have functions to send a string to execute.

This link looks interesting; it piggy backs off mysql mode and makes the mode more like how you want it to work.