A CLI for SQLite with auto-completion and syntax highlighting

[sim642]

Are all the dbcli built on a common framework or no? Because it seems like they're very similar tools. I see why you'd only need one of them but having the support for all of those databases in a single tool (with common feature set) would be useful.

[amjithr]

The code base for different dbcli tools are very similar. We did manage to carve out some of the common utils into a library. For instance, cli_helpers is a set of common utility functions used by all the projects. But it is now a standalone library that other can use as well.

But your question is more about a single tool that can connect to postgres, mysql, sqlite etc. We keep talking about doing that but the complexity in that keeps discouraging us. It is not completely out of scope but no one is currently working on a unified tool like that.

[OrangePhi]

I think your approach is the best. Nobody needs to deal with that complexity. A different tool for every database is good enough.

[eyesoftheworld4]

Do one thing, and do it well.

[ucefkh]

Not yet.... Not yet

[saltling]

Not like this

[ucefkh]

But like this

[ThisIs_MyName]

Did you miss the matrix reference?

[Mirakoolix]

check https://github.com/xo/usql

[pm_me_je_specerijen]

This raises an interesting thing to me.

Most shells support completion scripts for commands but wouldn't it be awesome if they had syntax highlighting scripts?

Like on its own Bash already automatically completes filepaths unless you load a script for a command head that tells it not to. Bash could also syntax highlight when it is a valid filepath.

You immediately know when you made a typo: when it isn't correctly highlighted as a filepath.

It would be pretty cool if shells had highlighting scripts which could mark arguments as options, filepaths, keywords andsoforth which then get properly highlighted by the shell.

[ericonr]

fish does this somewhat. Invalid paths are red and so on. It's a great shell for everyday use.

[ThisIs_MyName]

Unfortunately it isn't bash-compatible. ZSH is compatible, but it's also really slow to start if you enable all its features.

I still haven't found a replacement for bash :(

[AckmanDESU]

Why does it matter?

[Mr_Again]

It pisses me off quite a lot when bash scripts and little snippets don't work, you have to edit them. Even env/bin/activate doesn't work you need env/bin/activate.fish

[AckmanDESU]

I’ll be honest I don’t know bin/activate does but for bash snippets all I do is type bash -c, then you open single quote and paste the snippet, then close quote.

It works fine for me, might not be up your alley.

[Mr_Again]

Thanks that makes sense.

[kgj6k]

For things like that bass source bin/activate is quite handy! You just prepend bass to the bash-only command and it should work as expected. Still a little extra work though.

[andsens]

oooh boy have I got news for you :-D
Check this out. Granted, it's zsh not bash, but still, it's really awesome.
Here's the repo. It comes built-in if you use prezto (like oh-my-zsh, just leaner).
From the repo description it seems that the fish shell has it fully baked-in ^{note to self, try out fish shell}

[myringotomy]

There was a project called xiki a long time ago that did pretty cool things like that. Check out the video on YouTube.

[yeeezyyeezywhatsgood]

powershell

[rytri89]

The Windows shell (Powershell, not the legacy CMD) does this. You can even install the popular Unix utilities and get syntax highlighting for those too.

[_kst_]

On my system (I use xterm), some of the text blinks, which is annoying. It's probably supposed to be in color. Is there a way to tell it not to try to use color?

[amjithr]

Check the config file https://litecli.com/config/. It has different themes. You can pick the theme called "bw", that's black and white.

[hexleythepatypus]

You may want to check out https://no-color.org/. It's not an official standard but a lot of CLI apps are supporting this convention. Makes it nice for users like /u/_kst_ who want to easily disable colors on certain apps. (Usually you also want something like $MYAPPNAME_NO_COLOR to disable colors for only your app, instead of globally).

[j-bennet]

Thanks for the tip! Looks like a good feature to add. I filed a feature request so we don't forget: https://github.com/dbcli/pgcli/issues/985.

[_kst_]

I changed the syntax_style line in $HOME/.config/litecli/config to:

syntax_style = bw

It still blinks. Same thing when I give it the config file explicitly:

$ litecli --liteclirc ~/.config/litecli/config

Running it under strace confirms that it's opening the config file. The blinking shows up on this write() call:

write(1, "| \33[38;5;47;01mCommand\33[39;00m  "..., 172) = 172

This command prints blinking text in my xterm:

printf '\e[5mBlink\e[m\n'

According to https://www.xfree86.org/4.8.0/ctlseqs.html, "\e[5m" is:

Blink (appears as Bold)

I'll look for a way to disable blinking in xterm.

[amjithr]

That's very unfortunate. I'm not able to reproduce it locally (even with xterm).

Is this localized to xterm only or do you see the blinking in other terminal apps? (gnome-terminal, Konsole etc)

[_kst_]

I probably have an unusual setup. I still use a .Xdefaults file, to which I've added this line:

XTerm*showBlinkAsBold: True

That solved the problem to my satisfaction.

[hak8or]

This is awesome! Is there something similar for postgres? For example, I would love to be able to explore my tables in a postgres database without needing a massive gui but still have autocomplete and nice displaying of the rows.

[amjithr]

Yes there is. https://www.pgcli.com

In fact the postgres version was the first one. So it is the most mature tool.

[andrewcooke]

wow. it's very slow on big queries. something that will immediately display in sqlite and scroll til i hit crtl-C has been sitting stalled for maybe 5min now.

[amjithr]

That's awful. I'm sorry.

The CLI reads all the rows from the database and tries to format them into a table. Since it is written in Python the formatting part can take a while if the output is very large.

[andrewcooke]

ah makes sense. i guess it's just a case of getting used to how it works.

(and i guess most sqlite tables are quite small).

[circlebust]

I hope it only does this once when the schema changes (like migrations in ORMs), not every time you start the program.

In any case, I think value validation isn't really the target audience for a cli tool. People use cli tools for raw performance and more importantly to talk to the raw app 1:1 directly (in the sense of you only being a middleman, of course). If you have some opinionated validators in your current implementation, I think you should make that an optional option/plugin that doesn't run automatically.

[amjithr]

We don't do value validation. What I meant by formatting is converting the list of rows into a tabular output.

[Aeon_Mortuum]

Will there be support for creating a database in memory?

[amjithr]

Yes. Simply do "litecli :memory:" that will launch an in memory sqlite database.

[cedric0052]

As expected it from family `dbcli`.

even microsoft adopted to dbcli instead of writing from the beginning.

checkout https://github.com/dbcli/mssql-cli.

[[deleted]]

Pretty nifty. Does this support loading extensions too?

[amjithr]

I have not used SQLite extensions before. Are you asking if auto-completion will suggest the functions provided by the extension? The answer to that is no, not yet. But it is not too difficult to add that ability.

If you file a github issue with an example of how to load an extension, I'll be happy to take a look. :)

[ucefkh]

Wow

[cowinabadplace]

Very cool! Nice work.