Can I use Sqlite3 in ESPAsyncWebServer

[Meekdai]

Board: ESP32-ESP32-WROVER-B Sqlite3: 2.3 ESPAsyncWebServer: 1.2.3 Use platformio with vscode

I am building a small web server on ESP32. when I visit the website "http://myesp32ip/x", it response the data in Sqlite3. data_array is s global JsonArray variable, In dense web visits, it may have problems doing this. Can someone tell me the correct way, Thanks.

#include <Arduino.h>

#include <WiFi.h>
#include "time.h"
#include <ESPAsyncWebServer.h>
#include <AsyncTCP.h>

#include "AsyncJson.h"
#include "ArduinoJson.h"

#include <FS.h>
#include <SPIFFS.h>
#include <SPIFFSEditor.h>
#include <sqlite3.h>

#include <esp_heap_caps.h>

sqlite3 *db1;
AsyncWebServer server(80);

const char* ssid = "XXX";
const char* password = "XXX";

const char * hostName = "esp-async";
const char* http_username = "admin";
const char* http_password = "admin";

const char* data = "Callback function called";
static int callback(void *data, int argc, char **argv, char **azColName) {
   int i;
   Serial.printf("%s: ", (const char*)data);
   for (i = 0; i<argc; i++){
       Serial.printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   Serial.printf("\n");
   return 0;
}

DynamicJsonDocument doc(1024);
JsonArray data_array = doc.to<JsonArray>();

static int callbackJson(void *data, int argc, char **argv, char **azColName) {
    JsonObject row = data_array.createNestedObject();
    for (int i = 0; i < argc; i++) {
        char* field = argv[i];
        row[azColName[i]] = field ? field : NULL;
    }
    return 0;
}

int db_open(const char *filename, sqlite3 **db) {
   int rc = sqlite3_open(filename, db);
   if (rc) {
       Serial.printf("Can't open database: %s\n", sqlite3_errmsg(*db));
       return rc;
   } else {
       Serial.printf("Opened database successfully\n");
   }
   return rc;
}

char *zErrMsg = 0;
int db_exec(sqlite3 *db, const char *sql) {
   Serial.println(sql);
   long start = micros();
   int rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
   if (rc != SQLITE_OK) {
       Serial.printf("SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   } else {
       Serial.printf("Operation done successfully\n");
   }
   Serial.print(F("Time taken:"));
   Serial.println(micros()-start);
   return rc;
}

void notFound(AsyncWebServerRequest *request) {
    request->send(404, "text/plain", "Not found");
}

void setup() {
    time_t now;
    Serial.begin(115200);
    WiFi.mode(WIFI_STA);
    WiFi.begin(ssid, password);
    if (WiFi.waitForConnectResult() != WL_CONNECTED) {
        Serial.printf("WiFi Failed!\n");
        return;
    }

    Serial.print("IP Address: ");
    Serial.println(WiFi.localIP());

    SPIFFS.begin();

    sqlite3_initialize();
    SPIFFS.remove("/database.db");
    db_open("/spiffs/database.db", &db1);
    db_exec(db1, "CREATE TABLE IF NOT EXISTS espblog_article (pid INTEGER PRIMARY KEY, title TEXT, created INTEGER, modified INTEGER, text TEXT, cover TEXT, category TEXT, authorId INTEGER, look INTEGER)");
    db_exec(db1, "CREATE TABLE IF NOT EXISTS espblog_comments (cid INTEGER PRIMARY KEY, pid INTEGER, created INTEGER, authorId INTEGER, ownerId INTEGER, mail TEXT, url TEXT, ip TEXT, agent TEXT, text TEXT, parent INTEGER)");

    server.on("/x", HTTP_GET, [](AsyncWebServerRequest *request){
        doc.clear();
        // Serial.println(doc.memoryUsage());
        AsyncJsonResponse * response = new AsyncJsonResponse(true,2048);
        response->addHeader("Server","ESP Async Web Server");
        JsonArray root = response->getRoot();

        int args = request->args();
        String a,s,c,u;
        for(int i=0;i<args;i++){
            // Serial.printf("ARG[%s]: %s\n", request->argName(i).c_str(), request->arg(i).c_str());
            if(request->argName(i)=="a"){a = request->arg("a");}
            else if(request->argName(i)=="s"){s = request->arg("s");}
            else if(request->argName(i)=="c"){c = request->arg("c");}
            else if(request->argName(i)=="u"){u = request->arg("u");}
        }

        if(a=="h"){
            sqlite3_exec(db1, "SELECT * FROM espblog_article ORDER BY created DESC LIMIT 5", callbackJson, (void*)data, &zErrMsg);
        }
        root.set(data_array);
        serializeJson(root, Serial);
        response->setLength();
        request->send(response);
    });

    server.onNotFound(notFound);
    server.begin();
}

void loop() {
}