Joining CSV and JSON data with an in-memory SQLite database
simonwillison.net
simonwillison.net
sqlite-utils memory blah.json "select sql from sqlite_master"
sqlite-utils memory blah.json --dump
You've inspired me to add a --schema option which does the dump without also including the inserted rows. Issue here: https://github.com/simonw/sqlite-utils/issues/288
for row in db[ 'dogs' ].rows_where( 'age > 1', select = 'name, age', order_by = 'age desc' ): ...
sql = "select name, age from dogs where age > 1 order by age desc;"
for row in ...:
Perusing the docs I found that considerable effort is spent on explaining this and other ORM-like features, instead of just saying "use the SQL you already know" (people without this knowledge won't be easily able to use this API productively anyway).
The goal with sqlite-utils isn't to build an ORM - that .rows_where() method is definitely the most ORM-like piece of it, and it's purely there as a SQL-builder - it was a natural extension of the .rows property, which grew extra features (like order_by) over time as they were requested by users, eg https://github.com/simonw/sqlite-utils/issues/76
The vast majority of the library is aimed at making getting data IN to SQLite as easy as possible. Datasette is mainly about executing SELECT queries, and I found myself writing a lot of code to populate those database files - which grew into a combination library and CLI tool.
So yeah - I don't use the rows_where() feature much in my own code - I tend to use db.query() directly - but I've evolved the method over time based on user feedback. I don't have particularly strong opinions about it one way or the other.
It's also way easier to see what you were doing 6mo later and the performance is not terrible.
Also it is a different thing. Pandas is very nice to do data analytics or crunch numbers on reocurring data, but I wouldn't replace a database with it.
The big idea here is to use relational logic programming to express data transformation outside of storage access. The paper „Out of the Tar Pit“ proposed this as a way to reduce accidental complexity.
Also, if you think SQLite might be too constrained for your business case, you can expose any arbitrary application function to it. E.g.:
https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...
The very first thing we did was pipe DateTime into SQLite as a UDF. Imagine instantly having the full power of .NET6 available from inside SQLite.
Note that these functions do NOT necessarily have to avoid side effects either. You can use a procedural DSL via SELECT statements that invokes any arbitrary business method with whatever parameters from the domain data.
The process is so simple I am actually disappointed that we didn't think of it sooner. You just put a template database in memory w/ the schema pre-loaded, then make a copy of this each time you want to map domain state for SQL execution.
You can do conditionals, strings, arrays of strings, arrays of CSVs, etc. Any shape of thing you need to figure out a conditional or dynamic presentation of business facts.
Oh and you can also use views to build arbitrary layers of abstraction so the business can focus on their relevant pieces.
We didn't even bother with any indexing, because there are very few tables where we would exceed 100 rows.
There is no arbitrary point in database size (prior to the exact stated maximums [1]) at which SQLite just magically starts sucking ass for no good reason.
In the (very common) case of a single node, single tenant database server, you will never be able to extract more throughput from that box with a hosted solution over a well-tuned SQLite solution running inside the application binary. It is simply impossible to overcome the latency & other overhead imposed by all hosted SQL solutions. SQLite operations are effectively a direct method invocation. Microseconds, if that. Anything touching the network stack will start you off with 10-1000x more latency.
Unless you can prove you will ever need more capabilities than a single server can offer, SQLite is clearly the best engineering choice.
[1]: https://www.sqlite.org/limits.htmlMy rules of thumb are based entirely off experiments I've done with Datasette, which tends towards ad-hoc querying, often without the best indexes and with a LOT of group-by/count queries to implement faceting.
You've made me realize that those rules of thumb (which are pretty unscientific already) likely don't apply at all to projects outside of Datasette, so I should probably keep them to myself!
I'm curious what kind of business you're in?
- Who is writing the queries, and what interface do they use?
Are the SQL queries known at compile time, or does the user provide them to your compiled .NET program at runtime?
- What does the SQLite SQL dialect give you that Linq/functions does not?
> What does the SQLite SQL dialect give you that LINQ/functions do not?
It's not about SQLite's specific dialect. It's just about SQL. The relational algebra/calculi are capable of expressing any degree of complexity. LINQ (functions) require compile-time, which breaks our objectives.
But the experience helped me to look for the SQL patterns in the logic of the codebase I am working with.
Often there is very little. Mostly meaning that the rest is just an annoying heap of plumbing. It is not like I can magically make it go away, but it still seems unnecessary to me.
Turns out the most mature JavaScript database is SQLite ported to webassembly: https://github.com/sql-js/sql.js.
Based on that research, I wrote a bit about running SQL and other languages entirely in the browser here: https://datastation.multiprocess.io/blog/2021-06-16-language...
Just a few steps: convert JSON to CSV with jq, fire up interactive sqlite3 CLI which connects to in-memory database by default, run two .import FILE TABLE statements and finally the SQL query.
Alternatively one could use the SQLite JSON1 extension and write a small script if the task should be automated (and you do not like jq's syntax for JSON->CSV conversion)