Making interactive sqlite3 more useful


sqlite3 is a fantastic embedded database for rapid prototyping and resource-constrained environments, given its lack of dependencies or a server. You open one file with a CLI tool, and that’s it. You probably interacted with several today without realising it.

But I don’t think people appreciate just how capable it really is. I’ve been having a ton of fun implementing personal projects in it… perhaps more than I should be admitting. There’s a Ruben in a parallel universe who became a chartered accountant who lived in SQL and spreadsheets. But I digress.

To closer mimic a classic SQL server, add these to your ~/.sqliterc file to turn on columns and headers:

.headers ON
.mode columns

To clear the screen on *nix, you can issue the shell command:

.shell clear

sqlite3’s documentation touts its lack of rigid typing, but as of version 3.37.0 you can enforce type affinities with the STRICT statement. This limits backwards compatibility, but I’ve started doing this in my new schemas because old habits die hard.

And finally, if you instinctively reach for DUAL even after moving on from Oracle all those years ago, you can create this delightfully pointless table:

CREATE TABLE DUAL (DUMMY, sysdate, user);
INSERT INTO TABLE DUAL VALUES('X', datetime('now') ,'rubenerd');

This is worse than useless. For one thing, the data will be stale within a day. Your username might not even be rubenerd. And you won’t be using this DUAL table to select computations. But it tickles me.

The sqlite3 documentation lists more CLI shell information.

Author bio and support


Ruben Schade is a technical writer and infrastructure architect in Sydney, Australia who refers to himself in the third person. Hi!

The site is powered by Hugo, FreeBSD, and OpenZFS on OrionVM, everyone’s favourite bespoke cloud infrastructure provider.

If you found this post helpful or entertaining, you can shout me a coffee or send a comment. Thanks ☺️.