sqly - execute SQL against CSV / TSV / LTSV / JSON with shell

#showdev #sql #go

I have released a tool that execute SQL against CSV, TSV, LTSV, or JSON. It's called sqly. I would like to introduce it to you.

Why I wrote sqly command

I was frustrated with Excel, Numbers, and Google Spread Sheets crashing when I opened huge CSV files at work. I was wasting about 10~15 minutes of my time just to look up a writing error in one column. So, I immediately started thinking "I want to execute SQL against CSV in the terminal".

Apparently, there were many who thought the same thing; Tools to execute SQL against CSV were trdsql, q, csvq, TextQL. They were highly functional, hoewver, had many options and no input completion. I found it just a little difficult to use.

I really wanted input completion for SQL syntax. Therefore, I started developing a user friendly tool with input completion.

What can the sqly command do

The sqly imports CSV and JSON into SQLite3 (in memory mode). Therefore, the same SQL syntax as SQLite3 can be used. The sqly is written in Golang and distributed in a single binary.

If SQL is not passed to sqly as an argument, the sqly shell is strated. The sqly shell is similar to the client commands such as sqlite3, mysql, etc. The sqly shell has input completion, execution history, emacs key bindings, and helper commands.

$ sqly testdata/user.csv 
sqly v0.2.1 (work in progress)

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

sqly> .tables
+------------+
| TABLE NAME |
+------------+
| user       |
+------------+
sqly> .header user
+------------+
|    user    |
+------------+
| user_name  |
| identifier |
| first_name |
| last_name  |
+------------+
sqly> SELECT user_name, last_name  FROM user LIMIT 1
+-----------+-----------+
| user_name | last_name |
+-----------+-----------+
| booker12  | Booker    |
+-----------+-----------+
sqly> .import testdata/sample.json
sqly> SELECT id, age, first_name FROM sample LIMIT 1
+----+-----+------------+
| id | age | first_name |
+----+-----+------------+
|  1 |  43 | Dave       |
+----+-----+------------+

Of course, sqly can execute SQL without the sqly shell.

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

sqly can also convert files from CSV to JSON, JSON to CSV. However, json must be simple data structure.

$ 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

Conclusion

The sqly is designed to be more user-friendly than other tools. The sqly is a tool that has just been released. It will get better from now on. I will be happy if sqly solves your issues!! Thank you.

https://github.com/nao1215/sqly