How do I open an sqlite database from elisp?

Question

I have a filepath for a sqlite database. I want to open an sql client buffer. I would like to know the best way to open that database from elisp. So far I can see that sql-sqlite will open it but it requires an interaction.

I'm calling (sql-sqlite "/path/to/some/database") and it then prompts me in the minibuffer to confirm the path before opening the database. I can see from the docs that this is an interactive function. Is there a non-interactive way to do this?

You *are* calling it non-interactively, if you're evaluating an elisp form `(sql-sqlite "/path/to/some/database")`. Being an `interactive` function just means its *also* a command which can be called interactively (E.g. via `M-x` or a key binding). — phils, Jun 06 '20 at 07:10
As `C-h f` shows you, the signature for `sql-sqlite` is `(sql-sqlite &optional BUFFER)`. I very strongly suspect that `"/path/to/some/database"` is not a buffer name. — phils, Jun 06 '20 at 07:11
Yes, I'm convinced you are right. Is there another function that I should call instead? — gburnett, Jun 06 '20 at 07:13

db48x · Answer 1 · 2020-06-06T08:42:52.487

It depends on what you're trying to implement. sql-sqlite is a function that you would use if you want to interactively send commands to a sqlite process. This is what you want if you're writing some code that uses SQL and you want a REPL where you can test your queries.

On the other hand if you're writing an emacs mode that stores data in a database then this isn't what you want. You want the emacsql package, or possibly closql. emacsql lets you execute arbitrary queries using an s-expression syntax. Some examples from the documentation:

(emacsql db [:create-table people ([name (id integer :unique) salary])])
(emacsql db [:insert :into people
             :values (["Jeff"  1000 60000.0] ["Susan" 1001 64000.0])])

closql is a higher-level ORM that stores class instances in database tables for you.

Since you didn't say what you're actually doing, we can only guess which is more appropriate.

Edit:

Ah, since you do want a REPL, sql-sqlite is what you want. You may already know that you can tell it which database to open by setting the sql-database variable before calling it (or by adding a dynamic binding):

(let ((sql-database "~/temp/test.sql"))
  (sql-sqlite))

However, I've just checked and it still prompts for the database file name. It uses the value you set as the default value for the prompt, which will save you time, but a quick look through the code shows that there's no easy way to disable the prompt. If it were a different kind of database it would also prompt you for other details such as username and password. You could add advice to the function sql-get-login-ext so that it skips the prompt if there already is a value, but that might not be a good idea.

I would like to interactively send commands to a sqlite process. I have amended my question slightly. — gburnett, Jun 06 '20 at 08:00

How do I open an sqlite database from elisp?

1 Answers1