DEV Community

Cover image for Soul | A SQLite RESTful server
Vahid Al
Vahid Al

Posted on • Updated on

Soul | A SQLite RESTful server

Hi Folks,
For the last few weeks I've been working on Soul. As the title says, it's a SQLite RESTful server. In more simple words, Soul takes a SQLite database file and gives you a complete CRUD API to work with your database. The ultimate goal for Soul is to become a backend-as-a-server for folks who want to bootstrap projects real quick.

I took inspiration for building Soul from Supabase and Pocketbase, but mostly Pocketbase.
But the question is why Soul when there's Pocketbase. The reason is Go vs. Javascript. Pocketbase is written in Go, so to extend it you need to know Go which is less popular than Javascipt / Node.js. I'm developing Soul using Node.js because I believe it can potentially enable a larger community to work with it. Specially frontend developers who already are familiar with Javascript.

Quick Start

Starting with Soul is fairly simple first you need to install it:

npm install -g soul-cli
Enter fullscreen mode Exit fullscreen mode

and then all you need to run it:

soul -d your-sqlite-db.sqlite -p 8000
Enter fullscreen mode Exit fullscreen mode

Voila, Soul is listening on port 8000 and that's it!

A more in-depth walk-through

To see some of Soul's features, let's first download a sample database:

wget https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
Enter fullscreen mode Exit fullscreen mode

And then let's start to read this database:

soul -d ./Chinook_Sqlite.sqlite -p 8000
Enter fullscreen mode Exit fullscreen mode

Awesome now let's see how many tables it has:

curl 'localhost:8000/api/tables'
Enter fullscreen mode Exit fullscreen mode

Soul will return something like this:

{
  "data": [
    { "name": "Album" },
    { "name": "Artist" }
    // ...
  ]
}
Enter fullscreen mode Exit fullscreen mode

So far so good, now let's say we want to look through Albums:

curl 'localhost:8000/api/tables/Album/rows/'
Enter fullscreen mode Exit fullscreen mode

Soul will respond with something like this:

{
  "data": [
    {
      "AlbumId": 1,
      "Title": "For Those About To Rock We Salute You",
      "ArtistId": 1
    },
    { "AlbumId": 2, "Title": "Balls to the Wall", "ArtistId": 2 }
    // ...
  ],
  "total": 347,
  "next": "/tables/Album?page=2",
  "previous": null
}
Enter fullscreen mode Exit fullscreen mode

So what if we wanted to search for the word rock (_search)? Also we wanted to extend the related field ArtistId (_extend) and we only wanted albums by Artist whose Id is 90 (_filters). Let's make it more complicated, let's say that we want to limit each page by 20 rows (_limit) and we want the first page (_page). Also let's just get Title, we don't need the AlbumId and ArtistId (_schema).

curl 'localhost:8000/api/tables/Album/rows?_page=1&_limit=20&_search=rock&_ordering=-Title&_schema=Title,ArtistId&_extend=ArtistId&_filters=ArtistId:90'
Enter fullscreen mode Exit fullscreen mode

Soul will respond with something like this:

{
  "data": [
    {
      "Title": "Rock In Rio [CD2]",
      "ArtistId": 90,
      "ArtistId_data": { "ArtistId": 90, "Name": "Iron Maiden" }
    },
    {
      "Title": "Rock In Rio [CD1]",
      "ArtistId": 90,
      "ArtistId_data": { "ArtistId": 90, "Name": "Iron Maiden" }
    }
  ],
  "total": 2,
  "next": null,
  "previous": null
}
Enter fullscreen mode Exit fullscreen mode

Alright, so now you have some idea that how Soul works and what it could do to help you with your projects.

If you want more of these examples, checkout Soul's API examples, here.

If you had any issues, please create an issue in Soul's Github repository. I'll respond as soon as I can.

You have some ideas to make Soul a better tool? Don't hesitate to send me an email at thevahidal [at] gmail [dot] com.

Thanks for reading this and I'll see you in the next one!

Top comments (0)