🌿 Introduction

This is a rewrite in Rust of ws4sqlite, 30-50% faster, 10x less memory used, more flexible in respect to sqlite support. It is not a direct rewrite, more like a "sane" (I hope) redesign. You can read more about what's changed and how to migrate here.

sqliterg is a server-side application that, applied to one or more SQLite files, allows to perform SQL queries and statements on them via REST (or better, JSON over HTTP).

Full docs are available here and a tutorial too.

Possible use cases are the ones where remote access to a sqlite db is useful/needed, for example a data layer for a remote application, possibly serverless or even called from a web page (after security considerations of course).

As a quick example, after launching:

sqliterg --db mydatabase.db

It's possible to make a POST call 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 answer of:

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

🎞️ Features

• A single executable file (written in Rust);
• Can be built either against the system's SQLite or embedding one;
• HTTP/JSON access;
• Directly call sqliterg on a database (as above), many options available using a YAML companion file;
• In-memory DBs are supported;
• Serving of multiple databases in the same server instance;
• Named or positional parameters in SQL are supported;
• Batching of multiple value sets for a single statement;
• All queries of a call are executed in a transaction;
• For each query/statement, specify if a failure should rollback the whole transaction, or the failure is limited to that query;
• "Stored Statements": define SQL in the server, and call it from the client;
• "Macros": lists of statements that can be executed at db creation, at startup, periodically or calling a web service;
• Backups, rotated and also runnable at db creation, at startup, periodically or calling a web service;
• CORS mode, configurable per-db;
• Journal Mode (e.g. WAL) can be configured;
• Embedded web server to directly serve web pages that can access sqliterg without CORS;
• Quite fast!
• Comprehensive test suite;
• Docker images, for x86_64 and arm64;
• Binaries are provided with a bundled SQLite "inside" them, or linked against the system's installed SQLite.

Security Features

• Authentication can be configured
  • on the client, either using HTTP Basic Authentication or specifying the credentials in the request;
  • on the server, either by specifying credentials (also with hashed passwords) or providing a query to look them up in the db itself;
  • customizable Not Authorized error code (if 401 is not optimal);
• A database can be opened in read-only mode (only queries will be allowed);
• It's possible to enforce using only stored statements, to avoid some forms of SQL injection and receiving SQL from the client altogether;
• CORS/Allowed Origin can be configured and enforced;
• It's possible to bind to a network interface, to limit access.

Some design choices:

• Very thin layer over SQLite. Errors and type translation, for example, are those provided by the SQLite driver;
• Doesn't include HTTPS, as this can be done easily (and much more securely) with a reverse proxy.

🥇 Credits

Kindly supported by JetBrains for Open Source development.