The SQLite developers are advancing an experimental VFS module called CBS (Cloud Backed SQLite), which allows storing database content not in a local file but in external cloud storage. A specially optimized format for external storage is used, enabling dynamic loading of data from the external storage as needed, without the necessity to copy the entire database to the local system beforehand. Currently, Azure Blob Storage and Google Cloud Storage are supported for cloud storage. Any applications that support standard SQLite can be transitioned to use CBS.
The VFS module can operate in both background and active modes. In background mode, a background process continuously runs on the system, allowing access to external databases only in read mode, but supporting a shared cache that multiple processes can access. In active mode, both reading and writing are permitted, but only the local cache of the current process is used. At any moment, only one client can perform writing, and other clients will not see the changes made until they explicitly request them from the cloud storage. A typical SQLite locking system based on WAL logs is used to manage different write requests.
Developers are provided with an extended C API and command line interface that allows operations such as creating and clearing external storage, loading and unloading databases to/from external storage, creating a copy of a database in external storage, viewing the list of databases in cloud storage, and deleting unused blocks from the cloud storage.
Separately, the developers of the libSQL project (an extended fork of SQLite) have announced a server version of SQLite — sqld, which allows the organization of a shared DBMS whose clients connect via the PostgreSQL or HTTP protocol. For instance, existing client libraries designed for PostgreSQL can be used to connect to sqld, along with the CLI interface psql, among others. The sqld server can also be accessed using an API based on the HTTP protocol, which simplifies integration with web applications. The sqld code is written in Rust and is distributed under the MIT license.
Applications of sqld include backup, replication to other systems, and providing access to SQLite databases in serverless infrastructures that lack persistent storage, making it impossible to maintain a database in a local file. In serverless systems, the database used by sqld can be replicated from other databases. servers. To ensure high availability and fault tolerance, integration with mvSQLite (a distributed variant of SQLite that runs on top of FoundationDB) is supported. There is a mode for maintaining an active backup, implemented through periodic snapshot saving of the database state and replicating changes from the WAL log to cloud storage compatible with Amazon S3.
For applications, a library compatible with SQLite is provided, which can be loaded via LD_PRELOAD for transitioning from local file data storage to working through sqld. Extensions to SQLite can be loaded. Client libraries are prepared for TypeScript, JavaScript, Rust, Go, and Python. A database associated with sqld can be accessed simultaneously using standard SQLite tools, i.e., sqld acts as a layer to provide network access to the database, replication, and backup. For example: # Start the server for the SQLite DB foo.db: sqld -d foo.db -p 127.0.0.1:5432 —http-listen-addr=127.0.0.1:8000 # Access the database using PostgreSQL utilities: psql -q postgres://127.0.0.1 # Connect locally to the database using SQLite tools: sqlite3 foo.db # Access through HTTP: curl -s -d "{\"statements\": [\"SELECT * from databases;\"] }" \ http://127.0.0.1:8000
In conclusion, it is worth mentioning the LiteFS project developed by Fly.io. LiteFS is a FUSE module for managing a single shared SQLite database across multiple hosts. LiteFS replicates the state of the database between several systems by intercepting write operations to the database file and translating these operations to other systems, using a mechanism similar to transactions and saving transactions in a separate file in LTX format. The LiteFS code is written in Go and is distributed under the Apache 2.0 license.
Source: opennet.ru
