Hacker News Reader

Comment by bob1029 | Hacker News Reader

Indeed. The whole point is to extend it with application-specific functions since the database is typically hosted in-process.

This is what we have done. We have a lot of domain-specific UDFs available in our SQLite scopes. Both scalar and aggregate types. Note that not all functions must be pure. You can quickly repurpose SQLite into a full-blown DSL by going down this path.

See: https://www.sqlite.com/appfunc.html

And: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...

View on HN

I often use SQLite for data analysis. Put all my raw data in, query out parts, and graph them, etc. It's quite annoying to have to dig up another tool just to get some common functions.

I love SQLite, but recently I've found that a Jupyter notebook with duckdb and pandas is a pretty powerful solution. They're well integrated and duckdb sql can refer to dataframes in scope as if they were tables.

Honest question, if I have PostgreSQL containerized in docker such that it's trivially easy to spin up a new database, what then is the use-case for SQLite? I frequently hear people talk about it with such rosy tone, but I just don't see the reason to actually set it up when the alternatives today are so easy to use, what am I missing?

Single file Easy to embed No ports, no sockets Easy to back up

I mean, why reach for the cordless screwdriver when all you need is a couple turns of a Phillips head? It’s cool that it’s easy to spin up databases but that’s a lot of complexity that may not be necessary in all cases.

Because it's even easier to embed sqlite, that's why. Docker is a big and heavy tool that has nonzero baggage. SQLite is a single .c/.h file.

You'd be surprised how many programs running on things that aren't web application servers which end up using relational queries.

I remember my first encounter with it under the hood. It was a huge list of all my location data stored on my early generation iphone, you had to pull some tricks to extract the file off of your phone and then boom I got a map of where I had been around 2009 better than even I remember it. I think there was a congressional hearing over this. Now they just store that stuff off of the device.

If you were to ship a desktop app that stored its local data in relational format, would you bundle docker and postgres along with it?

How large are your data? I'm thinking about implementing something similar but I'm not sure if its worth the time versus reading in flat files.

Not big (a few GBs?). But being able to query is one easy way to get join and statistics.

I'm assuming you are plotting in python or R, is there an advantage here using sql queries instead of subsetting a flat file with some function and plotting these subsets?

gnuplot. Yes, like I mentioned JOIN between two data sets is terrifically useful and quite a pain to write iteratively. Do you have another approach for doing joins?

What language do you write your extensions in? what language is your codebase? and what are some non-deterministic UDFs you've written? Are they just randomness related or are there other types of non deterministic UDFs that you use?

Reply on news.ycombinator.com