Command Line Shell for SQLite | Hacker News Reader

Command Line Shell for SQLite

sqlite.org

155 pointshappy-go-lucky59 comments

Some of my coworkers have been dealing with major Ceph issues for the past month. As part of the work, there've been a number of Ceph information dumps, RBD image information, PG information, and lists of incomplete PDs, all in text or CSV files. To help out my coworkers, I wrote up Python code to parse all of those files into a SQLite database. Why? Well, because I can ship the database to anywhere, as one file, and it can be accessed through the SQLite CLI.

In another case, a client was building a list of files to consider for deletion. The list included path, size, type (file or directory), and owning user. Before doing anything else, I imported the entire thing into SQLite using the `.import` command. Which was good, because it was able to identify cases of files that included a tab in their paths!

I do exactly the same with traditional RDMS. Export to single file isn't nothing new.

Every time I see a message from you it's a variant of "back in my day", "this isn't New" or some other mildly snooty point about kids these days. At this point I'm curious what even brings you to consistently comment like this. I apologise for the ad hominem but frankly the pattern is frequent and irritating?

Could you at least describe your use case or tools please? Share the nostalgia or something, anything.

His posts are ridiculous. I’ve never once seen him say anything positive. It’s always condescending, presumptuous and elitist bullcrap. How he’s ended up with so much karma when his comments are consistently scraping the barrel is beyond me.

> His posts are ridiculous.

I went through some and it doesn't seem so. Yes, he is opinionated, but so are many HN-ers. His set of opinions is quite specific though (based on the comments I see, MS SQL Server users are a minority here, for example) so you might have that impression.

> How he’s ended up with so much karma

We're supposed to be adults but we still fall for this behavior engineering trick called karma. Really, who cares? What does it change in my life if I have a few points more or less?

One could even argue that you can create more value for the community by lowering your karma, making comments that are alien to the collective mindhive. A downvote is almost impulsive in such cases, but your comment (before it greys out) can spark a thought, an interest, an idea, or even some important doubt that would otherwise not arise.

I’m a big advocate that incorrect comments shouldn’t be downvoted, just corrected. Incorrect comments are valuable if they are corrected with informative comments.

But there is a difference between incorrect comments and comments that are broad generalisations presented in an obnoxious way. The former sparks informative conversations whereas the latter adds little informative value while pissing people off. I suspect the real value of the latter is just likeminded people patting themselves on the back for being “awesome”. Which I don’t particularly rate as a positive effect for discourse let alone the wider HN community

That is ironic because this entire web site consists of what appears to be like-minded people constantly patting each other on the back because they're so awesome; meanwhile they leave a lot to be desired, and the person you're criticizing so staunchly isn't necessarily one those persons... This website has turned into a hangout for "brogrammers", that's why I almost never participate here any more. Now I'll end up writing even less, I think.

> That is ironic because this entire web site consists of what appears to be like-minded people constantly patting each other on the back because they're so awesome

I didn’t used to be that way and there are still plenty of folk on here who aren’t just posting to sound clever. So I don’t think it’s fair to cast the same judgement for the entire active member base.

But in terms of your general point, I do agree the signal to noise ratio has gotten a lot worse over the years. Unfortunately that’s just part of the natural evolution of communication platforms. Happens to every message board, IRC channel, Facebook group and so on and so forth.

>based on comments I see, MS SQL Server users are a minority here,

The issue isn't that he's mentioning a specific tool that a minority uses. It's that his solution isn't even appropriate for the gp's scenario that had these 2 desirable properties:

(1) send one file

(2) the file is queryable (with SQL language)

I never downvote pjmlp's posts but the following type of comment isn't just opinionated... its tone will also be perceived as condescending:

- "I do exactly the same with traditional RDMS. Export to single file isn't nothing new."

So to entertain that suggestion, what single file would MS SQL Server export?

option A: Let's say we export to a single text CSV. Ok, we now satisfied the single file property -- and that's if we pre-join all relational tables into a diskspace-hogging output where columns repeat redundant denormalized data. But text files are not easily queryable. You either have to re-import into another tool that can run SQL commands or import into Excel to play with filters.

option B: Let's export to single MDF file. Now we're asking recipients to have MS SQL Server installed and ATTACH the mdf file.

The above "MS SQL Server solutions" are more complicated than a single SQLite file queryable with a zero-install Sqlite CLI utility.

But hey, offering up "alternative solutions" with a condescending tone is regular sport for HN so I guess we all roll with it. (https://news.ycombinator.com/item?id=9224)

When you see my name on a post, skip it, problem solved.

> How he’s ended up with so much karma

He has 65728 karma because of his many posts since December 12, 2010, but his h-index (community impact) is very low compared to others: https://github.com/antontarasenko/smq/blob/master/reports/ha...

This is one of the, leave a small comment on everything type of users.

And as expected you answered it.

I'll take that!

Now, before yinz flip out, you asked a question, and I will give you my honest answer. The way I see it. No fluff.

This was supposed to be the site where the creme de la creme, the best of the best, Silicon Valley stalwarts discuss the cutting edge of computer science.

Instead, n-gate.com correctly identified that it's a web site where "hacker news" with no formal education in computer science marvel like a turkey marvels its own excrement when they discover some tried and true technology. Oh, and they keep marveling at wheel reinventions. And you know what? One could say that it gets tiresome after a while, but it's far worse: it's deeply demoralizing, and makes those of us passionate about computer science deeply resent the information technology industry. And then we try to tell you how things should work, and instead of asking questions, yinz just start arguing and pushing reinvented, completely unnecessary wheels like "Kubernetes", "Docker", "Ansible", "Linux" because it's easier to mess around than warm up the chair and learn or listen to experience. The people on this website made me hate computers, because if this is the best our industry has to offer, it's devastating. So I understand where pjmlp is coming from. It's why I've all but withdrawn from here and don't bother posting anything any more.

SQLite has been around 20 years and designed to solve the kinds of problems being discussed. It’s older than git and nearly as old as SSH! So I don’t think it’s fair to brush this conversation with the same brush as those who advocate using Kubernetes for running a couple daemons.

This was in no way meant to be a critique on SQLite; SQLite is a phenomenal, high quality software and it is extremely useful. Why, I often use it myself and it's my first go-to database for solving all kinds of problems, or even just for simulations. I've known about and used SQLite for almost two decades now. Fantastic software, and that is rare in this day and age when most software is utter garbage. It would be so wonderful if more software were of as high a quality and as simple as SQLite...

What I was answering to is the completely unwarranted criticism of pjmlp from various posters here. Not only do I find it unjust, I find it appalling. The gall...

The attack isn't on `Pjmlp. I wanted to ask, as politely as possible, if he was aware that his comments consistently felt like a grumpy old man bringing nothing interesting to hacker news.

`Badsectoracula made a good case (better than the subject himself) for his opinion, and honestly, I agree with it! I just wish it wasn't always phrased in such a blasé negative fashion is all I wanted to convey.

Thought you should know, "yinz" is extremely regional and not nearly as recognizable as other dialectal/regional words meaning the same thing. You probably have a very low hit-rate comprehension-wise using it, considering it can be replaced with more easily recognizable words, especially for foreigners (though it's far from certain that people from the US but not from Pittsburgh or thereabouts will immediately recognize it).

I already know all of this; there was no need to lecture me. I did it on purpose.

And yet you apparently continue to read and post comments on HN. Without implying that you should not, I'm genuinely curious to know why you do.

Because every once in a while, the content of a post here is so absurd that I just have to react.

More absurdity: this a place where one is asked a question, answers it honestly and gets punished for it.

pjmlp longs for the days when developers (and/or their employers) paid for their tools because he believes that this allowed them to create tools of higher overall quality than the free FLOSS stuff you find nowadays that may excel in one area which developers find interesting/fun but are awful in several others and largely proliferate not because of their quality but of their price.

His comments basically follow out of this starting point so if you have that in mind they start to make sense.

(and TBH i do not completely disagree with this view myself, though i do not see the point of repeating it everywhere, this train not only left the station long time ago but the station has long been converted to an Amazon warehouse - besides it isn't like the development tools of old were always that great... to be relatively on topic, there were a ton of DBMSs that simply failed and others that started nice but created stinkers so awful that killed the entire company - see dBase as a great example for that)

That's true, I could've done exactly the same thing with MySQL, MariaDB, Postgres, etc.. But take note of the situations, and you'll notice some things which make SQLite stand out.

I said I was working in Python. I was working across three environments: Two separate compute environments, and my work macOS laptop. I didn't want to have to deal with installing the appropriate DB driver, and sqlite is part of the Python standard library.

Another thing to note is that I didn't already have a database server spun up, and I didn't think it was worth doing the work of spinning up a Postgres database server.

Finally, I didn't intend the "single file" to be an export. I intended it to be an actual, usable database, which my co-workers can use with minimal friction.

Those are a few of the reasons why I made the choice I did. And I think SQLite worked quite well!

> I do exactly the same with traditional RDMS.

For the use case of "I need a quick database for some ad hoc analysis", SQLite has a lot less friction than a traditional RDBMS. Creating a new DB, backing it up, sharing it with others – all these tasks are doable on a traditional RDBMS too, but generally involve additional steps or prerequisites compared to what SQLite requires.

> Export to single file isn't nothing new.

Most databases have some kind of export/import function based around CSV, SQL, XML, JSON, or even some proprietary binary format.

But SQLite is a lot easier than that – I can just copy a single database file to another system and just open it and it works straight away. (The only proviso is make sure the DB isn't open at the time of copy, to avoid any corruption issues.) No need to run some time consuming import/export process which converts between the external format and the database's native storage format.

Same here, depending on the project, I always have either SQL Developer with Oracle, or SQL Server Management Studio with SQL Server installed.

Copying a database to another machine is a lot simpler with sqlite than with either of the tools you mention. So I don't agree with the "same here" part.

Perhaps you should add your tables to Osquery?

https://osquery.io/

Thanks for the tip! Bookmarked, will look closer at OSQuery after vaca.

For everybody using sqlite cli from a terminal window, check out litecli [1]. It has some nice features like syntax color, better completion and query editing etc. It is part of the dbcli [2] family of database terminal clients. There are cli for mysql, postgres, redis and other databases.

[1] https://litecli.com/

[2] https://www.dbcli.com/

I've been really enjoying the litecli experience. Having been a ptpython¹ user I already knew about prompt_toolkit², but somehow had missed litecli.

This comment is just a general recommendation for Python users to have a poke around the GitHub organisation³, as the packages are awesome.

¹ https://pypi.org/project/ptpython/

² https://pypi.org/project/prompt-toolkit/

³ https://github.com/prompt-toolkit

I'm a fan of the series too, for one specific puropse: being able to set a pager.

Generally the sqlite cli would be sufficient, but not having a pager for the output overflows the terminal and is a major hindrance in working with standard database tools.

Using no GUI helps with learning too I think. There are workarounds to stay in the shell context and pipe to the utils and append a pager. For an analogy, it's `pager less` in mysql and `\pset pager less` in postgres. See my comment on v3.33 (when alot of cli enhancements got introduced): https://www.sqlite.org/forum/forumpost/3d157b175c

The mongodb cli neither has the ability to set a pager I noticed recently.

Nice! I came to the comment section precisely to see if someone could recommend an alternative to the default client.

My favorite terminal based client to explore SQLite databases and many other data formats is the TUI VisiData[1].

It has a spreadsheet-like interface and makes it very easy to explore a SQLite database in the terminal

[1] https://www.visidata.org/

[2] tutorial: VisiData in 60 Seconds(https://jsvine.github.io/intro-to-visidata/the-big-picture/v...)

SQLite continues to be a great lightweight DB solution with so many great features.

I love that for smaller projects (those that might have a few hundred or thousand rows in a DB at most) I can have everything self-contained in a single SQLite file.

* Backing up the DB is trivial - it's just backing up the file * Establishing a connection is super quick * No need to worry about Docker images or installation issues for psql, mysql, etc... * A whole host of features like zipping, dumping, etc... make it really easy to use

Great work to the entire community maintaining this.

I was wondering about backups! I thought, if doing a simple `cp`, you'd also have to make sure any remaining journal or WAL files are also copied.

But now as I'm looking through SQLite docs, I found VACUUM INTO (https://www.sqlite.org/lang_vacuum.html#vacuuminto), which lets you do a backup of an in-use database, combined with a VACUUM on the new database file!

There also is a backup API: https://www.sqlite.org/backup.html

I use something like:

  $ sqlite3 filename.sql ".backup backupFilename_YYYYMMDD.sql"

But VACUUM INTO looks better.

Why only a few thousand rows? Pretty sure sqlite can handle 2^64 rows per table

So, how does your ~/.sqliterc file look like?

I have

   .headers on
   .mode column
   .timer on
   .changes on

Is there a trick to choose whether or not to use the sqliterc file? I work on a pretty bad project that does database migration using thousands of lines of bash code that feeds queries to the sqlite3 commandline utility. For that one I'd like it to not read .sqliterc, but for my own use (on the same system) I'd like some of those options enabled.

I wonder if the simplest "trick" is to do the exact opposite. Name your ~/.sqliterc something else, and then "alias sqlite3='sqlite3 -init ~/.sqliterc-interactive'" in your shell's config?

Although, I'm wondering if using "sqlite3 -init /dev/null" in scripts would be a better idea in general. That way you would guard yourself against some random user's config doing something really weird.

One annoyance is that on Windows the binary distribution of the shell doesn't support readline bindings. You have to recompile the shell for that. Instead of recompiling, I wrote a simplistic rlwrap clone in Haskell https://gist.github.com/danidiaz/97edc1bf248f5dce34aeb370363... which does the job.

For people who prefer a GUI

https://sqlitebrowser.org

For those who use HeidiSQL: it has now (experimental) support for SQLite.

I love this tool. There is a feature I use a lot: you can write multi-step queries, select part of them and hit F5 to execute just that part. I haven't really dug into the CLI yet, but I imagine this to be less convenient there.

Personally I prefer SQLiteStudio [1] as it is closer to SQLite Expert Professional [2] that I used to use at work.

    [1] https://github.com/pawelsalawa/sqlitestudio
    [2] http://www.sqliteexpert.com/

The chap that makes datasette (the video tour on the home page is an excellent way to spend 20 minutes) also makes a few handy tools for common sqlite manipulation tasks: https://datasette.io/tools

You can just “pipx install <tool>” to avoid polluting your system python install with dependencies (pipx handles creating virtual environments and putting the tools on your path).

Shout out for the apple healthkit one, i had fun using datasette to explore that db.

The hackernews to sqlite tool works fine (user name is case sensitive).

The page doesn’t document the most universal tool there - sqlite-utils but there’s a sphinx doc for it https://sqlite-utils.datasette.io/en/stable/cli.html#transfo... some of the transform features are nice time savers.

I use it all the time. I saw a customer using a graphical app last week and I was surprised to see one. Of course there are, I see people connecting to PostgreSQL and MySQL databases on servers with DBeaver or similar apps. I always ssh and run the command line client. I think it's faster to operate. In the case of sqlite the command line client is a bit underpowered compared to the other databases but it's good enough.

That has existed for decades hasn't it? I thought this was about bringing uniform cli support across multiple RDMBs, such as Oracle's SQL*Plus syntax which is also partly supported by DB2 (and has text substitution variables, unlike sqlite's). Should there be an ISO standard for this?

Like those dot commands better than the backslash of psql.

Every so often I get excited to try SQLite, and every time I run into an impenetrable brick wall of unmaintained, deprecated bindings, nonexistant documentation, gotchas and hidden complexity.

I wish there was a lite version of SQLite with a much simplified interface that is actually a viable alternative to file_open().

Can you share a few of these issues? Been using SQLite for years and don’t remember a running into anything as you described.

What language are you using? SQLite has been well supported in most of the ones I’ve used.

It also has a pretty straightforward C API, so it might not be too hard to build bindings yourself if they don’t exist

Lua. Let me walk you through my experience:

Google for "Lua SQLite", click top link. http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki Go to download page, ok. http://lua.sqlite.org/index.cgi/index Download latest version, unzip it. It's in C. What? I thought this was a Lua binding.

Back to Google. Second link. https://makoserver.net/articles/Lua-SQLite-Database-Tutorial Umm... part of some Mako Server thing? I just want SQLite.

Back to Google, third link. http://lua-users.org/wiki/LuaSqlite "LuaSQLite is a Lua binding for SQLite." Alright! Goto website and... http://luaforge.net/projects/luasqlite/ It's a dead link. Uhh okay, back to previous page... "Other SQLite wrappers include:" Three out of four links dead. Only working link: https://github.com/maxdoom-com/lsqlite It's C again. What the hell.

Baaack to Google. Fourth link. https://mroth.net/lua-sqlite3 "Lua-Sqlite3 is a binding of Sqlite3 for Lua." Excellent. Download latest release... Finally, Lua files! Uhh there's a lot of stuff here. Documentation? "These parts needs to be written: ", "Please be patient... ", "Last update: 2006" uh oh. More C code... Prebuilt binaries? Nope!

Fifth Google link is a Lua plugin for SQLite, the inverse of what I want.

Sixth link, "(Yet Another*) Lua wrapper for SQLite" It's more C. No prebuilt binaries!

Seventh link. "LJSQlite3: SQlite3 Interface" No download. It's part of some large scientific computation package.

At this point, all I'm thinking is "this isn't worth it".

I know I could use LuaJIT's FFI to make my own wrapper to SQLite's C interface but at that point I'm not saving time anymore. Best to ignore the whole thing. Serializing data to/from files shouldn't be that complicated. I'll stick with my "piles of files", no matter how "awesomely engineered" SQLite is supposed to be, if it's this much pain to even start using.

The next best thing I have is this dead simple:

  local blob = filewriter()
  blob:write(literally_any_data_i_have)
  writefile("filename", blob:tostring())

Sorry if this is a dumb question (don’t know much about Lua), but have you tried installing LuaSqlite with luarocks?

It it works like pip for python, I think that `luarocks install lsqlite3` might be enough to start using it.

I use SQLite a ton, including the CLI. Didn't know about the zip file support.

Now I'm trying think of a valid use case. Interesting feature to have, nevertheless.

Also the “sqlar” archive format. I’ve known about it for a while, but that it is now built into the regular command shell was news to me. I’ll want to see if I can get sqlarfs to work with macFUSE next.

Reply on news.ycombinator.com