B4J Tutorial [Web][BANano] Plug n Play PHP CRUD REST API for SQLite with Authentication

Hi Fam

Previously, my post below dealt with adding a CRUD REST API in a matter of minutes to any back-end.

[SithasoDaisy] Plug n Play PHP CRUD REST API (MySQL, SQLite, MSServer, PostGreSQL) for BANano

Hi Plug n Play? Yep... 1. Add the api.php file to your assets. Get it from this GitHub repo I spoke about this in 2022 on the BVAD3 library. https://www.b4x.com/android/forum/threads/bananovuetifyad3-vflexdialog-mysql-crud-rest-api-php-using-axios.142814/ 2. Change the connection parameters...

www.b4x.com

We also looked at how we can add authentication to this REST API on this post.

[Web][SithasoDaisy] Secure CRUD MySQL REST API Web App using Api Key on top of HTTPS

Hi Fam This is a fully functional webapp that showcases categories & products. One is able to toggle views between table listing and a grid listing. Both these have functionality to edit, clone, delete records from the database. When adding products, we load categories in a select/combo box...

www.b4x.com

Today we look at how we can use a SQLite file for the backend with authentication also. Thanks to this Github project.

I have updated the api.php file to point to the SQLite file on the assets folder and also added the needed authentication key.

Let's do this.

 

[Mashiane]

Assets

1. We have the SQLIte database bibleshow.db
2. We have the api.php file. The config section has been updated to be

$config = new Config([
        'driver' => 'sqlite',
        'address' => '../assets/bibleshow.db',
        'controllers' => 'records,openapi,status,columns',
        'debug' => true,
        'tables' => 'all',
        'middlewares' => 'sslRedirect,apiKeyAuth,sanitation,json',
        'apiKeyAuth.keys' => 'cC7vzkeSMWeWvyR8B6PgwlGv0PLkSodZ16Gpjjg8xOuwzmp3KbKvcRcR0GU1okIpsIcuq3ORpcnGvpxnyqStyrJnq5fz10y1gCqzOaisGXUfKQQ5orKLkWWu4PedieyF',
        'apiKeyAuth.header' => 'X-API-Key',
    ]);

We use the sqlite driver, give the address to point to the assets file we want. Ensure that the PDO Sqlite driver is installed on your PHP WebServer.
We have also added a couple of controllers and middlewares to make everything work.

3. We have the love.txt file, this contains the auth key that we will use via fetch api. We read this and use it for the header key 'X-API-Key'

4. Both the api.php and sqlite file (bibleshow.db) will be on the assets folder when we deploy our app.

 

[Mashiane]

Deployment

Our webapp is deployed on Laragon

To access the CRUD REST API, we can use POSTMAN.

OpenAPI

Let's use the OpenAPI specs to get the schema of the API

https://localhost/sdsqliteapi/assets/api.php/openapi

Get Records from a Table

Let's read records from one of the tables, we will read the Analysis

https://localhost/sdsqliteapi/assets/api.php/records/Analysis

 

[Mashiane]

Getting the database schema

For us to be able to get the schema of the database, we added the columns controller. To get the complete schema of the database we execute this API call.

https://localhost/sdsqliteapi/assets/api.php/columns

 

[Mashiane]

Other useful API Calls

https://localhost/sdsqliteapi/assets/api.php/status/ping

 

[Mashiane]

REST API Documentation

With the OpenAPI call, you can export the result and import it on Swagger. Once imported you can view the REST API for the tables.