303 private links
Signal has open-sourced a SQLite extension that provides better support for non-latin languages (Chinese, Japanese, etc) in the Full-Text Search (FTS) virtual table.
“I ran a database company before this and I think the thing people like me never want to talk about is just about everyone has a few sub-10 gigabyte databases.” said Kurt Mackey, the CEO of Fly. “If you’re really in that category, you know this is super interesting because it’s SQL and it’s amazing for 10 gig databases.”
“[There’s] no need to get a database server up and running (or a docker container). It’s just a file. You can even send the database file to a coworker if you need some help with something.”
The software runs very fast without performance glitches, but there aren’t the same number of tools that can help support it. [...] I think the biggest complication for us is that there’s no tooling for it.
Le NoSQL est également plus adaptée pour le développement agile car les données ne sont pas structurées pendant les itérations.
Le SQL a réussi à traverser les âges car: il est simple, il est rapide et il est stable.
Les avantages de SQLite:
- facilité d'administration: tout le contenu peut être visualisé par lecture d'un fichier.
- facilité de déploiement: il est possible de pousser les BDD en production et de les migrer
- simplicité: sqlite est intégré avec tous les langages de programmation
- capacité: une BDD peut contenir plus d'un million de TB
Ses désavantages:
- requêtes concurrentes:
- les types de données limitées: NULL, INTEGER, REA L, TEXT, BLOB. Il n'y a pas de BOOL ni de dates.
- les données sont faiblement validées: "abcd" dans INTEGER o 20 charactères dans VARCHAR(5) passent.
- la sauvegarde des données est à faire soit même
- l'hébergement n'est pas possible avec tous les hébergeurs
Ok not bad at all. I still think Rust is not meant for prototyping, but let's give it a try.
Create webpages from SQL requests
Eḿbed SQL examples in a web page. It uses a browser-compatible compiled sqlite under the hood .
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.