Soul | A SQLite RESTful server
Tags: sqlite, api, node, realtime
Published: 2022-11-03 23:12:38

Hi Folks,
For the last few weeks I've been working on [Soul](github.com/thevahidal/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](supabase.com) and [Pocketbase](pocketbase.io), 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:
```bash
npm install -g soul-cli
```

and then all you need to run it:
```bash
soul -d your-sqlite-db.sqlite -p 8000
```
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:

```bash
wget https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
```
And then let's start to read this database:
```bash
soul -d ./Chinook_Sqlite.sqlite -p 8000
```

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

```bash
curl 'localhost:8000/api/tables'
```

Soul will return something like this:
```json
{
  "data": [
    { "name": "Album" },
    { "name": "Artist" }
    // ...
  ]
}
```

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

```bash
curl 'localhost:8000/api/tables/Album/rows/'
```

Soul will respond with something like this:
```json
{
  "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
}
```

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


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

Soul will respond with something like this:

```json
{
  "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
}
```

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](https://github.com/thevahidal/soul/blob/main/docs/api-examples.md).

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!