Sqly

<!-- ALL-CONTRIBUTORS-BADGE:START - Do not remove or modify this section -->

<!-- ALL-CONTRIBUTORS-BADGE:END -->

日本語 | Русский | 中文 | 한국어 | Español | Français

sqly is a command-line tool that executes SQL against CSV, TSV, LTSV, JSON, JSONL, Parquet, Microsoft Excel, ACH, and Fedwire files. It imports those files into an SQLite3 in-memory database. Compressed files (.gz, .bz2, .xz, .zst, .z, .snappy, .s2, .lz4) are also supported. CTE (WITH clause) is available for complex queries.

sqly has an interactive shell (sqly-shell) with SQL completion and command history. You can also execute SQL directly from the command line without the shell.

sqly --sql "SELECT * FROM data" data.csv.gz
sqly --sql "SELECT * FROM logs WHERE level='ERROR'" logs.tsv.bz2

How to install

Use "go install"

go install github.com/nao1215/sqly@latest

Use homebrew

brew install nao1215/tap/sqly

Supported OS & go version

• Windows
• macOS
• Linux
• go1.25.0 or later

How to use

The sqly automatically imports CSV/TSV/LTSV/JSON/JSONL/Parquet/Excel/ACH/Fedwire files (including compressed versions for tabular formats) into the DB when you pass file paths or directory paths as arguments. You can also mix files and directories in the same command. DB table name is the same as the file name or sheet name (e.g., if you import user.csv, sqly command create the user table).

Note: Table names are sanitized for SQL compatibility. Spaces, hyphens (-), and dots (.) are replaced with underscores (_). Other special characters (e.g., @, #, $) are removed. If the resulting name starts with a digit, a sheet_ prefix is added.

Examples:

• bug-syntax-error.csv → table bug_syntax_error
• 2023-data.csv → table sheet_2023_data
• data@v2.csv → table datav2

Excel Sheet Names

When importing Excel files, table names are created in the format filename_sheetname. Sheet names are also sanitized for SQL compatibility:

• Spaces, hyphens, and dots are replaced with underscores
• Non-ASCII characters (such as accented characters like é) are removed

For example:

• File data.xlsx with sheet A test → table data_A_test
• File report.xlsx with sheet Café → table report_Caf

You can specify a sheet name using the --sheet option with the original name (before sanitization):

$ sqly data.xlsx --sheet="A test"
$ sqly report.xlsx --sheet="Café"

The sqly automatically determines the file format from the file extension, including compressed files.

ACH Files

ACH (Automated Clearing House) files (.ach) are loaded as multiple tables for easy querying:

• {filename}_file_header — file-level header (1 row)
• {filename}_batches — batch header information
• {filename}_entries — entry detail records (main transaction data)
• {filename}_addenda — addenda records

For IAT (International ACH Transactions), additional tables are created: {filename}_iat_batches, {filename}_iat_entries, {filename}_iat_addenda.

$ sqly ppd-debit.ach
$ sqly --sql "SELECT * FROM ppd_debit_entries WHERE amount > 10000" ppd-debit.ach

Fedwire Files

Fedwire files (.fed) are loaded as a single message table:

• {filename}_message — flat table with all FEDWireMessage fields

$ sqly customer-transfer.fed
$ sqly --sql "SELECT * FROM customer_transfer_message" customer-transfer.fed

Execute sql in terminal: --sql option

--sql option takes an SQL statement as an optional argument.

$ sqly --sql "SELECT user_name, position FROM user INNER JOIN identifier ON user.identifier = identifier.id" testdata/user.csv testdata/identifier.csv
+-----------+-----------+
| user_name | position  |
+-----------+-----------+
| booker12  | developrt |
| jenkins46 | manager   |
| smith79   | neet      |
+-----------+-----------+

Directory import

You can import entire directories containing supported files. The sqly automatically detects all supported files (CSV, TSV, LTSV, JSON, JSONL, Parquet, Excel, ACH, Fedwire, including compressed versions) in the directory recursively and imports them:

# Import all files from a directory
$ sqly ./data_directory

# Mix files and directories
$ sqly file1.csv ./data_directory file2.tsv

# Use with --sql option
$ sqly ./data_directory --sql "SELECT * FROM users"

Interactive shell: .import command

In the sqly shell, you can use the .import command to import files or directories:

sqly:~/data$ .import ./csv_files
Successfully imported 3 tables from directory ./csv_files: [users products orders]

sqly:~/data$ .import file1.csv ./directory file2.tsv
# Imports file1.csv, all files from directory, and file2.tsv

sqly:~/data$ .tables
orders
products
users

Change output format

The sqly output sql query results in following formats:

• ASCII table format (default)
• CSV format (--csv option)
• TSV format (--tsv option)
• LTSV format (--ltsv option)

$ sqly --sql "SELECT * FROM user LIMIT 2" --csv testdata/user.csv 
user_name,identifier,first_name,last_name
booker12,1,Rachel,Booker
jenkins46,2,Mary,Jenkins

Run sqly shell

The sqly shell starts when you run the sqly command without the --sql option. When you execute sqly command with file path, the sqly-shell starts after importing the file into the SQLite3 in-memory database.

$ sqly 
sqly v0.10.0

enter "SQL query" or "sqly command that begins with a dot".
.help print usage, .exit exit sqly.

sqly:~/github/github.com/nao1215/sqly(table)$ 

The sqly shell functions similarly to a common SQL client (e.g., sqlite3 command or mysql command). The sqly shell has helper commands that begin with a dot. The sqly-shell also supports command history, and input completion.

The sqly-shell has the following helper commands:

sqly:~/github/github.com/nao1215/sqly(table)$ .help
        .cd: change directory
     .clear: clear terminal screen
      .dump: dump db table to file in a format according to output mode (default: csv)
      .exit: exit sqly
    .header: print table header
      .help: print help message
    .import: import file(s) and/or directory(ies)
        .ls: print directory contents
      .mode: change output mode
       .pwd: print current working directory
    .tables: print tables

Output sql result to file

For linux user

The sqly can save SQL execution results to the file using shell redirection. The --csv option outputs SQL execution results in CSV format instead of table format.

$ sqly --sql "SELECT * FROM user" --csv testdata/user.csv > test.csv

For windows user

The sqly can save SQL execution results to the file using the --output option. The --output option specifies the destination path for SQL results specified in the --sql option.

$ sqly --sql "SELECT * FROM user" --output=test.csv testdata/user.csv 

Key Binding for sqly-shell

|Key Binding |Description| |:--|:--| |Ctrl + A |Go to the beginning of the line (Home)| |Ctrl + E |Go to the end of the line (End)| |Ctrl + P |Previous command (Up arrow)| |Ctrl + N |Next command (Down arrow)| |Ctrl + F |Forward one character| |Ctrl + B |Backward one character| |Ctrl + D |Delete character under the cursor| |Ctrl + H |Delete character before the cursor (Backspace)| |Ctrl + W |Cut the word before the cursor to the clipboard| |Ctrl + K |Cut the line after the cursor to the clipboard| |Ctrl + U |Cut the line before the cursor to the clipboard| |Ctrl + L |Clear the screen|
|TAB |Completion| |↑ |Previous command| |↓ |Next command|

Supported file formats

| Format | Extensions | Notes | |:--|:--|:--| | CSV | .csv | | | TSV | .tsv | | | LTSV | .ltsv | | | JSON | .json | Stored in data column; use json_extract() to query | | JSONL | .jsonl | Stored in data column; use json_extract() to query | | Parquet | .parquet | | | Excel | .xlsx | Each sheet becomes a separate table | | ACH | .ach | Creates multiple tables (_file_header, _batches, _entries, _addenda) | | Fedwire | .fed | Creates a single _message table |

CSV/TSV/LTSV/JSON/JSONL/Parquet/Excel also support the following compression extensions: .gz, .bz2, .xz, .zst, .z, .snappy, .s2, .lz4 (e.g. .csv.gz, .tsv.bz2, .ltsv.xz)

Benchmark

CPU: AMD Ryzen 5 3400G with Radeon Vega Graphics
Execute:

SELECT * FROM `table` WHERE `Index` BETWEEN 1000 AND 2000 ORDER BY `Index` DESC LIMIT 1000

|Records | Columns | Time per Operation | Memory Allocated per Operation | Allocations per Operation | |---------|----|-------------------|--------------------------------|---------------------------| |100,000| 12| 1715818835 ns/op | 441387928 B/op |4967183 allocs/op | |1,000,000| 9| 11414332112 ns/op | 2767580080 B/op | 39131122 allocs/op |

Alternative Tools

|Name| Description| |:--|:--| |nao1215/sqluv|Simple terminal UI for DBMS and local CSV/TSV/LTSV| |[harelba/q](https://github.com/