202 private links
It uses the sqlean-fileio extension. It allows to import/export data from files such as JSON :)
And the related project to use SQLite in memory of a frontend app.
The database file is loaded from a server or a local file... which can be convenient for offline databases!
If the database is available online, it is still ok if it is small and the SQLite file can be downloaded :)
A small project but cool :D
Much like QEMU, new trends in the industry are taking it into a completely new direction: the rise of use cases around Edge compute, due to its limited resources and limited environments means that SQLite fits the bill perfectly.
It lists some distributed data project based on SQLite. The most promising project seems to be LiteFS: https://github.com/superfly/litefs. However SQLite is not open to contribution...
That'S why the author starts a new project, open to contribution named LibSQL (https://libsql.org/). It can be merged with SQLite if they change their code of conduct. They want to add async interfaces for example, or WASM support.
An explanation of the WAL of SQLite.
SQLite is a great database, but builtin functions are limited.
only 7 builtin aggregate functions: avg, count, group_concat, min, total, max, and sum
A shell history as SQLite database. It provides also more po
However, SQLite continued to improve and eventually introduced the write-ahead log (WAL) journaling mode and even the wal2 journaling mode. These provide significantly better support for concurrent readers.
Moral of the story: only use SQLite for websites where updates to the database happen rarely (less often than every page loaded).
Think about indexes:
As I mentioned in my first edit, database indexes dramatically reduce query time, but this is more of a general observation about databases than it is about SQLite.
And transactions to speed up writes:
However, there is another trick you can use to speed up SQLite: transactions. Whenever you have to do multiple database writes, put them inside a transaction. Instead of writing to (and locking) the file each and every time a write query is issued, the write will only happen once when the transaction completes.
With this it is possible to try SQLite in the browser. The whole thing runs using WASM (compiled using Emscripten) and was announced by Richard Hipp as part of the SQLite 3.39.0 beta in the SQLite forum.
What is also interesting is that to load the page, less than 1 MB of data needs to be loaded. If compression were enabled, this could probably be reduced further.
sqlite3 fiddle could make it easier to quickly try something with SQLite. Instead of downloading sqlite3 first and then trying it out locally, you just open the page and can quickly use the latest sqlite3 version.
How to have temporary data:
1 - Common Table Expression (or basically a named subquery)
2 - View: works like a CTE, but you can reference it by name and not repeat the subquery every time. Views are computed on the fly, similar to CTEs.
3 - A temporary table is like a real table: it stores data on disk, and you can build indexes. But it exists only while the database connection is open.
PostgreSQL and others have materialized views, which store data on disk
When to use temporary tables ?
Temporary tables are great for experimenting when you’re just getting to know the data.
x15 write speed :D
How ?
With Queued Writes rqlite itself can now queue a set of received write-requests, internally batch them, and then write that batch to the Raft log as a single log entry. This is key — by putting more data in a single Raft log, all of those previously distinct requests now result in a single Raft transaction, reducing the number of network round trips to a minimum.
Implementation of the queue : https://github.com/rqlite/rqlite/tree/v7.5.0/queue
Creating virtual columns in SQLite to greatly improve SQL queries. So
however I don't think that storing JSON as-is is a good idea...
But yes, it is almost a NoSQL database ツ
It is possible to export SQLite data to markdown 😯
.mode markdown
That's great !
Et une application tournant sous électron avec une BDD SQLite en typescript bien propre, c'est partiii.
Ou un programme Deno / NodeJS
A command-line client for SQLite databases that has auto-completion and syntax highlighting.