SQLiteSweep

Query millions of SQLite databases across remote hosts via SSH, aggregating results in real-time. Designed for multi-tenant apps where each tenant has their own SQLite database.

Installation

gem install sqlitesweep

Or add to your Gemfile:

gem "sqlitesweep"

Usage

sqlitesweep \
  -q 'SELECT count(*) FROM products WHERE price > 0' \
  -a sum \
  -s 'rails runner "Account.active.find_each { |a| puts a.db_uri }"' \
  -c 16

The -s (source) command should output one database URI per line. URIs can be:

Local paths: /data/tenants/acme.sqlite3
File URIs: file:///data/tenants/acme.sqlite3
SSH URIs: ssh://[email protected]/data/tenants/acme.sqlite3

Results go to stdout, progress goes to stderr — so it's pipe-friendly:

sqlitesweep -q "SELECT count(*) FROM users" -a sum -s "cat db_uris.txt" > result.txt

Actions

Action	Description
`sum`	Sum the first column across all databases (default)
`average` / `avg`	Average the first column across all databases
`list`	Write all rows to a JSONL file, print the file path

Options

Flag	Long	Default	Description
`-q`	`--query`	(required)	SQL query to execute on each database
`-a`	`--action`	`sum`	`sum`, `average`/`avg`, or `list`
`-s`	`--source`	(required)	Shell command that outputs database URIs
`-c`	`--concurrency`	`8`	Max parallel query workers
	`--max-ssh`	`50`	Max SSH master connections
	`--no-live`	`false`	Disable live progress display
	`--batch-size`	`4`	Databases to query per SSH call
	`--ssh-timeout`	`10`	SSH connect timeout (seconds)
	`--query-timeout`	`30`	Per-query timeout (seconds)

How it works

Local databases are queried directly via the sqlite3 gem
Remote databases are queried by shelling out to ssh + sqlite3 on the remote host
SSH connections use ControlMaster multiplexing — one master connection per host, shared across queries
Multiple databases on the same host are batched into a single SSH call (configurable via --batch-size)
A thread pool (via concurrent-ruby) runs queries in parallel with back-pressure

Requirements

Ruby >= 4.0
sqlite3 available on remote hosts (for SSH queries)
SSH agent or key-based auth configured (BatchMode is enforced — no password prompts)

Development

bundle install
bundle exec rake test

Run the integration test:

bundle exec ruby test/integration/test_local_sweep.rb

Watch the live progress display with a slow-drip demo:

ruby test/integration/harness/live_demo.rb
ruby test/integration/harness/live_demo.rb --count 50 --delay 0.5
ruby test/integration/harness/live_demo.rb --action list

Run the benchmark:

ruby test/integration/harness/sweep_bench.rb --db-count 1000

Docker-based SSH testing

docker compose -f test/integration/harness/docker-compose.yml up -d
ruby test/integration/harness/sweep_bench.rb --docker --db-count 500

License

MIT