sqly

Summary #

47 suites · 397 scenarios

Contents #

• sqly ACH/Fedwire native write-back — 4 scenarios
  • round-trips an ACH file through –save –force after an UPDATE
  • writes a reconstructed ACH file into a directory with –save-dir
  • round-trips a Fedwire file through –save –force after an UPDATE
  • still rejects a single-table –output to .ach
• sqly batch mode (piped stdin) — 16 scenarios
  • runs SQL read from stdin
  • switches output mode and runs the following query
  • exits non-zero and names the failing statement and its line on error
  • reports the line span of a failing multiline statement
  • stops at .exit with a success status
  • still exits non-zero when a failure precedes .exit
  • runs a multiline SELECT terminated by a semicolon
  • runs a multiline UNION ALL across bare newlines as one statement
  • runs a multiline WITH (CTE) query
  • runs multiple statements and helper commands in one payload
  • ignores a semicolon inside a leading line comment
  • ignores a semicolon inside a block comment
  • ignores a semicolon inside a trailing line comment
  • does not split on a semicolon inside a bracket-quoted identifier
  • does not split on a semicolon inside a backtick-quoted identifier
  • reports an error for incomplete SQL
• sqly batch fail-fast — 4 scenarios
  • stops later statements after a SQL failure
  • does not run .save –force after an earlier failure
  • does not run .dump after an earlier failure
  • does not write back for empty stdin with –save –force
• sqly –cache import cache — 8 scenarios
  • writes a cache on the cold run and reuses it on the warm run
  • invalidates the cache when the source changes
  • rebuilds after –cache-clear
  • falls back to a cold import when the cache path is unwritable
  • invalidates the cache when content changes but size and mtime do not (#592)
  • reuses the cache that lives inside the imported directory and ignores its manifest
  • keeps the cache warm when only an unsupported sibling file changes
  • still invalidates the cache when the supported file changes
• sqly changes delta cross-checks (exhaustive-set semantics) — 2 scenarios
  • omitting sqly’s history DB from an exhaustive list is rejected
  • the exhaustive list including the history DB passes
• sqly CLI surface — 10 scenarios
  • prints the version
  • prints usage with –help
  • fails on a non-existent file
  • fails on invalid SQL with –sql
  • reports an unknown flag as a CLI error
  • reports conflicting output mode flags as a CLI error
  • joins two imported files
  • writes JSON results to the given path with –output
  • applies an output-mode flag placed after the file path
  • fails fast on an unknown flag after the file path
• sqly table-name collision — 1 scenario
  • fails when two inputs sanitize to the same table name
• sqly –compare workflow — 10 scenarios
  • reports schema, row count, and keyed rows as JSON
  • emits a human-readable summary with –compare-format text
  • resolves an uppercase –compare-key against a lowercase column
  • rejects a missing key column
  • rejects a duplicate key value as ambiguous
  • rejects a genuinely missing –compare-tables name
  • rejects an ambiguous single-table compare
  • follows CLI input order for left and right, not table-name sorting
  • reverses left and right when the inputs are swapped
  • keeps the keyed diff correct on a larger input
• sqly cross-format workflows — 1 scenario
  • joins a Parquet table to a CSV table and computes a column
• sqly empty command arguments — 3 scenarios
  • rejects .save "" and leaves the source unchanged
  • rejects .dump with an empty destination
  • rejects .import with an empty path
• sqly excel export — 2 scenarios
  • writes a non-executable .xlsx with –output
  • writes a non-executable .xlsx with the .dump command
• sqly export format inference — 11 scenarios
  • infers parquet from the destination extension without a flag
  • infers ndjson with gzip and writes a compressed file
  • re-imports a gzip-compressed csv it wrote
  • writes the CSV fallback to an unknown extension path without rewriting it
  • rejects –output to an existing directory
  • rejects .dump to an existing directory
  • errors when an explicit mode flag disagrees with the path extension
  • rejects bzip2 output
  • rejects compression on parquet
  • infers tsv from the .dump destination path
  • keeps –json –output result.json writing json
• sqly helper commands reject extra args — 7 scenarios
  • rejects .schema with an extra argument
  • rejects .describe with an extra argument
  • rejects .tables with an extra argument
  • rejects .mode with an extra argument
  • rejects .pwd with an extra argument
  • rejects .clear with an extra argument
  • does not let .exit with an extra argument silently terminate the batch
• sqly filesql integration — 8 scenarios
  • imports and queries a CSV file
  • imports and queries a JSONL file
  • imports and queries a Parquet file
  • imports and queries an Excel file
  • imports and queries an ACH file
  • imports and queries a Fedwire file
  • preserves filesql-detected column types for schema inspection
  • produces identical tables on repeated ACH imports
• sqly shell helper commands — 9 scenarios
  • .help groups commands, shows usage, and flags destructive save
  • .cd changes directory with a relative path and reports it
  • expands a bare ~ in .cd to the home directory
  • expands ~/file in .import
  • .clear emits no ANSI escapes to stdout in batch mode
  • keeps –json stdout parseable when .clear precedes a query
  • .import imports a quoted path containing a space as one argument
  • .mode fails on a missing mode name but still lists the modes
  • .dump infers TSV from the .tsv extension in table mode, not CSV
• sqly hermetic environment — 3 scenarios
  • runs with HOME inside the sandbox
  • routes the history DB into the sandbox
  • still runs sqly normally inside the sandbox
• sqly history tolerance — 5 scenarios
  • runs –sql and warns instead of failing
  • runs batch mode without a writable history DB
  • runs –sql when the history DB is read-only after startup
  • runs –inspect when the history DB is read-only after startup
  • runs batch mode and warns when a history write fails after startup
• sqly import failure handling — 5 scenarios
  • fails query mode on a partial import and keeps stdout clean
  • names the failed count and first path when every input fails
  • fails –inspect on a partial import
  • fails batch .import on a partial import and stops later commands
  • keeps stdout clean when a stdin dataset fails to import
• sqly .import with space-containing paths — 5 scenarios
  • imports a backslash-escaped space path as a single argument
  • imports a double-quoted space path as a single argument
  • imports a single-quoted space path as a single argument
  • imports a file inside a space-containing directory when escaped
  • splits an unquoted space path into two failing arguments
• sqly –inspect — 7 scenarios
  • prints a JSON report for a single file
  • maps every table from a multi-table ACH file to its source
  • keeps stdout as pure JSON for a directory and stays quiet on stderr
  • fails with a clear error when no input is given
  • emits a schema-only report with –inspect-sample 0
  • limits sample rows with –inspect-sample
  • rejects a negative –inspect-sample
• sqly –inspect conflicts — 3 scenarios
  • rejects –inspect with –sql
  • rejects –inspect with –output and writes no file
  • rejects –inspect with –save-dir and creates no save dir
• sqly JSON/NDJSON NULL handling — 2 scenarios
  • emits NULL as JSON null in –json
  • emits NULL as JSON null in –ndjson
• sqly metamorphic relations — 5 scenarios
  • COUNT(*) equals the number of selected rows
  • WHERE 1=1 returns all rows and WHERE 1=0 returns none
  • ORDER BY preserves the row multiset
  • csv and ndjson yield the same rows
  • CSV dump reimported yields identical data
• sqly .mode banner routing — 3 scenarios
  • keeps stdout pure JSON after .mode json
  • keeps stdout pure NDJSON after .mode ndjson
  • reports the typed mode by name and emits typed output after .mode json-typed
• sqly output formats — 5 scenarios
  • –json renders results as a JSON array
  • –json prints [] for an empty result
  • –ndjson renders one JSON object per line
  • –ndjson prints nothing for an empty result
  • –csv renders header and rows as CSV
• sqly –output requires –sql — 2 scenarios
  • rejects –output with no query
  • rejects –output for batch SQL from stdin
• sqly file-output status routing — 3 scenarios
  • keeps stdout empty for –output and reports on stderr
  • keeps stdout free of the .dump status line
  • keeps the .save confirmation off stdout
• sqly parquet export — 6 scenarios
  • writes a parquet file that re-imports with the same rows
  • appends the .parquet extension
  • writes query results to the given parquet path with –output
  • preserves leading-zero codes through a parquet round-trip
  • preserves SQL NULL through a parquet round-trip
  • reports a clear error when exporting an empty result
• sqly input path validation — 4 scenarios
  • imports a deeply nested path
  • imports a file whose name literally contains ..%2f
  • rejects a symlink alias that resolves to a blocked system path
  • imports a symlink alias that resolves to an ordinary user file
• sqly –profile workflow — 10 scenarios
  • reports per-column data quality as JSON
  • profiles a stdin dataset
  • profiles multiple tables in one run
  • emits a human-readable summary with –profile-format text
  • counts a blank string as a distinct value in JSON output
  • counts a blank string as a distinct value in text output
  • flags a padded null-like placeholder and its whitespace together
  • warns only about whitespace for a padded ordinary value
  • counts comma-formatted numerals as numeric, matching table-mode
  • right-aligns the same comma-formatted column in table-mode
• README examples — 29 scenarios
  • prints the full user table as an ASCII table
  • joins two files on a shared key
  • runs the analytics script (CTE + window + GROUP BY)
  • extracts JSON fields from a JSONL file
  • reads a gzipped CSV transparently
  • queries a Parquet file
  • joins a compressed CSV with a plain CSV
  • renders CSV with –csv
  • renders JSON with –json
  • renders NDJSON with –ndjson
  • renders a markdown table with –markdown
  • renders LTSV with –ltsv
  • writes CSV to the path given by –output
  • queries piped CSV through the default stdin table
  • joins piped stdin with a file argument
  • runs a helper command and a query from piped stdin
  • runs SQL from join.sql while stdin carries a dataset
  • prints a JSON inspect report with a stable source and column types
  • omits sample rows with –inspect-sample 0
  • prints the CREATE TABLE statement with .schema
  • prints column information with .describe
  • writes the updated table to –save-dir, leaving the source untouched
  • rejects –save without –force
  • rejects a schema-changing statement under –save-dir before writing anything
  • overwrites the source in place with –save –force
  • imports every supported file under a directory
  • loads ACH records into multiple tables
  • queries the ACH entries table
  • loads a Fedwire file into a single message table
• sqly interactive shell (pty) — 4 scenarios
  • run a query and read its rendered result table over a pty
  • the .tables dot-command renders the imported table over a pty
  • a computed aggregate round-trips through the pty shell
  • the prompt reflects a live .mode switch over a pty
• sqly sandbox_home + changes (history DB isolation) — 2 scenarios
  • sqly –sql writes exactly its history DB, only inside the sandbox home
  • a second sqly batch run leaves its sandbox home byte-identical
• sqly write-back — 8 scenarios
  • writes to –save-dir without modifying the source
  • refuses –save without –force
  • overwrites the source in place with –save –force
  • re-imports a file rewritten in place (round-trip)
  • preserves gzip compression on in-place save
  • saves via the .save command in batch mode
  • guides a non-interactive –save with no input files toward passing input
  • guides a batch .save with no imported tables toward passing input
• sqly schema inspection — 7 scenarios
  • .schema prints a CREATE TABLE statement for a CSV table
  • .schema emits a structured object in json mode
  • .schema returns the stored CREATE VIEW for a differently cased view name
  • .schema errors on a missing table
  • .describe lists columns and types for a CSV table
  • .describe emits structured column metadata in json mode
  • .describe errors on a missing table
• sqly –sheet validation — 8 scenarios
  • rejects –sheet with a non-Excel file and –sql
  • rejects –sheet with a non-Excel file and –inspect
  • still imports an Excel file with –sheet
  • rejects an explicit empty –sheet
  • rejects –sheet for a directory with no Excel files
  • tells the user how to recover when –sheet has no Excel input
  • names the workbook and suggests recovery on a single-workbook sheet miss
  • names every checked workbook on a multi-workbook sheet miss
• sqly — 3 scenarios
  • count rows in a CSV fixture
  • filter rows and select a column
  • markdown output format works
• sqly –sql-file — 7 scenarios
  • runs a multiline query loaded from a file against a file input
  • joins a piped –stdin dataset with a query loaded from a file
  • runs multiple statements from a file in order
  • rejects –sql and –sql-file together
  • fails for a missing SQL file
  • fails for an empty SQL file
  • locates a failing statement by its line in the SQL file
• sqly –sql-file –output — 4 scenarios
  • exports a single-SELECT script to the output file with clean stdout
  • exports a single result set even when the script first runs DDL/DML
  • rejects a script that produces no result set
  • rejects a script that produces multiple result sets
• sqly –stdin dataset — 11 scenarios
  • queries piped CSV through the default stdin table
  • queries piped TSV data
  • queries piped JSONL data stored in a data column
  • overrides the stdin table name with –stdin-name
  • joins piped stdin with an imported file argument
  • reports a stable stdin source in –inspect, not a temp path
  • rejects –save –force for a stdin-backed table
  • rejects a non-identifier –stdin-name so the name stays queryable
  • rejects a path-like –stdin-name
  • reports a clear error for an unsupported stdin format
  • still reads stdin as SQL and helper commands without –stdin
• sqly typed JSON output — 7 scenarios
  • emits native numbers, booleans, and null with –json-typed
  • keeps the legacy string contract with plain –json
  • emits native scalars per line with –ndjson-typed
  • keeps a large integer column lossless (no scientific notation)
  • leaves a leading-zero value as a string
  • uses the typed contract for –inspect sample rows
  • rejects plain –json combined with –inspect
• sqly v0.18.0 binary bug fixes — 22 scenarios
  • rejects an empty –output
  • rejects an empty –sql-file
  • rejects an empty –save-dir
  • rejects an empty –stdin
  • rejects conflicting output mode flags
  • prints rows for a DML RETURNING statement
  • rejects –output for a non-rowset DML statement
  • exports RETURNING rows with –output
  • rejects a comment-only –sql-file
  • strips a UTF-8 BOM from a –sql-file script
  • strips a UTF-8 BOM from batch stdin
  • rejects non-empty piped stdin with –sql-file
  • fails a –stdin dataset run with no query
  • reports per-file provenance for a sanitized basename
  • rejects duplicate basenames from different subdirectories
  • reports an overwrite when re-importing a directory
  • rejects –output that aliases an imported source
  • rejects –save-dir that resolves to the source directory
  • rejects a –save-dir destination that already exists
  • keeps stdout clean when write-back fails
  • skips write-back for a read-only query under –save –force
  • skips workbooks lacking the requested sheet (multi-workbook –sheet)
• sqly v0.19.0 binary bug fixes — 26 scenarios
  • quotes a CSV value containing a comma
  • quotes a CSV value containing a double quote
  • rejects an LTSV value containing a tab
  • rejects duplicate JSON keys
  • rejects duplicate NDJSON keys
  • keeps a Markdown row on one line when a value has a newline
  • accepts a leading block comment in direct –sql
  • accepts PRAGMA in direct –sql
  • accepts VALUES in direct –sql
  • accepts the TABLE shorthand in direct –sql
  • accepts CREATE TABLE in direct –sql
  • accepts ANALYZE in direct –sql
  • runs WITH … UPDATE without RETURNING as DML
  • rejects –stdin-name without –stdin
  • rejects –inspect-sample without –inspect
  • rejects –force without –save
  • rejects –inspect combined with –csv
  • imports an empty JSON array as a zero-row table
  • imports an empty JSONL file as a zero-row table
  • rejects an –output path ending with a slash
  • rejects an –output ACH destination
  • parses a helper command after a terminated statement
  • parses a helper command after a leading comment
  • does not write back for an EXPLAIN under –save-dir
  • does not write back for a zero-row DML under –save-dir
  • keeps stdout clean when parquet write-back fails
• sqly v0.20.0 binary regressions — 32 scenarios
  • write-back rejects: ALTER TABLE RENAME COLUMN
  • write-back rejects: DROP TABLE
  • write-back rejects: CREATE VIEW
  • write-back rejects: CREATE INDEX
  • write-back rejects: CREATE TABLE
  • write-back rejects: REINDEX
  • write-back rejects: ANALYZE
  • rejects CREATE TABLE AS SELECT under –save-dir and writes nothing
  • preflight rejects a CTAS+DML script before it executes
  • allows a .import + UPDATE batch under –save-dir and writes the change
  • neutral success: CREATE VIEW
  • neutral success: CREATE TABLE
  • neutral success: ANALYZE
  • runs a setter PRAGMA
  • runs a command PRAGMA that returns no rows
  • rejects BEGIN in a –sql-file script
  • rejects VACUUM
  • rejects VACUUM INTO and writes no file
  • rejects ATTACH DATABASE and persists no external file
  • runs a multiline CREATE TRIGGER from a –sql-file
  • accepts a schema-qualified .schema name
  • lists session-created views and temp tables in .tables
  • prints CREATE VIEW for a view in .schema
  • imports an empty compressed JSON array as a zero-row table
  • imports an empty compressed JSONL file as a zero-row table
  • imports /dev/stdin as CSV
  • imports /proc/self/fd/0 as CSV
  • rejects –output to a multi-compressed ACH destination
  • rejects .dump to a multi-compressed Fedwire destination
  • rejects an invalid LTSV output label
  • rejects duplicate LTSV output labels
  • rejects an LTSV import with duplicate labels
• sqly v0.21.0 binary regressions — 30 scenarios
  • prefers a TEMP table over a same-named main table in .schema
  • prefers a TEMP view over a same-named main table in .schema
  • keeps both a main and a same-named TEMP object in .tables
  • targets a literal dotted table name in .schema
  • targets a literal dotted table name in .describe
  • targets a literal dotted table name in .header
  • targets a literal dotted table name in .dump
  • prints a paste-safe quoted identifier in .tables
  • keeps the full spaced table name in .header
  • keeps the TEMP keyword for a temp-qualified table in .schema
  • keeps the TEMP keyword for a temp-qualified view in .schema
  • direct –sql rejects multi-statement input: two SELECTs
  • direct –sql rejects multi-statement input: SELECT then UPDATE
  • rejects multi-statement –sql –output before writing the file
  • rejects under –save –force: PRAGMA user_version=1
  • rejects under –save –force: PRAGMA incremental_vacuum
  • rejects under –save –force: PRAGMA journal_mode=OFF
  • rejects a setter PRAGMA under –save-dir and writes nothing
  • rejects a command PRAGMA under –save-dir and writes nothing
  • rejects END in direct –sql
  • rejects END in batch stdin
  • rejects END in a –sql-file script
  • –output rejects nested compression suffixes: out.csv.gz.zst
  • –output rejects nested compression suffixes: out.parquet.gz.zst
  • –output rejects nested compression suffixes: out.xlsx.gz.zst
  • rejects a nested .dump destination and writes nothing
  • emits structured .tables output under .mode json
  • emits structured .header output under .mode ndjson
  • does not rewrite an unchanged source on .save –force
  • writes no directory export for an unchanged session on .save DIR
• sqly v0.22.0 binary regressions — 18 scenarios
  • inspects a literal “main.x” table with .schema
  • inspects a literal “temp.x” table with .describe
  • inspects a literal “main.v” view with .header
  • exports a literal “temp.v” view with .dump
  • prints a paste-safe literal “main.x” name in .tables
  • rejects a –output destination that stacks .gzip and .zst on a format suffix
  • rejects a –output .json.gzip.zst destination
  • rejects a –output .ach.gzip.zst destination as input-only
  • rejects a .dump destination that stacks .gzip and .zst
  • runs the SELECT after a leading empty statement in direct –sql
  • runs the SELECT after multiple leading empty statements in direct –sql
  • exports the SELECT after a leading empty statement with –output
  • still rejects ATTACH after a leading empty statement in direct –sql
  • does not rewrite an unchanged CSV when only a TEMP table changed
  • does not fail on an unchanged JSONL import when only a scratch table changed
  • does not rewrite the source after net-zero CSV edits
  • does not rewrite the source after net-zero edits via –sql-file –save –force
  • still persists a genuine CSV change with .save –force
• sqly v0.25.0 binary regressions — 15 scenarios
  • rejects an explicit empty –sql value
  • reports a hint when non-interactive run gets empty stdin and no file
  • reports a hint when non-interactive run gets empty stdin with a file
  • reports a stable stdin reference instead of the staging temp path
  • fails batch mode when .schema is missing its table name
  • fails batch mode when .header is missing its table name
  • fails batch mode when .describe is missing its table name
  • fails batch mode when .mode is missing its mode name
  • fails batch mode when .dump is missing its destination
  • fails batch mode when .import is missing its path
  • fails batch mode when .save is missing its argument
  • keeps –inspect quiet on stderr after a successful directory import
  • keeps –profile quiet on stderr after a successful directory import
  • guides “sqly help” to –help instead of an import error
  • guides “sqly version” to –version instead of an import error

sqly ACH/Fedwire native write-back #

Source: test/e2e/tools/sqly/ach_fedwire_writeback.atago.yaml

Scenario: round-trips an ACH file through –save –force after an UPDATE #

Given #

• Fixture file payment.ach is created.

When #

sqly --sql "UPDATE payment_entries SET individual_name='E2E Receiver' WHERE entry_index=0" --save --force payment.ach
sqly --json --sql "SELECT individual_name FROM payment_entries WHERE entry_index=0" payment.ach

Then #

• after sqly --sql "UPDATE payment_entries SET individual_name='E2E Receiver' WHERE entry_index=0" --save --force payment.ach:
  • stderr contains Saved
• after sqly --json --sql "SELECT individual_name FROM payment_entries WHERE entry_index=0" payment.ach:
  • exit code is 0
  • stdout contains E2E Receiver

Scenario: writes a reconstructed ACH file into a directory with –save-dir #

Given #

• Fixture file payment.ach is created.

When #

sqly --sql "UPDATE payment_entries SET individual_name='Dir Receiver' WHERE entry_index=0" --save-dir out payment.ach
sqly --json --sql "SELECT individual_name FROM payment_entries WHERE entry_index=0" out/payment.ach

Then #

• after sqly --sql "UPDATE payment_entries SET individual_name='Dir Receiver' WHERE entry_index=0" --save-dir out payment.ach:
  • stderr contains Saved
• after sqly --json --sql "SELECT individual_name FROM payment_entries WHERE entry_index=0" out/payment.ach:
  • exit code is 0
  • stdout contains Dir Receiver

Scenario: round-trips a Fedwire file through –save –force after an UPDATE #

Given #

• Fixture file transfer.fed is created.

When #

sqly --sql "UPDATE transfer_message SET sender_reference='E2EREF'" --save --force transfer.fed
sqly --json --sql "SELECT sender_reference FROM transfer_message" transfer.fed

Then #

• after sqly --sql "UPDATE transfer_message SET sender_reference='E2EREF'" --save --force transfer.fed:
  • stderr contains Saved
• after sqly --json --sql "SELECT sender_reference FROM transfer_message" transfer.fed:
  • exit code is 0
  • stdout contains E2EREF

Scenario: still rejects a single-table –output to .ach #

Given #

• Fixture file payment.ach is created.

When #

sqly --sql "SELECT * FROM payment_entries" --output out.ach payment.ach

Then #

• exit code is 1
• stderr contains input-only

sqly batch mode (piped stdin) #

Source: test/e2e/tools/sqly/batch.atago.yaml

Scenario: runs SQL read from stdin #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT user_name FROM user ORDER BY identifier LIMIT 1

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains booker12

Scenario: switches output mode and runs the following query #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

.mode ndjson
SELECT user_name FROM user ORDER BY identifier LIMIT 1

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains {"user_name":"booker12"}
• stderr contains Change output mode

Scenario: exits non-zero and names the failing statement and its line on error #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT user_name FROM user ORDER BY identifier LIMIT 1;
SELECT * FROM no_such_table;

When #

sqly user.csv

Then #

• exit code is 1
• stdout contains booker12
• stderr contains batch statement 2 failed at line 2, no_such_table

Scenario: reports the line span of a failing multiline statement #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT user_name FROM user ORDER BY identifier LIMIT 1;
SELECT 1;
SELECT *
FROM no_such_table;

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains batch statement 3 failed at lines 3-4, no_such_table

Scenario: stops at .exit with a success status #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

.exit
SELECT * FROM no_such_table

When #

sqly user.csv

Then #

• exit code is 0

Scenario: still exits non-zero when a failure precedes .exit #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT * FROM no_such_table;
.exit

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains no_such_table

Scenario: runs a multiline SELECT terminated by a semicolon #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT user_name
FROM user
ORDER BY identifier
LIMIT 1;

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains booker12

Scenario: runs a multiline UNION ALL across bare newlines as one statement #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

.mode csv
SELECT 1 AS n
UNION ALL
SELECT 2;

When #

sqly user.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout equals an exact value
• stdout equals an exact value
• stderr contains Change output mode

Scenario: runs a multiline WITH (CTE) query #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

WITH x AS (
  SELECT user_name FROM user ORDER BY identifier LIMIT 1
)
SELECT * FROM x;

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains booker12

Scenario: runs multiple statements and helper commands in one payload #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

.tables
SELECT COUNT(*) AS c FROM user;
SELECT user_name FROM user ORDER BY identifier LIMIT 1;

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains TABLE NAME, booker12

Scenario: ignores a semicolon inside a leading line comment #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

-- comment ;
SELECT 'v' AS x;

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains v

Scenario: ignores a semicolon inside a block comment #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

/* comment ; */
SELECT 'v' AS x;

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains v

Scenario: ignores a semicolon inside a trailing line comment #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT 'first' AS x; -- trailing ; comment
SELECT 'second' AS y;

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains first, second

Scenario: does not split on a semicolon inside a bracket-quoted identifier #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT 'v' AS [a;b];

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains a;b, v

Scenario: does not split on a semicolon inside a backtick-quoted identifier #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT 'v' AS `a;b`;

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains a;b, v

Scenario: reports an error for incomplete SQL #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT * FROM (

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains batch statement

sqly batch fail-fast #

Source: test/e2e/tools/sqly/batch_failfast.atago.yaml

Scenario: stops later statements after a SQL failure #

Given #

• Fixture file u.csv is created.

Inputs #

Fixture u.csv:

user_name,identifier,first_name
booker12,1,Rachel
jenkins46,2,Mary

stdin for sqly:

SELECT * FROM no_such_table;
SELECT 1 AS later;

When #

sqly u.csv

Then #

• exit code is 1
• stdout does not contain later
• stderr contains no_such_table

Scenario: does not run .save –force after an earlier failure #

Given #

• Fixture file u.csv is created.

Inputs #

Fixture u.csv:

user_name,identifier,first_name
booker12,1,Rachel
jenkins46,2,Mary

stdin for sqly:

UPDATE u SET first_name = 'BROKEN' WHERE identifier = 1;
SELECT * FROM no_such_table;
.save --force

When #

sqly u.csv

Then #

• exit code is 1
• stdout contains affected
• stderr does not contain Saved
• file u.csv is checked

Scenario: does not run .dump after an earlier failure #

Given #

• Fixture file u.csv is created.

Inputs #

Fixture u.csv:

user_name,identifier,first_name
booker12,1,Rachel
jenkins46,2,Mary

stdin for sqly:

SELECT * FROM no_such_table;
.dump u out.csv

When #

sqly u.csv

Then #

• exit code is 1
• stderr contains no_such_table
• file out.csv does not exist

Scenario: does not write back for empty stdin with –save –force #

Given #

• Fixture file u.csv is created.

Inputs #

Fixture u.csv:

user_name,identifier,first_name
booker12,1,Rachel
jenkins46,2,Mary

When #

sqly u.csv --save --force

Then #

• exit code is 1
• stderr contains no TTY detected
• stderr does not contain Saved
• file u.csv contains Rachel

sqly –cache import cache #

Source: test/e2e/tools/sqly/cache.atago.yaml

Scenario: writes a cache on the cold run and reuses it on the warm run #

Given #

• Fixture file data.csv is created.

Inputs #

Fixture data.csv:

id,name
1,Alice
2,Bob
3,Carol

When #

sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" data.csv
sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" data.csv

Then #

• after sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" data.csv:
  • exit code is 0
  • stdout contains 3
  • stderr contains cache: reused

Scenario: invalidates the cache when the source changes #

Given #

• Fixture file data.csv is created.
• Fixture file data.csv is created.

Inputs #

Fixture data.csv:

id,name
1,Alice
2,Bob
3,Carol

Fixture data.csv:

id,name
1,Alice
2,Bob
3,Carol
4,Dave

When #

sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" data.csv
sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" data.csv

Then #

• after sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" data.csv:
  • exit code is 0
  • stdout contains 4
  • stderr does not contain cache: reused

Scenario: rebuilds after –cache-clear #

Given #

• Fixture file data.csv is created.

Inputs #

Fixture data.csv:

id,name
1,Alice
2,Bob
3,Carol

When #

sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" data.csv
sqly --cache snap.cache --cache-clear --sql "SELECT COUNT(*) AS n FROM data" data.csv

Then #

• after sqly --cache snap.cache --cache-clear --sql "SELECT COUNT(*) AS n FROM data" data.csv:
  • exit code is 0
  • stdout contains 3
  • stderr does not contain cache: reused

Scenario: falls back to a cold import when the cache path is unwritable #

Given #

• Fixture file data.csv is created.
• Fixture file snap.cache/keep is created.

Inputs #

Fixture data.csv:

id,name
1,Alice
2,Bob
3,Carol

Fixture snap.cache/keep:

x

When #

sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" data.csv

Then #

• exit code is 0
• stdout contains 3
• stderr contains cache

Scenario: invalidates the cache when content changes but size and mtime do not (#592) #

Given #

• Fixture file d.csv is created.
• Fixture file d.csv is created.

Inputs #

Fixture d.csv:

id,name
1,Alice
2,Bob

Fixture d.csv:

id,name
1,Carol
2,Eve

When #

sqly --cache snap.cache --sql "SELECT group_concat(name, ',') AS names FROM d" d.csv
sqly --cache snap.cache --sql "SELECT group_concat(name, ',') AS names FROM d" d.csv

Then #

• after sqly --cache snap.cache --sql "SELECT group_concat(name, ',') AS names FROM d" d.csv:
  • exit code is 0
  • stdout contains Carol,Eve
  • stdout does not contain Alice,Bob
  • stderr does not contain cache: reused

Scenario: reuses the cache that lives inside the imported directory and ignores its manifest #

Given #

• Fixture file indir/data.csv is created.

Inputs #

Fixture indir/data.csv:

id,name
1,Alice
2,Bob
3,Carol

When #

sqly --cache indir/snap.cache --sql "SELECT COUNT(*) AS n FROM data" indir
sqly --cache indir/snap.cache --sql "SELECT group_concat(name, ',') AS t FROM sqlite_master WHERE type='table'" indir

Then #

• after sqly --cache indir/snap.cache --sql "SELECT group_concat(name, ',') AS t FROM sqlite_master WHERE type='table'" indir:
  • exit code is 0
  • stdout contains data
  • stderr contains cache: reused
  • stdout does not contain manifest, snap

Scenario: keeps the cache warm when only an unsupported sibling file changes #

Given #

• Fixture file indir/data.csv is created.
• Fixture file indir/ignore.txt is created.
• Fixture file indir/ignore.txt is created.

Inputs #

Fixture indir/data.csv:

id,name
1,Alice

Fixture indir/ignore.txt:

note

Fixture indir/ignore.txt:

changed

When #

sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" indir
sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" indir

Then #

• after sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" indir:
  • exit code is 0
  • stdout contains 1
  • stderr contains cache: reused

Scenario: still invalidates the cache when the supported file changes #

Given #

• Fixture file indir/data.csv is created.
• Fixture file indir/ignore.txt is created.
• Fixture file indir/data.csv is created.

Inputs #

Fixture indir/data.csv:

id,name
1,Alice

Fixture indir/ignore.txt:

note

Fixture indir/data.csv:

id,name
1,Alice
2,Bob

When #

sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" indir
sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" indir

Then #

• after sqly --cache snap.cache --sql "SELECT COUNT(*) AS n FROM data" indir:
  • exit code is 0
  • stdout contains 2
  • stderr does not contain cache: reused

sqly changes delta cross-checks (exhaustive-set semantics) #

Source: test/e2e/tools/sqly/changes_crosscheck.atago.yaml

Scenario: omitting sqly’s history DB from an exhaustive list is rejected #

only when sqly --version succeeds · skipped on Windows

Given #

• Fixture file inner.atago.yaml is created.

Inputs #

Fixture inner.atago.yaml:

version: "1"
suite:
  name: inner
scenarios:
  - name: under-specified created list
    steps:
      - fixture:
          file: user.csv
          content: |
            n,v
            a,1
      - run:
          sandbox_home: true
          command: sqly --sql "SELECT 1" user.csv
      - assert:
          changes:
            created: []

When #

${atago} run inner.atago.yaml

Then #

• exit code is 1
• stdout contains unexpected created file
• stdout contains .atago-home/.config/sqly/history.db

Scenario: the exhaustive list including the history DB passes #

only when sqly --version succeeds · skipped on Windows

Given #

• Fixture file user.csv is created.
• The command runs with an isolated home under ${workdir}/.atago-home (HOME/XDG or APPDATA redirected).

Inputs #

Fixture user.csv:

n,v
a,1

When #

sqly --sql "SELECT 1" user.csv

Then #

• exit code is 0
• the step changed exactly created .atago-home/.config/sqly/history.db, modified nothing, deleted nothing

sqly CLI surface #

Source: test/e2e/tools/sqly/cli.atago.yaml

Scenario: prints the version #

When #

sqly --version

Then #

• exit code is 0
• stdout contains sqly

Scenario: prints usage with –help #

When #

sqly --help

Then #

• exit code is 0
• stdout contains [Usage], --json

Scenario: fails on a non-existent file #

When #

sqly --sql "SELECT 1" does_not_exist.csv

Then #

• exit code is 1
• stderr contains does not exist

Scenario: fails on invalid SQL with –sql #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELEKT * FROM user" user.csv

Then #

• exit code is 1
• stderr contains syntax error

Scenario: reports an unknown flag as a CLI error #

When #

sqly --no-such-flag

Then #

• exit code is 1
• stderr contains unknown flag

Scenario: reports conflicting output mode flags as a CLI error #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --csv --json --sql "SELECT 1" user.csv

Then #

• exit code is 1
• stderr contains conflicting output mode flags

Scenario: joins two imported files #

Given #

• Fixture file user.csv is created.
• Fixture file identifier.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2

Fixture identifier.csv:

id,position
1,developrt
2,manager

When #

sqly --csv --sql "SELECT user_name, position FROM user INNER JOIN identifier ON user.identifier = identifier.id ORDER BY user.identifier LIMIT 1" user.csv identifier.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout contains booker12

Scenario: writes JSON results to the given path with –output #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --json --output result.json --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv

Then #

• exit code is 0
• stderr contains result.json
• file result.json contains "user_name":"booker12"

Scenario: applies an output-mode flag placed after the file path #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv --csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout contains booker12

Scenario: fails fast on an unknown flag after the file path #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly user.csv --nope

Then #

• exit code is 1
• stderr contains unknown flag

sqly table-name collision #

Source: test/e2e/tools/sqly/collision.atago.yaml

Scenario: fails when two inputs sanitize to the same table name #

Given #

• Fixture file a-b.csv is created.
• Fixture file a_b.csv is created.

Inputs #

Fixture a-b.csv:

id,name
1,A

Fixture a_b.csv:

id,name
2,B

When #

sqly --inspect a-b.csv a_b.csv

Then #

• exit code is 1
• stderr contains collision

sqly –compare workflow #

Source: test/e2e/tools/sqly/compare.atago.yaml

Scenario: reports schema, row count, and keyed rows as JSON #

Given #

• Fixture file rev1.csv is created.
• Fixture file rev2.csv is created.

Inputs #

Fixture rev1.csv:

id,name,age
1,Alice,30
2,Bob,25
3,Carol,40

Fixture rev2.csv:

id,name,age
1,Alice,31
2,Bob,25
4,Dave,50

When #

sqly --compare --compare-key id rev1.csv rev2.csv

Then #

• exit code is 0
• stdout at $.schema.equal equals true
• stdout at $.row_count.delta equals 0
• stdout at $.rows.key equals id
• stdout contains "4", "3"

Scenario: emits a human-readable summary with –compare-format text #

Given #

• Fixture file rev1.csv is created.
• Fixture file rev2.csv is created.

Inputs #

Fixture rev1.csv:

id,name,age
1,Alice,30
2,Bob,25
3,Carol,40

Fixture rev2.csv:

id,name,age
1,Alice,31
2,Bob,25
4,Dave,50

When #

sqly --compare --compare-key id --compare-format text rev1.csv rev2.csv

Then #

• exit code is 0
• stdout contains schema: identical, 1 added, 1 removed, 1 modified

Scenario: resolves an uppercase –compare-key against a lowercase column #

Given #

• Fixture file rev1.csv is created.
• Fixture file rev2.csv is created.

Inputs #

Fixture rev1.csv:

id,name,age
1,Alice,30
2,Bob,25
3,Carol,40

Fixture rev2.csv:

id,name,age
1,Alice,31
2,Bob,25
4,Dave,50

When #

sqly --compare --compare-key ID rev1.csv rev2.csv

Then #

• exit code is 0
• stdout at $.rows.key equals ID

Scenario: rejects a missing key column #

Given #

• Fixture file rev1.csv is created.
• Fixture file rev2.csv is created.

Inputs #

Fixture rev1.csv:

id,name,age
1,Alice,30
2,Bob,25
3,Carol,40

Fixture rev2.csv:

id,name,age
1,Alice,31
2,Bob,25
4,Dave,50

When #

sqly --compare --compare-key nope rev1.csv rev2.csv

Then #

• exit code is 1
• stderr contains compare key

Scenario: rejects a duplicate key value as ambiguous #

Given #

• Fixture file dupe.csv is created.
• Fixture file single.csv is created.

Inputs #

Fixture dupe.csv:

id,name
1,Alice
1,Bob

Fixture single.csv:

id,name
1,Alice

When #

sqly --compare --compare-key id dupe.csv single.csv

Then #

• exit code is 1
• stderr contains not unique

Scenario: rejects a genuinely missing –compare-tables name #

Given #

• Fixture file rev1.csv is created.
• Fixture file rev2.csv is created.

Inputs #

Fixture rev1.csv:

id,name,age
1,Alice,30
2,Bob,25
3,Carol,40

Fixture rev2.csv:

id,name,age
1,Alice,31
2,Bob,25
4,Dave,50

When #

sqly --compare --compare-tables "nope,rev2" rev1.csv rev2.csv

Then #

• exit code is 1
• stderr contains compare table

Scenario: rejects an ambiguous single-table compare #

Given #

• Fixture file rev1.csv is created.

Inputs #

Fixture rev1.csv:

id,name,age
1,Alice,30
2,Bob,25
3,Carol,40

When #

sqly --compare rev1.csv

Then #

• exit code is 1
• stderr contains exactly two tables

Scenario: follows CLI input order for left and right, not table-name sorting #

Given #

• Fixture file zebra.csv is created.
• Fixture file ant.csv is created.

Inputs #

Fixture zebra.csv:

id,name
1,Alice

Fixture ant.csv:

id,name
1,Alice

When #

sqly --compare --compare-format text zebra.csv ant.csv

Then #

• exit code is 0
• stdout equals an exact value

Scenario: reverses left and right when the inputs are swapped #

Given #

• Fixture file zebra.csv is created.
• Fixture file ant.csv is created.

Inputs #

Fixture zebra.csv:

id,name
1,Alice

Fixture ant.csv:

id,name
1,Alice

When #

sqly --compare --compare-format text ant.csv zebra.csv

Then #

• exit code is 0
• stdout equals an exact value

Scenario: keeps the keyed diff correct on a larger input #

Given #

• Fixture file big1.csv is created.
• Fixture file big2.csv is created.

Inputs #

Fixture big1.csv:

id,name,score
0,name0,0
1,name1,1
2,name2,2
3,name3,3
4,name4,4
5,name5,5
6,name6,6
7,name7,7
8,name8,8
9,name9,9
10,name10,10
11,name11,11
12,name12,12
13,name13,13
14,name14,14
15,name15,15
16,name16,16
17,name17,17
18,name18,18
… (truncated, 31 more lines)

Fixture big2.csv:

id,name,score
0,name0,1
1,name1,1
2,name2,2
3,name3,3
4,name4,4
5,name5,5
6,name6,6
7,name7,7
8,name8,8
9,name9,9
10,name10,10
11,name11,11
12,name12,12
13,name13,13
14,name14,14
15,name15,15
16,name16,16
17,name17,17
18,name18,18
… (truncated, 31 more lines)

When #

sqly --compare --compare-key id --compare-format text big1.csv big2.csv

Then #

• exit code is 0
• stdout contains 1 added, 1 removed, 1 modified

sqly cross-format workflows #

Source: test/e2e/tools/sqly/cross_format.atago.yaml

Scenario: joins a Parquet table to a CSV table and computes a column #

Given #

• Fixture file products.parquet is created.
• Fixture file sales.csv is created.

Inputs #

Fixture sales.csv:

product_id,quantity
1,3
2,10
3,5

When #

sqly --csv --sql "SELECT p.name, p.price, s.quantity, ROUND(p.price * s.quantity, 2) AS revenue FROM products p JOIN sales s ON p.id = s.product_id ORDER BY revenue DESC" products.parquet sales.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout contains Laptop, 2999.97

sqly empty command arguments #

Source: test/e2e/tools/sqly/empty_args.atago.yaml

Scenario: rejects .save "" and leaves the source unchanged #

Given #

• Fixture file u.csv is created.

Inputs #

Fixture u.csv:

user_name,identifier,first_name
booker12,1,Rachel

stdin for sqly:

UPDATE u SET first_name = 'EMPTY' WHERE identifier = 1;
.save ""

When #

sqly u.csv

Then #

• exit code is 1
• stdout contains affected
• stderr contains .save requires
• file u.csv is checked

Scenario: rejects .dump with an empty destination #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.dump user ""

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains .dump requires

Scenario: rejects .import with an empty path #

Inputs #

stdin for sqly:

.import ""
.tables

When #

sqly

Then #

• exit code is 1
• stderr contains empty import path

sqly excel export #

Source: test/e2e/tools/sqly/excel_export.atago.yaml

Scenario: writes a non-executable .xlsx with –output #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --excel --output out.xlsx --sql "SELECT * FROM user LIMIT 1" user.csv

Then #

• exit code is 0
• stderr contains output mode=excel
• file out.xlsx exists
• file out.xlsx is checked

Generated artifacts #

• out.xlsx

Scenario: writes a non-executable .xlsx with the .dump command #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.mode excel
.dump user dump.xlsx

When #

sqly user.csv

Then #

• exit code is 0
• stderr contains mode=excel
• file dump.xlsx exists
• file dump.xlsx is checked

Generated artifacts #

• dump.xlsx

sqly export format inference #

Source: test/e2e/tools/sqly/export_inference.atago.yaml

Scenario: infers parquet from the destination extension without a flag #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv --output result.parquet

Then #

• exit code is 0
• stderr contains output mode=parquet
• file result.parquet exists

Generated artifacts #

• result.parquet

Scenario: infers ndjson with gzip and writes a compressed file #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv --output result.ndjson.gz

Then #

• exit code is 0
• stderr contains output mode=ndjson
• file result.ndjson.gz exists
• file result.ndjson.gz is checked

Generated artifacts #

• result.ndjson.gz

Scenario: re-imports a gzip-compressed csv it wrote #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2

When #

sqly --csv --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv --output result.csv.gz
sqly --csv --sql "SELECT user_name FROM result LIMIT 1" result.csv.gz

Then #

• after sqly --csv --sql "SELECT user_name FROM result LIMIT 1" result.csv.gz:
  • exit code is 0
  • stdout equals an exact value
  • stdout equals an exact value

Scenario: writes the CSV fallback to an unknown extension path without rewriting it #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT user_name FROM user LIMIT 1" user.csv --output out.unknown

Then #

• exit code is 0
• stderr contains out.unknown
• file out.unknown exists
• file out.csv does not exist

Generated artifacts #

• out.unknown

Scenario: rejects –output to an existing directory #

Given #

• Fixture file user.csv is created.
• Fixture file outdir/.keep is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT identifier FROM user LIMIT 1" user.csv --output outdir

Then #

• exit code is 1
• stderr contains directory
• file outdir.csv does not exist

Scenario: rejects .dump to an existing directory #

Given #

• Fixture file user.csv is created.
• Fixture file outdir/.keep is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.dump user outdir

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains directory

Scenario: errors when an explicit mode flag disagrees with the path extension #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --json --sql "SELECT user_name FROM user LIMIT 1" user.csv --output result.csv

Then #

• exit code is 1
• stderr contains conflicts with destination path

Scenario: rejects bzip2 output #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT user_name FROM user LIMIT 1" user.csv --output result.csv.bz2

Then #

• exit code is 1
• stderr contains bzip2

Scenario: rejects compression on parquet #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT user_name FROM user LIMIT 1" user.csv --output result.parquet.gz

Then #

• exit code is 1
• stderr contains cannot be compressed

Scenario: infers tsv from the .dump destination path #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.dump user dump.tsv

When #

sqly user.csv

Then #

• exit code is 0
• stderr contains mode=tsv
• file dump.tsv exists

Generated artifacts #

• dump.tsv

Scenario: keeps –json –output result.json writing json #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2

When #

sqly --json --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv --output result.json

Then #

• exit code is 0
• stderr contains output mode=json
• file result.json contains "user_name":"booker12"

sqly helper commands reject extra args #

Source: test/e2e/tools/sqly/extra_args.atago.yaml

Scenario: rejects .schema with an extra argument #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.schema user extra

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains .schema

Scenario: rejects .describe with an extra argument #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.describe user extra

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains .describe

Scenario: rejects .tables with an extra argument #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.tables extra

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains .tables

Scenario: rejects .mode with an extra argument #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.mode csv extra

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains .mode

Scenario: rejects .pwd with an extra argument #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.pwd extra

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains .pwd

Scenario: rejects .clear with an extra argument #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.clear extra

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains .clear

Scenario: does not let .exit with an extra argument silently terminate the batch #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.exit extra
SELECT 1;

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains .exit

sqly filesql integration #

Source: test/e2e/tools/sqly/filesql_integration.atago.yaml

Scenario: imports and queries a CSV file #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

SELECT COUNT(*) FROM user

When #

sqly user.csv

Then #

• exit code is 0
• stdout contains 3

Scenario: imports and queries a JSONL file #

Given #

• Fixture file sample.jsonl is created.

Inputs #

stdin for sqly:

SELECT COUNT(*) FROM sample

When #

sqly sample.jsonl

Then #

• exit code is 0
• stdout contains COUNT(*)

Scenario: imports and queries a Parquet file #

Given #

• Fixture file products.parquet is created.

Inputs #

stdin for sqly:

SELECT COUNT(*) FROM products

When #

sqly products.parquet

Then #

• exit code is 0
• stdout contains 3

Scenario: imports and queries an Excel file #

Given #

• Fixture file sample.xlsx is created.

Inputs #

stdin for sqly:

SELECT COUNT(*) FROM sample_test_sheet

When #

sqly sample.xlsx

Then #

• exit code is 0
• stdout contains COUNT(*)

Scenario: imports and queries an ACH file #

Given #

• Fixture file ppd-debit.ach is created.

Inputs #

stdin for sqly:

SELECT COUNT(*) FROM ppd_debit_entries

When #

sqly ppd-debit.ach

Then #

• exit code is 0
• stdout contains COUNT(*)

Scenario: imports and queries a Fedwire file #

Given #

• Fixture file customer-transfer.fed is created.

Inputs #

stdin for sqly:

SELECT COUNT(*) FROM customer_transfer_message

When #

sqly customer-transfer.fed

Then #

• exit code is 0
• stdout contains COUNT(*)

Scenario: preserves filesql-detected column types for schema inspection #

Given #

• Fixture file products.parquet is created.

Inputs #

stdin for sqly:

.describe products

When #

sqly products.parquet

Then #

• exit code is 0
• stdout contains name, price

Scenario: produces identical tables on repeated ACH imports #

Given #

• Fixture file ppd-debit.ach is created.

Inputs #

stdin for sqly:

.tables

stdin for sqly:

.tables

When #

sqly ppd-debit.ach
sqly ppd-debit.ach

Then #

• after sqly ppd-debit.ach:
  • stdout contains ppd_debit_entries
• after sqly ppd-debit.ach:
  • stdout contains ppd_debit_entries

sqly shell helper commands #

Source: test/e2e/tools/sqly/helpers.atago.yaml

Scenario: .help groups commands, shows usage, and flags destructive save #

Inputs #

stdin for sqly:

.help

When #

sqly

Then #

• exit code is 0
• stdout contains Import / Export, .import PATH, .dump TABLE FILE, .save DIR, .save --force, destructive

Scenario: .cd changes directory with a relative path and reports it #

Given #

• Fixture file testdata/x.csv is created.

Inputs #

Fixture testdata/x.csv:

id,name
1,a

stdin for sqly:

.cd testdata
.pwd

When #

sqly

Then #

• exit code is 0
• stdout contains testdata

Scenario: expands a bare ~ in .cd to the home directory #

Given #

• Fixture file home/sqly_tilde.csv is created.

Inputs #

Fixture home/sqly_tilde.csv:

id,name
1,foo

stdin for sqly:

.cd ~
.pwd

When #

sqly

Then #

• exit code is 0
• stdout contains home

Scenario: expands ~/file in .import #

Given #

• Fixture file home/sqly_tilde.csv is created.

Inputs #

Fixture home/sqly_tilde.csv:

id,name
1,foo

stdin for sqly:

.import ~/sqly_tilde.csv
.tables

When #

sqly

Then #

• exit code is 0
• stdout contains sqly_tilde

Scenario: .clear emits no ANSI escapes to stdout in batch mode #

Inputs #

stdin for sqly:

.clear

When #

sqly

Then #

• exit code is 0
• stdout is empty

Scenario: keeps –json stdout parseable when .clear precedes a query #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.clear
SELECT 1 AS x;

When #

sqly --json user.csv

Then #

• exit code is 0
• stdout equals an exact value

Scenario: .import imports a quoted path containing a space as one argument #

Given #

• Fixture file sqly_e2e space.csv is created.

Inputs #

Fixture sqly_e2e space.csv:

id,name
1,foo

stdin for sqly:

.import "sqly_e2e space.csv"
.tables

When #

sqly

Then #

• exit code is 0
• stdout contains sqly_e2e_space

Scenario: .mode fails on a missing mode name but still lists the modes #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.mode

When #

sqly user.csv

Then #

• exit code is 1
• stderr contains json, ndjson

Scenario: .dump infers TSV from the .tsv extension in table mode, not CSV #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.dump user out.tsv

When #

sqly user.csv

Then #

• exit code is 0
• stderr contains mode=tsv
• file out.tsv contains user_name identifier
• file out.tsv is checked

sqly hermetic environment #

Source: test/e2e/tools/sqly/hermetic.atago.yaml

Scenario: runs with HOME inside the sandbox #

only when env SQLY_E2E_SANDBOX is set

When #

case "$HOME" in "$SQLY_E2E_SANDBOX"*) exit 0 ;; *) exit 1 ;; esac

Then #

• exit code is 0

Scenario: routes the history DB into the sandbox #

only when env SQLY_E2E_SANDBOX is set

When #

case "$SQLY_HISTORY_DB_PATH" in "$SQLY_E2E_SANDBOX"*) exit 0 ;; *) exit 1 ;; esac

Then #

• exit code is 0

Scenario: still runs sqly normally inside the sandbox #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT 1 AS one" user.csv

Then #

• exit code is 0
• stdout contains one

sqly history tolerance #

Source: test/e2e/tools/sqly/history_tolerance.atago.yaml

Scenario: runs –sql and warns instead of failing #

Given #

• Fixture file actor.csv is created.

Inputs #

Fixture actor.csv:

actor
Adam Sandler
Harrison Ford

When #

sqly --csv --sql "SELECT actor FROM actor ORDER BY actor LIMIT 1" actor.csv

Then #

• exit code is 0
• stdout contains Adam Sandler
• stderr contains history disabled

Scenario: runs batch mode without a writable history DB #

Given #

• Fixture file actor.csv is created.

Inputs #

Fixture actor.csv:

actor
Adam Sandler
Harrison Ford

stdin for sqly:

.tables
SELECT actor FROM actor ORDER BY actor LIMIT 1

When #

sqly actor.csv

Then #

• exit code is 0
• stdout contains TABLE NAME, Adam Sandler
• stderr contains history disabled

Scenario: runs –sql when the history DB is read-only after startup #

Given #

• Fixture file user.csv is created.
• Fixture file h.db is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2

When #

sqly --sql "SELECT 1" user.csv
sqly --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv

Then #

• after sqly --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv:
  • exit code is 0
  • stdout contains booker12

Scenario: runs –inspect when the history DB is read-only after startup #

Given #

• Fixture file user.csv is created.
• Fixture file h.db is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2

When #

sqly --sql "SELECT 1" user.csv
sqly --inspect user.csv

Then #

• after sqly --inspect user.csv:
  • exit code is 0
  • stdout equals an exact value
  • stdout contains "name": "user"

Scenario: runs batch mode and warns when a history write fails after startup #

Given #

• Fixture file user.csv is created.
• Fixture file h.db is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2

stdin for sqly:

SELECT user_name FROM user ORDER BY identifier LIMIT 1

When #

sqly --sql "SELECT 1" user.csv
sqly user.csv

Then #

• after sqly user.csv:
  • exit code is 0
  • stdout contains booker12
  • stderr contains history disabled

sqly import failure handling #

Source: test/e2e/tools/sqly/import_failure.atago.yaml

Scenario: fails query mode on a partial import and keeps stdout clean #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --json --sql "SELECT user_name FROM user LIMIT 1" user.csv /no/such/file.csv

Then #

• exit code is 1
• stdout is empty
• stderr contains failed to import, inputs failed to import: path does not exist

Scenario: names the failed count and first path when every input fails #

When #

sqly --sql "SELECT 1" /no/such/a.csv /no/such/b.csv

Then #

• exit code is 1
• stdout is empty
• stderr contains all 2 import(s) failed, /no/such/a.csv, +1 more

Scenario: fails –inspect on a partial import #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --inspect user.csv /no/such/file.csv

Then #

• exit code is 1
• stdout is empty
• stderr contains failed to import

Scenario: fails batch .import on a partial import and stops later commands #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.import user.csv /no/such/file.csv
.tables

When #

sqly

Then #

• exit code is 1
• stdout does not contain TABLE NAME
• stderr contains failed to import

Scenario: keeps stdout clean when a stdin dataset fails to import #

When #

sqly --stdin csv --json --sql "SELECT COUNT(*) FROM stdin"

Then #

• exit code is 1
• stdout is empty
• stderr contains Import failed

sqly .import with space-containing paths #

Source: test/e2e/tools/sqly/import_quoting.atago.yaml

Scenario: imports a backslash-escaped space path as a single argument #

Given #

• Fixture file space name.csv is created.

Inputs #

Fixture space name.csv:

label,score
alpha,1
beta,2

stdin for sqly:

.import space\ name.csv
SELECT label FROM space_name ORDER BY score;

When #

sqly

Then #

• exit code is 0
• stdout contains alpha, beta

Scenario: imports a double-quoted space path as a single argument #

Given #

• Fixture file space name.csv is created.

Inputs #

Fixture space name.csv:

label,score
alpha,1
beta,2

stdin for sqly:

.import "space name.csv"
SELECT label FROM space_name ORDER BY score;

When #

sqly

Then #

• exit code is 0
• stdout contains alpha

Scenario: imports a single-quoted space path as a single argument #

Given #

• Fixture file space name.csv is created.

Inputs #

Fixture space name.csv:

label,score
alpha,1
beta,2

stdin for sqly:

.import 'space name.csv'
SELECT label FROM space_name ORDER BY score;

When #

sqly

Then #

• exit code is 0
• stdout contains alpha

Scenario: imports a file inside a space-containing directory when escaped #

Given #

• Fixture file space dir/nested.csv is created.

Inputs #

Fixture space dir/nested.csv:

label,score
gamma,3
delta,4

stdin for sqly:

.import space\ dir/nested.csv
SELECT label FROM nested ORDER BY score;

When #

sqly

Then #

• exit code is 0
• stdout contains gamma, delta

Scenario: splits an unquoted space path into two failing arguments #

Given #

• Fixture file space name.csv is created.

Inputs #

Fixture space name.csv:

label,score
alpha,1
beta,2

stdin for sqly:

.import space name.csv

When #

sqly

Then #

• exit code is 1
• stderr contains space

sqly –inspect #

Source: test/e2e/tools/sqly/inspect.atago.yaml

Scenario: prints a JSON report for a single file #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --inspect user.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout contains "name": "user", "row_count": 3, "user_name"

Scenario: maps every table from a multi-table ACH file to its source #

Given #

• Fixture file ppd-debit.ach is created.

When #

sqly --inspect ppd-debit.ach

Then #

• exit code is 0
• stdout contains "name": "ppd_debit_entries", ppd-debit.ach

Scenario: keeps stdout as pure JSON for a directory and stays quiet on stderr #

Given #

• Fixture file ins/a.csv is created.
• Fixture file ins/b.csv is created.

Inputs #

Fixture ins/a.csv:

user_name,identifier
booker12,1

Fixture ins/b.csv:

id,position
1,developrt

When #

sqly --inspect ins

Then #

• exit code is 0
• stdout equals an exact value
• stdout contains "name": "a", "name": "b"
• stderr does not contain Successfully imported

Scenario: fails with a clear error when no input is given #

When #

sqly --inspect

Then #

• exit code is 1
• stderr contains no tables to inspect

Scenario: emits a schema-only report with –inspect-sample 0 #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --inspect --inspect-sample 0 user.csv

Then #

• exit code is 0
• stdout contains "sample_rows": []
• stdout does not contain booker12

Scenario: limits sample rows with –inspect-sample #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --inspect --inspect-sample 1 user.csv

Then #

• exit code is 0
• stdout contains booker12
• stdout does not contain jenkins46

Scenario: rejects a negative –inspect-sample #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --inspect --inspect-sample -1 user.csv

Then #

• exit code is 1
• stderr contains inspect-sample

sqly –inspect conflicts #

Source: test/e2e/tools/sqly/inspect_conflicts.atago.yaml

Scenario: rejects –inspect with –sql #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --inspect --sql "SELECT * FROM user LIMIT 1" user.csv

Then #

• exit code is 1
• stderr contains --inspect

Scenario: rejects –inspect with –output and writes no file #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --inspect --output out.csv user.csv

Then #

• exit code is 1
• stderr contains --inspect
• file out.csv does not exist

Scenario: rejects –inspect with –save-dir and creates no save dir #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --inspect --save-dir save user.csv

Then #

• exit code is 1
• stderr contains --inspect
• file save does not exist

sqly JSON/NDJSON NULL handling #

Source: test/e2e/tools/sqly/json_null.atago.yaml

Scenario: emits NULL as JSON null in –json #

When #

sqly --json --sql "SELECT NULL AS n, '' AS e, 1 AS x"

Then #

• exit code is 0
• stdout contains "n":null, "e":""

Scenario: emits NULL as JSON null in –ndjson #

When #

sqly --ndjson --sql "SELECT NULL AS n, '' AS e, 1 AS x"

Then #

• exit code is 0
• stdout contains "n":null, "e":""

sqly metamorphic relations #

Source: test/e2e/tools/sqly/metamorphic.atago.yaml

Scenario: COUNT(*) equals the number of selected rows #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --csv --sql "SELECT COUNT(*) AS c FROM user" user.csv
sqly --csv --sql "SELECT user_name FROM user" user.csv

Then #

• after sqly --csv --sql "SELECT COUNT(*) AS c FROM user" user.csv:
  • stdout equals an exact value
• after sqly --csv --sql "SELECT user_name FROM user" user.csv:
  • stdout contains booker12, jenkins46, grey07

Scenario: WHERE 1=1 returns all rows and WHERE 1=0 returns none #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --csv --sql "SELECT COUNT(*) AS c FROM user WHERE 1=1" user.csv
sqly --json --sql "SELECT user_name FROM user WHERE 1=0" user.csv

Then #

• after sqly --csv --sql "SELECT COUNT(*) AS c FROM user WHERE 1=1" user.csv:
  • stdout equals an exact value
• after sqly --json --sql "SELECT user_name FROM user WHERE 1=0" user.csv:
  • stdout equals an exact value

Scenario: ORDER BY preserves the row multiset #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --csv --sql "SELECT user_name FROM user ORDER BY user_name DESC" user.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout contains grey07, booker12

Scenario: csv and ndjson yield the same rows #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --ndjson --sql "SELECT user_name FROM user ORDER BY identifier" user.csv

Then #

• stdout equals an exact value
• stdout equals an exact value

Scenario: CSV dump reimported yields identical data #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

.dump user rt.csv

When #

sqly user.csv
sqly --csv --sql "SELECT * FROM rt ORDER BY identifier" rt.csv

Then #

• after sqly --csv --sql "SELECT * FROM rt ORDER BY identifier" rt.csv:
  • exit code is 0
  • stdout equals an exact value
  • stdout equals an exact value
  • stdout equals an exact value

sqly .mode banner routing #

Source: test/e2e/tools/sqly/mode_banner.atago.yaml

Scenario: keeps stdout pure JSON after .mode json #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.mode json
SELECT user_name FROM user LIMIT 1

When #

sqly user.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout does not contain Change output mode
• stderr contains Change output mode from table to json

Scenario: keeps stdout pure NDJSON after .mode ndjson #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.mode ndjson
SELECT user_name FROM user LIMIT 1

When #

sqly user.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout does not contain Change output mode
• stderr contains Change output mode from table to ndjson

Scenario: reports the typed mode by name and emits typed output after .mode json-typed #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.mode json-typed
SELECT 7 AS n, 'x' AS s

When #

sqly user.csv

Then #

• exit code is 0
• stdout does not contain Change output mode
• stderr contains Change output mode from table to json-typed
• stdout contains "n":7, "s":"x"

sqly output formats #

Source: test/e2e/tools/sqly/output_format.atago.yaml

Scenario: –json renders results as a JSON array #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --json --sql "SELECT user_name, identifier FROM user ORDER BY identifier LIMIT 2" user.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout contains {"user_name":"booker12","identifier":"1"}
• stdout contains {"user_name":"jenkins46","identifier":"2"}

Scenario: –json prints [] for an empty result #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --json --sql "SELECT user_name FROM user WHERE user_name = 'nobody'" user.csv

Then #

• exit code is 0
• stdout equals an exact value

Scenario: –ndjson renders one JSON object per line #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --ndjson --sql "SELECT user_name, identifier FROM user ORDER BY identifier LIMIT 2" user.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout equals an exact value

Scenario: –ndjson prints nothing for an empty result #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --ndjson --sql "SELECT user_name FROM user WHERE user_name = 'nobody'" user.csv

Then #

• exit code is 0
• stdout is empty

Scenario: –csv renders header and rows as CSV #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2

When #

sqly --csv --sql "SELECT user_name, identifier FROM user ORDER BY identifier LIMIT 1" user.csv

Then #

• exit code is 0
• stdout equals an exact value
• stdout equals an exact value

sqly –output requires –sql #

Source: test/e2e/tools/sqly/output_requires_sql.atago.yaml

Scenario: rejects –output with no query #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly user.csv --output out.csv

Then #

• exit code is 1
• stderr contains --output requires --sql
• file out.csv does not exist

Scenario: rejects –output for batch SQL from stdin #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

SELECT user_name FROM user ORDER BY identifier LIMIT 1

When #

sqly user.csv --output out.csv

Then #

• exit code is 1
• stderr contains --output requires --sql
• file out.csv does not exist

sqly file-output status routing #

Source: test/e2e/tools/sqly/output_status.atago.yaml

Scenario: keeps stdout empty for –output and reports on stderr #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

When #

sqly --sql "SELECT 1 AS x" --output out.csv user.csv

Then #

• exit code is 0
• stdout is empty
• stderr contains Output sql result to
• file out.csv exists

Generated artifacts #

• out.csv

Scenario: keeps stdout free of the .dump status line #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1

stdin for sqly:

.dump user dump.csv

When #

sqly user.csv

Then #

• exit code is 0
• stdout is empty
• stderr contains dump user table to
• file dump.csv exists

Generated artifacts #

• dump.csv

Scenario: keeps the .save confirmation off stdout #

Given #

• Fixture file u.csv is created.

Inputs #

Fixture u.csv:

user_name,identifier
booker12,1

stdin for sqly:

UPDATE u SET user_name = 'X' WHERE identifier = 1;
.save saved

When #

sqly u.csv

Then #

• exit code is 0
• stdout contains affected
• stdout does not contain Saved
• stderr contains Saved u to

sqly parquet export #

Source: test/e2e/tools/sqly/parquet_export.atago.yaml

Scenario: writes a parquet file that re-imports with the same rows #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

.mode parquet
.dump user user.parquet

When #

sqly user.csv
sqly --csv --sql "SELECT COUNT(*) AS c FROM user" user.parquet

Then #

• after sqly user.csv:
  • stderr contains Change output mode
  • file user.parquet exists
• after sqly --csv --sql "SELECT COUNT(*) AS c FROM user" user.parquet:
  • exit code is 0
  • stdout equals an exact value

Generated artifacts #

• user.parquet

Scenario: appends the .parquet extension #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

stdin for sqly:

.mode parquet
.dump user result

When #

sqly user.csv

Then #

• file result.parquet exists

Generated artifacts #

• result.parquet

Scenario: writes query results to the given parquet path with –output #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --parquet --output q.parquet --sql "SELECT user_name FROM user LIMIT 2" user.csv

Then #

• exit code is 0
• stderr contains q.parquet
• file q.parquet exists

Generated artifacts #

• q.parquet

Scenario: preserves leading-zero codes through a parquet round-trip #

When #

sqly --parquet --output codes.parquet --sql "SELECT '007' AS code, '00042' AS acct"
sqly --csv --sql "SELECT code, acct FROM codes" codes.parquet

Then #

• after sqly --csv --sql "SELECT code, acct FROM codes" codes.parquet:
  • exit code is 0
  • stdout contains 007, 00042

Scenario: preserves SQL NULL through a parquet round-trip #

When #

sqly --parquet --output n.parquet --sql "SELECT CAST(NULL AS TEXT) AS id, 'A' AS name UNION ALL SELECT '1' AS id, 'B' AS name"
sqly --json-typed --sql "SELECT * FROM n" n.parquet

Then #

• after sqly --json-typed --sql "SELECT * FROM n" n.parquet:
  • exit code is 0
  • stdout contains "id":null
  • stdout does not contain "id":""

Scenario: reports a clear error when exporting an empty result #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --parquet --output empty.parquet --sql "SELECT user_name FROM user WHERE 1=0" user.csv

Then #

• exit code is 1
• stderr contains empty result

sqly input path validation #

Source: test/e2e/tools/sqly/path_validation.atago.yaml

Scenario: imports a deeply nested path #

Given #

• Fixture file a/b/c/d/e/f/g/h/i/j/k/user.csv is created.

Inputs #

Fixture a/b/c/d/e/f/g/h/i/j/k/user.csv:

user_name,identifier
booker12,1
jenkins46,2
grey07,3

When #

sqly --csv --sql "SELECT COUNT(*) AS c FROM user" a/b/c/d/e/f/g/h/i/j/k/user.csv

Then #

• exit code is 0
• stdout equals an exact value

Scenario: imports a file whose name literally contains ..%2f #

Given #

• Fixture file ..%2fuser.csv is created.

Inputs #

Fixture ..%2fuser.csv:

user_name,identifier
booker12,1

When #

sqly --inspect "..%2fuser.csv"

Then #

• exit code is 0
• stdout contains user_name
• stderr does not contain dangerous path pattern

Scenario: rejects a symlink alias that resolves to a blocked system path #

skipped on Windows

When #

ln -s /etc/hosts hosts_alias.csv
sqly --inspect hosts_alias.csv

Then #

• after sqly --inspect hosts_alias.csv:
  • exit code is 1
  • stderr contains system directory not allowed

Scenario: imports a symlink alias that resolves to an ordinary user file #

skipped on Windows

Given #

• Fixture file real.csv is created.
• Fixture file user_alias.csv is created.

Inputs #

Fixture real.csv:

user_name,identifier
booker12,1

When #

sqly --inspect user_alias.csv

Then #

• exit code is 0
• stdout contains user_name

sqly –profile workflow #

Source: test/e2e/tools/sqly/profile.atago.yaml

Scenario: reports per-column data quality as JSON #

Given #

• Fixture file messy.csv is created.

Inputs #

Fixture messy.csv:

id,score,note
1,10, hi
2,abc,
3,30,N/A

When #

sqly --profile messy.csv

Then #

• exit code is 0
• stdout contains "row_count": 3, "column_count": 3, mixed numeric and non-numeric, null placeholders

Scenario: profiles a stdin dataset #

Inputs #

stdin for sqly:

id,name
1,Alice
2,Bob

When #

sqly --stdin csv --profile

Then #

• exit code is 0
• stdout contains "name": "stdin", "row_count": 2

Scenario: profiles multiple tables in one run #

Given #

• Fixture file messy.csv is created.
• Fixture file orders.csv is created.

Inputs #

Fixture messy.csv:

id,score,note
1,10, hi
2,abc,
3,30,N/A

Fixture orders.csv:

oid,amount
1,9.99
2,5.00

When #

sqly --profile messy.csv orders.csv

Then #

• exit code is 0
• stdout contains "name": "messy", "name": "orders"

Scenario: emits a human-readable summary with –profile-format text #

Given #

• Fixture file orders.csv is created.

Inputs #

Fixture orders.csv:

oid,amount
1,9.99
2,5.00

When #

sqly --profile --profile-format text orders.csv

Then #

• exit code is 0
• stdout contains table orders: 2 rows, 2 columns

Scenario: counts a blank string as a distinct value in JSON output #

Given #

• Fixture file blank.csv is created.

Inputs #

Fixture blank.csv:

id,v
x,
x,A

When #

sqly --profile blank.csv

Then #

• stdout contains "blank_count": 1, "distinct_count": 2

Scenario: counts a blank string as a distinct value in text output #

Given #

• Fixture file blank.csv is created.

Inputs #

Fixture blank.csv:

id,v
x,
x,A

When #

sqly --profile --profile-format text blank.csv

Then #

• stdout contains blanks=1 distinct=2

Scenario: flags a padded null-like placeholder and its whitespace together #

Given #

• Fixture file nullspace.csv is created.

Inputs #

Fixture nullspace.csv:

v
" NULL "

When #

sqly --profile nullspace.csv

Then #

• stdout contains null placeholders, leading or trailing whitespace

Scenario: warns only about whitespace for a padded ordinary value #

Given #

• Fixture file padded.csv is created.

Inputs #

Fixture padded.csv:

v
" hello "

When #

sqly --profile padded.csv

Then #

• stdout does not contain null placeholders
• stdout contains leading or trailing whitespace

Scenario: counts comma-formatted numerals as numeric, matching table-mode #

Given #

• Fixture file commas.csv is created.

Inputs #

Fixture commas.csv:

amount
"1,000"
"2,500"

When #

sqly --profile commas.csv

Then #

• stdout contains "numeric_count": 2
• stdout does not contain mixed numeric

Scenario: right-aligns the same comma-formatted column in table-mode #

Given #

• Fixture file commas.csv is created.

Inputs #

Fixture commas.csv:

amount
"1,000"
"2,500"

When #

sqly --sql "SELECT * FROM commas" commas.csv

Then #

• exit code is 0
• stdout contains | 1,000 |

README examples #

Source: test/e2e/tools/sqly/readme_examples.atago.yaml

Scenario: prints the full user table as an ASCII table #

Given #

• Fixture file user.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier,first_name,last_name
booker12,1,Rachel,Booker
jenkins46,2,Mary,Jenkins
smith79,3,Jamie,Smith

When #

sqly --sql "SELECT * FROM user" user.csv

Then #

• exit code is 0
• stdout contains user_name, booker12, smith79

Scenario: joins two files on a shared key #

Given #

• Fixture file user.csv is created.
• Fixture file identifier.csv is created.

Inputs #

Fixture user.csv:

user_name,identifier,first_name,last_name
booker12,1,Rachel,Booker
jenkins46,2,Mary,Jenkins
smith79,3,Jamie,Smith

Fixture identifier.csv:

id,position
1,developrt
2,manager
3,neet

When #

sqly --sql "SELECT user_name, position FROM user JOIN identifier ON user.identifier = identifier.id" user.csv identifier.csv

Then #

• exit code is 0
• stdout contains position, developrt

Scenario: runs the analytics script (CTE + window + GROUP BY) #

Given #

• Fixture file actor.csv is created.
• Fixture file analytics.sql is created.

When #

sqly --sql-file analytics.sql actor.csv

Then #

• exit code is 0
• stdout contains Harrison Ford, 50+ movies

Scenario: extracts JSON fields from a JSONL file #

Given #

• Fixture file sample.jsonl is created.
• Fixture file json.sql is created.

When #

sqly --sql-file json.sql sample.jsonl

Then #

• exit code is 0
• stdout contains Charlie, Nagoya

Scenario: reads a gzipped CSV transparently #

Given #

• Fixture file user.csv.gz is created.

When #

sqly --csv --sql "SELECT user_name FROM user ORDER BY identifier LIMIT 1" user.csv.gz

Then #

• exit code is 0
• stdout equals an exact value

Scenario: queries a Parquet file #

Given #

• Fixture file products.parquet is created.

When #

sqly --csv --sql "SELECT name FROM products ORDER BY CAST(price AS REAL) DESC LIMIT 1" products.parquet

Then #

• exit code is 0
• stdout equals an exact value

Scenario: joins a compressed CSV with a plain CSV #

Given #