I've been using Astro (https://astro.build) for static site serving + PostgREST Htmx for just simple data-centric components.

For anything mid-large size, I believe a separate abstraction layer is needed, which would be an API. I also tried to build something similar on top of SQL and the tech stack is Jinja-templated SQL and an OpenAPI layer implemented in YML but I would still scope it out for internal tooling. Here it is: https://jinj.at