Encrypted Database through REST API with JSON results

This article describes how to use an encrypted database through REST APIs. The database used behind the scene is Siodb, which integrates natively REST APIs and encryption to allows you to push and retrieve encrypted data in JSON format.

1 – Get the database

wget https://siodb.io/packages/InstallSiodb.sh
chmod u+x ./InstallSiodb.sh
sudo ./InstallSiodb.sh

2 – Create a user with a token

USERTOKEN=$(openssl rand -hex 64)

sudo -i -u siodb siocli --user root <<eof
create user REST_API_USER ;
alter user REST_API_USER add token TOKEN_1 x'${USERTOKEN}' ;
eof

3 – Create your table

sudo -i -u siodb siocli --user root <<eof
create database REST_API_DB ;
create table REST_API_DB.EMPLOYEES( firstname text, lastname text, salary float, hire_date timestamp) ;
eof

4 – Push data

curl -k -X POST \
-d '[
    { "firstname": "马","lastname": "云","salary": "249000.00","hire_date": "1964-09-10"},
    { "firstname": "Юрий","lastname": "Гагарин","salary": "49000.00","hire_date": "1934-03-09"},
    { "firstname": "Barack","lastname": "Obama","salary": "149000.00","hire_date": "1961-08-04"}
]' \
https://rest_api_user:${USERTOKEN}@localhost:50443/databases/rest_api_db/tables/employees/rows \
| json_pp -json_opt pretty,utf8

Returns

{
	"status": 200,
	"affectedRowCount": 3,
	"trids": [1, 2, 3]
}

5 – Retrieve data

curl -s -k \
https://rest_api_user:${USERTOKEN}@localhost:50443/databases/rest_api_db/tables/employees/rows \
| json_pp -json_opt pretty,utf8

Returns

{
   "rows" : [
      {
         "FIRSTNAME" : "马",
         "HIRE_DATE" : "1964-09-10",
         "LASTNAME" : "云",
         "SALARY" : 249000,
         "TRID" : 1
      },
      {
         "FIRSTNAME" : "Юрий",
         "HIRE_DATE" : "1934-03-09",
         "LASTNAME" : "Гагарин",
         "SALARY" : 49000,
         "TRID" : 2
      },
      {
         "FIRSTNAME" : "Barack",
         "HIRE_DATE" : "1961-08-04",
         "LASTNAME" : "Obama",
         "SALARY" : 149000,
         "TRID" : 3
      }
   ],
   "status" : 200
}

6 – Update data

curl -k \
-X PATCH \
-d '[
{ "salary": "349000.00" }]' \
https://rest_api_user:${USERTOKEN}@localhost:50443/databases/rest_api_db/tables/employees/rows/1 \
| json_pp -json_opt pretty,utf8

Returns

{
   "affectedRowCount" : 1,
   "status" : 200,
   "trids" : [
      1
   ]
}

7 – Delete data

curl -k \
-X DELETE \
https://rest_api_user:${USERTOKEN}@localhost:50443/databases/rest_api_db/tables/employees/rows/2 \
| json_pp -json_opt pretty,utf8

Returns

{
   "affectedRowCount" : 1,
   "status" : 200,
   "trids" : [
      2
   ]
}

Bottom line

This article shows how easy it is to start pushing and retrieving data through REST APIs into a database. Siodb handles the REST APIs, encryption, and unique IDs for all your data. If you need more details, please comment below.

Willing to simplify your data technology for faster and safer apps coding? Try Siodb now!

Nicolas Penot

🧑Design, automate, and scale data technologies in the cloud for my clients.
📱 Text me to get support on Siodb 👉👉 +41 78 853 85 07
🚀 Contribute to the Siodb open source project 👉👉 https://github.com/siodb/siodb