Folder-based middleware

[purityhealth]

In my projects, a lot of header code gets repeated. Authentication, role checking, shell inclusion, etc. Also I often have to get the user_id from the session table using the cookie in order to filter queries.

It would be helpful to create a "middleware" script for a given folder that runs whenever any script in that folder is accessed. It would be like run_sql(...) except that any variables it creates internally are copied into the parent scope.

Here's an example of what I was thinking (postgres):

/auth/index.msql: (this is the middleware script)

set session_id = (
    select 
        session.id 
    from 
        session 
    where 
        session.sid = sqlpage.cookie('sid') 
        and session.created_at > now() + interval '-1 days' 
    );

-- invalid session, logout
select 'redirect' as component, '/logout.sql' as link where $session_id is null;

-- include shell
SELECT 'dynamic' AS component, sqlpage.read_file_as_text('shell.json') AS properties;

-- save user_id for future scripts
set user_id = (select user_id from session where id = cast($session_id as int));

/auth/index.sql:

select 'table' as component;
select * from data where user_id = cast($user_id as int);

/auth/detail.sql:

select 'table' as component;
select * from another_data_table where user_id = cast($user_id as int);

So the rule would for execution would be: In the current folder path, prior to executing any sql file in that path, look for an "index.msql" file and execute it. Take any resulting variables and copy them into the global scope, and then executed the requested script.

I have never written rust code before, but I could take a swing at this if it's something interesting.

[lovasoa]

Hello and welcome to SQLPage !

Your suggestion is interesting. Trying to split the problem and the suggested solution, I see two pain points when using SQLPage

Problems encountered

• For any reasonably complex website, sqlpage.run_sql(...) has to be repeated at the beginning of all pages. Repeating it everywhere is tedious, and forgetting to do so can even result in security vulnerabilities if authentication is handled in the included file.
• The only way to pass data from a file included with sqlpage.run_sql(...) to the main page is through the database (using a session variable or a temporary table). It would be nice to be able to easily pass values inside SQLPage from the included file to the parent.

Proposed solution

I see obvious advantages to your proposed solution

• backwards compatible (does not break existing sites)
• very "character-efficient": this requires very little code to get to the expected result. Once you would know the mechanism, using it would be

I see some big drawbacks too:

• Custom file extension, that would not be recognized by editors. But we could use another canonical name
• The bigger problem is teachability and newcomer-friendliness
• This adds a layer of magic, that could not be discovered naturally, and that you would have to learn when getting started, in addition to the concepts of sqlpage functions and components.
• Less debuggable: run_sql is explicit and easy to look up, documented together with other functions. That would be implicit, and would make errors harder to debug, as one would have to first understand where the issue comes from.
• You would never know where a variable comes from. Introducing a temporary variable in the index.msql could have unintented effects in another page that happens to use a URL parameter with the same name.
• Performance. Maybe less important, but this would slow down all existing installations even if they do not use the feature, since every request would now have to check for the existence of a middleware.
• Duplicates functionality. What would we recommend to include a common shell ? run_sql, or a middleware.

Alternative solution

• For having to repetitively type sqlpage.run_sql: I may be wrong, but I think this is worth the cost of making imports explicit, resulting in more readable and debuggable apps.
• For passing variables from an included file to the parent: What would you think of adding a new argument to run_sql, to explicitly specify the variables that you want to import from the included script ? sqlpage.run_sql('auth.sql', null, json_array('user_id')) to import $user_id from auth.sql.
• For security: we should offer a better authentication mechanism by default in sqlpage, so that it requires less manual coding to implement, and cannot be "forgotten".

@djyotta @matthewlarkin @Pieter3033 @lozdown @alexisrc1 @pchemguy I would be happy to have your thoughts on this.

[YunusMechery]

Two thoughts:

1. Incorporating auth and session related parameters to shell component itself
2. or, Something like the 404.sql solution

[paulpr0]

Hi, I was wondering whether a different function might make sense rather than adding parameters to sqlpage.run_sql.
How about a function sqlpage.include('header.sql') where the included file is run as if it's been pasted into the file. This would solve for the case where your header sets up variables to be used within the page without making the run_sql more complicated.

[lovasoa]

How about a function sqlpage.include('header.sql') where the included file is run as if it's been pasted into the file.

What would be the return value of such a function? It would need to be some kind of "magic" function that does not follow the semantics of SQL...

Or do you just mean a function that returns json just like run_sql, but also sets variable values in the currently running file?

[paulpr0]

Point taken, as I described it, it would be a "magic" function in comparison to how the other functions work in sqlpage.

I think your suggestion of an additional argument which specifies a list of variables to import would be very useful.

On a related note, what about a component which sets a variable? If you could do:

select 'variable' as component, '$foo' as name, 'bar' as value

In @purityhealth 's original example, the multiple line header could be replaced by one stored procedure (in postgres, at least) along the lines of:

select * from my_page_header_function(sqlpage.cookie('sid'));

that function then either returns a single row with a redirect component, or two rows - a shell component and a variable component.

I could have a go at implementing a variable component if you think it's worth having. With a quick look it seems like it would involve a small change to stream_query_results_with_conn to call execute_set_variable_query in cases where a row matches component='variable'.

I love this project by the way - it makes database driven websites so quick and easy to buld!

[djyotta]

@paulpr0 you could take this a step further and have each page insert dynamically generated sql into _sqlpage_files and then redirect to the dynamic page.
I recall there was some issue with this approach (multiple concurrent transactions I think), not to mention the security implications of dynamically generating code and running it...
This was my first attempt to avoid excessive code duplication before I came up with my crazy nested run_sql hack...

[purityhealth]

Regarding drawbacks, perhaps a better answer is to put middleware into sqlpage/middleware path, so that it's separated from regular requests. And then to add a return value from run_sql which allows controlling copying variables to the current scope, as setting up the request context for the user in each script can be a lot of repeated code.

So the new file instead of /auth/index.msql is something like /sqlpage/middleware/auth.sql

select 'dynamic' as component, sqlpage.run_sql('middleware/auth.sql') as properties_copy_scope; -- properties_copy_scope means copy the variables.

This design may address these drawbacks:

• Custom file extension, no longer an issue
• Teachability is perhaps not as bad because important data and files are already being put under sqlpage path like templates, and run_sql requires pretty good understanding of the documentation already
• Magic and debug-ability may not be such a large issue anymore because it's still called explicitly.
• Performance no longer impacted as it's explicit
• Now still using run_sql so can continue to recommend that option

So maybe the only drawback that still exists are the potential to overwrite variables and to be confused by that. Maybe having it as a separate return property from run_sql handles that since they are choosing to do so?

Also with this sort of plan, now that it's being called explicitly, the utility of what I was thinking is reduced from what I originally proposed. So the main problem this would solve is now things like setting the user_id in scope.

I think the issue of "forgetting" authentication is a big problem that should be handled somehow though, and a lot of frameworks that exist out there provide a magical way to restrict URLs.

Here are some examples of what I find myself repeating:

1. Checking the user session is active
2. Including the shell
3. Filtering the data the user can access to only their allowed data
4. Auditing the access of the data to an audit log
5. Checking that user has a proper role for the section of the application they are trying to access.

[lovasoa]

sqlpage.run_sql is just a function that returns a array. And the dynamic component is just a component called in normal sql; it's not some built-it sqlpage syntax. When the dynamic component is called, it's already too late, it cannot access the variables from middleware/auth.sql, since it just gets a json array.

The user could just as well call

select 'dynamic' as component, JSON('{"component": "text", "contents": "Hello world"}') as properties_copy_scope;

and the component would see the same thing as when you call

select 'dynamic' as component, sqlpage.run_sql('middleware/auth.sql') as properties_copy_scope;

and the component would have nowhere to take the variables from.

Functions do not know in which component they are called, and components do not know which function was called to generate their properties. And I think this is something we should keep, this provides encapsulation and helps to reason about the code.

[matthewlarkin]

Very interesting problems to solve!

@lovasoa, a built-in auth solution would be very handy of course, but I do agree that writing more explicit and repeated code in the meantime (and getting the debug-ability) is worth it; I tend to prefer that.

For authentication workflows, the simplest I've been able to get down to is a few lines:

1. setting the user variables to grab dynamic user data
2. invoking various filters to redirect unauthorized folks
3. invoking the shell (perhaps a different shell per view (admin/user/guest, etc)

set user = sqlpage.run_sql('ui/return/user.sql')->>0;
select 'dynamic' as component, sqlpage.run_sql('ui/filters/authenticated.sql') as properties;
select 'dynamic' as component, sqlpage.run_sql('ui/shells/authenticated.sql') as properties;

--

select 'title' as component,
  'Hello ' || $user->>'name' as contents;

Here, a few lines of repeated code works for me, and if I am running a larger more complex project, and I'm concerned about security and making sure I'm not forgetting code, perhaps an external shell script that checks the required .sql files within certain directories can help me.

Maybe it's best to keep that stuff that would be handy in complex scenarios outside of SQLPage's scope. After all, if we're doing complex things, hopefully we have the budget to maintain a few simple shell scripts that do the housekeeping.

---

Of course, if SQLPage can handle this elegantly, I'm very open. I think the explicit exporting of values inside the run_sql function is a nice approach.

[pchemguy]

Well, the main concept behind SQLPage, which is using mostly pure SQL for all web building tasks has both advantages and limitations. One limitation, which I discussed in one of my posts, is the lack of inherent ability to structure code modules. sqlpage.run_sql() makes it possible to factor out certain code pieces as separate modules, and otherwise one has to rely on comments.

Among other things, comments can be used to provide visual separation of common code as "header/footer" blocks. Such blocks may include, for example, code that provides an authentication guard and sets module-level variables, such as authenticated user id. Personally, I do not type every module from scratch, in which case it might be easier to forget something. Instead, I have a template for pages requiring authentication that includes appropriate code in the "header" block. Yes, there is still some violation of the DRY concept, but for what it is worth (I have not attempted to develop a big project with SQLPage) I do not see it as a big issue. See CRUD - Authentication for an example.

Also, I agree that "magic" is a double-edged sword, as it abstracts certain pieces of code, but usually makes it more difficult to follow the code for someone not familiar with it.

[djyotta]

Sorry for jumping in so late in the discussion.

I haven't put much thought into an ideal solution for this yet, but have faced this issue.

TLDR: I would appreciate a way to "include" sql into a page. I feel this is the least magic approach.
Bearing in mind @lovasoa 's comment: #584 (reply in thread)

Maybe something like this could work:

• add include_sql('path/to/file.sql')
• it should return no rows
• it should read the file contents and push it onto the parser stack

Alternatively, add a comment to include a file:

-- sqlpage.include_sql('path/to/file.sql')

And just preprocess each file looking for the special comments and modifying the contents before passing to the parser.

The former (if possible/practical) seems most elegant to me.

The latter is probably the most simple approach to implement. I don't like that it's abusing comments, and I don't like that it's a preprocessor instruction. But at least it's not invisible magic.

Either approach will introduce complexity to showing correct line of source code on errors.

---

For those who are interested, this is how I work around it using run_sql alone...

My "solution" was to funnel all pages through the same entry point and then call run_sql on an "inner" sql depending on the get/post params.
Because the inner sql inherits the parent scope, then I can share variables.
This would work for authentication too as you put auth on the entry point and then call an inner sql depending on the query params.

The biggest issues I've had with my work around (it's not really a solution) includes:

• hitting max recursion depth (it's about 5 I think)
• rather odd code structure takes a bit of getting used to
• redirects are tricky as the redirect component must come first
• you lose the streaming page feature for copious data as everything is done inside run_sql

Code probably explains it better than I can...
Example 1:
My clipboard app
Remarks:

• there are 4 apps (clip, goto, upload, code) sharing common code in the sqlpage folder
• the entrypoint of each app is the index.sql in the app dir

Live app: https://shandan.one/clip

Example 2:
I've written some pretty complex apps using this method, such as my grocery app. Actually, it's a bit messier than it needs to be, I plan to refactor it a bit when I get some time.

There is common filter I use on every page, and so I set the filter_options in the entry page and then descend into run_sql to render the actual page based on the page variable. Each page sets a few vars including the page var and then calls run_sql on the sqlpage/sqlpage/internal/entry.sql For example: transactions.sql

Live app: https://shandan.one/grocery/volume.sql

Unfortunately, the most intricate example is an app I wrote at work that I can not share here. But yeah, you can work around the need to copy-paste same logic over and over just using run_sql.

@lovasoa an option to configure recursion depth of run_sql would be nice - I could do with just one more level...

[lovasoa]

Very interesting!

Can you open a new issue for the configurable recursion depth? This one doesn't need discussion, and isn't hard to implement, we'll get it done for 0.32.

Your include_sql is doable too, since we parse the sql statements and already do transformations to the abstract syntax tree before passing the sql to the database. We could have something like

CALL sqlpage.include_sql('file.sql');

All the arguments would have to be hardcoded in order for it to be executable at parse time instead of runtime...

Error reporting would be confusing too if we don't rework it entirely to take included files into account...

[djyotta]

I think for include, need for variable arguments (or any args at all) is atypical anyway considering behaviour of other languages' import or include statements, so I would like the CALL approach over the comments approach as it would cause a fail if sqlpage version doesnt support it rather then silent ignore.

[paulpr0]

I ended up building something quite like the pre-processor suggestion to meet my requirements. It lives at paulpr0/simple-include (or https://crates.io/crates/simple-include).

It can run as a simple pre-processor for deployment but also has a watcher option so that I can keep it running in the background for development. I make changes in the src directory, and run sqlpage from the target, so simple-include runs in the background and handles copying changes from src to target.

My use case is fairly simple, so I haven't built in nested processing (so if file1.sql is included in file2.sql, including file2.sql in another file won't work), but if you are building something like this into SqlPage this would be useful.

@lovasoa the CALL syntax you suggest looks good to me.

[djyotta]

Ah, great idea. I toyed with the idea of preprocessing sql source but decided against due to necessity to run it manually. But your approach of running it continually is more workable. And always better if someone does it for me :)

[djyotta]

What about this:

• when _page.sql (or some other name convention) in dir
• then _page.sql is preprocessed and loaded into _sqlpage_files table as 'page.sql'
• and /page.sql routes to the preprocessed page in db

Should mean line num are accurate for error case.

This approach stinks of a framework, and I don't particularly like this sort of magic. But could be rather elegant approach.

[mingfang]

I like sveltekit's folder based routing.
It also has a concept of layouts that are inherited by sub-folders.
https://svelte.dev/tutorial/kit/layouts