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.

PocketBase [1] provides realtime notifications and it works great (PocketBase is a single-binary self-hosted alternative to Supabase and uses SQLite internally)

[1]: https://pocketbase.io/

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.

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.

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.

Oooh, it would be fantastic if this enabled us to use SQLite as a backend for react-admin.

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

https://pocketbase.io is doing something similar but are a bit further ahead.

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?

Hi anaganisk, Thanks for your comment, I just mentioned fronted devs as an example but I'm not sure why you consider them as the last people?

I appreciate this motivation. Go is fine, but it’s much easier to read and contribute to JS/Typescript.

Hi Aeolun, Thanks for your comment.

doesn't matter much to me as a sysadmin. It's all running in a docker container anyways

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.

And there's a basic API too, to use it over a network: https://github.com/aaviator42/StorX-API

Hi aaviator, Thanks for your recommendation, I'll make sure to check it out.

PostgREST (https://postgrest.org/) for PostgreSQL should be mentioned as well.

Hi Toastal, Exactly, PostgREST is one of the best ones out there.

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.

Hi, I definitely take a look into it to take some inspirations! Thanks for your comment.

This looks useful. But some examples of how the http responses look without spinning it up would be helpful.

Thanks Mongol, Yeah exactly I should create a wiki for it.

In the news section:

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

You probably misclicked the comment section, I guess you wanted to go here: https://news.ycombinator.com/item?id=33346091

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.

Yeah it's reasonable.

If you use this as your production database on the public internet that’s entirely on you. It’s the same issue people have with Redis.

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.

That’s like saying cars don’t need airbags because people should drive responsibly.

Edit: case study: Optus, who leaked pii due to an unauthenticated api intended for internal use only but accidentally exposed to the internet.

I hadn't heard that about the Optus leak. Do you have a link with further details?

I’m sure google would, it was headline news aus wide for over a week.

Looks like a well structured one. Great. It'd also be nice to see the API docs on Wiki though. I like rqlite's simple documentation, for instance. [0]

https://github.com/rqlite/rqlite/blob/master/DOC/DATA_API.md

Thanks feldrim, I'll give it a high priority for sure. Also thanks for the rqlite, I'll make sure to check it out.

Also, I found this report that might be helpful for you. https://github.com/dbohdan/automatic-api/

Thanks a lot, I'll definitely check it out.

Ah neat.

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

Hi @KoenDG, Thanks for checking out Soul, I'm glad that you liked it!

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!

Yeah Directus is great, a bit shallow on docs though.

Also it connects to sqllite so how would it stack against OP's project?

Hi Bbkane, Thanks for your recommendations, I'll check it out!

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.

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.

At this point... isn't it better to just use rqlite?

Hi didip, Yeah, You're completely right, obviously rqlite is a very mature project and the comparison does not make any sense at this point!

Really awesome! I sent you an email. There are so many applications for this.

Thanks gl9, I'll respond to your email asap.

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.