execute SQL against CSV/TSV/LTSV/JSON with shell.

sqly command imports CSV/TSV/LTSV/JSON file(s) into an in-memory DB and executes SQL against the CSV/TSV/LTSV/JSON. sqly uses SQLite3 as its DB. So, sql syntax is same as SQLite3.

The sqly command 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.

How to install

Use "go install"

If you does not have the golang development environment installed on your system, please install golang from the golang official website.

$ go install github.com/nao1215/sqly@latest

※ Main dependency is github.com/mattn/go-sqlite3 and gcc.

How to use

sqly command automatically imports the CSV/TSV/LTSV/JSON file into the DB when you pass a CSV/TSV/LTSV/JSON file as an argument. DB table name is the same as the file name (e.g., if you import user.csv, sqly command create the user table)

--sql option: execute sql in terminal

--sql option takes an SQL statement as an optional argument. You pass file path(s) as arguments to the sqly command. sqly command import them. sqly command automatically determines the file format from the file extension.

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

Change output format

sqly command output sql results in ASCII table format, CSV format (--csv option), TSV format (--tsv option), LTSV format (--ltsv option) and JSON format (--json option). This means that conversion between csv and json is supported.

$ 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

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

$ sqly --sql "SELECT * FROM user LIMIT 2" --json testdata/user.csv > user.json

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

run sqly shell

If the --sql option is not specified, the sqly shell is started. When you execute sqly command, it is optional whether or not to specify file(s). The sqly shell functions similarly to a common SQL client (e.g., sqlite3 command or mysql command). sqly shell has helper commands, SQL execution history management and input complement.

sqly helper command

The command beginning with a dot is the sqly helper command; I plan to add more features in the future to make the sqly shell run more comfortably.

$ sqly 
sqly v0.3.0 (work in progress)

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

sqly> .help
      .dump: dump db table to csv file
      .exit: exit sqly
    .header: print table header
      .help: print help message
    .import: import csv file(s)
      .mode: change output mode
    .tables: print tables

Output sql result to file

For linux user

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

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

For windows user

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

All options

[OPTIONS]
  -c, --csv             change output format to csv (default: table)
  -h, --help            print help message
  -j, --json            change output format to json (default: table)
  -l, --ltsv            change output format to ltsv (default: table)
  -m, --markdown        change output format to markdown (default: table)
  -o, --output string   destination path for SQL results specified in --sql option
  -s, --sql string      sql query you want to execute
  -t, --tsv             change output format to tsv (default: table)
  -v, --version         print help message

Features to be added

• import json
• print json format
• dump json file
• import tsv
• ptint tsv format
• dump tsv file
• import ltsv
• print ltsv format
• dump ltsv file
• import swagger
• print markdown format
• ignore csv header option
• The file type is determined by the file extension. This specification is to reduce the number of options.
• change input position (left arrow, right arrow, delete char)
• sqly Fix problem where multiple lines of input in the shell would cause display corruption. To be fixed using escape sequences.
• input completion (Tab)
• emacs key-bindings (Ctrl-a, Ctrl-e, Ctrl-w)
• history search (Ctrl-r)
• Convert CSV character encoding to UTF-8 if necessary
• Unit test coverage 80%

Limitions (Not support)

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

Contributing

First off, thanks for taking the time to contribute! ❤️ Contributions are not only related to development. For example, GitHub Star motivates me to develop!

Contact

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

LICENSE

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