A bit of status report. The
ws4sqlfork (to integrateduckdbalongsidesqlite) is unfortunately stuck, because I cannot get the new 1.2.0 duckdb version to compile statically as the old version used to. Also unfortunately, in the last year or so my time is severely limited, and I am not able to dedicate personal resources to this project. So any hiccup (as the one I described) becomes a major showstopper. 😒 I am sorry about the situation, I will continue maintaining this branch and attempting to go on with the other, too.
If you find this project useful, and can help, please let me know. I could use some help right now 😊.
ws4sqlite is a server application that, applied to one or more sqlite files, allows to perform SQL querie
8000
s and statements on them via REST (or better, JSON over HTTP).
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).
Client libraries are available, that will abstract the "raw" JSON-based communication. See here for Java/JVM, here for Go(lang); others will follow.
As a quick example, after launching
ws4sqlite --db mydatabase.dbIt's possible to make a POST call to http://localhost:12321/mydatabase, e.g. with the following body:
// Set Content-type: application/json
{
"resultFormat": "map", // "map" or "list"; if omitted, "map"
"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 }
]
}
]
}- Aligned to SQLite 3.49.1;
- A single executable file (written in Go);
- HTTP/JSON access, with client libraries for convenience;
- Directly call
ws4sqliteon 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;
- Batching of multiple value sets for a single statement;
- Parameters may be passed to statements positionally (lists) or by name (maps);
- Results of queries may be returned as key-value maps, or as values lists;
- 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;
- CORS mode, configurable per-db;
- Scheduled tasks, cron-like and/or at startup, also configurable per-db;
- Scheduled tasks can be: backup (with rotation), vacuum and/or a set of SQL statements;
- Provide initialization statements to execute when a DB is created;
- WAL mode enabled by default, can be disabled;
- Quite fast!
- Embedded web server to directly serve web pages that can access ws4sqlite without CORS;
- Compact codebase;
- Comprehensive test suite (
make test); - 11 os's/arch's directly supported;
- Docker images, for amd64, arm 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 Authorizederror 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;
- Doesn't support SQLite extensions, to improve portability.
Let's meet on Discord!
Many thanks and all the credits to these awesome projects:
- lnquy's cron (MIT License);
- robfig's cron (MIT License);
- gofiber's fiber (MIT License);
- klauspost's compress (3-Clause BSD license);
- mitchellh's go-homedir (MIT License);
- modernc.org's sqlite (3-Clause BSD License);
- wI2L's jettison (MIT License)
- and of course, Google Go.
Kindly supported by JetBrains for Open Source development