Pretty URLs feature via GET param?

[matthewlarkin]

Hey SQLPagers,

Has anyone run into the need to display pretty urls for certain routes? For instance, instead of mydomain.com/get-started.sql we have mydomain.com/get-started.

For static links, I believe this is possible like so:

sqlpage/
get-started/
└── index.sql
index.sql

But it would be ideal to permit something more dynamic, such as:

• mydomain.com/meet/matthew
• mydomain.com/meet/rachel

(where matthew and rachel here are dynamic)

---

If it's not already built-in, I wonder if rewriting the URL based on a given get parameter might be feasible. For instance, via a configuration setting that watches for a declared get parameter and rewrites the URL accordingly.

Where something like:

{
  "allow_exec" : true,
  "port" : "3001",
  "pretty_param" : "q"
}

would instruct SQLPage to rewrite mydomain.com/meet.sql?q=matthew to mydomain.com/meet/matthew

[lovasoa]

Hello !

SQLPage itself does not have built-in support for URL rewriting. However, you can achieve this by using a reverse proxy in front of SQLPage.

Here's a step-by-step guide to setting up nginx as a reverse proxy for SQLPage with URL rewriting:

1. Install Nginx:
   Ensure that Nginx is installed on your server. On debian or ubuntu you can do this by running:

   sudo apt-get update
   sudo apt-get install nginx

2. Configure Nginx:
   Create a new Nginx configuration file for your SQLPage. For example, create a file named sqlpage.conf in the Nginx configuration directory:

   sudo nano /etc/nginx/sites-available/sqlpage.conf

3. Nginx Configuration:
   Add the following configuration, adjusting the values as needed:

   server {
       listen 80;
       server_name yourdomain.com;
   
       location / {
           proxy_pass http://127.0.0.1:8080;  # Change the port if SQLPage is running on a different port
           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;
       }
   
       # Rewrites URLs for the '/meet/' path to match the SQLPage query structure.
       # For example, transforms 'mydomain.com/meet/matthew' to 'mydomain.com/meet.sql?q=matthew'.
       location /meet/ {
           rewrite ^/meet/(.*)$ /meet.sql?q=$1 last;
       }
   
       # Add more location blocks for other dynamic routes as needed
   
       # Additional Nginx configuration goes here if necessary
   }

   Save the file and exit the editor.

4. Enable the Site:
   Create a symbolic link to enable the site:

   sudo ln -s /etc/nginx/sites-available/sqlpage.conf /etc/nginx/sites-enabled/

5. Test Configuration:
   Ensure there are no syntax errors in your Nginx configuration:

   sudo nginx -t

6. Restart Nginx:
   Restart Nginx to apply the changes:

   sudo systemctl restart nginx

Now, your SQLPage should be accessible through Nginx, and URLs like mydomain.com/meet/matthew will be rewritten to mydomain.com/meet.sql?q=matthew. Adjust the configuration as needed for other dynamic routes.

Other benefits

Using a reverse proxy not only enables URL rewriting but also provides additional benefits like :

• Access Control: Nginx allows you to restrict access to your SQLPage service based on IP addresses or authentication mechanisms, adding an additional layer of security.
• High Availability: Load balancing enhances reliability by ensuring that if one SQLPage instance fails, others can still handle requests.
• Static Content: Nginx can cache static content, reducing the load on your SQLPage server and improving response times for frequently accessed resources.
• Efficient Static File Serving: Nginx excels at serving static files, reducing the load on your SQLPage service for tasks better suited to a web server.
• Rate limiting to ensures fair resource allocation by preventing individual clients or malicious actors from monopolizing server resources. This helps maintain consistent performance for all users accessing your SQLPage service.
• Multiple websites Nginx lets you co-locate SQLPage with another website on the same server, allowing both applications to share server resources, optimizing resource utilization and reducing overall hosting costs.

[matthewlarkin]

Great Ophir, this is perfect, I use NGINX but didn't realize it could do this.

🙏 Thank you!

[lovasoa]

Just in case someone would stumble on this discussion: there is now a guide to using a reverse proxy in front of sqlpage on the website: https://sql.ophir.dev/your-first-sql-website/nginx.sql