Ask HN: How do you use SQLite?

I use SQLite for sharing structured open data - it's a better alternative to zipped csv datasets, for example. See the football.db [1] as some example SQLite datasets e.g. the English Premier League in england.db and so on. [1]: https://github.com/openfootball/england/releases

PS: The big elephant for SQLite data packages is Simon Willison's Datasette [2]. [2]: https://datasette.io

I have a hobby project where I have two different kinds of IDs and I have to maintain lookups of one type of ID to another and vice versa. It's a web socket server with no need for a persistent data store.

Rather than maintaining two separate hash maps and keeping them both updated, it was easier to simply have a sqlite instance in memory and do inserts/updates/selects in sql.

I mostly use it for convenient ad-hoc processing of CSVs with SQL using this tool I wrote:

https://github.com/jolmg/cq

I have used it to deal with the SQLite databases that some programs use, as well as for data queries in general where SQL is helpful (in addition to SQLite databases, the command-line tool can also load CSV and JSON, so it is very helpful for a lot of kind of data), and also I have used in the following programs I write:

- bystand (NNTP client) - Saves the article and configuration database; also for user customization (including the header displays)

- sqlnetnews (NNTP server) - Saves the article database, including list of newsgroups, and overview data

- Free Hero Mesh (a puzzle game engine) - Batch operations , user customization, user cache, and storing user data such as move lists (although solutions can be exported to another file); not used for the game rules (which uses a custom programming language)

- frezed (ZZT external editor) - Batch operations, queries, user customization, and more; almost half of this program is written in SQL

- TeXnicard (a similar purpose to Magic Set Editor and similar programs) - Store the card database, make queries and batch operations, PCRE callouts (mainly because, as far as I know, the PostScript interpreter may not be reentrant), custom random pack/deck definitions (defined as views), and can call SQL codes from PostScript codes

In all of these programs the user can also enter their own SQL codes (Fossil, below, also supports this), except sqlnetnews (which is server software, so the user input is not considered trusted in this case).

I also use Fossil, which also uses SQLite.

I would also like to have virtual table interfaces for some remote data (some sort of standardized protocol(s) with open source implementations), especially that which changes often, or that incremental updates will be useful; then SQLite can be used for that, too. For other data, SQLite databases that you can just download and query on your own computer, would be a good things to have, too.

So, SQLite is very good, for many more things than I had just listed, too. A few other things which use SQLite are listed at: http://fileformats.archiveteam.org/wiki/Category:SQLite_base...

To add my own example: I use SQLite for the backend of my blog. (https://jlelse.blog/)