# Creating a Todo App - 7: Database Manipulation via API

In the database tutorial, we already showed [how to create a REST-ful API](https://picolisp-blog.hashnode.dev/how-to-create-a-restful-api-to-the-picolisp-database) 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 http://localhost:8086/!getAllTodos: 


![getAllTodos.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637247539061/nlFQY8H64.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](https://gitlab.com/picolisp-blog/web-applications/-/blob/main/todo-list-example/todo-rest.l).


------------------

# Sources

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