Exporting Sqlite Query Results to CSV in Emacs

For ad hoc SQL analysis against Postgres, I reach for Azure Data Studio. It packs all the features I typically need into a slick VS Code interface. Occasionally, I need to crunch some joins on data available in CSV files. For that, I use sqlite and Emacs. Rapidly sending queries to a SQL interactive session does the trick most days. Still, the prompt-based interface doesn't lend itself to easily export results for sharing. I know I can direct sqlite3 output to a file, but for quick iterations I'd rather have query results pop up in an editor buffer immediately.

Now, with sqlite3 compiled into Emacs 29, we can do just that (to check if your build has it, run M-: (sqlite-available-p)). The built in sqlite-mode provides an elisp interface to the sqlite3 API, as well as a limited database browser. The pieces are there to interact with sqlite in any way using elisp.

I wrote a library using these pieces to directly query sqlite and display results in a buffer formatted as CSV.

Sample Setup includes a quick reference to set up sample data to play with.

1. Usage

To use the library, download sqlite-query.el and save it on your load path.

Add this to your .emacs to load the extension:

(require 'sqlite-query)

Open a sqlite interactive session with M-x sql-sqlite.

Note: This also works with databases opened by M-x sqlite-mode-open-file. Database selection is implicit, with preference given to the last visited sqlite-mode buffer.

Put your cursor in a SQL query and run M-x sqlite-query-paragraph-to-csv. There are additional functions to query a string or the region.

select * from blooms limit 10;

The results will pop up in a temp buffer formatted as CSV. Throw the buffer away or run a quick C-x C-s to save it.

sqlite3 test.db

create table blooms (
    plant text,
    area text,
    ts timestamptz
);

insert into blooms values
    ('dogwood', 'deck', '2023-04-10'),
    ('tulip', 'front', '2023-04-19'),
    ('oak', 'side', '2023-04-20'),
    ('hemlock', 'side', '2022-04-15');