Use Example: Working With a Database File in the kvsql Shell

Sometimes you just want to look inside a database. Maybe a test left a file behind, or you’re checking what a service wrote, or you want to try a query before putting it in code. The kvsql shell opens a file directly, with no server running, and takes SQL and key-value commands side by side.

kvsql --file jobs.kvsql

Here is a short session, as the project’s use example program ran it through the shell’s own command code, one command at a time the way -c takes them. The output is exactly what came back:

kvsql> CREATE TABLE jobs (id INTEGER PRIMARY KEY, kind TEXT, state TEXT)
0 rows changed
kvsql> INSERT INTO jobs VALUES (1, 'resize', 'done'), (2, 'resize', 'queued'), (3, 'email', 'queued')
3 rows changed
kvsql> SELECT state, count(*) AS n FROM jobs GROUP BY state
state  | n
-------+--
done   | 1
queued | 2
(2 rows)
kvsql> .row jobs 2
2 | resize | queued
kvsql> .put notes deploy shipped 0.1.0
ok
kvsql> .get notes deploy
shipped 0.1.0
kvsql> .verify
ok: commit 3, 9 pages (2 free), 3 keyspaces, 5 keys

.row reads a table row by its primary key without SQL. .put and .get work on plain keyspaces, and .verify checks every page, row and index in the file. At the interactive prompt, end each SQL statement with ;. It may span several lines, and runs when a line ends with the semicolon.

The shell also scripts well. -c runs one command and may be repeated, a script can come in on standard input, and the exit code is 1 if any command failed:

kvsql --file jobs.kvsql -c "SELECT count(*) FROM jobs WHERE state = 'queued'"
kvsql --file jobs.kvsql < cleanup.sql

Point it at a running server with --url instead of --file, and the same commands go over HTTP. A file that a server has open is locked, so while kvsqld runs, talk to it through the server.