LiteCLI – A user-friendly command-line client for SQLite database | Hacker News Reader

LiteCLI – A user-friendly command-line client for SQLite database

pgcli.com

316 pointsluord38 comments

Slightly off topic - one of the authors, Amjith Ramanujam who is active here, gave a great talk at Pycon 2017: https://www.youtube.com/watch?v=hJhZhLg3obk

It covers the powerful features of the prompt-toolkit library that is used to make this client.

Thank you for the kind words. It's always nice to hear from happy customers .

Neat, this is much nicer than the stock sqlite3 CLI client! The pager behavior is a bit annoying, actually, since if my SELECT has more columns than can fit on screen, the pager switches to an alternate screen and when I quit, my results are hidden from me. Is it possible to turn this behavior off?

[append] Off for a specific query, I mean. I think I'd prefer to leave it on by default.

I have a few recommendations for you.

We use `less -XRF` by default. You can change this to whatever you want via the config file `~/.config/litecli/config`. Just uncomment the line that says `# pager = "less -SXRF".

Adding the `-S` to the list of options will make it much better. It will not wrap long lines but instead it will enable horizontal scrolling (using arrow keys). Check for pager section on this page for some details: https://litecli.com/output/

The other option is to set the `auto_vertical_output = True` in the config file.

This option will cause results to be displayed vertically if they are too wide for the current window.

Thanks! I didn't even realize there was a documentation section. Looking closer, I probably missed it because it's called "Features" rather than "Documentation". I'll definitely use this for my daily SQLite work!

Doesn't seem to support sqlite3 dot commands such as .mode/.separator/.import/.output/.headers... (these are really implemented in sqlite3 CLI and not part of the sqlite library). These are, at least for me, among the main reasons to use the CLI in conjunction with SQLite. (The SQLite CLI is very good for massaging CSV data into the correct shape; as far as I know there is no canonical file extension for CLI scripts, I use .sqs, and the CLI has no parameter to run scripts directly, so you have to start the CLI manually and do .read foo.sqs).

We don't support all of the special commands but we do support a small subset of them. We support .mode .schema, .table and a few others.

>and the CLI has no parameter to run scripts directly, so you have to start the CLI manually and do .read foo.sqs).

It does work, but only sort of. Just did:

$ sqlite3 -init select_contact.sql test_sqlite.db

-- Loading resources from select_contact.sql

Alan|Acre|50|Male|100.0

Betty|Smith|20|Female|200.0

Charlie|Copenhagen|30|Male|400.0

Dorothy|Delhi|80|Female|400.0

Edwin|Bombay|40|Male|100.0

Edwin|Pune|55|Female|110.0

SQLite version 3.8.11 2015-07-27 13:49:41

Enter ".help" for usage hints.

sqlite>

where select_contact.sql contains "select * from contact;"

So it can run a script given as a parameter.

But it leaves you in the CLI instead of cleanly exiting. And prints those extra headers, etc. Not very Unix-philosophy-adherent, it seems.

Edit: formatting

Missed opportunity to call it CLite? :)

Damn it! :)

Would you consider changing pip to pip3 in your command? Lead the way with good practice?

I'm not sure how that is good practice?

Its a Python3 script, shouldn't it use pip3 ?

  I installed it with: $ sudo pip3 install litecli

  (seemed to work)

It's compatible with python2

Dont use sudo pip, it can break your system.

Pip3 is usually a sign that you're installing into your system python packages instead of a virtualenv. The usual best practice is to use a virtualenv.

Please don't. That's the sort of name that makes it impossible to talk about the project, let alone use it at work.

I see that this is based off of pgcli and the link above is from pgcli's blog page; presumably ownership is same between these two projects. What is the difference between LiteCLI and pgcli? I could not find it in the blog post nor the web page

DBCLI (https://www.dbcli.com/) is an open source org that made pgcli, mycli, mssql-cli etc.

LiteCLI is part of that org. So you can think of it as a clone of mycli that happens to work with SQLite.

The code base is largely similar between mycli and litecli. The changes are to support the SQLite backend and to support the dot commands (like .tables, .mode etc).

So the feature set should be same.

The DBCLI tools are not created by a single individual (anymore). We have a team of core devs and the lead maintainer for pgcli, mycli and litecli are all different people.

I have been using pgcli itself for many years and it is a fantastic piece of software. If you're a terminal user that deals with Postgresql, I highly recommend you try out pgcli!

This is fantastic - really pleasant to use, and a very obvious upgrade to the default client.

Thank you, Simon. That means a lot coming from the author of datasette. I love your work.

Very cool. I tried it on Windows 8.1 and it works with some issues. For instance, I downloaded the SQLite sample DB (http://www.sqlitetutorial.net/sqlite-sample-database/). For some tables (albums, artists), doing a select * from table results in :

'charmap' codec can't encode character u'\xe3' in position 2052: character maps to <undefined>

Feature request: allow for snippet expansion. For instance, when I type 'ssf' + tab, it should expand to Select * From and show the table dropdown.

Looks like nice, will definitely use this.

Without knowing either program very well, how does it compare feature-wise to "DB Browser for SQLite" (apart from the GUI) ?

This is awesome! I use pgcli everyday and love it so much. This is gonna be a great addition to my SQLite toolbelt!

Personally I like to use ptipython (think a better version of the CLI for ipython) and SQLAlchemy whenever I'm debugging my SQLite databases. It's light, can install most places, and has my JEDI autocomplete.

«Lit»+Tab will now be one of my most used commands on Ubuntu. Thank you!

Looks great!

What would be neccessary if someone would like to integrate it with osqueryi?

Ah, this is very nice! Also a great tool to teach people SQL :)

Interesting. I love SQLite and this looks awesome :) Hopefully I'll report back later with some criticism.

Awesome I wanted to create something similar when I saw pgcli but it seems you beat me to the punch!

Awesome! Very clean design :)

Going to start using this every time I work with a sqlite database now!

Does this go through the same ridged testing as SQLite?

Considering that it is a third-party product, I would say no.

Haha. No.

We have a large set of tests, but the coverage is no where close to the level that SQLite has.

AFAIK the CLI has tests but not to the depth and detail the main library is tested. Makes sense, since the CLI is often not shipped in applications.

Thanks for building this!

Reply on news.ycombinator.com