Proposal: Custom Database Error Handling in SQLPage

[grodger]

Hi @lovasoa & Team,

Before I start, I want to thank you for the excellent work you've put into SQL-Page.

So, I've used SQL-Page to develop a feature-rich MVP, to the point that it could be used as a final product. However, one area that users have commented on is error handling and the display of errors. I'd like to propose changes that allow the developer to indicate, on a per-page basis, whether an alternative mechanism is used. I don't believe there is a mechanism to do this currently.

Now, I'm not a Rust programmer, so I asked Claude Code to come up with a proposed set of requirements and possible areas of change. I can't determine whether the latter is sensible or not, but I've included the output verbatim from my query to Claude. I hope you don't mind that I've called on our robot overlords for assistance here :-) — the intent is to make the requirements clearer and provide a better, more streamlined example than I could have put together myself.

Details

Proposal: Custom Database Error Handling in SQLPage

Background

Currently, when a database error occurs mid-page, SQLPage calls handle_error() in
src/render.rs:845, which renders the built-in error.handlebars component and (via
stop_at_first_error() in src/webserver/database/execute_queries.rs:264) halts all
further SQL execution. Application developers have no way to intercept, customise, or
suppress this behaviour.

---

User Stories

US-1 — Custom error display (inline component)

As a SQLPage developer, I want to declare at the top of my SQL file that errors should
be displayed using a named component (e.g. alert) instead of the default red error
box, so end-users see a styled, application-consistent message.

US-2 — Custom error handler SQL file

As a SQLPage developer, I want to nominate another .sql file as my error handler, so
I can write full component logic (including user-friendly message mapping and conditional
display) that runs whenever an error occurs on this page.

US-3 — Error details in the handler

As a SQLPage developer, I want structured error information available as variables
inside my error handler SQL file ($error_message, $sqlstate, $query_number), so
I can display or map technical database errors to friendly copy.

US-4 — Continue-on-error

As a SQLPage developer, I want to opt a given page into "continue on error" mode so
that non-fatal errors are displayed inline but page rendering continues for subsequent
queries (e.g., one failing widget should not blank the rest of the dashboard).

US-5 — Default behaviour unchanged

As an existing SQLPage user, I want no change in behaviour unless I explicitly declare
an error handler, so existing pages are not affected.

---

Proposed SQL API

Declare an error handler (header phase — must come before any body component)

-- Option A: delegate to another SQL file
SELECT 'error_link' AS component,
       'partials/my_error.sql' AS handler,
       TRUE AS continue_on_error;   -- default FALSE

-- Option B: render inline using an existing component
SELECT 'error_link' AS component,
       'alert'      AS handler,     -- name of any existing component (alert, html, …)
       TRUE         AS continue_on_error;

-- Rest of the page follows normally:
SELECT 'hero' AS component, 'My Dashboard' AS title;
SELECT * FROM potentially_failing_query;

Error handler SQL file (partials/my_error.sql)

The file is executed with the following variables automatically set:

Variable	Content
$error_message	Human-readable error string
$sqlstate	SQL state code, or NULL
$query_number	1-based index of the failing statement

-- partials/my_error.sql
SELECT 'alert'         AS component,
       'Query failed'  AS title,
       CASE
         WHEN $sqlstate = '23505' THEN 'That record already exists.'
         ELSE 'An internal error occurred. Please try again.'
       END             AS description,
       'warning'       AS color,
       TRUE            AS dismissible;

Option B — inline component usage

When handler is a component name rather than a .sql file path, the server renders
that component with error fields injected automatically as the component's top-level row:
description = error_message, color = 'danger', title = 'Error'.

---

Architectural Analysis

How stop_at_first_error and handle_error interact today

stream_query_results_with_conn()          ← produces DbItem stream
    └─ stop_at_first_error()              ← halts stream on first DbItem::Error
           ↓
build_response_header_and_stream()        ← header phase (statuscode, redirect, …)
    └─ stream_response()
           └─ renderer.handle_error()     ← renders error.handlebars, stream is now empty

The problem: stop_at_first_error() is applied before build_response_header_and_stream()
runs, so the header phase has no chance to configure error-handling behaviour.

Proposed execution flow

stream_query_results_with_conn()
    ↓
build_response_header_and_stream()        ← header phase reads error_link config
    └─ if continue_on_error:
         stream_response(raw_stream, renderer)
       else:
         stream_response(stop_at_first_error(stream), renderer)
              └─ renderer.handle_error()  ← dispatches to custom handler or built-in

---

Implementation Plan (Rust — minimal changes)

1. New ErrorHandlerConfig type

Add to src/render.rs (or a small companion file):

#[derive(Debug, Clone)]
pub enum ErrorHandler {
    /// Render an existing component name with error fields injected
    InlineComponent(String),
    /// Execute a SQL file with error vars set as request variables
    SqlFile(String),
}

#[derive(Debug, Clone)]
pub struct ErrorHandlerConfig {
    pub handler: ErrorHandler,
    pub continue_on_error: bool,
}

parse_error_link() distinguishes file from component name by checking whether handler
ends in .sql.

2. HeaderComponent::ErrorLink — src/render.rs (HeaderContext)

Add ErrorLink to the HeaderComponent enum and handle it in handle_row():

Some(HeaderComponent::ErrorLink) => {
    let config = parse_error_link(&data)?;
    self.request_context.error_handler = Some(config);
    Ok(PageContext::Header(self))
}

This follows exactly the same pattern as StatusCode, Authentication, Redirect, etc.

3. RequestContext gains error_handler: Option<ErrorHandlerConfig>

Threads the config from the header phase into the body renderer so HtmlRenderContext
can consult it.

4. Move stop_at_first_error — src/webserver/http.rs:277

Remove the unconditional application:

// REMOVE this line:
let database_entries_stream = stop_at_first_error(database_entries_stream);

Add a conditional version inside build_response_header_and_stream, at the
body-phase transition point, after the error handler config is known:

PageContext::Body { renderer, http_response } => {
    let body_stream: Box<dyn Stream<Item = DbItem> + Unpin> =
        if renderer.error_handler.as_ref().map_or(true, |c| !c.continue_on_error) {
            Box::pin(stop_at_first_error(stream))
        } else {
            Box::pin(stream)
        };
    return Ok(ResponseWithWriter::RenderStream {
        http_response,
        renderer,
        database_entries_stream: body_stream,
    });
}

The stream would need to be type-erased (boxed) here; this pattern is already used
elsewhere in the codebase.

5. Modify handle_error() — src/render.rs:845

pub async fn handle_error(&mut self, error: &anyhow::Error) -> anyhow::Result<()> {
    if let Some(config) = &self.error_handler.clone() {
        return self.handle_error_custom(error, config).await;
    }
    // existing built-in logic unchanged …
}

async fn handle_error_custom(
    &mut self,
    error: &anyhow::Error,
    config: &ErrorHandlerConfig,
) -> anyhow::Result<()> {
    self.close_component()?;
    let query_number = self.current_statement;
    let (message, sqlstate) = extract_error_details(error);

    match &config.handler {
        ErrorHandler::InlineComponent(component_name) => {
            let data = json!({
                "description":  message,
                "color":        "danger",
                "title":        "Error",
                "sqlstate":     sqlstate,
                "query_number": query_number,
            });
            let saved = self.open_component_with_data(component_name, &data).await?;
            self.close_component()?;
            self.current_component = saved;
        }
        ErrorHandler::SqlFile(sql_path) => {
            // mirrors the run_sql SQLPage function
            let vars = json!({
                "error_message": message,
                "sqlstate":      sqlstate,
                "query_number":  query_number.to_string(),
            });
            self.run_error_handler_sql(sql_path, vars).await?;
        }
    }
    Ok(())
}

run_error_handler_sql() mirrors the internals of the existing run_sql SQLPage function
(src/webserver/database/sqlpage_functions/functions.rs:712). It needs access to
ExecutionContext and a database connection; see Open Questions below for the preferred
way to thread these into HtmlRenderContext.

extract_error_details() is a small helper that inspects the anyhow::Error chain for
sqlx error fields (SQLState, message text, etc.).

---

Files Changed

File	Change	Effort
src/render.rs	Add ErrorHandlerConfig; HeaderComponent::ErrorLink; new handle_error_custom()	Medium
src/webserver/http.rs	Remove unconditional stop_at_first_error(); add conditional at body-phase transition	Small
src/webserver/database/execute_queries.rs	No change required	—
Handlebars templates	No new templates required	—

---

What Is Explicitly Reused (no new templates needed)

Existing feature	Role in this proposal
alert component	Ideal for inline, dismissible error banners
modal component	Available via the SQL file handler option
html component	For fully custom raw HTML error output
run_sql pattern	SQL file handler mirrors this exactly
Variable forking	Used to pass $error_message etc. to the handler file
Header-phase component pattern	error_link sits alongside statuscode, authentication, redirect

---

Open Questions for the Maintainer

1. Type erasure on the stream — moving stop_at_first_error to the body-phase
   transition requires boxing the stream (Box<dyn Stream<...>>). Is this acceptable,
   or is there a preferred pattern for stream-type erasure in this codebase?

2. HtmlRenderContext access to ExecutionContext and DB connection — for the SQL
   file handler option, the renderer needs these. One option is to pass them into
   handle_error() as parameters; another is to store them on the context at
   construction time. What fits the ownership model better?

3. Scope of error_link — should it be header-only (one global setting per page),
   or allowed mid-page to change the handler for subsequent queries? Header-only is
   simpler and consistent with statuscode / authentication.

4. Error in the error handler — if the error handler SQL file itself fails, what
   should happen? Suggestion: fall back silently to the built-in error component,
   always, to prevent infinite error loops.

---

Full Example — Dashboard with Continue-on-Error

-- dashboard.sql

SELECT 'error_link'                AS component,
       'partials/widget_error.sql'  AS handler,
       TRUE                         AS continue_on_error;

SELECT 'shell' AS component, 'My Dashboard' AS title;

SELECT 'card' AS component;
SELECT title, value FROM expensive_widget_1;   -- if this fails…

SELECT 'card' AS component;
SELECT title, value FROM expensive_widget_2;   -- …this still renders

-- partials/widget_error.sql

SELECT 'alert'               AS component,
       'Widget unavailable'   AS title,
       $error_message          AS description,
       'warning'               AS color,
       TRUE                    AS dismissible;

---

Summary

This proposal requires approximately 3–4 medium Rust changes concentrated in
src/render.rs and src/webserver/http.rs. It introduces no new Handlebars template
files, reuses the existing header-phase component pattern and the run_sql execution
pattern, and is fully backwards-compatible — pages without an error_link declaration
behave exactly as today.

[lovasoa]

Hello! Thank you for opening this issue, this is definitely a point we could improve on.

I have access to coding agents too, so the implementation details are not really the important things to discuss here.

What I am more interested about is your feedback as an user. What does your application do? Where are the errors in your application, can you give examples? How do users encounter them (bad input from them, or programming errors from you)? What would you like to be able to show them instead in each case? During development, do you find the errors useful or do you think they often miss something?

[lovasoa]

Also Claude is wrong about error.handlebars: you can overwrite it like any other template.

[grodger]

Yes, and thanks for that, I do find I have to go through Claude's output carefully with the project I am working on :-)

[grodger]

Hi @lovasoa ,

Thanks for your prompt response.

So, the platform is aimed at FinTech, centred around delivering an API-first solution (so focused on SaaS), with an emphasis on high transaction volumes, dual ledger's, low transaction latency, and real-time balance, threshold and rule triggering. The back-end is built around PostgreSQL (most of the business logic sits within stored procedures and functions), with PostgREST as the OpenAPI layer. There are also some simple Golang helpers — e.g. to send emails (picked off a queue) and call external APIs (also picked off a queue). The UI is built with SQL-Page.
You can access the 'lite' demo at https://ui-lite.ledgerflow.ai/ using the following credentials (these are open public demo credentials):

Username: demo@ledgerflow.ai
Password: pynkyd-qevnew-sYjvi3

The main issue is that I rely on the underlying referential integrity to enforce consistency. For example, when I try to delete an entity from the UI that is referenced from another table, I get this error:

In "setup/entity.sql": The following error occurred while executing an SQL statement:
error returned from database: update or delete on table "entity" violates foreign key constraint "payments_entity_id_fkey" on table "payments"
The SQL statement sent by SQLPage was:
DELETE FROM api.entity WHERE CAST($1 AS TEXT) IS NOT NULL AND CAST($1 AS TEXT) <> '' AND id = CAST($1 AS TEXT)::BIGINT AND CAST($2 AS TEXT) = 'true' AND CAST($3 AS TEXT) = 'delete' AND CAST($4 AS TEXT) = 'true' RETURNING true;
setup/entity.sql: lines 47 to 51

I'd like to be able to intercept that error, present a user-friendly message, and in this use case, continue rendering the page — there's no harm in doing so.

i.e. where I attempt to delete LedgerFlow (the red trashcan), which I know is referenced elsewhere I get the error where I ideally would provide a nice user friendly error and let the page continue rendering.

And the error which leaves the user experienced 'hanging' so to speak.

Let me know if you have any more questions.

Thanks,
Grant

[grodger]

BTW, I find the errors very useful for development, it is just about presentation from an end user perspective.

[lovasoa]

Thank you for the detailed explanation !
I'll try to find

In the meantime you should either override error.handlebars or explicitly add a where not exists ... clause to your delete.