SQLPage Plugin System

[a-gonzalo]

Hi all!

Recently I was thinking about extending SQLPage for my specific use-cases.

Actually, if we want to extend SQLPage, we need to clone the repo, implement the functions associated and interact with them with sqlpage.func()

To avoid that, I was thinking about a plugin system. Something like:

• Having a second class language (like Rhai or Lua) to implement scripts and use something as sqlpage.plugin('my_plugin_name','my_function','my arguments')
• Or having the ability to extend sqlpage.functions in runtime.
• Or having the ability to make edge functions with some lang (as JavaScript) like other systems do (Supabase, PocketBase)

All of them systems would work (for the user) similar: inside sqlpage dir, you have a plugin/ folder where you can store you plugins.

I don't know if you think it's a good idea, but why I'm thinking about it:

• If you use some DBMS like PostgreSQL or MySQL, you can just connect to the DB and do the operations you want without fearing any concurrency problem as is managed from the DBMS
• BUT if you use SQLite, having mixed read/writes from different applications is a nightmare. This can be achieved doing some API endpoints from SQLPage exposing what you need to, and then interacting with it from another service/microservice. But I think it would be (at least... Well, for me) a good fit having some sort of plugin injection to avoid that kind of scenarios.

What do you think?

[lovasoa]

Interesting ! Did you encounter cases where sqlpage.exec was impractical or too slow ? I'd love to hear more about the use cases before diving into the technical details.

[lovasoa]

About read/writes from multiple apps in SQLite: they are supposed to work. You should use WAL mode (and optionally a high busy_timeout). Don't do anything fancy (like long write transactions) in the executed script.

To convert a database to wal mode just run the following query on it once:

PRAGMA journal_mode=WAL;

[a-gonzalo]

More than being slow or impractical, is related with security. As you explain in the docs, using sqlpage.exec can be error prone or generate some security issues.
Also, sqlpage.exec makes you depend on "another process".

So having a plugin system would correct both "issues":

• When you write your plugin, you know how it will interact with the rest of the world, and can be safer as can be sandboxed (as mentioned, like Rhai)
• Having a plugin system makes you run the process inside sqlpage (for example as a tokio process) and could be easier to debug I guess.

My personal use cases are:

• I want to open a TCP/IP socket that will receive messages in HL7 format (a Health Standard format). This messages will be parsed and deserialized into a Table (or tables) format.
  Then with SQLPage, I want to display that data.

• Also, the inverted way -> I have a structured data into DB, and I want to send that data into HL7 format (via TCP or HTTP request)

• I want to make some REST API without JSON serialization with SQL.

All of them cases (excluding the TCP example) can be achieved only with SQLPage. I can parse the request body with bare SQL, and probably some regex magic, so I can serialize and deserialize that format and send it via HTTP. But doing that kind of work in bare SQL is the hard way and not very maintenable.
Then, I might use sqlpage.exec and some extra script or process to parse messages, but this leads in a secondary process which I don't specifically control inside "the platform" (SQLPage) as a whole.

If I can programmatically with a extension, make the hard work and then display my data and user interaction with SQLPage, would be absolutely magnific for me

[lovasoa]

I don't think a plugin system would be safer than sqlpage.exec. Currently there is a clear security boundary by default: writing files to the filesystem allows running arbitrary sql on the db, but does not allow running arbitrary code on the host. Breaking that boundary requires enabling sqlpage.exec in the configuration.
Having a separate process gives you more control, more debugability, more opportunities to limit accessible resources, not less. It also lets you access existing library ecosystems like https://crs4.github.io/hl7apy/

The only valid reason I see would be to increase performance for very high traffic applications running on limited hardware, which is not a use case I have seen so far.

[a-gonzalo]

Well, if the use cases nowadays don't really need this, I understand that is probably something you don't want to have right now.

As I stated, my principal concern cames from security, but if a plugin/extensión system doesn't add more security, It doesn't have a reason to exist (yet).

I'll appreciate if anyone else in the future see this discussion and explain the use cases they have for, but I think I will stick with an approach where I can have a DBMS instead a bare SQLite file to interact with different processes.

Thank you! @lovasoa

[sgindeed]

[lovasoa]

Is this ai generated ?

[a-gonzalo]

Is this ai generated ?

I didn't want to point it out, but It looks like xD