SQLPage + ROAPI ?

[accforgithubtest]

Has anyone had success with using roapi to connect multiple databases and integrate it with sqlpage ?
I somehow seem to be running into issues with every step of the way, so if anyone has any working examples to share, I would be very grateful to learn from it.

Here are the issues I face (which I am asking for help in roapi issues as well) -

1. Unable to connect to Mysql (This apparently needs custom docker builds that I haven't been able to get working, and the roapi team also seem to be unable to get it working as per the comments - 319 ).
2. Unable to get Postgres connection 355
3. Unable to connect to SQLite 354

To test some basic functionality, I got roapi to read a csv file and a json file (all test data), connected sqlpage to roapi using postgres wire, and ran into more issues -

1. Support for variables when using postgres wire protocol to connect sqlpage and roapi.
   I use variables (mysql) to make some bits of the data/code reusable. To adapt the same in postgres, I need to use with testvars(var1, var2) as (values(1, 2)). However roapi is erroring out as sqlpage tries to run this as a sql on a postgres connection, but roapi doesnt seem to deal with this the variables. This seems like a big concern as it means the entire sqlpage application that connects to roapi as the datasource cannot use variables and therefore the sql files might get messy with repeating code, too many hard-coded strings, etc.

So, If anyone has got any success stories with roapi and sqlpage, I would be very keen to hear about it and thankful to learn from it !

[lovasoa]

what is your setup? do you have one MySQL and one postgres database?

Have you tried using postgres foreign data wrappers (FDW) ?

[accforgithubtest]

Yes, I have both mysql and postgres, and would like eventually be able to query multiple databases.
But the issues I am facing above are all just initial attempts to connect to just one database at a time using roapi.
So far with roapi, I have been able to only test load json file, but json also ran into issues with nested data

The issue about the support for variables is also just one connection to roapi, which is reading a test json file.
And trying to use the with since sqlpage is connected to roapi using postgres connection.

In terms of set up - its just vanilla sqlpage and roapi via docker compose.
The relevant bits of configurations are

sqlpage docker compose file important bits of change -

environment: 
  DATABASE_URL: postgresql://roapi:5432
  DEFAULT_THEME: 'dark'

roapi docker compose file -

services:
  roapi:
    image: ghcr.io/roapi/roapi:latest
    container_name: roapi
    command: --config /config/roapi.yml
    environment:
      - RUST_BACKTRACE=1
    ports:
      - 5432:5432
    volumes:
      - ./config/:/config
      - ./testdata/:/data:ro

roapi.yml file -

- name: "test_data"
    uri: "/data/test.json"
    option:
      format: "json"

test.json file in the testdata folder for roapi

[
  {
    "Name": "BP Willoughby",
    "Price": 175.9,
    "Brand": "BP",
    "IsAlternate": false,
    "Prices": [
      {
        "FuelType": "E10",
        "Price": 175.9
      },
      {
        "FuelType": "U91",
        "Price": 177.9
      },
      {
        "FuelType": "P98",
        "Price": 200.9
      },
      {
        "FuelType": "PDL",
        "Price": 180.9
      }
    ]
  },
  {
    "Name": "BP Lane Cove",
    "Price": 175.9,
    "Brand": "BP",
    "IsAlternate": false,
    "Prices": [
      {
        "FuelType": "E10",
        "Price": 175.9
      },
      {
        "FuelType": "U91",
        "Price": 177.9
      },
      {
        "FuelType": "P98",
        "Price": 200.9
      },
      {
        "FuelType": "PDL",
        "Price": 181.9
      },
      {
        "FuelType": "LPG",
        "Price": 104.9
      }
    ]
  },
  {
    "Name": "BP Willoughby",
    "Price": 175.9,
    "Brand": "BP",
    "IsAlternate": false,
    "Prices": [
      {
        "FuelType": "E10",
        "Price": 175.9
      },
      {
        "FuelType": "P95",
        "Price": 191.9
      },
      {
        "FuelType": "P98",
        "Price": 200.9
      },
      {
        "FuelType": "PDL",
        "Price": 181.9
      }
    ]
  },
  {
    "Name": "Speedway Roseville",
    "Price": 179.7,
    "Brand": "Speedway",
    "IsAlternate": false,
    "Prices": [
      {
        "FuelType": "E10",
        "Price": 179.7
      },
      {
        "FuelType": "U91",
        "Price": 181.7
      },
      {
        "FuelType": "P98",
        "Price": 201.7
      },
      {
        "FuelType": "PDL",
        "Price": 171.9
      }
    ]
  }
]

then in index.sql file in sqlpage

with envVars (defaultThemeLightDark) as ( values ( sqlpage.environment_variable( 'DEFAULT_THEME' ) ) )

[lovasoa]

Ideed, roapi has its own SQL parser, that may not support all features supported by traditional databases.

You should try connecting to an actual postgres database instead, and use foreign data wrappers to connect to secondary databases.

[accforgithubtest]

use foreign data wrappers to connect to secondary databases

thank you for your reply @lovasoa !

I am hearing about foreign data wrappers for the first time now, I will have a look into.
Sounds like it be a good solution to get all traditional postgres features needed to run full sqlpage feature set, and still be able to use roapi behind it. I will have a read thru for how to set it up and will post updates here. Cheers!