search

lovasoa / SQLpage

SQL-only webapp builder, empowering data analysts to build websites and applications quickly
https://sql.ophir.dev
MIT License
880 stars 62 forks source link

`configuration_directory` env variable seems not to work in 0.24.0 #454

Closed matthewlarkin closed 2 days ago

matthewlarkin commented 1 week ago

Introduction

Hi @lovasoa, it seems 0.24.0 has broken the configuration_directory env variable.

To Reproduce

Change configuration_directory in the .env file. SQLPage is sourcing the .env file, and web_root setting does still apply correctly.

I also tried removing the web_root and bringing all files to the .env path, but no success there either.

Downgrading to 0.23 resolves the issue.

Version information

lovasoa commented 1 week ago

Can you provide specific reproduction steps ? Step by step, what are you doing, what would you expect, and what do you see instead ?

I just tried the following:

❯ cat > /tmp/sqlpage.json
{ "database_url": "xyz" }

❯ echo CONFIGURATION_DIRECTORY=/tmp/ > .env

and it does pick up the configuration file from the specified CONFIGURATION_DIRECTORY:

❯ ./sqlpage 
[2024-06-24T16:22:49.865Z INFO  sqlpage] Loaded environment variables from "/home/olojkine/dev/sqlpage/.env"
[2024-06-24T16:22:49.869Z ERROR sqlpage] "xyz" is not a valid database URL. Please change the "database_url" option in the configuration file.
matthewlarkin commented 6 days ago

Hey @lovasoa,

Here is my replication. I've removed SQLPage from the system and downloaded both the 0.23.0 and 0.24.0 MacOS binaries to my desktop. Here's a step-by-step in bash:

# Simple test project setup
mkdir my-sqlpage && cd my-sqlpage
mkdir source && mkdir config
echo 'CONFIGURATION_DIRECTORY="./config/"' >> .env
echo 'WEB_ROOT="./source"' >> .env
mkdir config/templates && echo '{{ contents }}' >> config/templates/custom.handlebars
echo "select 'custom' as component, 'my test' as contents;" >> source/index.sql

# Installing 0.23.0
mv ~/Desktop/sqlpage-0.23.0.bin /usr/local/bin/sqlpage && chmod +x /usr/local/bin/sqlpage

sqlpage

Output

[2024-06-26T03:50:18.690Z INFO  sqlpage] Loaded environment variables from "/Users/groveos/my-sqlpage/.env"
[2024-06-26T03:50:18.694Z INFO  sqlpage::app_config] No DATABASE_URL provided, "sqlpage/sqlpage.db" is writable, creating a new database file.
[2024-06-26T03:50:18.695Z INFO  sqlpage::webserver::database::connect] Connecting to database: sqlite://sqlpage/sqlpage.db?mode=rwc
[2024-06-26T03:50:18.726Z INFO  sqlpage::webserver::database::migrations] Not applying database migrations because './config/migrations' does not exist
[2024-06-26T03:50:18.726Z INFO  sqlpage::webserver::http] Will start HTTP server on 0.0.0.0:8080
[2024-06-26T03:50:18.731Z INFO  sqlpage] Server started successfully.
        SQLPage is now listening on http://0.0.0.0:8080 (accessible on all networks of this computer)
        You can write your website's code in .sql files in ./source.
[2024-06-26T03:50:35.621Z INFO  actix_web::middleware::logger] 127.0.0.1 "GET / HTTP/1.1" 200 841 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36" 0.006787

🙌 That succeeds, both at serving ./source/index.sql and fetching the ./config/templates/custom.handlebars component

Then I remove 0.23.0 and install 0.24.0.

# Upgrade to 0.24.0
rm /usr/local/bin/sqlpage
mv ~/Desktop/sqlpage-0.24.0.bin /usr/local/bin/sqlpage && chmod +x /usr/local/bin/sqlpage

sqlpage

Output

[2024-06-26T03:58:48.886Z INFO  sqlpage] Loaded environment variables from "/Users/groveos/my-sqlpage/.env"
[2024-06-26T03:58:48.891Z INFO  sqlpage::webserver::database::connect] Connecting to database: sqlite:///Users/groveos/my-sqlpage/sqlpage/sqlpage.db
[2024-06-26T03:58:48.925Z INFO  sqlpage::webserver::database::migrations] Not applying database migrations because './sqlpage/migrations' does not exist
[2024-06-26T03:58:48.931Z INFO  sqlpage] SQLPage v0.24.0 started successfully.
        Now listening on 0.0.0.0:8080: accessible from the network, and locally on http://localhost:8080
        You can write your website's code in .sql files in ./source

🙅‍♂️ While it does source ./source/index.sql correctly, it does not source the ./config directory like 0.23.0 did. It's insisting on defaulting to ./sqlpage as the default config directory.

Any idea what might be causing this? I know there was an issue with DATABASE_URL recently. Maybe something related?

lovasoa commented 6 days ago

I tried to reproduce it, but couldn't : 

/tmp/my-sqlpage 
❯ mkdir source && mkdir config

/tmp/my-sqlpage 
❯ echo 'CONFIGURATION_DIRECTORY="./config/"' >> .env
echo 'WEB_ROOT="./source"' >> .env

/tmp/my-sqlpage 
❯ mkdir config/templates && echo '{{ contents }}' >> config/templates/custom.handlebars

/tmp/my-sqlpage 
❯ echo "select 'custom' as component, 'my test' as contents;" >> source/index.sql

/tmp/my-sqlpage 
❯ sudo cp /tmp/sqlpage.bin /usr/local/bin/sqlpage

/tmp/my-sqlpage took 2s 
❯ sqlpage
[2024-06-26T08:22:05.304Z INFO  sqlpage] Loaded environment variables from "/tmp/my-sqlpage/.env"
[2024-06-26T08:22:05.305Z INFO  sqlpage::webserver::database::connect] Connecting to database: sqlite:///tmp/my-sqlpage/config/sqlpage.db
[2024-06-26T08:22:05.328Z INFO  sqlpage::webserver::database::migrations] Not applying database migrations because './config/migrations' does not exist
[2024-06-26T08:22:05.333Z INFO  sqlpage] SQLPage v0.24.0 started successfully.
        Now listening on 0.0.0.0:8080: accessible from the network, and locally on http://localhost:8080
        You can write your website's code in .sql files in ./source

Maybe it's a MacOS-specific issue ? Do you have access to a linux machine on which you could try to reproduce it ? I'll try to reproduce on macos when I can.

lovasoa commented 6 days ago

Also, could you post the logs of a reproduction of the issue with more verbose logging activated ?

RUST_LOG=trace sqlpage

Here is what the results look like for me:

/tmp/my-sqlpage 
❯ RUST_LOG=sqlpage=trace sqlpage
[2024-06-26T08:25:35.727Z INFO  sqlpage] Loaded environment variables from "/tmp/my-sqlpage/.env"
[2024-06-26T08:25:35.727Z DEBUG sqlpage::app_config] Loading configuration from "/tmp/my-sqlpage/config"
[2024-06-26T08:25:35.728Z TRACE sqlpage::app_config] Configuration sources: { shlvl => 1, vte_version => 7600, gnome_setup_display => :1, gnome_shell_session_mode => ubuntu, _ => /usr/local/bin/sqlpage, ssh_auth_sock => /run/user/1000/keyring/ssh, lc_numeric => en_GB.UTF-8, lc_name => en_GB.UTF-8, lc_measurement => en_GB.UTF-8, xdg_runtime_dir => /run/user/1000, rust_log => sqlpage=trace, web_root => ./source, session_manager => local/ophirwork:@/tmp/.ICE-unix/3303,unix/ophirwork:/tmp/.ICE-unix/3303, memory_pressure_write => c29tZSAyMDAwMDAgMjAwMDAwMAA=, qt_accessibility => 1, oldpwd => /tmp, wayland_display => wayland-0, im_config_phase => 1, language => en_GB:en, colorterm => truecolor, starship_session_key => 2594724771608829, user => olojkine, qt_im_module => ibus, xauthority => /run/user/1000/.mutter-Xwaylandauth.5SXUP2, xdg_session_class => user, term => xterm-256color, lc_telephone => en_GB.UTF-8, xdg_data_dirs => /usr/share/ubuntu:/usr/share/gnome:/usr/local/share/:/usr/share/:/var/lib/snapd/desktop, lc_time => en_GB.UTF-8, xdg_session_type => wayland, xdg_menu_prefix => gnome-, lc_monetary => en_GB.UTF-8, lc_paper => en_GB.UTF-8, xdg_current_desktop => ubuntu:GNOME, username => olojkine, xmodifiers => @im=ibus, gnome_desktop_session_id => this-is-deprecated, gtk_modules => gail:atk-bridge, gnome_terminal_service => :1.151, xdg_session_desktop => ubuntu, display => :0, lc_address => en_GB.UTF-8, memory_pressure_watch => /sys/fs/cgroup/user.slice/user-1000.slice/user@1000.service/session.slice/org.gnome.Shell@wayland.service/memory.pressure, path => /home/olojkine/.cargo/bin:/home/olojkine/.pyenv/bin:/home/olojkine/.local/bin:/home/olojkine/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin:/snap/bin, shell => /bin/bash, debuginfod_urls => https://debuginfod.ubuntu.com , pyenv_root => /home/olojkine/.pyenv, gsm_skip_ssh_agent_workaround => true, lang => en_GB.UTF-8, lc_identification => en_GB.UTF-8, dbus_session_bus_address => unix:path=/run/user/1000/bus, logname => olojkine, papersize => a4, desktop_session => ubuntu, gnome_terminal_screen => /org/gnome/Terminal/screen/920f0d66_e399_46d8_9145_162f8468f164, configuration_directory => ./config/, xdg_config_dirs => /etc/xdg/xdg-ubuntu:/etc/xdg, pwd => /tmp/my-sqlpage, systemd_exec_pid => 3334, home => /home/olojkine, starship_shell => bash, gdmsession => ubuntu,  }
[2024-06-26T08:25:35.728Z DEBUG sqlpage::app_config] Using the default database file in /tmp/my-sqlpage/config/sqlpage.db
[2024-06-26T08:25:35.728Z DEBUG sqlpage::app_config] Loaded configuration: AppConfig {
        database_url: "sqlite:///tmp/my-sqlpage/config/sqlpage.db",
        max_database_pool_connections: None,
        database_connection_idle_timeout_seconds: None,
        database_connection_max_lifetime_seconds: None,
        sqlite_extensions: [],
        listen_on: None,
        port: None,
        unix_socket: None,
        database_connection_retries: 6,
        database_connection_acquire_timeout_seconds: 10.0,
        web_root: "./source",
        configuration_directory: "./config/",
        allow_exec: false,
        max_uploaded_file_size: 5242880,
        https_domain: None,
        https_certificate_email: None,
        https_certificate_cache_dir: "/tmp/my-sqlpage/sqlpage/https",
        https_acme_directory_url: "https://acme-v02.api.letsencrypt.org/directory",
        environment: Development,
        site_prefix: "/",
        max_pending_rows: 256,
        compress_responses: true,
    }
[2024-06-26T08:25:35.728Z DEBUG sqlpage::webserver::database::connect] Connecting to a Sqlite database on sqlite:///tmp/my-sqlpage/config/sqlpage.db
[2024-06-26T08:25:35.728Z INFO  sqlpage::webserver::database::connect] Connecting to database: sqlite:///tmp/my-sqlpage/config/sqlpage.db
[2024-06-26T08:25:35.728Z DEBUG sqlpage::webserver::database::connect] Not creating a custom SQL database connection handler because "./config/on_connect.sql" does not exist
[2024-06-26T08:25:35.730Z DEBUG sqlpage::webserver::database::connect] Initialized database pool: Pool {
        size: 1,
        num_idle: 1,
        is_closed: false,
        options: PoolOptions {
            max_connections: 16,
            min_connections: 0,
            connect_timeout: 10s,
            max_lifetime: None,
            idle_timeout: None,
            test_before_acquire: true,
        },
    }
lovasoa commented 6 days ago

Just tried it on MacOs (with sqlpage 0.24 from homebrew). Can't reproduce either:

/tmp
❯ mkdir my-sqlpage && cd my-sqlpage

/tmp/my-sqlpage 
❯ mkdir source && mkdir config

/tmp/my-sqlpage 
❯ echo 'CONFIGURATION_DIRECTORY="./config/"' >> .env

/tmp/my-sqlpage 
❯ echo 'WEB_ROOT="./source"' >> .env

/tmp/my-sqlpage 
❯ mkdir config/templates && echo '{{ contents }}' >> config/templates/custom.handlebars

/tmp/my-sqlpage on  
❯ echo "select 'custom' as component, 'my test' as contents;" >> source/index.sql

/tmp/my-sqlpage 
❯ brew install sqlpage
[...]
==> Pouring sqlpage--0.24.0.sonoma.bottle.tar.gz
🍺  /usr/local/Cellar/sqlpage/0.24.0: 9 files, 27.2MB
[...]

/tmp/my-sqlpage on  took 24s 
❯ sqlpage
[2024-06-26T09:37:01.920Z INFO  sqlpage] Loaded environment variables from "/private/tmp/my-sqlpage/.env"
[2024-06-26T09:37:01.920Z INFO  sqlpage::app_config] No DATABASE_URL provided, /private/tmp/my-sqlpage/config/sqlpage.db is writable, creating a new database file.
[2024-06-26T09:37:01.920Z INFO  sqlpage::webserver::database::connect] Connecting to database: sqlite:///private/tmp/my-sqlpage/config/sqlpage.db?mode=rwc
[2024-06-26T09:37:01.942Z INFO  sqlpage::webserver::database::migrations] Not applying database migrations because './config/migrations' does not exist
[2024-06-26T09:37:01.946Z INFO  sqlpage] SQLPage v0.24.0 started successfully.
        Now listening on 0.0.0.0:8080: accessible from the network, and locally on http://localhost:8080
        You can write your website's code in .sql files in ./source
matthewlarkin commented 2 days ago

@lovasoa, following up here.

It's working for me now; I don't know why exactly. I copied the same script I pasted above, and now it just works.

The only thing I changed recently was completely reseting my sqlpage installs everywhere I could think:

sudo rm /usr/local/bin/sqlpage
brew uninstall sqlpage
brew unlink sqlpage

And then brew installed it again

brew install sqlpage

Maybe I didn't do those steps the first time? I thought I did...

Oh well, happy to be on the 0.24.0 train 🕺

lovasoa commented 2 days ago

Good that it's fixed. Don't hesitate to reopen if you can reproduce again.

matthewlarkin commented 2 days ago

Absolutely @lovasoa, thanks! By the way, I did discover this:

At least on ubuntu, CONFIGURATION_DIRECTORY seems to be case sensitive while WEB_ROOT is not:

(ls -a && cat .env && timeout 1s sqlpage);
sed -i 's/^configuration_directory/CONFIGURATION_DIRECTORY/' .env;
(ls -a && cat .env && timeout 1s sqlpage);

outputs:

.  ..  config  .env  source
configuration_directory="./config/"
web_root="./source"
[2024-06-30T16:52:16.485Z INFO  sqlpage] Loaded environment variables from "/root/sqlpage-tmp/.env"
[2024-06-30T16:52:16.485Z INFO  sqlpage::app_config] No DATABASE_URL provided, ./sqlpage/sqlpage.db is writable, creating a new database file.
[2024-06-30T16:52:16.485Z INFO  sqlpage::webserver::database::connect] Connecting to database: sqlite://./sqlpage/sqlpage.db?mode=rwc
[2024-06-30T16:52:16.506Z INFO  sqlpage::webserver::database::migrations] Not applying database migrations because './sqlpage/migrations' does not exist
[2024-06-30T16:52:16.508Z INFO  sqlpage] SQLPage v0.24.0 started successfully.
        Now listening on 0.0.0.0:8080: accessible from the network, and locally on http://localhost:8080
        You can write your website's code in .sql files in ./source
.  ..  config  .env  source  sqlpage
CONFIGURATION_DIRECTORY="./config/"
web_root="./source"
[2024-06-30T16:52:17.801Z INFO  sqlpage] Loaded environment variables from "/root/sqlpage-tmp/.env"
[2024-06-30T16:52:17.801Z INFO  sqlpage::webserver::database::connect] Connecting to database: sqlite:///root/sqlpage-tmp/config/sqlpage.db
[2024-06-30T16:52:17.821Z INFO  sqlpage::webserver::database::migrations] Not applying database migrations because './config/migrations' does not exist
[2024-06-30T16:52:17.822Z INFO  sqlpage] SQLPage v0.24.0 started successfully.
        Now listening on 0.0.0.0:8080: accessible from the network, and locally on http://localhost:8080
        You can write your website's code in .sql files in ./source