Save to a SQLite database

Queries run in memory by default. When you want to keep data, sqx can import files into a SQLite database file, save query results as tables, and run any write statement.

Import files into a database

sqx import -db analytics.db -t users users.csv
cat logs.jsonl | sqx import -db analytics.db -t logs
sqx import -db analytics.db -t countries https://example.com/countries.csv

import appends to an existing table and adds any new columns automatically.
-replace drops the table first: sqx import -db analytics.db -t users -replace users.csv.
Without -db, it writes to sqx.db in the current folder.

Check what you have:

sqx tables -db analytics.db
sqx schema -db analytics.db

Query the database

sqx -db analytics.db "SELECT country, COUNT(*) FROM users GROUP BY country"

The file is a normal SQLite database, so you can also open it with the sqlite3 shell, DB Browser for SQLite, Datasette or any programming language.

Save a query result

-save <table> writes the result of a query into the database. Running it again replaces the table, so a report stays up to date:

sqx -db analytics.db -save big_spenders "
  SELECT u.name, SUM(e.amount) AS total
  FROM users u JOIN events e ON e.user_id = u.id
  GROUP BY u.id HAVING total > 40"

Tables from stdin and -f are never written into -db. Repeating a command never duplicates rows.

Create, insert and update

Statements that don't return rows (CREATE, INSERT, UPDATE, DELETE, …) run as writes. Separate several with ;. INSERT … RETURNING prints the returned rows.

sqx -db shop.db "CREATE TABLE stores (id INTEGER PRIMARY KEY, city TEXT, region TEXT)"
sqx -db shop.db "INSERT INTO stores (city) VALUES ('BLR'), ('DEL'), ('MUM')"
sqx -db shop.db "INSERT INTO stores (city) VALUES ('HYD') RETURNING id, city"

Update a saved table from a file:

sqx -db shop.db -f orders=data/headerless.csv -no-header "
  UPDATE stores SET region = (SELECT c1 FROM orders WHERE orders.c2 = stores.city LIMIT 1)"

Upsert from a file

sqx -db shop.db "CREATE UNIQUE INDEX IF NOT EXISTS idx_users_id ON users(id)"
sqx -db shop.db -f incoming=data/users.csv "
  INSERT INTO main.users SELECT * FROM incoming WHERE true
  ON CONFLICT(id) DO UPDATE SET plan = excluded.plan, email = excluded.email"

SQLite requires the WHERE true when an INSERT … SELECT has an ON CONFLICT clause. A table loaded with -f or from stdin hides a saved table of the same name, so write main.<name> for the saved one when names collide.