The SQL shell

pondra opens a SQL shell on a lake, the way duckdb or psql open one on a database. Use it to try SQL, load a file, or run a script, with nothing to start first.

Open a shell

pondra                        # the lake in ./lake (made if it isn't there)
pondra my-lake                # another folder
pondra s3://my-bucket/lake    # a lake in a bucket

This is a session on a terminal. Type a statement, end it with ;, and read the answer:

$ pondra shop
Pondra 0.29.0 on shop, also at http://127.0.0.1:37957. Databases: crm, shop. End each statement with ;  .tables and .databases list them, .quit leaves.
The console, as this shell: http://127.0.0.1:37957/#key=6f0c…
pondra> SELECT item, sum(qty) AS items FROM orders GROUP BY item ORDER BY item;
+------+-------+
| item | items |
+------+-------+
| cake | 1     |
| tea  | 3     |
+------+-------+
(0.008 s)
pondra> SELECT 1 +
   ...> 1 AS two;
+-----+
| two |
+-----+
| 2   |
+-----+
(0.004 s)
pondra> .quit

• A statement ends at a ; that is outside a string, a quoted name and a comment. It can run over several lines.
• An error is printed and the session goes on.
• .tables lists the tables and views of every database with each one’s kind (table, view, materialized view, external table: pondra.tables), .databases lists the databases, and .quit leaves (so do .exit, \q and Ctrl+D).

The shell starts a node on the lake, in the background, and sends each statement to it. So everything a node does keeps running while the shell is open: views update, tasks run, and other programs can use the lake. The startup line gives the node’s address (http://127.0.0.1:37957 above), and pondra.connect("http://127.0.0.1:37957") reaches it. The node stops when the shell does.

Run a script

Piped or redirected input runs the same way, with no prompts and no timings, so it works in scripts:

pondra shop <<'SQL'
CREATE TABLE orders (id BIGINT, item VARCHAR, qty INT, amount DOUBLE);
INSERT INTO orders VALUES (1, 'tea', 2, 7.0), (2, 'cake', 1, 4.5), (3, 'tea', 1, 3.5);
SELECT item, sum(qty) AS items FROM orders GROUP BY item ORDER BY item
SQL

{"table":"orders","publish":[]}
{"rows":3}
+------+-------+
| item | items |
+------+-------+
| cake | 1     |
| tea  | 3     |
+------+-------+

Writes print a one-line outcome, queries print a table. The last statement runs even without its ;. A file works the same way: pondra shop < setup.sql. A statement that fails prints its error and the script goes on with the next one, then the shell exits with an error status, so a script or a CI job can tell:

printf 'SELECT nope FROM missing;\nSELECT 1 AS one' | pondra scratch || echo "exit status: $?"

Error: Error during planning: table 'scratch.public.missing' not found
+-----+
| one |
+-----+
| 1   |
+-----+
exit status: 1

Use the lakes beside yours as databases

Every folder next to your lake that holds a lake is a database in the shell, under its folder’s name. Tables are database.table, or database.schema.table, and you can join across them:

mkdir -p databases && cd databases
echo "CREATE TABLE customers (id BIGINT, name VARCHAR); INSERT INTO customers VALUES (1, 'ann'), (2, 'bob');" | pondra crm
pondra shop <<'SQL'
CREATE TABLE orders (id BIGINT, item VARCHAR);
INSERT INTO orders VALUES (1, 'tea'), (2, 'cake');
.databases
SELECT o.item, c.name FROM orders o JOIN crm.customers c ON c.id = o.id ORDER BY o.id
SQL

{"table":"customers","publish":[]}
{"rows":2}
{"table":"orders","publish":[]}
{"rows":2}
+----------+
| database |
+----------+
| crm      |
| shop     |
+----------+
+------+------+
| item | name |
+------+------+
| tea  | ann  |
| cake | bob  |
+------+------+

INSERT, UPDATE, DELETE and MERGE work on crm.customers too. CREATE DATABASE archive; makes a new lake in a folder beside this one, and it appears in .databases at once.

Only the shell does this, and only for the folder you started it in: a lake you open from somewhere else lists no neighbours, and nothing is written into the lake about them. Attaching a lake that lives elsewhere is in Tables.

Read files on your machine

The shell may read and write files on your own machine. Name a file where SQL takes a table:

printf 'day,item,amount\n2026-09-27,tea,7.0\n2026-09-28,cake,4.5\n' > sales.csv
printf 'day,item,amount\n2026-09-28,cake,5.0\n2026-09-29,pie,3.0\n' > new.csv
pondra books <<'SQL'
CREATE TABLE sales AS SELECT * FROM 'sales.csv';
MERGE INTO sales s USING 'new.csv' n ON s.day = n.day AND s.item = n.item
  WHEN MATCHED THEN UPDATE SET amount = n.amount
  WHEN NOT MATCHED THEN INSERT VALUES (n.day, n.item, n.amount);
SELECT * FROM sales ORDER BY day
SQL

{"rows":2}
{"rows":2,"updated":1,"deleted":0,"inserted":1}
+------------+------+--------+
| day        | item | amount |
+------------+------+--------+
| 2026-09-27 | tea  | 7.0    |
| 2026-09-28 | cake | 5.0    |
| 2026-09-29 | pie  | 3.0    |
+------------+------+--------+

Note

The node a shell starts is yours alone, and it lets only this shell read your disk, by a key that only the shell knows. A node you start with pondra serve refuses SQL that reads or writes its own disk, whoever asks. Files and other lakes has the rest.

Run a SQL file with parameters

pondra run runs a .sql file: its statements in order, on a node it starts for the run. $name in the file takes the value of --name:

cat > report.sql <<'SQL'
CREATE TABLE IF NOT EXISTS sales (day DATE, item VARCHAR, amount DOUBLE);
INSERT INTO sales VALUES (DATE '2026-09-27', 'tea', 7.0), (DATE '2026-09-28', 'cake', 4.5);
SELECT item, amount FROM sales WHERE day = $day AND amount >= $min;
SQL
pondra run report.sql daily --day 2026-09-27 --min 5

+------+--------+
| item | amount |
+------+--------+
| tea  | 7.0    |
+------+--------+

• The second argument is the lake (./lake if you leave it out).
• Numbers and true / false are passed as such; everything else is text. --day=2026-09-27 works too, and a dash in a name becomes an underscore (--min-amount fills $min_amount).
• The node binds each value; it is never pasted into the SQL text.
• The last statement’s answer is printed. A failing statement stops the run with its error, and the command exits with an error status.

To run the file on a node that is already running, give --url instead of the lake. It and the parameters may come in any order:

cat > today.sql <<'SQL'
SELECT item, amount FROM sales WHERE day = $day
SQL
pondra run today.sql --url http://localhost:8080 --day 2026-09-28

A lake and --url together are refused: a script runs on one or the other.

+------+--------+
| item | amount |
+------+--------+
| cake | 4.5    |
+------+--------+

If the node has tokens, pass one with --token, or set PONDRA_TOKEN.

Caution

The shell has no line editing of its own: on a terminal you get your terminal’s, and there is no history between sessions. For longer SQL, write a file and run it with pondra shop < file.sql or pondra run.