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 dozens 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.)

[lovasoa]

Here is an example setup that connects to two databases: a main postgres database, and a second mysql database using the postgres mysql foreign data wrapper:

docker-compose.yml

version: '3.8'

services:

  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: mysecretpassword
      MYSQL_DATABASE: my_mysql_db
    healthcheck:
      test: [ "CMD-SHELL", "mysqladmin ping -h localhost -uroot -pmysecretpassword" ]
      interval: 5s
      timeout: 5s
      retries: 5
    volumes:
      - ./mysql_init.sql:/docker-entrypoint-initdb.d/mysql_init.sql
    ports:
      - "3306:3306"

  postgres:
    image: postgres:15-bookworm
    depends_on:
      mysql:
        condition: service_healthy
    environment:
      POSTGRES_PASSWORD: mysecretpassword
    ports:
      - "5432:5432"
    volumes:
      - ./mysql_fdw.sql:/docker-entrypoint-initdb.d/mysql_fdw.sql
    healthcheck:
      test: [ "CMD-SHELL", "pg_isready -U postgres" ]
      interval: 5s
      timeout: 5s
      retries: 5
    command: >
      bash -c "
        apt-get update &&
        apt-get install -y postgresql-15-mysql-fdw &&
        docker-entrypoint.sh postgres"

  sqlpage:
    image: lovasoa/sqlpage:latest
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      - DATABASE_URL=postgresql://postgres:mysecretpassword@postgres:5432/postgres
    ports:
      - "8080:8080"
    volumes:
      - ./:/var/www

mysql_fdw.sql

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (
    host 'mysql',
    port '3306',
    fetch_size '100',
    character_set 'auto',
    use_remote_estimate 'true',
    reconnect 'true'
);

CREATE USER MAPPING FOR CURRENT_USER SERVER mysql_server OPTIONS (username 'root', password 'mysecretpassword');

IMPORT FOREIGN SCHEMA my_mysql_db
FROM
    SERVER mysql_server INTO public;

mysql_init.sql

CREATE TABLE
    customers (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100),
        email VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

INSERT INTO
    customers (name, email)
VALUES
    ('John Doe', '[email protected]'),
    ('Jane Smith', '[email protected]'),
    ('Bob Wilson', '[email protected]'),
    ('Alice Brown', '[email protected]'),
    ('Charlie Davis', '[email protected]');

You can then query the mysql tables as if they were postgres tables from sqlpage:

select * from customers; -- customers is a mysql table, but it is accessible from sqlpage's postgres connection

select 'debug' as component, version() as version;

@accforgithubtest , this may be of interest to you.

[accforgithubtest]

@lovasoa - this is awesome !
Looks like a great solution, definitely going to try this first instead of roapi.

Thank you so much for your time ! really appreciate this detailed helpful example.

[accforgithubtest]

@lovasoa - Tried to set this up today, and I see this log in postgres container logs -

PostgreSQL Database directory appears to contain a database; Skipping initialization

Both the postgres installation as well as the mariadb/mysql installation are pre-existing and have data in them.

Does that mean that the mysql_fdw.sql in postgres docker-entrypoint-initdb.d wont get executed ?

I have tried restarting the postgres container many time, yet see no logs about the mariadb/mysql connection.
I assume something about the connection from postgres to mariadb would show up in the logs ?

[lovasoa]

Indeed, scripts in /docker-entrypoint-initdb.d/ are run only when the database is initialized. If you already have a database that you manage through other tools, then use those tools to initialize the foreign data wrapper. For instance, if you manage your database schema using sqlpage migrations, then you can run the initialization script as a migration, from the ./sqlpage/migrations/ directory.

[accforgithubtest]

thankyou @lovasoa, will give the sqlpage migrations approach a try.