Introduction
SQLite is a very popular open source, embedded SQL database with some unusual characteristics. It has proved itself to be robust, fast and scalable, and has been widely adopted in a range of applications including web browsers, mobile devices, and embedded systems.
The "embedded" part means that developers access SQLite as a library of C functions that run as part of a larger application. This contrasts with more familiar database applications like Microsoft's SQL Server or Oracle that are accessed as network services.
MWS uses SQLite for the tiddler store and associated data. It brings many advantages:
- Performance: the optimising query engine inside SQLite makes it much faster and more efficient than could be achieved in plain JavaScript. Because it is stored in a single file, it can be faster than writing directly to the file system
- Reliability: SQLite uses protocols that ensure data integrity and consistency, even when the application crashes.
- Scalability: SQLite can handle extremely large datasets and complex queries
- Portability: Because SQLite databases are stored as a single file, they can be easily copied and moved between systems.
Misconceptions
TiddlyWiki 5 has always incorporated a database. Until MWS, that database has always been a custom tiddler database written in JavaScript. Over the years it has been enhanced and optimised with indexes and other database features that have given us reasonably decent performance for a range of common operations.
One particular misconception to avoid is the idea that SQLite replaces the folders of .tid files that characterise the Node.js configuration of TiddlyWiki. Those files are generated by a separate sync operation. They are not the actual database itself. In the context of MWS, SQLite is a fast and efficient way to store tiddlers between requests. Regardless of how tiddlers are stored internally, MWS can still save .tid files to the file system, just as TW5 does today.
Database Engines
SQLite is perfect for MWS because it doesn't require any extra setup. But MWS is not restricted to SQLite. It uses Prisma for the database access layer, which supports several other database engines, including MariaDB (the MySQL fork) and Postgres.
Better-SQLite3
Currently WAL mode is not enabled. It has plenty of advantages, and a few minor disadvantages, but mostly it just takes extra thought to use correctly. It has more advantages for high-traffic servers that need serious concurrency. Better-SQLite3 defaults to synchronous=NORMAL for WAL mode. Eventually we will probably add a setting to enable it.
Better-SQLite3 supports multi-threading via Node workers. Either way we have to implement proper support for transactions, which mostly just means reserving a worker for the duration of the transaction.
Better-SQLite3 has foriegn keys enabled by default.
Better-SQLite3 uses native addons. If your platform isn't supported, or you need a wasm-only solution, feel free to open an issue on Github sharing your use-case.
MWS uses Prisma to communicate with SQLite, and in theory, MWS should work with anything Prisma supports.