Untitled

Overview

Learn more about SQLite Soul. Learn more about Soul’s certification and CRUD API through hands-on experience.

SQLite authentication

Authentication is mandatory when running a real API server. In Soul, you can set up related functions very simply.

1soul -p 8000 --d inventory.db -a --ts=secret --atet=4H --rtet=3D --iuu=penguin --iup=YQ8iTCN3OFhVF2r!

For each option below, we explain what it means.

  • a option: Enable authentication and authorization [boolean].
  • ts option: JWT TOKEN SECRET KEY [string].
  • atet option: Access Token expiration time [string]
  • rtet option: Refresh Token expiration time [string]
  • iuu option: Initial User ID [string] [string
  • IUP option: Initial user password [string]

If you run the server with this option, the user and permission tables are created and authentication is requested for all APIs.

superuser

When the server is initially started in authentication-required mode, a user is created with the initial values. The user will be granted per-table permissions for CRUD for each API, but will only have READ permissions unless otherwise specified.

Untitled

For ease of testing, we’ll change the user we initially created to a superuser, although this shouldn’t be necessary if you’re actually configuring a program with Soul.

1soul --d inventory.db updatesuperuser --id=1 --is_superuser=true

Authenticate

After initially creating the user, you will not need to enter a user and password the next time you spin up the server.

1$ soul -p 8000 --d inventory.db -a --ts=secret --atet=4H --rtet=3D

You can use the following API to get the authentication value.

 1$ curl -X POST 'http://localhost:8000/api/auth/token/obtain' \
 2--header 'Content-Type: application/json' \
 3--data '{
 4    "fields":         {
 5            "username" :"penguin",
 6            "password" : "YQ8iTCN3OFhVF2r!"
 7        }
 8}'
 9
10{"message":"Success","data":{"userId":1}}

If the authentication API succeeds, it will send down the cookie values for the Access Key and Secret Key as headers.

If you include the cookie values and execute the following, the API values will respond normally.

1curl -X GET 'http://localhost:8000/api/tables/Product/rows' \
2--header 'Cookie: accessToken={accessToken}'
  • The response value
 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}

Lookups

In general, Soul provides a lot of filtering or paging related processing that can be done by developing a lookup API.

Paging

You can utilize paging functionality when making a lookup.

  • The request
1curl -X GET 'http://localhost:8000/api/tables/Product/rows?_limit=2&_page=1' \
2--header 'Cookie: accessToken={accessToken}'
  • Response
 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    "total": 5,
17    "next": "/tables/Product/rows?_limit=2&_page=2",
18    "previous": null
19}

The response value gives the URL path to the previous or next page, if any: (previous, next)

Searching

The List API provides search functionality. You can search for a specific value or compare values.

  • Search for values that contain the word Laptop

    1curl -X GET 'http://localhost:8000/api/tables/Product/rows?_search=laptop' \
2--header 'Cookie: accessToken={accessToken}'

  • Response value

     1{
 2    "data": [
 3        {
 4            "id": 1,
 5            "name": "Laptop",
 6            "category": "Electronics",
 7            "price": 1200
 8        }
 9    ],
10    "total": 1,
11    "next": Null,
12    "previous": null
13}

  • Retrieve products with prices over 100 won

    1curl -X GET 'http://localhost:8000/api/tables/Product/rows?_filters=price__gt:100' \
2--header 'Cookie: accessToken={accessToken}'

  • Response value

     1{
 2    "data": [
 3        {
 4            "id": 1,
 5            "name": "Laptop",
 6            "category": "Electronics",
 7            "price": 1200
 8        },
 9        {
10            "id": 4,
11            "name": "Monitor",
12            "category": "Electronics",
13            "price": 150
14        }
15    ],
16    "total": 2,
17    "next": Null,
18    "previous": null
19}

Single lookup

You can get a single lookup of a specific value using an ID.

  • The request value

    1curl --location 'http://localhost:8000/api/tables/product/rows/5' \
2--header 'Cookie: accessToken={accessToken}'

  • Response value

     1{
 2    "data": [
 3        {
 4            "id": 5,
 5            "name": "Chair",
 6            "category": "Furniture",
 7            "price": 85
 8        }
 9    ]
10}

Create

You can use the POST function to create a resource for a table.

  • The request value

     1curl -X POST 'http://localhost:8000/api/tables/product/rows' \
 2--header 'Content-Type: application/json' \
 3--header 'Cookie: accessToken={accessToken}' \ --header 'Cookie: accessToken={accessToken}'
 4--data '{
 5    "fields" : {
 6        "name": "Monitor",
 7        "category": "Electronics",
 8        "price": 500
 9    }
10}'

  • Response value

    1{
2    "message": "Row inserted",
3    "data": {
4        "changes": 1,
5        "lastInsertRowid": 6
6    }
7}

Update

Updates the value of a specific resource. The update is performed as a partial update. (Update price value to 1000)

  • Request value

    1curl -X PUT 'http://localhost:8000/api/tables/product/rows/6' \
2--header 'Content-Type: application/json' \
3--header 'Cookie: accessToken={accessToken}' \ --header 'Cookie: accessToken={accessToken}'
4--data '{
5    "fields" : {
6        "price": 1000
7    }
8}'

  • Response value

    1{
2    "message": "Row updated",
3    "data": {
4        "changes": 1,
5        "lastInsertRowid": 6
6    }
7}

Delete

Deletes a resource in a table with a specific ID value.

  • Request value

    1curl -X DELETE 'http://localhost:8000/api/tables/product/rows/6' \
2--header 'Cookie: accessToken={acccesToken}'

  • Response value

    1{
2    "message": "Row deleted",
3    "data": {
4        "changes": 1,
5        "lastInsertRowid": 6
6    }
7}

Summary

In this post, we’ve shown how to set up and work with authentication in Soul, and how you can create CRUD APIs in no time with just SQLite, calling all of them, and see how they work in practice. While it may not be enough for real complex needs, I think it’s a project that can be incredibly productive for simple needs and in certain situations.

In addition to this CRUD, Soul also has support for the Join syntax, the ability to perform literal transactions, and web sockets. If I have time in the future, I’ll try to write a third installment.