Implement LiteREST: A lightweight SQLite-based REST API by stritt · Pull Request #10 · outerbase/starbasedb

stritt · 2024-10-05T05:08:42Z

LiteREST: SQLite API

This is a small API for SQLite. It's like PostgREST, but simpler because well it's for SQLite.

What it does:

Creates, reads, updates, and deletes data
Works with any table in your SQLite database
Prevents SQL injection

How it works:

You make HTTP requests to it
It turns those into SQL
It runs the SQL and gives you back the results

What's next:

Better filtering and sorting
Transactions
Auth

Testing the REST

1. Test the POST Endpoint:

curl -X POST "https://starbasedb.{YOUR-WORKER-ID}.dev/lite/user" \
  -H "Content-Type: application/json" \
  -H 'Authorization: Bearer ABC123' \
  --data-raw '{"id": 753, "name": "Alice"}'

Expected Result:
A successful response with the message "Resource created successfully" and the inserted data.

2. Test the GET Endpoint:

curl -X GET "https://starbasedb.{YOUR-WORKER-ID}.dev/lite/user" \
  -H 'Authorization: Bearer ABC123'

Expected Result:
A list of users, including the newly inserted user.

3. Test the PATCH Endpoint:

curl -X PATCH "https://starbasedb.{YOUR-WORKER-ID}.dev/lite/user/753" \
  -H "Content-Type: application/json" \
  -H 'Authorization: Bearer ABC123' \
  --data-raw '{"name": "Alice Updated"}'

Expected Result:
A successful response with the message "Resource updated successfully" and the updated data.

4. Test the DELETE Endpoint:

curl -X DELETE "https://starbasedb.{YOUR-WORKER-ID}.dev/lite/user/753" \
  -H 'Authorization: Bearer ABC123'

Expected Result:
A successful response with the message "Resource deleted successfully".

I've tested each operation manually. However, we should add some automated tests next.

Let me know what you think. Is this useful? What would you change?

Brayden · 2024-10-07T15:33:17Z

Love this. A couple of thoughts I'm just going to tag here to keep track of:

Need to properly add these requests to the enqueueOperation so they can get processed in order
One thing left to consider is tables that don't rely on id as their PK. Should we fetch their PK first (assuming one column exists as that constraint) and replace the usage of id with their actual column name?
Should we include a PUT here alongside the already existing PATCH?

Overall very much on board with having a built in REST API interaction layer built right on top of the database for those who don't have complex querying use cases. Future considerations may be optionally allowing this to be deployed independently on its own worker binded to the same DO resource. At the same time though, this is lightweight enough where it won't impact anything for the users.

Brayden · 2024-10-10T13:31:06Z

Worked with Brandon on refactoring some of the code and utilizing some recently added helper functions for properly enqueueing requests. Below are some test cURLs to verify functionality.

GET

Fetch data from the database.

Equals

Get any entry that matches the column named name inside the users table where name = Alice.

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?name=Alice' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

Not Equals

Get any result that does NOT equal the provided value.

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?name.ne=Alice' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

LIKE

The URL has %25 appended to it which represents the % character. We need the % character to represent in SQL any number of characters can appear here to be considered "LIKE".

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?name.like=Al%25' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

IN

Get all results that match the names in the IN criteria, which the example below includes Alice and Bob.

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?name.in=Alice,Bob' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

Greater Than

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?user_id.gt=0' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

Greater Than or Equal

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?user_id.gte=1' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

Less Than

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?user_id.lt=3' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

Less Than or Equal

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?user_id.lte=3' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

SORT BY & ORDER

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?sort_by=user_id&order=DESC' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

LIMIT & OFFSET

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?limit=2&offset=1' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

A bit of everything

curl --location --request GET 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users?name.in=Alice%2CBob&user_id.gte=0&email.like=%25example.com&sort_by=user_id&order=DESC&limit=10&offset=0' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Content-type=application/json'

POST

curl --location 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: text/plain' \
--header 'Content-type: application/json' \
--data-raw '{
  "name": "Brayden",
  "email": "[email protected]"
}'

DELETE

curl --location --request DELETE 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users/4' \
--header 'Authorization: Bearer ABC123'

PUT

A PUT command is to do a FULL replacement of the entry in the table. For partial updates see PATCH

curl --location --request PUT 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users/4' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: text/plain' \
--header 'Content-type: application/json' \
--data-raw '{
    "name": "Brandon",
    "email": "[email protected]"
}'

PATCH

A PATCH command is to do a PARTIAL replacement of the entry in the table. For full updates see PUT

curl --location --request PATCH 'https://starbasedb.{YOUR-IDENTIFIER}.workers.dev/lite/users/4' \
--header 'Authorization: Bearer ABC123' \
--header 'Content-Type: text/plain' \
--header 'Content-type: application/json' \
--data-raw '{
    "email": "[email protected]"
}'

Implement LiteREST: A lightweight SQLite-based REST API

1961313

stritt and others added 5 commits October 8, 2024 21:15

added in put and refactored a bit

809ae9f

Take original files

a54afe5

Merge remote-tracking branch 'origin/main' into pr/10

ef82aae

Update logic in LiteREST file

3dbd7a7

Add some documentation code

1c317d3

Brayden added 2 commits October 10, 2024 09:39

Remove unnecessry logs

75e2537

Add a README for LiteREST examples

ac04674

Brayden merged commit ff62e31 into outerbase:main Oct 10, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Implement LiteREST: A lightweight SQLite-based REST API#10

Implement LiteREST: A lightweight SQLite-based REST API#10
Brayden merged 8 commits intoouterbase:mainfrom
stritt:brandon/literest

stritt commented Oct 5, 2024

Uh oh!

Brayden commented Oct 7, 2024

Uh oh!

Brayden commented Oct 10, 2024 •

edited

Loading

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

stritt commented Oct 5, 2024

LiteREST: SQLite API

What it does:

How it works:

What's next:

Testing the REST

1. Test the POST Endpoint:

2. Test the GET Endpoint:

3. Test the PATCH Endpoint:

4. Test the DELETE Endpoint:

Uh oh!

Brayden commented Oct 7, 2024

Uh oh!

Brayden commented Oct 10, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

GET

Equals

Not Equals

LIKE

IN

Greater Than

Greater Than or Equal

Less Than

Less Than or Equal

SORT BY & ORDER

LIMIT & OFFSET

A bit of everything

POST

DELETE

PUT

PATCH

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Brayden commented Oct 10, 2024 •

edited

Loading