Shell

sqly with no --sql opens a REPL. Type SQL, or a helper command beginning with a dot.

$ sqly testdata/user.csv
sqly v0.29.0

enter "SQL query" or "sqly command that begins with a dot".
.help print usage, .exit exit sqly.

sqly:~/sqly(table)$ SELECT * FROM user LIMIT 1;

The prompt shows the working directory and the current output mode. Tab completes SQL keywords, table names, column names, and paths, and after a dot-command it completes that command’s arguments: .dialect m reaches mysql, .mode m reaches markdown. History persists across sessions, and records a line even when sqly rejected it, so the up-arrow brings it back to be fixed. It records the line as typed, so a URL carrying credentials is stored as typed too — SQLY_HISTORY_PATH says where, and the file is a plain text one with an entry per line, readable and editable with anything (a statement typed across several lines is stored on one, with its newlines escaped). What sqly prints back is redacted: a password in a URL never reaches a message, a refusal, or an --inspect report.

Multi-line statements #

A statement is buffered until a ; ends it, so a pasted or typed multi-line query runs as one statement. The prompt becomes ...> while it is buffering:

sqly:~/sqly(table)$ SELECT user_name,
   ...> identifier FROM user;

A ; ends a statement where SQLite ends one, and nowhere else. Inside a string literal, a quoted identifier, or a comment it is ordinary text; inside a trigger body it separates the body’s own statements without ending the CREATE TRIGGER that contains them. In each case the buffer keeps collecting:

sqly:~/sqly(table)$ CREATE TRIGGER audit_trg AFTER INSERT ON audit BEGIN
   ...>   INSERT INTO log VALUES ('written');
   ...> END;
statement executed successfully

A complete comment after the terminator does not delay anything: SELECT 1; -- note runs on that Enter. An unclosed /* still buffers, because the statement after it has not been read yet.

One line may hold several statements. Each runs in order and each result is printed, which is what pasting a snippet usually needs:

sqly:~/sqly(table)$ CREATE TABLE t (a); INSERT INTO t VALUES (1); SELECT a FROM t;

Dot-commands are single-line and run on Enter. To run a query without typing ;, press Enter on a blank line.

Keys #

Ctrl-C reaches a statement that is already running, so a query that turns out to be slower than expected can be stopped:

sqly:~/data(table)$ SELECT COUNT(*) FROM huge JOIN huge;
^C
canceled: SELECT COUNT(*) FROM huge JOIN huge;
sqly:~/data(table)$

A canceled statement changes nothing: its transaction rolls back, and the session carries on. Canceling is not a failure, so a session that ends normally afterward still exits 0.

Dot-commands #

Session #

.mode, .dialect, and .row-mismatch are the session settings. Called with no argument each reports and succeeds:

sqly:~/data(table)$ .mode
current output mode: table (available: table, vertical, csv, tsv, ltsv, json, jsonl, markdown)

Two of them used to fail instead, so a script that meant .mode csv would not continue in the wrong mode. A value the setting does not accept is still rejected, so that typo is still caught.

Every one of those lines goes to stderr, so a script that names its dialect still pipes into jq.

.mode names what the screen shows, so excel and parquet are not among its values: neither can be rendered to a terminal. Write one with a destination instead. In a display mode (table, vertical) the extension names the format, so .dump TABLE out.xlsx writes a workbook; in a mode that names a format, an extension that disagrees is still a conflict and is rejected. From the command line it is --output-format excel --output FILE.

Navigate #

Inspect #

Import and export #

.import reads a workbook the way the session was started: one table per sheet the workbook shows, unless sqly was launched with --include-hidden-sheets. The sheet policy is a session setting rather than a per-import one, so it does not change halfway through a session; see Excel sheets.

Batch mode #

Without a TTY, the same commands are read from stdin as a script. This is how you script sqly:

printf '.mode csv\nSELECT user_name FROM user;\n' | sqly user.csv

sqly user.csv <<'EOF'
.import extra.csv
SELECT COUNT(*) FROM extra;
.save ./out
EOF

A failing statement stops the script and exits non-zero, naming the statement and its line. --sql carries one statement, so its failure names neither: there is no line to return to and no run to stop.

A helper command must start its own line; SELECT 1; .save ./out is rejected, because reading it as two things depends on knowing where the statement ended, which is what the line does not show. Leading whitespace is fine, so a script can indent. A . inside a string, a -- comment, or a /* */ block is part of the statement, not a command.

A helper command runs here and in a --script-file, which is the same script read from a file instead of a pipe:

sqly --script-file monthly.sqly user.csv

--sql-file holds SQL, and a dot-command in one is rejected by name and line — a .sql file that runs .save is a shell script wearing a SQL extension. The flag that permits side effects says script in its name. See the reference for what each one accepts.

Write-back from the shell #

sqly:~/data(table)$ UPDATE user SET first_name = 'Rachelle' WHERE identifier = 1;
affected is 1 row(s)
sqly:~/data(table)$ .save ./out
Saved user to out/user.csv

.save DIR never touches the sources. .save --in-place overwrites them. A session that changed no row writes no file and says so.

Write-back lives here rather than on the command line: it is the one thing sqly does that cannot be undone, and .save puts it after the statements that changed something, where you can look first. The same commands work in a piped script, so a batch job writes back the same way a person does. The reference has the full contract — which formats can be written, what happens when several files are saved at once, and what is guaranteed if one of them fails.