The CLI lets you use multiple lines to enter your SQL - which is good.

Is there a trick to edit and re-send the last-run SQL command? Ideally - via an external editor.

For example: here's an interactive session where I am adding in a three-line SQL command; and after running it - I press arrow-up on my keyboard. This brings back the last line : and you can arrow-up to each line in turn and re-enter them in order - but it is not the most user friendly way.

SQLite version 3.39.0 2022-03-10 23:37:58
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> WITH my_table(x,y,z) AS
   ...> (VALUES(1,2,3))
   ...> SELECT * FROM my_table;
x|y|z
1|2|3
sqlite> SELECT * FROM my_table; -- cursor-up brings back last line (correct, but anyway to edit full SQL?)

Aside: editing values

I note there is a useful function in the CLI called edit() which lets you edit values in your text editor (here I am on Linux, using vi).

with a(my_text) AS (VALUES(123)) SELECT edit(my_text,'vi') FROM a;

Which then opens the editor up with a temporary file - pre-populated with '123' in this case; I then manually added some text in the editor and saved; which then returned the value back to the CLI:

123 - I edited this in vi

(Silly example of course; the use-case is more appropriate for an UPDATE etc - but just to show the mechanism).

Why did I bring this feature up?

Just thinking that if the last-run-SQL was held in some system table somewhere, we might be able to then at least to use the 'edit' function to dump the SQL to a file, and then use '.read' on it back in the editor. (Or even just .once the output to a file - the important thing would be to get a handle on the buffer somehow).

I should looked harder; literally after posting this I saw the '.trace' option; which should do it - with a bit of manual copy/pasting / screen-scraping needed:

sqlite> .trace stdout
sqlite> with a(x,y,z) AS -- typed line 1
   ...> (values(1,2,3))  -- typed line 2
   ...> select * from a; -- typed line 3
with a(x,y,z) AS -- trace output
(values(1,2,3))  -- trace output
select * from a; -- trace output
x|y|z
1|2|3
sqlite> with a(x,y,z) AS -- manually copied and pasted from the trace output.
   ...> (values(1,2,3))
   ...> select * from a;
with a(x,y,z) AS
(values(1,2,3))
select * from a;
x|y|z
1|2|3

(-- comments are mine for annotation, the output doesn't show these)

Is there a trick to edit and re-send the last-run ...

Not at present. However, a similar capability is on my short list of feature enhancements for the CLI. I will ponder keeping some line-group history available for edit and reuse.

Sounds good! Thanks!

This would be great. I find myself in the same boat as the original poster. Thanks.

Somebody is tweeting about this: https://twitter.com/ohmypy/status/1516429995345915904

Elon? ;-)

Actually - there is some interesting stuff in that Twitter feed. Like for instance - I didn't know somebody had made a Javascript version of SQLite !

https://github.com/sql-js/sql.js/

Yup - this works pretty well: just need to train my muscle-memory a bit. (I do this already actually when building up a new schema - but when I'm in 'explore-mode' - its always when I have just written some multi-line monstrosity that I realise that I needed to have kept it somewhere ;-) )

When working on a complex SQL expression, my method is slightly different: Edit the command in an external editor – a graphical one, not inside the terminal – and when I want to try it out, select all (a single keystroke in many editors), copy, move to the terminal running sqlite, and paste.

The described method does not work for me on Windows 11.

Your method works fine for me although I've been using

.shell notepad

to avoid the notepad prompt to create a file when starting afresh and I just save the file using notepad and before

.read myFile.sql or I simply copy (from notepad) and paste into the SQLite CLI

When resuming work-in-progress, I use

.shell notepad myFile.sql

No doubt you meant Edit, Save, Run, Repeat - else the changes won't be reflected in the Run i.e. the .read!

A neat trick:

.once -e
select  '-- Script edited ' || date('now') as [];

This opens the default text editor and adds a timestamp comment; the column name is blank, the column name underlining passes for a comment and the reminder/verbose comment is timetamped.

The described method does not work for me on Windows 11.

Oh. Well, it’s a unix trick, but I have been under the impression that windows is quite unix like these days, so I expected it to work there as well. My mistake, sorry.

The described method does not work for me on Windows 11.

Correct, the behavior is using verbatim insert (Ctrl-V) from most Unix terminals. The Windows terminal has no counterpart to that shortcut.

Note that on Windowz you should use .shell start <editor> if you want the editor to run as an unsupervised sub-process. By default .shell invokes a supervised sub-process (the CLI does not regain control until the sub-process exits).

.once -e sends the output to the <editor> as an unsupervised process.

.shell is an alias for .system

Instead of hitting return between lines of your multiline command, you could type C-v C-j instead (that is, Control-V followed by Control-J). Now you have a single multilne input that will be stored as such in history, though only in the present session. When stored in the history file and recalled in a future session, it will be multiple lines once more.

Thanks for that tip. If only I'd learned that years ago!

Interesting sorcery!

Just tried it - works ! Thanks! Although it is slightly disconcerting not seeing the continuation-prompt ;-)

(I did a quick google: this is readline stuff I guess?)

Control-V means add the next character to the input buffer verbatim, without interpretation. Control-J is '\n', which the SQLite parser treats as white space.

Ah - thanks for that. (Also: now I remember using CTRL-V for generating those ANSI codes to change colours in the shell - to get the ESCAPE char).

Yes, but its history goes back further. C-v (or ^V, as it was written once) really means enter the next character literally, even if it is a control character.

If you run stty -a in a terminal on a unix-like system (linux, mac), you will see listed, among the cchars: lnext = ^V;. That is the bit of the standard setup that makes ^V behave that way.

Thanks again !