ws4sqlite is a server application that, applied to one or more SQLite data, permits to build SQL queries and statements on them by skill of REST (or higher, JSON over HTTP).

That you may want to also imagine consume cases are these the keep a ways-off access to a sqlite db is handy/wanted, let’s impart an knowledge layer for a a ways-off application, presumably serverless or even known as from a internet based internet page (after security considerations in spite of the total thing).

Shopper libraries could be found in, that can summary the “raw” JSON-based completely conversation. Ogle
here for Java/JVM, here for Sail(lang); others will regulate to.

As a snappy instance, after launching

ws4sqlite --db mydatabase.db

Or now not it’s most likely to rep a POST name to http://localhost: 12321/mydatabase, e.g. with the following body:

{
    "transaction": [
        {
            "statement": "INSERT INTO TEST_TABLE (ID, VAL, VAL2) VALUES (:id, :val, :val2)",
            "values": { "id": 1, "val": "hello", "val2": null }
        },
        {
            "query": "SELECT FROM TEST_TABLE"
        }
    ]
}

Obtaining an solution of

{
    "results": [
        {
            "success": true,
            "rowsUpdated": 1
        },
        {
            "success": true,
            "resultSet": [
                { "ID": 1, "VAL": "hello", "VAL2": null }
            ]
        }
    ]
}

Docs and a Tutorial.

• A single executable file (written in Sail);
• HTTP/JSON access, with client libraries for convenience;
• Directly name ws4sqlite on a database (as above), many alternatives out there the utilization of a YAML accomplice file;
• [In-memory DBs] are supported (https://germ.gitbook.io/ws4sqlite/documentation/configuration-file#direction);
• Serving of more than one databases in the linked server instance;
• Batching of more than one be aware sets for a single assertion;
• All queries of a name are completed in a transaction;
• For each question/assertion, specify if a failure can also soundless rollback the total transaction, or the failure is little to that question;
• “Saved Statements“: account for SQL in the server, and rep in touch with it from the client;
• CORS mode, configurable per-db;
• Upkeep scheduling (VACUUM and backups), also configurable per-db;
• Builtin encryption of fields, given a symmetric key;
• Present initialization statements to build when a DB is created;
• WAL mode enabled by default, could even be disabled;
• Quite swiftly!
• Compact codebase (~900 lines of code);
• Entire test suite (rep build-test);
• Docker photos, each for amd64 and arm32.

• Authentication could even be configured
  • on the client, both the utilization of HTTP Long-established Authentication or specifying the credentials in the ask;
  • on the server, both by specifying credentials (also with hashed passwords) or offering a matter to peek them up in the db itself;
• A database could even be opened in be taught-only mode (only queries could be allowed);
• Or now not it’s most likely to implement the utilization of only saved statements, to retain away from some forms of SQL injection and receiving SQL from the client altogether;
• CORS Allowed Beginning keep could even be configured and enforced;
• Or now not it’s most likely to bind to a network interface, to restrict access.

Some build picks:

• Very thin layer over SQLite. Errors and sort translation, let’s impart, are these supplied by the SQLite driver;
• Would not consist of HTTPS, as this could presumably well also be performed easily (and rather more and plenty more securely) with a reverse proxy;
• Would not make stronger SQLite extensions, to make stronger portability.

Many thanks and the total credits to those good projects:

• lnquy’s cron (MIT License);
• robfig’s cron (MIT License);
• gofiber’s fiber (MIT License);
• mitchellh’s streak-homedir (MIT License);
• mattn’s streak-sqlite3 (MIT License);
• wI2L’s jettison (MIT License)
• DataDog’s zstd (Simplified BSD license);
• and in spite of the total thing, Google Sail, VS Code and CodeServer!