thevahidal / soul

🕉 A SQLite REST and realtime server

A SQLite REST and Realtime server

Installation

Install Soul CLI with npm

  npm install -g soul-cli

Usage

1. Running Soul

Soul is command line tool, after installing it, Run soul -d sqlite.db -p 8000 and it'll start a REST API on http://localhost:8000 and a Websocket server on ws://localhost:8000.

Usage: soul [options]


Options:
            --version                               Show version number                                 [boolean]
  -d,       --database                              SQLite database file or :memory:                    [string] [required]
  -p,       --port                                  Port to listen on                                   [number]
  -r,       --rate-limit-enabled                    Enable rate limiting                                [boolean]
  -c,       --cors                                  CORS whitelist origins                              [string]
  --env,    --envpath                               Path to load .env file                              [string]
  -a,       --auth                                  Enable authentication and authorization             [boolean]

  --iuu,     --initialuserusername                   Initial user username                               [string]
  --iup,     --initialuserpassword                   Initial user password                               [string]

  --ts,      --tokensecret                           Token Secret                                        [string]
  --atet,    --accesstokenexpirationtime             Access Token Expiration Time    (Default: 5H)       [string]
  --rtet,    --refreshtokenexpirationtime            Refresh Token Expiration Time   (Default: 1D)       [string]
  -S,       --studio                                 Start Soul Studio in parallel
  --help                                             Show help

Then to test Soul is working run the following command

curl http://localhost:8000/api/tables

It should return a list of the tables inside sqlite.db database.

2. Running Soul in Auth mode

To run Soul in auth mode, allowing login and signup features with authorization capabilities in your database tables, follow these steps:

Run the Soul command with the necessary parameters:

  soul -d foobar.db -a --ts=<your_jwt_secret_value> --atet=4H --rtet=3D --iuu=john --iup=<your_password>

Note: When configuring your JWT Secret, it is recommended to use a long string value for enhanced security. It is advisable to use a secret that is at least 10 characters in length.

In this example:

The -a flag instructs Soul to run in auth mode. The --ts flag allows you to pass a JWT secret value for the access and refresh tokens generation and verification. Replace <your_jwt_secret_value> with your desired secret value. The --atet flag sets the JWT expiration time for the access token. In this case, it is set to four hours (4H), meaning the token will expire after 4 hours. The --rtet flag sets the JWT expiration time for the refresh token. In this case, it is set to three days (3D), meaning the token will expire after 3 days. The --iuu flag is used to pass a username for the initial user The --iup flag is used to pass a password for the initial user

Here are some example values for the atet and rtet flags

• 60M: Represents a duration of 60 minutes.
• 5H: Represents a duration of 5 hours.
• 1D: Represents a duration of 1 day.

NOTE: It is crucial to securely store a copy of the --ts(Token Secret) value used in Soul. Once you pass this values, make sure to keep a backup because you will need it every time you restart Soul. Losing this secret values can result in a situation where all of your users are blocked from accessing Soul.

3. Updating Super Users

To modify a superuser information in a database, you can utilize the updatesuperuser command. This command allows you to change a superuser's password or upgrade/downgrade a normal user to a superuser. Below is an example of how to use it:

soul -d foobar.db updatesuperuser --id=1 password=<new_password_for_the_user> // Update the password for the superuser with ID 1

soul -d foobar.db updatesuperuser --id=1 --is_superuser=true // Upgrade the user with ID 1 to a superuser

soul -d foobar.db updatesuperuser --id=1 --is_superuser=false // Revoke the superuser role from the superuser with ID 1

Passing Custom Path for .env File

There might be cases where you want to pass a custom path for your .env file. For this, you can use the --env flag when running the soul command, providing the absolute file path of your .env file.

soul -d foobar.db --env=/Users/Documents/Projects/React-Project/.env

NOTE:

• You should pass an absolute file path of the .env file.
• Relative paths are not allowed.
• Don't forget to include the .env file in the specified path.

Documentation

API documentation is available while the project is running at http://localhost:8000/api/docs

There's also a list of all endpoints examples at docs/api-examples.md

For websocket examples, check docs/ws-examples.md

For detailed information on how authentication works in Soul, please refer to the docs/auth.md

Extending Soul

Soul is able to be extended (e.g. Adding custom APIs) via extensions, you can find a list of extensions at docs/extensions-examples.md

Soul-mates

A collection of projects that revolve around the Soul ecosystem.

• Soul Studio provides a GUI to work with your database.

  Right now Soul Studio is in early stages of development and not useful to work with.

  

• RCO-Soul The purpose of this project is to demonstrate how to run a React admin client using Soul as a REST API service.

Development

git clone https://github.com/thevahidal/soul # Clone project

cp .env.sample .env # Duplicate sample environment variables
nano .env # Update the environment variables

npm install # Install dependencies
npm run dev # Start the dev server

Testing

Set the AUTH variable to true in your .env file and use the command below to run the tests

 npm run test

Community

Join the discussion in our Discord server and help making Soul together.

License

MIT

Contributing

Contributions are always welcome!

See CONTRIBUTING.md for ways to get started and please adhere to CODE OF CONDUCT.

Contributors ✨

Thanks goes to these wonderful people (emoji key):

Vahid Al 💻 👀

Abenezer Melkamu 💻

Ian Mayo 💻 👀

Hanz 💻

Koen De Groote 💻

Muhammad Taha Khan 💻

This project follows the all-contributors specification.

soul's Issues

Support for relations and joins

Load testing

Screenshot of studio in readme

The repo readme refers to studio, and how to start it. But, it doesn't explain the role of studio.

A picture (well, screenshot) of soul studio in the readme would paint a thousand words, IMHO.

GET, UPDATE, DELETE based on models primary key instead of id field

Maybe instead of doing this, give the user to select which field to find object via that.

A good README

Auto backups (Ideally to a S3 bucket)

Dockerfile

Better cors handling

`better-sqlite3` installation keeps failing when i run `npm install` in the core folder (for MacOs)

When i run npm install in the core folder, better-sqlite3 installation keeps failing due to node-gyp

To Reproduce

1. Go to the /soul/core folder
2. Run npm install

Expected behavior
I was expecting all of the packages to be installed in the node_modules folder so i can run the core API

OS

• OS: Macos Montery 12.6
• Browser: Chrome

Import data from json file

It should be something similar to the fixtures and loaddata command in Django.

Live updates for changes via websocket

Soul Studio - A GUI for Soul

• Restructure components
• Selected tab should be shown as selected.
• Add new record option
• Option for selecting multiple row and deleting them
• All cells should be editable
• Add filters, limiting and fields option
• All columns should be sort-able
• Option for extending related fields
• Add icon for the type of column in column headers next to the name
• Reset all filters option
• Add closable table tabs feature
• Add option for opening new tab
• Options as query params for persistence
• Better styles
  • Infinite rows (and columns)
  • No white background
  • Awesome horizontal scroll experience
  • Light theme

A good documentation

Declare shiny new `Automatic-API`

A github repo is acting as an index of libraries that expose HTML apis for databases:
https://github.com/dbohdan/automatic-api

An entry should be added for soul.

Add filtering, ordering and searching among rows

A query param to only send some of the fields

Transaction endpoint

Add permissions and user groups

CLI mode

Failure installing on Apple Silicon

OS: macOS Ventura 13.0
Architecture: arm64
Node: v14.20.1
NPM: 6.14.17

MacBook-Pro ➜  npm install -g soul-cli
/Users/justin/.nvm/versions/node/v14.20.1/bin/soul -> /Users/justin/.nvm/versions/node/v14.20.1/lib/node_modules/soul-cli/soul/index.js

> [email protected] install /Users/justin/.nvm/versions/node/v14.20.1/lib/node_modules/soul-cli/node_modules/better-sqlite3
> prebuild-install || npm run build-release

prebuild-install warn install No prebuilt binaries found (target=14.20.1 runtime=node arch=arm64 libc= platform=darwin)

> [email protected] build-release /Users/justin/.nvm/versions/node/v14.20.1/lib/node_modules/soul-cli/node_modules/better-sqlite3
> node-gyp rebuild --release

  TOUCH ba23eeee118cd63e16015df367567cb043fed872.intermediate
  ACTION deps_sqlite3_gyp_locate_sqlite3_target_copy_builtin_sqlite3 ba23eeee118cd63e16015df367567cb043fed872.intermediate
  TOUCH Release/obj.target/deps/locate_sqlite3.stamp
  CC(target) Release/obj.target/sqlite3/gen/sqlite3/sqlite3.o
  LIBTOOL-STATIC Release/sqlite3.a
env: python: No such file or directory
make: *** [Release/sqlite3.a] Error 127
rm ba23eeee118cd63e16015df367567cb043fed872.intermediate
gyp ERR! build error
gyp ERR! stack Error: `make` failed with exit code: 2
gyp ERR! stack     at ChildProcess.onExit (/Users/justin/.nvm/versions/node/v14.20.1/lib/node_modules/npm/node_modules/node-gyp/lib/build.js:194:23)
gyp ERR! stack     at ChildProcess.emit (events.js:400:28)
gyp ERR! stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:285:12)
gyp ERR! System Darwin 22.1.0
gyp ERR! command "/Users/justin/.nvm/versions/node/v14.20.1/bin/node" "/Users/justin/.nvm/versions/node/v14.20.1/lib/node_modules/npm/node_modules/node-gyp/bin/node-gyp.js" "rebuild" "--release"
gyp ERR! cwd /Users/justin/.nvm/versions/node/v14.20.1/lib/node_modules/soul-cli/node_modules/better-sqlite3
gyp ERR! node -v v14.20.1
gyp ERR! node-gyp -v v5.1.0
gyp ERR! not ok
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] build-release: `node-gyp rebuild --release`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] build-release script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     /Users/justin/.npm/_logs/2022-10-29T23_55_45_410Z-debug.log
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] install: `prebuild-install || npm run build-release`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] install script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     /Users/justin/.npm/_logs/2022-10-29T23_55_45_567Z-debug.log

Migrate to fastify

Unit tests / End to end tests

Improve project structure

Right now Soul has no structure, but we need to change it!

-- 
-- soul/
  -- services/
  -- controllers/
  -- routes/
    -- tables/
    -- rows/
  -- index.js

Extensions support for APIs

Open studio using CLI

Thanks. After upgrade it fails with:

    Thanks. After upgrade it fails with:

PS C:\Users\marti\tmp\soul-test> soul -port 8080 -d sakila.db
C:\Users\marti\AppData\Roaming\npm\node_modules\soul-cli\src\config\index.js:43
  throw new Error(`Config validation error: ${error.message}`);
  ^

Error: Config validation error: "NODE_ENV" is required

Originally posted by @maacl in #48 (comment)

A solution for aggregates and ...

Support full set of REST calls expected by react-admin

The combination of a react-admin interface for a SQLite backend could be a really lightweight solution to many problems. But, there isn't (yet) a REST wrapper for SQLite that is capable of integrating with react-admin

It now looks like soul may be the missing component to getting this working. But, the Soul API needs to be extended, just a little:
https://marmelab.com/react-admin/DataProviderWriting.html#request-format

Soul must be extendable

To be able to be adapted to any needs, Soul needs to be extendable, because no matter how many built-in features it offers, eventually, people come with unique needs that the Soul can't and shouldn't have an answer for them.

For now, I'm thinking of three types of extensions:

1. API extensions: A way to add new APIs besides the default ones
   • e.g. /api/greeting which returns a greeting message.
2. Middleware extensions:
   • e.g. Auth middleware which forces authentication on endpoints.
3. Callback extensions:
   • e.g. After adding a row do an action

Logo

Better way to import database file

Add request validation to all endpoints

Apply _schema to _extend-ed fields too

A self-explanatory landing page for demoing

Host static web-content

Is your feature request related to a problem? Please describe.

I'm trying to run react-admin against a SQLite file wrapped in Soul. But, I'm having CORS issues. I saw the .env.sample file - containing the WHITELIST, and renamed it to .env - but it didn't fix the issue. Yes, the URL in the file matches the actual origin (localhost:3000) of my node server. Update: a few minutes later the CORS issue is resolved. Silly Ian mistakenly tried to modify the behaviour of an installed instance of Soul by modifying a file in a source repo. When I ran Soul from the repo the .env trick worked perfectly :-)

I remain interested in the idea of running static content from within Soul.

Describe the solution you'd like

One solution for the above would be for the Soul server to host static web content in a sub-folder. This would neatly prevent my CORS issues, and result in one less server process (node) running on my machine.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Hmm, on reflection would an extension be a way to do this? We'd create an extension called root, that serves index.html from the root folder. Would that do it @thevahidal ?

Support for URL (non-local) database file

The `extensions.js` file throws an error when i try to run an extension

Describe the bug

I am trying to run a soul extension inside the soul/core folder but the code is throwing an error in the /core/src/extensions.js file, i have followed the instructions in here to run the extension.

To Reproduce

1. Go to the soul/core folder and create a folder named _extensions

2. Create a file named api.js inside the _extensions folder and copy and paste the sample extension code from here

3. Add the path of your extension in your .env file

   EXTENSIONS=./_extensions
   

4. Run the project

   npm run dev
   

Expected behavior
When i run the project, it throws an error in this line, it can't require the _extensions/api.js file,

Screenshots

Additional context
The code works fine when i modify the path of the extension like this

- const apiExtensions = require(`${extensionsPath}/${extension}`);

+ const apiExtensions = require(`../${extensionsPath}/${extension}`);

Protection against SQL injection

• Use parameterized query / prepared statement
• Specify an environment variable to restrict transaction endpoint [default (or production) disabled]
• Ideally, move all queries to the services directory

_extend-ed file must get JSON parsed

Rate limit for requests

Single script runner for both Core and Studio

Soul must support relative paths for extensions.

Is your feature request related to a problem? Please describe.

(#77 (comment))

Describe the solution you'd like

Soul should accept relative paths for the extensions directory. e.g. ./_extensions/

Describe alternatives you've considered

Right now we can use absolute paths. e.g. /home/user/_extensions/

Additional context
_

API documentation

dev experience when running on empty db

Hey, I saw your post on HN and was curious, and have a bunch of random feedback.

I followed the readme for getting things set up. However, I didn't have an sqlite db lying around, so i made an empty one.

Studio started on a different port than 3000, probably because I'm running a different dev server on 3000, so I got a cors error when running on this different port. Possibly set cors origins to allow * in dev?

Besides that running studio I got some semi-broken UI, probably because my db is empty, and errors in console. I fixed some of them locally but ended up getting too many retries (http) errors and ended up giving up 😭

Anyways.. mostly a feedback around "maybe you should try to get it working with an empty db file" ?

Anyways, the concept is nice, good luck and keep it up

Code of conduct and contribiuting files

Publish to npm

Environment variables, everywhere

Any specific usage scenarios for this?

Thanks for creating this tool.

I'm just curious and want to know what this tool can be used.

Thanks for sharing.

Fails with missing port on both Windows and Linux

Following README starting soul fails on both Windows and Linux as follows:

❯ soul -d sqlite.db -p 8000
/mnt/c/Users/marti/AppData/Roaming/npm/node_modules/soul-cli/soul/config/index.js:32
  throw new Error(`Config validation error: ${error.message}`);
  ^

Error: Config validation error: "PORT" is required

#3 but with any field