How to do URL Prefixing in SQLPage Using Reverse Proxy

[dittybijil]

I have a setup where I use SQLPage with path at abc/index.sql. I want to make this page accessible with a prefix, such as /sample/test/, and I am attempting to achieve this using a reverse proxy (e.g., Nginx).

However, I am facing issues where the URL routing does not work as expected. Could you clarify if SQLPage supports such prefixing inherently, or if there are specific reverse proxy configurations required to make this work?

Thank you for your guidance!

[lovasoa]

Hello and welcome to SQLPage !

If I understand well, you launch sqlpage from a root folder xxx which is not the one you store your sql files in (they are in xxx/abc/). In addition to that, you want to have a site prefix of /sample/test/. This is possible using the SQLPage configuration file:

{
	"site_prefix": "/sample/test/",
	"web_root": "./abc/"
}

(you can also use environment variables, or command line arguments to set the parameters)

/tmp/sqlpage_site_prefix
❯ tree
.
├── abc
│   └── index.sql
└── sqlpage
    ├── sqlpage.db
    └── sqlpage.json

3 directories, 3 files

This works as-is, without a reverse proxy.
If you need to add a reverse proxy anyway (for instance to host other web services on paths other than /sample/test/), we have a guide for the setup of nginx and sqlpage.

[rinshadka]

You have to handle that at the application level. Your application's links should either all be relative, or contain the path prefix. If you want to be able to configure it in only one place and change it easily, you can store it in an environment variable.

in your sql code, everywhere you have a link:

select 'button' as component;

select 'Relative link, no /' as title, 'index.sql' as link;
select 'Absolute link, with /' as title, sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || 'index.sql' as link;

then run sqlpage with

export SQLPAGE_SITE_PREFIX='/sample/test/'
./sqlpage.bin

Thanks @lovasoa , so this will solve the difference we are having in the sqlpage sample code too right? I will demostrate what happens in our code comparing that with the sample code we have,

Started the application using:

SQLPAGE_SITE_PREFIX="/myapp" SQLPAGE_PORT=5006 SQLPAGE_HOST=10.10.11.248 sqlpage

and the prefix is working fine. Then added nginx and proxy_pass with the site prefix ENV like below:

    location /myapp {

        proxy_pass http://10.10.11.248:5006;

        # Set headers to pass the original host and IP information to the backend
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }

Proxy pass is working fine , and the move over is also working as expected.

But below link is not showing the full link along with the site prefix and nginx proxy passing breaks,

Final landing page:

Thanks.

[lovasoa]

This requires changes to the application. You cannot take an existing app and host it with a site_prefix of /myapp/ if the app was written for being hosted only on /. The app in examples/official-site was designed to run on https://sql.datapage.app/, without any site prefix.

[lovasoa]

What exactly is your final goal? Do you really want to operate your own clone of sql.datapage.app, or is this just an example?

[rinshadka]

What exactly is your final goal? Do you really want to operate your own clone of sql.datapage.app, or is this just an example?

this was an example. We have an sqlpage site which requires proxy passing via nginx. I was thinking and asking the developer that application should read the site_prefix just like the first screenshot , so that when served via nginx , it will be routed to the proper url. So developer should work on the second screenshot and update the link to include the site_prefix just like the first screenshot.
@dittybijil

[lovasoa]

So developer should work on the second screenshot and update the link to include the site_prefix

Or just use relative URLs (without a slash in the beginning) everywhere. That is the easiest option.

[rinshadka]

Understood Thank you 👍