Modify DATABASE_URL on the fly

[abt-123]

Hello,

I am interested in using SQLPage as a dashboard to display information from multiple databases.
I was able to create a table of results from Table in DB #1, and it looks great and was easy using SQLPAGE! :)

But I would like to also link to a new page where I can display results from an entirely different DB.
Since I am using a Windows environment variable for DATABASE_URL, is there a way for me to be able to change the DATABASE_URL when navigating from page 1 to page 2?

I read through the documentation and did not see anything related other than the use of the sqlpage.json, which I don't think would be of any help as it only appears to have the ability to list a single database url.

Apologies if I missed something already documented, I would be thankful to be pointed in the direction of where to look to learn my answer.

Thank you!

[lovasoa]

Hello and welcome to SQLPage !

Unfortunately, a single instance of SQLPage cannot query multiple databases at the moment. I will be working on that soon, though, because it's one of the building blocks of SQLPage cloud.

Current solutions

Launch multiple instances of SQLPage

SQLPage is extremely lightweight, and a single low-end server can easily run tens of instances of SQLPage, each with their own environment variables. The multiple instances must be listening on different ports, but they can share the same root folder.

You can use standard hyperlinks between the multiple instances. And if you have a domain name and want the result to be totally transparent to the user when they look at the URL, you can setup a reverse proxy on top of the multiple instances to dynamically select which instance serves which path.

Connect to one database from the other one

If you have full access to one of the databases, then you can use your database's ability to connect to another database. What database system are you using exactly ?

Here are a few pointers to start:

• postgres : https://www.postgresql.org/docs/current/postgres-fdw.html
• Microsoft SQL Server : https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver16&tabs=dedicated
• MySQL : https://dev.mysql.com/doc/refman/8.0/en/federated-create-server.html
• sqlite : https://www.sqlite.org/lang_attach.html
• multiple data sources : https://roapi.github.io/docs/index.html

[stegl83]

Hi,

I'm using SQLite and just tried a lot of things to run SQLPage with multiple Sqlite-Databases.
The idea was to create a "master" DB which has all the other sqliteDBs attached. Then, I can use my own Application to register dynamically datebase by saving a DB-File in the folder and modifiing the views of the master DB.

The bad news is, it seems that in sqlite attached DBs are not persistent on the database itself. The attach is bound to the connection and so the attachments have to be renewed after each connection.open.

I can use attach in migrations-commands but when I open SQLPage-URL the attachment is gone.
But I can not use ATTACH in the queries of .sql files.

So the missing piece for me is:
Modifying the sqlite-file (or connString) by parameter in the URL.
OR
The Ability to run preparing SQL-statements (attach) after the connection was opened and before the queries for the components were run.

[lovasoa]

Hi @althaf004 ! Did you have a problem with on_connect.sql ? You should be able to write all your ATTACH statements in sqlpage/on_connect.sql

[althaf004]

im trying to access another postgres from SQLPage. Is it possible.

[lovasoa]

I'm not sure I understand your situation: are you trying to display information from

• two postgres databases ?
• a postgres and a SQLite ?

[althaf004]

im trying to display from two postgres databases in same instance

[lovasoa]

Good, you should probably use https://www.postgresql.org/docs/current/postgres-fdw.html then.

It allows you to request an external postgres server from your first server.

[alitrack]

if support DuckDB, we can easily access DuckDB, SQLite, Postgres, MYSQL, CSV,Excel, json, API, Parquet at the same time

[lovasoa]

What format is your data in ? Are you just speaking generally, or do you have a particular usecase in mind ?

In the latter case, SQLite, Postgres, MYSQL are already supported natively. For the other formats you may be interested to try roapi, which supports many different data formats, and can be connected to using the postgres protocol.

[accforgithubtest]

Thanks for pointing to roapi !

I have been keeping an eye out for the multiple databases support in sqlpage.
Can you pls let me know your thoughts on whether roapi will be the way forward for multiple database support in sqlpage ?

Also, for anyone new to roapi / thinking of using it with sqlpage, are there any caveats / drawbacks in using roapi with sqlpage in terms of performance or any limitations to the current sqlpage feature-set / etc?

(I understand roapi is meant for slow moving data and the limitations associated with a slow-moving read-only data as the starting point.)