Hi everyone,
for the last couple of weeks I started to work on Soul, which a SQLite RESTful server. Basically it takes an SQLite file and create CRUD endpoints for it.
Right now it consists of Soul CLI and Soul Studio (Which studio is at the early stages of development).
Studio is going to be GUI for soul that let's users to work with their database in a graphical way.
Eventually I want Soul to be a simple backend-as-a-service that people can use it to quickly start their ideas.
Please let me know if you have any ideas to improve Soul, or how it'll become useful for your project.
Looks awesome! I'm not sure if it would be one of your goals, but to me a killer piece of projects like this is realtime notifications. Supabase has something like this for Postgres.
There are an interesting number of options for SQL via HTTP and SQL via GraphQL popping up in general - some of these might have some features worth looking into.
Hi Lucasycas,
Thanks for your kind words. Real-time events / Websockets is definitely one of the must-have features for Soul. Right now I'm not sure how to approach it, but I welcome any contributions for it.
PocketBase [1] provides realtime notifications and it works great
(PocketBase is a single-binary self-hosted alternative to Supabase and uses SQLite internally)
Hi simjnd,
Eventually Soul is following the same path that Pocketbase is going, the main difference is that Soul is written in Node.js while Pocketbase is writtern in Go. Considering the larger community of Javascript developers I decided to start Soul.
What’s the story around concurrency and concurrent updates to the db file? Are all queries in a single thread or are concurrent sql statements allowed to execute? Are there any benchmarks vs Postgres for example?
Hi le-mark, as I mentioned Soul is really young and I just started to work on it. For now my goal is to create a decent prototype out of it and when the concept is matured enough, we can go towards performance. There's no benchmark done yet, but I invite you to talk more about the things you think is important to consider.
This is probably not ready to be used in production by others, but I wrote a library that gives you a key-value storage interface to SQlite files: https://github.com/aaviator42/StorX
I've been dogfooding for a while by using it in my small side projects.
This needs auth with a randomly generated password by default. Standing up products like this is exactly how data breaches happen, it’s effectively sql injection as a feature.
Exactly, more generally I'm thinking of a way for Soul to be extendable via plugins, which one of the most important one is authentication / authorization and permissions level.
If we are talking of RESTful servers and mention Redis, then we should mention Webdis, which is a RESTful server for Redis -- and it has ACL. https://webd.is/ It is a very old but still active project.
Yeah Pocketbase is one of my main inspirations.
I admire it a lot and the reason I started Soul, is because I think Javascript / Node.js could potentially be a better language to approach it as it's more popular than Go and for example to a huge community of Front-end developers it would be easier to adopt as they are already familiar with it.
Honest question, aren’t the front end devs the last people who would be interested to work on SQLITE? Why would they invest time to contribute to a tool that they wouldn’t use?
Also see https://directus.io/ - it adds a GUI and a GraphQL API to supported DBs (including SQLite). I'm still playing with it, and I'll add this to the list!
I also like to use NocoDB for this since, not only does it produce a REST API interface for the underlying SQLite/Postres/MySQL/etc database, you can also use it to build airtable like interfaces via its Web UI.
Projects like Soul are a REST API wrapper around databases to allow devs easily start their projects without being so much concerned about doing backend stuff.
Two things. First, I saw you use string interpolation to build SQL queries. Is this safe? Second, it’s not a RESTful API. I don’t see hypermedia controls so it’s just an HTTP interface.
Hi nesarkvechnep,
Thanks for your comment.
For the first question, I see your point, we're going to make Soul battle-tested as soon as we pass this prototyping period and we'll definitely think a way of keeping our queries safe.
About the second question you're right, I need to add hypermedia controls.
I'm a noob to this. Basic question: Why can't you just send SQL queries to the database, instead of needing to learn an HTTP-based API with lots of verbs? Seems like needless indirection.
Hi ogogmad,
The thing that Soul or other similar tools follow is to automatically turn your database into you backend. You don't need to implement any endpoints, Soul already did it for you.
I was looking to create a little database to keep track of spending, put things in categories... and then wanted a useable GUI and having to create that sure sounds dreadful... that, and the rest calls for each table and every possible operation, aaaa.
56 comments
[ 3.1 ms ] story [ 114 ms ] threadEventually I want Soul to be a simple backend-as-a-service that people can use it to quickly start their ideas.
Please let me know if you have any ideas to improve Soul, or how it'll become useful for your project.
There are an interesting number of options for SQL via HTTP and SQL via GraphQL popping up in general - some of these might have some features worth looking into.
[1]: https://pocketbase.io/
React-Admin is amazing, but I haven't found a REST wrapper for SQLite that works with it.
Soul certainly adds the record count (which sandman2 omits). But, I see Soul doesn't support multiple specific rows (getMany).
I've been dogfooding for a while by using it in my small side projects.
And there's a basic API too, to use it over a network: https://github.com/aaviator42/StorX-API
https://github.com/rqlite/rqlite/blob/master/DOC/DATA_API.md
Edit: case study: Optus, who leaked pii due to an unauthenticated api intended for internal use only but accidentally exposed to the internet.
Also it connects to sqllite so how would it stack against OP's project?
> 2022 Wreck of Äpplet warship, sistership to Vasa, found in Sweden.
For anyone who’s not been to Stockholm and seen the Vasa, I thoroughly recommend you push it to your things-to-see-before-I-die list.
I was looking to create a little database to keep track of spending, put things in categories... and then wanted a useable GUI and having to create that sure sounds dreadful... that, and the rest calls for each table and every possible operation, aaaa.
So this sounds like a neat thing