SQLite is not a toy database

#1

Post by nalgeon » Thu, Mar 25, 2021, 2:20 PM UTC

Re: SQLite is not a toy database

#2

Post by snicker7 » Thu, Mar 25, 2021, 2:51 PM UTC

> SQLite is serverless.

Maybe if you use SQLite as a file format. But if you use it like an actual database (e.g. in a web application), I find that one is best off setting up a daemon thread to queue/batch transactions.

Re: SQLite is not a toy database

#3

Post by annoyingnoob » Thu, Mar 25, 2021, 2:54 PM UTC

Thanks for the info. Probably would not have considered SQLite in place of something like pandas.

Re: SQLite is not a toy database

#4

Post by bob1029 » Thu, Mar 25, 2021, 2:54 PM UTC

Don't forget about User-Defined Functions.

https://www.sqlite.org/appfunc.html

We just started enhancing our SQL dialect with new functions which are implemented in C# code. One of them is an aggregate and it is really incredible to see how it simplifies projections involving multiple rows.

One huge benefit of SQLite's idea of UDFs is that you can actually set breakpoints and debug them as SQL is executing.

Re: SQLite is not a toy database

#5

Post by maxbaines » Thu, Mar 25, 2021, 2:56 PM UTC

Thanks, always agreed. And a good read.

Re: SQLite is not a toy database

#6

Post by myth2018 » Thu, Mar 25, 2021, 2:58 PM UTC

I'd also like to add the possibility of using SQLite databases as an application file format: https://news.ycombinator.com/item?id=23508923

I had to work on a data import/export tool some time ago and SQLite has simplified the design a lot.

Re: SQLite is not a toy database

#7

Post by tgb » Thu, Mar 25, 2021, 2:59 PM UTC

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

Re: SQLite is not a toy database

#8

Post by jarym » Thu, Mar 25, 2021, 3:00 PM UTC

Oh it’s totally a toy. A great toy though! Love it to bits. SQLite + Postgres here and maybe will add DuckDB to the mix soon

Re: SQLite is not a toy database

#9

Post by [deleted] » Thu, Mar 25, 2021, 3:00 PM UTC

[deleted]

Re: SQLite is not a toy database

#10

Post by nalgeon » Thu, Mar 25, 2021, 3:02 PM UTC

tgb wrote: post #7

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

I do. Apache Superset, Metabase, Redash - all support SQLite. Other BI tools also do.