Jobs, Observability, Includes & Testability

[guspower]

Hi,

I continue to enjoy using SQLPage. I thought I'd list a few possible ideas for the toolkit here to get peoples' opinions. These are not requests for features per se but rather points for discussion.

These are ideas that have come up over the course of various projects, and the problems also apply to other tech stacks. For each, my questions are:

• should SQLPage consider tackling this problem and, if so
• how might it work

Job Management / Scheduling (cron)

Problem: a task must be run regularly (e.g. a data refresh), scheduled for a specific time (e.g. close of business) or will be long-running / async and the eventual outcome captured.

Maybe SQLPage could use something like tokio-cron-scheduler along with a sqlpage_configuration/job directory, or a schedule component. The jobs would probably need to be held in a database table alongside _sqlx_migrations (_sqlx_job?) to handle multi-node scenarios.

Observability (status/metrics)

Problem: many cloud operating environments now expect deployed systems to provide both status endpoints (for service availability checking) and metric endpoints (for load balancing, performance monitoring).

There are numerous "standards" in this space, but providing out-of-the-box observability might increase adoption of SQLPage.

Precompilation / Includes

Problem: Currently the unit of work in SQLPage is a .sql file which can contain one or more queries (including functions) and components. There are methods of reuse e.g. run_sql, however many toolchains provide the ability (often via the build system) to run tasks before system launch that can be used to generate the final artefacts from templates - in our context this could be the ability to pull in custom includes that would cut down on duplication (often an issue with SQL queries).

Build systems usually become an oubliette of pain and complexity because people stretch and pull them to do things they were never designed to do. However they may be a SQLPage way here of producing something simple and opinionated that ultimately makes codebases more maintainable.

Testability

Problem: How do we help SQLPage developers ensure that their systems keep working as they add new functionality and make changes to existing data and code?

I spend a lot of time designing systems to be easily testable. I am thinking about ways of making SQLPage more testable. A few ideas that spring to mind:

• Add a test database in tandem to the production database, add the ability to snapshot/backup/restore it and run SQLPage against it
• Provide some sort of a web crawler that can crawl the pages for validity and correctness (pages load, no broken links)
• Provide some sort of form post test harness to allow testing write operations
• Provide some sort of test step to execute either before launch or separately
• Enable running of .sql files without UI, record query success and output, provide access to that data for later test assertion
• Record duration of queries and duration of page loads, fail tests if thresholds are exceeded (e.g. <95% are >2secs)
• Use wiremock-rs or similar to simulate playback from 3rd party endpoints used by fetch

Comment most welcome

[lovasoa]

Hey @guspower !
It sounds like you have large SQLPage applications in production, and we are especially interested in making such use cases successful. Could we have a chat sometimes soon ? Feel free to contact me on contact at ophir dot dev.

Given our currently limited resources, we have to be laser-focused on what we prioritize, so I think tasks that other tools do better than what we could implement in a reasonable time frame are not going to be a priority soon. But I think at least for observability, we should be able to get to something satisfying quickly. Would be happy to discuss it with you.

Could you share with the community here the ways you solved the problems above in your existing projects ?

[speakradmin]

Sorry for resurrecting this old thread, I was interested to know if any decision or direction has been taken regarding job scheduling?

Somewhat related and also, I would imagine, more technically challenging, is event listening. Not sure if that's something that's being considered? For me one of the missing components in SQLPage is automated logic processing like triggers and actions. I'm sure it wouldn't be trivial to implement but I thought I'd ask out of interest in the projects direction in general.

[lovasoa]

Hey @speakradmin , I would be interested to know more about how you are using sqlpage ! Could you write a few paragraphs about your business domain, why you chose sqlpage, and what you are doing with it ? Understanding how people are using SQLPage helps shape the priorities !

[speakradmin]

Sure! I'm seeing so many possibilities with SQLPage I don't know where to start 😄

I am in the ISP industry, mainly fibre internet. I was drawn to SQLPage for it's simplicity and how quick it is to build a functional application. We desperately need to be able to iterate on our applications without needing to wait for developers to do the heavy lifting in back-end code. Luckily our team are SQL nerds so it seemed like a good fit!

Firstly, we are working on order fulfilment. We want to manage and track the life cycle of a customers order. Detailed insight into the time taken to move from one step to the next to see where the delays are. Also very important in this aspect is automating as much as we can whether by sending requests to external API's or listening for a callback from an external system. We also have internal systems that would need provisioning through API's and webhooks.

After that, we are looking to build a billing engine that can invoice customers and generate PDF's and alert users when payment is due. A customer portal would be very important for this. I realise this is quite ambitious and I am not sure if SQLPage is ready for a project of that size.

Hope that helps paint a better picture

[lovasoa]

internal tools built quickly and directly by the people who know the data and use it everyday is exactly the kind of use cases SQLPage was built for!

Don't hesitate to ping me if you think I can help you with anything!