Future developments of ws4sqlite and sqliterg

[proofrock]

After some recent developments, I've started to think about the mid-term strategy for ws4sqlite and its "young brother" sqliterg.

I forked sqliterg as a rewrite in rust, mainly to explore the benefits rust gives. They are there, for sure! And can be summed up in:

1. smaller binary
2. less memory occupied (numbers needed)
3. faster performances (-50%)
4. ability to use the sqlite libraries of the OS, at runtime

Plus, I was able to break the compatibility and implement some new ideas.

On the other hand, the complexity of development is much higher. It was fun, to tell the truth, felt like solving a riddle at times, and in a good way. But when @thanhnguyen2187 asked some changes, it was a lot less fun. Rust, rusqlite and serde are much more rigid to develop in, it's much more difficult to use them for things they weren't meant for.

Implementing them in go(lang), on the other hand, proved very easy. Felt like a playground vs a bad day at the office.

So, I'm thinking (just thinking, I'd like to know your idea) to switch the focus back to ws4sqlite.

About the points above:

1. It's not of great concern, these days. We are talking <20Mb after all.
2. Same thing, if we can keep memory low, <64Mb would be ideal.
3. Mattn's driver is again actively updated against sqlite, and switching (back) to it may compensate some of that gap, at the price of losing some "less used" platforms like risc or s390... which could be re-gained in time if Github Actions provided a build environment for them (and they aren't very widespread).
4. This we would lose, but if the driver keeps up the pace it's less of a problem.

Then, in time, the API improvements done on the rusqlite side would get ported back to ws4sqlite. I feel that as a tool it's still in its adolescence, so a well-documented breaking change may be acceptable.

The long-term goal would then be to include support not just to sqlite but to other rdbms too, in the spirit of what @CallumDowling did for sqliterg and MariaDB, and @mskyttner suggested for DuckDB. Also, implement a less-basic authentication (JWT) could be useful; and - without doubts - many other things.

One specific thing I'm pondering is to allow only one db to be managed for each instance of ws4sqlite. While the "multiple dbs" mode is cool, in practice it may not be used that much (a project is usually one db), and it makes code and - above all - configurations way more complex than they should be.

So, what do you think? Is something missing? I'd really appreciate your thoughts.

Thanks for reading,

 Germano

PS: Thanks a lot to @thanhnguyen2187, @CallumDowling and @mskyttner for all the ideas!

[thanhnguyen2187]

For performance, I feel like it should not be a great concern right now. I'm not sure what is it with other people, but for me, I reach out to SQLite when I need a... good enough SQL database that is simple to use and to set up, not for performance. ws4sqlite/sqliterg is a really cool layer that helps, too (instead of setting up a backend, I can just sit back and define the queries and avoid tons of boilerplate of "traditional" backend-to-frontend data munging).

For development direction, I think ws4sqlite/sqliterg has a lot that overlap with rqlite (HTTP API; basic authentication), but the "selling point" of ws4sqlite is its simplicity (a HTTP layer above SQLite and nothing more), and useful backend functionalities (user-configured prepared statements; scheduled tasks). I feel like we can focus on those instead. Caching is one thing popped up in my mind.

The long-term goal of having one HTTP layer above Postgres/MySQL is cool, too. Even though something like PostgREST exists, the REST to SQL translation is not something I enjoy. I would rather have a tool like ws4sqlite on top of Postgres that receives SQL.

[mskyttner]

I feel that quickly getting a http layer on top of sqlite/duckdb/postgres/mysql databases w optional JWT auth provides a very useful uncomplicated component that can be easily combined with various other tools. Recently the latest version of duckdb (v 0.10) provides "multi-database" support, see this announcement. The tests I have done so far getting to sqlite databases via duckdb seems to work well.

The duckdb binaries used to include a REST server, but it was removed some time ago. At that point my interest in ws4sqlite/sqliterg/duckrg and friends grew stronger. I think there are go bindings available that are up-to-date...

Since duckdb is great for reading but not really designed to support massive concurrent writing, this new multi-database-support allows a client to write to a database that is geared towards heavy write scenarios while at the same time reading via an optimized fast columnar database engine. A REST layer in front can expose a smaller surface for both fast write and read operations. At least I hope for it offering a way to "get the best of two worlds".

Simplicity, with being able to quickly define that kind of read/write operations and then getting some auth easily, like JWT, and running this as a container provides a lightweight nimble and uncomplicated way of exposing data to other components.

Since we are talking about "proxying" database engines - what are your thoughts about sometime in the future supporting the "Arrow Flight SQL" protocol?

[proofrock]

Gee thanks! I also feel that there's a lot of value in simplicity - SQLite is an amazing piece of software, I don't think I need to reinvent the semantics... especially because other people already adopted a more complex approach, the mentioned rqlite, postgrest, even pocketbase of course... I think there's some space in between, and that's where ws4sqlite is aimed. In this frame, I think that performance largely comes "for free", with an unobtrusive layer and simple, clean mappings.

So:

• usefulness, given we don't reinvent wheels
• simplicity and cleanness, where it does not hinder usefulness
• performance, where it doesn't hinder cleanness

...with a good pinch of attention to security. I quite like this. Thanks!!

what are your thoughts about sometime in the future supporting the "Arrow Flight SQL" protocol?

To tell the truth I am not very familiar with it. I'll look into it!

[Dialga]

Regarding just adding an REST API on top of the various databases mentioned, there is already project written in go dbrest that does that.

[proofrock]

Thanks for pointing it out! Like postgrest, they look like a "proper" REST interface, i.e. it uses REST semantics to request and submit data. ws4sqlite on the other hand is more like a "write your sql" solution, allowing for more flexibility, transactions etc - while admittedly being much less "REST-y". That's why I wrote somewhere "json over http".

[blaix]

This project is fantastic. Excellent work. Have you made a decision on where to focus (ws4sqlite vs sqliterg)? I agree with you and the others here that a focus on simplicity is key. I am not choosing sqlite for performance reasons, but for ease of use and setup (and with things like Litestream and LiteFS it's plenty good enough for production). I'd also add that working in a language you enjoy is a huge factor in the longevity of a project. I've made a similar detour to rust with performance in mind and eventually hit a wall. Very happy to be back with my preferred stack and discover my performance concerns were overblown.

For what it's worth, I'm hoping to use this with gren, which is still new and doesn't have a good story for database support yet (no TCP, can't pull in node dependencies), so ws4sqlite opens up the door for people to use a database with gren very simply, the way they are used to, with minimal dependencies.

[proofrock]

Thanks for your kind words! For the time being, I am not looking too much into performances, and I sort of decided to favor ws4sqlite even if it involves a much larger binary... flexibility and speed of development is really more important, and that wall you mention, I've definitely seen it. In due time, I could "re-integrate" here some of the ideas of sqliterg, but I didn't receive much feedback over them, so I'd like to avoid unnecessary breaking changes.

Good job with gren, it seems a great project, I will definitely look into it. Elm has been on my watchlist for a long time, but I am mainly a backend guy, so this is even more interesting to me. If you need anything, just drop me a line!

[blaix]

Great, and thank you! Gren is @robinheghan's project, but I've been contributing, and created https://github.com/blaix/prettynice, a full stack web framework, where I just updated my database example to use ws4sqlite: https://github.com/blaix/prettynice/tree/main/examples/next/database-http

[proofrock]

Haha, I noticed 😁 But it's a good job all the same! Have a nice day, and if you have any idea, please feel free!