Best practice for implementing clean language URLs (e.g., /en/, /pl/) with file-based routing?

[amrutadotorg]

Hi,

I'm implementing multilingual support in my SQLPage application and would like to ask about the best approach for handling clean language URLs like /en/ or /pl/ instead of using query parameters like ?lang=en.

Current Implementation

I have a single index.sql file that handles the main content and uses a lang variable throughout included files:

-- index.sql
SET lang = coalesce(
    json_extract(sqlpage.variables('get'), '$.lang'),
    'pl'
);

SELECT 'dynamic' AS component, sqlpage.run_sql('shell.sql') AS properties;
SELECT 'dynamic' AS component, sqlpage.run_sql('header.sql') AS properties;
-- ... other includes that use $lang

Proposed Solution for Clean URLs

To support URLs like https://example.com/en/, I created a 404.sql file to act as a router:

-- 404.sql
-- Extract 2 characters after the leading slash (e.g., 'en' from '/en/')
SET lang = substr(sqlpage.path(), 2, 2);

-- Validate the language code, defaulting to 'pl' if not recognized
SET lang = CASE 
    WHEN $lang IN ('pl', 'en') THEN $lang
    ELSE 'pl'
END;

-- Execute index.sql with the language as a parameter
SELECT 'dynamic' AS component, 
       sqlpage.run_sql('index.sql', json_object('lang', $lang)) AS properties;

And then I modified index.sql to accept parameters from run_sql():

-- index.sql (updated)
SET lang = coalesce(
    json_extract(sqlpage.variables('post'), '$.lang'),  -- From run_sql() parameter
    json_extract(sqlpage.variables('get'), '$.lang'),   -- From query string
    'pl'
);

-- ... rest of the content

Questions

1. Is this the recommended approach for implementing clean language URLs in SQLPage?
2. Are there any potential issues with using 404.sql to route language-specific URLs and then calling run_sql('index.sql')?
3. Is there a more elegant way to handle this pattern without duplicating routing logic?
4. Performance considerations: Does using run_sql() from within 404.sql have any significant overhead?

This approach works perfectly and maintains backward compatibility (both /en/ and ?lang=en work), but I want to ensure I'm following SQLPage best practices.

Summary of Behavior

• https://example.com/ → Serves index.sql directly (defaults to Polish).
• https://example.com/?lang=en → Serves index.sql with the lang query parameter.
• https://example.com/en/ → Hits 404.sql, which then runs index.sql with the language passed as a parameter.

Thanks for creating such an elegant framework! The file-based routing is fantastic for rapid development.

[lovasoa]

Hi ! The way you are doing it is currently the only way in sqlpage.

If you already have a reverse proxy in front of your app, then you can use it instead, and simplify your code. For instance, in nginx:

# /etc/nginx/sites-available/your_app.conf
server {
    listen 80;
    server_name example.com; # Replace with your domain

    # Point Nginx's root to your application's root (where index.sql is)
    root /path/to/your/sqlpage_app_directory;

    # Handle language prefixes: /en/path -> /path?lang=en
    # This location block processes requests starting with /en/ or /pl/
    location ~ ^/(en|pl)(/.*)?$ {
        # Capture the language code (e.g., 'en' or 'pl')
        set $lang_code $1;
        # Capture the rest of the path (e.g., '/about.sql' or empty string for '/en/')
        set $remaining_path $2;

        # If the request is for '/en/' or '/pl/' (empty $remaining_path),
        # rewrite to the root path '/' with the lang parameter.
        if ($remaining_path = "") {
            rewrite ^ /?lang=$lang_code&$args last;
        }

        # If there's a specific path (e.g., /en/about.sql),
        # rewrite to that path with the lang parameter.
        rewrite ^/(en|pl)(/.*)$ $remaining_path?lang=$lang_code&$args last;
    }

    # Proxy all requests (including rewritten ones) to the SQLPage application server
    # Assumes SQLPage is running via `sqlpage serve` on http://127.0.0.1:8080
    location / {
        proxy_pass http://127.0.0.1:8080; # Adjust port if necessary
        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;
    }

    # Optional: Serve static assets directly from Nginx for better performance
    # SQLPage doesn't need to process these files.
    location ~* \.(css|js|jpg|jpeg|gif|png|ico|svg|webp|woff2|woff|ttf|eot)$ {
        try_files $uri =404; # Nginx serves these directly
        expires 1y; # Cache for a year
        add_header Cache-Control "public";
    }
}