Hey! This project is archived. I decided to switch back the development on ws4sqlite, for reasons expressed in this discussion. If you'll want to hack on it, though, I'll be more than glad to assist!

duckrg 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).

This is a almost-1-to-1 adaptation to DuckDB of sqliterg. Until it will have its own documentation, please refer to sqliterg's, it's basically the same but for the points listed in the page Changes_from_sqliterg.md in this repository.

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:

duckrg --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 }
            ]
        }
    ]
}

• A single executable file (written in Rust);
• [TODO] Can be built either against the system's duckdb or embedding one;
• HTTP/JSON access;
• Directly call duckrg 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;
• "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;
• Embedded web server to directly serve web pages that can access duckrg without CORS;
• Comprehensive test suite;
• [TODO] Docker images, for x86_64 and arm64;

• 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 DuckDB. Errors and type translation, for example, are those provided by the DuckDB driver;
• Doesn't include HTTPS, as this can be done easily (and much more securely) with a reverse proxy.

Kindly supported by JetBrains for Open Source development.