🦆 Introduction

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

🎞️ Features

• 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;

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

🥇 Credits

Kindly supported by JetBrains for Open Source development.