Sidebar + Navbar Template

[srcarvalho12]

It would be interesting to have the option to change the general template of the application, for example: Template with Sidebar + Navbar and Template with Navbar only

[lovasoa]

Hello and welcome to SQLPage!
The general template of the application is defined in the shell component, that already has quite a few options available.
However, creating a sidebar is currently not possible. Would you be interested in implementing this feature ? It shouldn't be too hard, because all the pieces are already in place. And I'll be here if you need help !

To get started, here are:

• the shell component source code: https://github.com/lovasoa/SQLpage/blob/main/sqlpage/templates/shell.handlebars
• an example of how to create a side menu with tabler, the css framework used in sqlpage: https://tabler.io/docs/layout/page-layouts

[srcarvalho12]

Wonderful, go ahead, I'll help you create a sidebar in the next few days

[lovasoa]

That's fantastic! I appreciate your willingness to collaborate. If you have any questions or encounter any challenges while working on it, feel free to reach out. You can open a "draft" pull request as soon as you have something, and I'll be happy to review it and give you pointers if you get stuck.

[srcarvalho12]

I managed to create a small sidebar draft #265 .
I had problems with "scrollbar-color", as it has a predefined style and I would like to use transparent, for example:
scrollbar-color: rgba(var(--tblr-scrollbar-color, var(--tblr-body-color-rgb)), .16) transparent;

[srcarvalho12]

I'm trying to configure nginx to use the reverse proxy on a VPS which works normally when pointing to "/", but if I try to access any endpoint like "/user.sql" it downloads the .sql file instead of opening the page, what can I do? Here is my vhost:

server {
listen 80;
listen [::]:80;
listen 443 ssl http2;
listen [::]:443 ssl http2;
{{ssl_certificate_key}}
{{ssl_certificate}}
server_name www.my-url.com;
return 301 https://my-url.com$request_uri;
}

server {
listen 80;
listen [::]:80;
listen 443 ssl http2;
listen [::]:443 ssl http2;
{{ssl_certificate_key}}
{{ssl_certificate}}
server_name my-url.com www1.my-url.com;
{{root}}

{{nginx_access_log}}
{{nginx_error_log}}

if ($scheme != "https") {
rewrite ^ https://$host$uri permanent;
}

location @reverse_proxy {
proxy_pass {{reverse_proxy_url}};
proxy_http_version 1.1;
proxy_set_header X-Forwarded-Host $host;
proxy_set_header X-Forwarded-Server $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;
proxy_set_header Host $http_host;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection "Upgrade";
proxy_pass_request_headers on;
proxy_max_temp_file_size 0;
proxy_connect_timeout 900;
proxy_send_timeout 900;
proxy_read_timeout 900;
proxy_buffer_size 128k;
proxy_buffers 4 256k;
proxy_busy_buffers_size 256k;
proxy_temp_file_write_size 256k;
}

{{settings}}

add_header Cache-Control no-transform;

index index.html;

location ^~ /.well-known {
auth_basic off;
allow all;
try_files $uri @reverse_proxy;
}

location / {
try_files $uri @reverse_proxy;
}
}

[lovasoa]

The try_files directive in Nginx specifies the files to attempt to serve before falling back to a specified URI or passing the request to a proxy server. It's typically used within a location block to define the behavior when a request matches that location.

In the provided configuration, the try_files directive is used within the location blocks to attempt to serve a file based on the specified URI. If the file is not found, it falls back to the @reverse_proxy location block, which proxies the request to another server.

When a request is made for an endpoint like "/user.sql," Nginx interprets it as a file request and attempts to serve the file directly if it exists in the specified root directory. If the file exists, Nginx serves it, which is not the desired behavior.

Therefore, you must ensure that Nginx doesn't serve files directly when handling requests meant for the reverse proxy. Adding a specific location block to handle requests for certain file extensions, like ".sql" in this case, can prevent Nginx from serving them directly and instead passing them to the reverse proxy.

[srcarvalho12]

Wonderful, thank you. Is it possible to forward endpoints like "/usar.sql" to "/user" using rewriting in nginx?

[lovasoa]

location ~ \.sql$ {
    include my/proxy/conf;
}

location / {
    try_files $uri @reverse_proxy;
}

location @reverse_proxy {
    include my/proxy/conf;
}

[srcarvalho12]

I sincerely thank you for your help!

[lovasoa]

possible to forward endpoints like "/user.sql" to "/user" using rewriting in nginx?

Yes, it's possible. See this previous discussion: #231

[srcarvalho12]

Incredible! I sincerely thank you for your great help.

[srcarvalho12]

I use nginx served through CloudPanel, its vhost comes in this standard that I sent above, but I tried to add these configuration lines:

location ~* ^(.).sql$ {
rewrite ^(.).sql$ $1 permanent;
}

It redirects to the page without the .sql, but gives a 404 error

[lovasoa]

What you want is the opposite, isn't it? You want nginx to receive requests without .sql, and rewrite then to add .sql before forwarding them to SQLPage.

[srcarvalho12]

In fact, I would like to call pages with .sql and nginx remove the .sql, for example /clientes.sql for /clientes

[lovasoa]

You want your browser to show /clientes, and SQLPage to load the file clientes.sql. So you need nginx to rewrite the request sent by your browser from /clientes to /clientes.sql before forwarding it to SQLPage. This way, the user sees /clientes and SQLPage sees /clientes.sql.

[srcarvalho12]

I added these lines to the vhost, but instead of directing to, for example, /users is directing to /users/ and SQLPage does not recognize these requests:

Reescrever apenas as solicitações para arquivos .sql

rewrite ^/(.*)\.sql$ /$1 break;

# Não reescrever solicitações para arquivos existentes
if (!-e $request_filename) {
    # Reescrever apenas solicitações para arquivos .css e .js
    rewrite ^/(.*\.(css|js))$ /$1 break;
    rewrite ^/(.*)$ /$1.sql break;
}

[srcarvalho12]

In fact, discovering that requests, such as /users/ are going to users/.sql/index.sql, I believe it was my error in the rewriting.
However, requests to /users work normally.

[lovasoa]

Here is an example nginx configuration rule:

    location / {
      
      # When a request doesn't end with a '/' and doesn't have an extension, add '.sql' at the end 
      rewrite ^/((.*/)?[^/.]+)$ /$1.sql last;
      
      proxy_pass      http://localhost:8080;
    }

try online

[srcarvalho12]

Wonder! It worked as expected, thank you again, from the bottom of my heart for your help!

[lovasoa]

And a detailed explanation of how the ^/((.*/)?[^/.]+)$ regex works, with examples: https://regex101.com/r/edOrEC/1

[srcarvalho12]

Thank you very much!

[srcarvalho12]

Can I now use SQLPage to create complex applications like a customer manager, for example? Application that communicates with APIs and databases, or is it too early for that? Is it safe for you to use it for this purpose?

[lovasoa]

Hi ! If you want to get an idea of what can be built with sqlpage, you can have a look at this talk I gave at pgconf last december: https://www.youtube.com/watch?v=mXdgmSdaXkg

A customer manager sounds very possible, but of course it will depend on your precise requirements. Communication with APIs is possible using fetch, and with databases... well... you will write your entire application in sql, so of course !

And if you get stuck, don't hesitate to ask for help here !

[srcarvalho12]

Wonderful, thank you from the bottom of my heart for the information and help!

[srcarvalho12]

In the next update, would it be possible to implement some encryption functions, such as HMAC for example?

[srcarvalho12]

Something like:

Cargo.toml:
[dependencies]
ring = "0.16.20"

Function:

use ring::{hmac, digest};
use ring::rand::SystemRandom;

fn generate_hmac(secret_key: &[u8], message: &[u8]) -> Vec<u8> {
    // Choose a hash algorithm. In this example, we use SHA256.
    let algorithm = &digest::SHA256;

    // Creates an HMAC using the chosen algorithm and secret key.
    let hmac_key = hmac::Key::new(algorithm, secret_key);

    // Generates the HMAC for the message.
    let hmac_result = hmac::sign(&hmac_key, message);

    // Returns the HMAC as a vector of bytes.
    hmac_result.as_ref().to_vec()
}

[lovasoa]

Hello! This seems to be a different topic, maybe you can open a new feature request, where you present your project, and explain why you think a message signing function would be a good idea ?
Postgres already has a hmac function as part of its crypto module for instance.

[srcarvalho12]

Got it, I'll take a look at the PGSQL functions. But my idea of ​​use is that to use a certain API, I need to encrypt my password and pass it as HASH (via HMAC) to the endpoint.

[srcarvalho12]

Searching around I found this function for MySQL and MariaDB:

DROP FUNCTION IF EXISTS HMACSHA256;

-- here val is the message generate a HMAC for
DELIMITER //
CREATE FUNCTION HMACSHA256(secret_key VARCHAR(256), val VARCHAR(2048))
  RETURNS CHAR(64) DETERMINISTIC
BEGIN
DECLARE ipad,opad BINARY(64);
DECLARE hexkey CHAR(128);
DECLARE hmac CHAR(64);

SET hexkey = RPAD(HEX(secret_key),128,"0");

IF LENGTH(secret_key) > 64 THEN
   SET hexkey = RPAD(SHA2(secret_key, '256'), 128, "0");
END IF;

SET ipad = UNHEX(CONCAT(
    LPAD(CONV(CONV( MID(hexkey,1  ,16), 16, 10 ) ^ CONV( '3636363636363636', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,17 ,16), 16, 10 ) ^ CONV( '3636363636363636', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,33 ,16), 16, 10 ) ^ CONV( '3636363636363636', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,49 ,16), 16, 10 ) ^ CONV( '3636363636363636', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,65 ,16), 16, 10 ) ^ CONV( '3636363636363636', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,81 ,16), 16, 10 ) ^ CONV( '3636363636363636', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,97 ,16), 16, 10 ) ^ CONV( '3636363636363636', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,113,16), 16, 10 ) ^ CONV( '3636363636363636', 16, 10 ),10,16),16,"0")
));

SET opad = UNHEX(CONCAT(
    LPAD(CONV(CONV( MID(hexkey,1  ,16), 16, 10 ) ^ CONV( '5c5c5c5c5c5c5c5c', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,17 ,16), 16, 10 ) ^ CONV( '5c5c5c5c5c5c5c5c', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,33 ,16), 16, 10 ) ^ CONV( '5c5c5c5c5c5c5c5c', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,49 ,16), 16, 10 ) ^ CONV( '5c5c5c5c5c5c5c5c', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,65 ,16), 16, 10 ) ^ CONV( '5c5c5c5c5c5c5c5c', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,81 ,16), 16, 10 ) ^ CONV( '5c5c5c5c5c5c5c5c', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,97 ,16), 16, 10 ) ^ CONV( '5c5c5c5c5c5c5c5c', 16, 10 ),10,16),16,"0"),
    LPAD(CONV(CONV( MID(hexkey,113,16), 16, 10 ) ^ CONV( '5c5c5c5c5c5c5c5c', 16, 10 ),10,16),16,"0")
));

SET hmac = SHA2(CONCAT(opad,UNHEX(SHA2(CONCAT(ipad,val), '256'))), '256');

RETURN hmac;

END //
DELIMITER ;

[lovasoa]

I created a separate issue to avoid discussing it in this GitHub issue, which is about having a sidebar in the shell.

Let's move the discussion about hmac to #303

[srcarvalho12]

Ok, thanks!