Overview
I came across an interesting open source project called SQLite Soul on geeknews, so I thought I’d give it a try and post about it. It’s a bit hands-on, so I’m splitting it into two posts.
What is SQLite Soul
SQLite Soul is an open source project that provides a Realtime REST server for SQLite. To summarize, you can create a RESTful API server using SQLite alone without any development implementation.
https://thevahidal.github.io/soul/
Preparation
To actually implement this project, you’ll need the following
- SQLite
- nodejs
Install SQLite
- On a MAC, install
1brew install sqlite
2```bash brew install sqlite
3
4- Installing on Linux (Debian)
5
6```bash
7sudo apt-get update
8sudo apt install sqlite3
Install nodejs
- Installing on MAC
1brew install node
- Install on Linux (Debian)
Verify installation
- Verify node installation
- Install sqlite
Install Soul
After completing the above preparations, execute npm to install Soul.
1 npm install -g soul-cli
If the installation is successful, you can see the output like below when you enter soul.
1> soul
2
3Usage: soul [options]
4
5Options:
6 --version Show version number [boolean]
7 -d, --database SQLite database file or :memory: [string] [required]
8 -p, --port Port to listen on [number]
9 -r, --rate-limit-enabled Enable rate limiting [boolean]
10 -c, --cors CORS whitelist origins [string]
11 -a, --auth Enable authentication and authorization [boolean]
12
13 --iuu, --initialuserusername Initial user username [string]
14 --iup, --initialuserpassword Initial user password [string]
15
16 --ts, --tokensecret Token Secret [string]
17 --atet, --accesstokenexpirationtime Access Token Expiration Time (Default: 5H) [string]
18 --rtet, --refreshtokenexpirationtime Refresh Token Expiration Time (Default: 1D) [string]
19 -S, --studio Start Soul Studio in parallel
20 --help Show help
Prepare SQLite data
To test Soul, we created the Product and Inventory tables for an inventory management program in the SQLite database and inserted sample data.
Product table
- id: Product’s unique number
- name: Product name
- category: Product category
- price: Product price
| id | name | category | price |
|---|---|---|---|
| 1 | Laptop | Electronics | 1200 |
| 2 | Mouse | Electronics | 25 |
| 3 | Keyboard | Electronics | 45 |
| 4 | Monitor | Electronics | 150 |
| 5 | Chair | Furniture | 85 |
Inventory table
- product_id: Unique number of the product (associated with the id in the Product table)
- quantity: Inventory quantity
| product_id | quantity |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 15 |
| 4 | 17 |
| 5 | 8 |
Script
- Create a database
1sqlite3 inventory.db
- DDL and data insertion
1CREATE TABLE Product (
2 id INTEGER PRIMARY KEY,
3 name TEXT NOT NULL,
4 category TEXT NOT NULL,
5 price DECIMAL NOT NULL
6);
7
8CREATE TABLE Inventory (
9 product_id INTEGER NOT NULL,
10 quantity INTEGER NOT NULL,
11 FOREIGN KEY(product_id) REFERENCES Product(id)
12);
13
14INSERT INTO Product (id, name, category, price) VALUES
15(1, 'Laptop', 'Electronics', 1200),
16(2, 'Mouse', 'Electronics', 25),
17(3, 'Keyboard', 'Electronics', 45),
18(4, 'Monitor', 'Electronics', 150),
19(5, 'Chair', 'Furniture', 85);
20
21INSERT INTO Inventory (product_id, quantity) VALUES
22(1, 10),
23(2, 20),
24(3, 15),
25(4, 17),
26(5, 8);
After executing and exiting the above script, we have all the data set up for testing: (inventory.db)
Run the server
Execute the command below to start the SQLite-based Realtime API server on port 8000.
1soul -d inventory.db -p 8000
2
3Warning: Soul is running in open mode without authentication or authorization for API endpoints.
4Please be aware that your API endpoints will not be secure.
5No extensions directory provided
6Soul is running...
7 > Core API at http://localhost:8000/api/
When the server is executed above, the RESTful API server is operated based on the table. Currently, the tables we created are Product and Inventory, and if you want to get all the product-related data, you can call them like below.
- Calling the Product Table
1curl -X GET http://localhost:8000/api/tables/Product/rows
- Call result
1{
2 "data":[
3 {
4 "id":1,
5 "name":"Laptop",
6 "category":"Electronics",
7 "price":1200
8 },
9 {
10 "id":2,
11 "name":"Mouse",
12 "category":"Electronics",
13 "price":25
14 },
15 {
16 "id":3,
17 "name":"Keyboard",
18 "category":"Electronics",
19 "price":45
20 },
21 {
22 "id":4,
23 "name":"Monitor",
24 "category":"Electronics",
25 "price":150
26 },
27 {
28 "id":5,
29 "name":"Chair",
30 "category":"Furniture",
31 "price":85
32 }
33 ],
34 "total":5,
35 "next":null,
36 "previous":null
37}
As you can see, we’ve simply connected SQLite via soul, and the requests are exposed as if we’ve implemented a real API server. We can even go into localhost:8000/api/docs and see that it even generates the relevant API-related Swagger for us.
Summary
In this post, we learned about the Soul project and did some preliminary work to get started with it. We saw a simple table stored in SQLite and called through the server, and in the next post, we will go deeper into the authentication and CRUD features provided by Soul.
Translated with DeepL.com (free version)