sqly

README

sqly - execute SQL against CSV easily

sqly command imports CSV file(s) into an in-memory DB and executes SQL against the CSV. 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 file into the DB when you pass a CSV file as an argument. DB table name is the same as the CSV 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.

$ sqly --sql 'SELECT * FROM user' testdata/user.csv 
+-----------+------------+------------+-----------+
| user_name | identifier | first_name | last_name |
+-----------+------------+------------+-----------+
| booker12  |          1 | Rachel     | Booker    |
| jenkins46 |          2 | Mary       | Jenkins   |
| smith79   |          3 | Jamie      | Smith     |
+-----------+------------+------------+-----------+

run sqly shell

If the --sql option is not specified, the sqly shell is started. sqly command arguments may or may not specify CSV file(s). The sqly shell functions similarly to a common SQL client (e.g., sqlite3 command or mysql command). sqly shell has helper commands and SQL execution history management.

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.0.7 (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
      .help: print help message
    .import: import csv file(s)
    .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

Execute query sample

sqly> .tables                                                                                      
+------------+
| TABLE NAME |
+------------+
| user       |
| identifier |
+------------+
sqly> SELECT user_name, position FROM user INNER JOIN identifier ON user.identifier = identifier.id
+-----------+-----------+
| user_name | position  |
+-----------+-----------+
| booker12  | developrt |
| jenkins46 | manager   |
| smith79   | neet      |
+-----------+-----------+
sqly> UPDATE user SET user_name = 'nchika' WHERE identifier = '2'                                  
affected is 1 row(s)
sqly> SELECT * FROM user WHERE identifier = '2'                                                    
+-----------+------------+------------+-----------+
| user_name | identifier | first_name | last_name |
+-----------+------------+------------+-----------+
| nchika    |          2 | Mary       | Jenkins   |
+-----------+------------+------------+-----------+

Features to be added

• import json
• import tsv
• import swagger
• 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

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.