Creating a Todo App - 7: Database Manipulation via API

Creating a Todo App - 7: Database Manipulation via API

Mia Temma's photo
Mia Temma
·

5 min read

In the database tutorial, we already showed how to create a REST-ful API that returns JSON-formatted output. Now let's apply our knowledge to this project as well.

We want to be able to send POST-requests to the application in order to delete or modify items.

The standard PicoLisp http.l library is defining POST and GET requests. (PUT, DELETE and so on are not available). If you want to check the source code, you can find it in the lib/folder of your PicoLisp installation (search for the client and http function).

Target

Let's create three functions:

  • getAllTodos should return all ToDo items from the database as JSON,
  • deleteTodo should delete a Todo item which is specified via an ID parameter
  • addTodo should return a new Todo item.

As little simplification, let's assume that all three functions should be reachable from the outside without authentication.

Basic syntax for POST and GET requests

In order to be able to reach a function or variable from the outside, it needs to be set in the allowed function. Let's add them first:

(allowed ... "!addTodo" "!deleteTodo" "!getAllTodos" *ToDoItem *Date *Color *Item *ItemID)

Generally, we have two possibilities to define global variables via the GET or POST request. Let's say we want to call the function deleteTodo with the parameter *ItemID.

Variant 1: using command line parameters:

curl <base-url>/!deleteTodo?*ItemID=-A333

Due to the -, the parser recognizes this parameter as external symbol.

Variant 2: using body payload and the -F option in curl:

 curl http://localhost:8086/\!deleteTodo -F "*ItemID={650}"

Note that with the latter option, the *ItemID "{650}" is parsed as a plain string.

The getAllTodos function

First of all, let's create the getAllTodos function. We want to return a JSON formatted output. First of all, we need add the json library @lib/json.l to the load function.

Then we define the list. We can get all items that have a date entry with the following query:

(collect 'dat '+Todo)

This returns a list. (Note: of course this doesn't find any objects with empty datefield).

Then we feed this list into the printJson function and format it a little bit:

(de getAllTodos ()
   (httpHead "application/json" 0)
   (ht:Out *Chunked
      (printJson
         (mapcar
            '((This)
               (list
                  This
                  (cons 'date (datStr (: dat)))
                  (cons 'item (: item))
                  (cons 'color (: color)) ) )
            (collect 'dat '+Todo) ) ) ) )

Now we can start a curl request or access it from the browser at localhost:8086/!getAllTodos:

getAllTodos.png

The addTodo function

Next, let's create the addTodo function. We take the command line or body parameters *Date, *Item and Color. All three of them are strings, so it doesn't matter really if they come in by GET or POST request.

However, if you want to restrict it to either of them, you can check the type of request by *Post, which will be NIL for GET and T for POST requests.

Now that we don't have a front-end check anymore, we need to double-check that the items are valid before calling the addToDatabase function (we can also check it directly in that function). We have three parameters. *Date needs to be a valid date string, Color needs to be either black, green, blue, red or yellow, and *Item should not be NIL. You could think of further checks if needed.

(ifn 
   (and 
      (strDat *Date) 
      (member *Color '("black" "green" "blue" "red" "yellow"))
      *Item )
         (respond "Wrong format")

If any of these conditions is not fulfilled, the application replies with "Wrong format". Otherwise we create a new item and return the JSON of that item.

Let's avoid to duplicate the whole long printJson function. Instead, we can refactor the previous JSON-code into a listToJson function that takes a list as argument:

(de listToJson (lst)
   (httpHead "application/json" 0
   ...

(de getAllTodos()
   (listToJson (collect 'dat +Todo)) )

(de addTodo ()
   (ifn (and (strDat *Date) (member *Color '("black" "green" "blue" "red" "yellow"))
         *Item )
      (respond "Wrong format")
      (listToJson 
         (list 
            (addToDatabase *Item (strDat *Date) *Color)) ) ) )

Now we can add items via curl, for example like this:

$ curl http://localhost:8086/\!addTodo -X POST -F "*Item=new Item" -F "*Color=green" -F "*Date=2021-12-15"
{"{663}": {"date": "2021-12-15", "item": "new Item", "color": "green"}}

The delete function

Finally, let's also implement the delete function. It should take the symbol name as argument in a GET-Request. If the deletion was successful, it should reply with "Deleting successful", otherwise with "Item doesn't exist or wrong format". Deleting is a dangerous operation, so we should make sure to have some checks inside.

First of all, the external symbol should exist. This we can check with the ?ext function. Secondly, it should be a +Todo object (remember that we also stored +User objects in the same database!). We check this with isa '+Todo.

Thirdly, the T property should not be set. Why? Because the method lose!> that is used to delete items from the database, is actually not really deleting them physically (this is done by the garbage collector at a later point). Instead, it sets the T property as a deleted flag. Since we don't want to confuse the user, we should avoid deleting objects that are already deleted.

Let's write all of these conditions into a ifn statement:

(de deleteTodo ()
   (respond
      (ifn
         (with *ItemID
            (and
               (ext? This)
               (not (: T))
               (isa '+Todo This) ) )
         "Item doesn't exist or wrong format"
         (lose!> *ItemID)
         "Deleting successful") ) )

Now we can delete database entrys remotely with commands like: curl http://localhost:8086/\!deleteTodo -F "*ItemID=-{650}" (note: the -d data option does not work, you need to use -F for "form fields").

How authentication could be added

If we don't require authentication (like in this example), anybody could mess with the database without any protections. So in a real application, how should we do it?

One idea would be to request username and password for each request.Of course we need a HTTPS connection to do that (we will learn about that in the next post). The disadvantage is that the plaintext password is maybe stored somewhere in log files. A probably better idea is to generate API-Keys that identify the user. The API keys are stored in the database and could be sent via cookie or body payload.

You can find the source code of the finished version here.

Sources

picolisp.com/wiki/?ServerSetup
software-lab.de/doc/httpGate.html

Functional ProgrammingdatabaseWeb Development