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

sqly is a powerful command-line tool that can execute SQL against CSV, TSV, LTSV, and Microsoft Excel™ files. The sqly import those files into SQLite3 in-memory database.

The sqly has sqly-shell. You can interactively execute SQL with sql completion and command history. Of course, you can also execute SQL without running the sqly-shell.

# Works with compressed files!
sqly --sql "SELECT * FROM data" data.csv.gz
sqly --sql "SELECT * FROM logs WHERE level='ERROR'" logs.tsv.bz2

go install github.com/nao1215/sqly@latest

brew install nao1215/tap/sqly

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

The sqly automatically imports CSV/TSV/LTSV/Excel files (including compressed versions) into the DB when you pass file path as an argument. 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).

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

--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      |
+-----------+-----------+

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

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
      .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)
        .ls: print directory contents
      .mode: change output mode
       .pwd: print current working directory
    .tables: print tables

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

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 

sqly now uses filesql library for enhanced performance and functionality. The filesql package was created to provide a standardized SQL interface for working with various file formats, making it easier for developers to build similar tools.

Key advantages of filesql integration:

• Better Performance: Optimized bulk insert operations with transaction batching

• Automatic Type Detection: Numbers are properly sorted and handled as numeric types

• Compressed File Support: Built-in support for .gz, .bz2, .xz, .zst files

• Standardized Interface: Uses familiar sql.DB interface.

• Official documentation for users & developers: https://nao1215.github.io/sqly/

• Alternative tool created by the same developer: simple terminal UI for DBMS & local CSV/TSV/LTSV

sqly now supports compressed files! You can directly process:

• Gzip compressed files (.csv.gz, .tsv.gz, .ltsv.gz, .xlsx.gz)
• Bzip2 compressed files (.csv.bz2, .tsv.bz2, .ltsv.bz2, .xlsx.bz2)
• XZ compressed files (.csv.xz, .tsv.xz, .ltsv.xz, .xlsx.xz)
• Zstandard compressed files (.csv.zst, .tsv.zst, .ltsv.zst, .xlsx.zst)

• filesql Integration: Enhanced performance and functionality using the filesql library
• Improved Performance: Bulk insert operations with transaction batching for faster file processing
• Better Type Handling: Automatic type detection ensures proper numeric sorting and calculations
• Compressed File Support: Native support for .gz, .bz2, .xz, and .zst compressed files

• JSON Support: JSON file format support has been removed in favor of focusing on structured data formats (CSV, TSV, LTSV, Excel)
  • Use CSV export from JSON tools if you need to process JSON data with sqly
  • The removal allows for better optimization of the core file formats

• The --json flag has been removed
• JSON files (.json) are no longer supported as input
• Numeric formatting in output may differ slightly due to improved type detection

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

• DDL such as CREATE
• DML such as GRANT
• TCL such as Transactions

First off, thanks for taking the time to contribute! See CONTRIBUTING.md for more information. Contributions are not only related to development. For example, GitHub Star motivates me to develop!

Please see the document, section "Document for developers".

When adding new features or fixing bugs, please write unit tests. The sqly is unit tested for all packages as the unit test tree map below shows.

If you would like to send comments such as "find a bug" or "request for additional features" to the developer, please use one of the following contacts.

• GitHub Issue

The sqly project is licensed under the terms of MIT LICENSE.

Thanks goes to these wonderful people (emoji key):

CHIKAMATSU Naohiro 💻 📖

Add your contributions

This project follows the all-contributors specification. Contributions of any kind welcome!