Proper way to do SQL error handling?

[jdh148]

Is there a way to proper way to handle SQL errors - for example, when form input passed to an INPUT violates a unique key constraint. If SQLPage is running in "production" mode, then the user receives a generic error message telling them to contact the administrator. Is there some way to show the user a proper error message, e.g. pass the SQL message back to the user? The only way I could find is to leave SQLPage in "development" mode so the user has a way of seeing the error message, albeit very verbose with a lot of detail.

[lovasoa]

The canonical way to handle constraint violation errors in SQLPage is to avoid triggering them in the first place:

WITH ins AS (
    INSERT INTO your_table (col1, col2)
    VALUES ($a, $b)
    ON CONFLICT DO NOTHING -- on conflict, just return zero rows
    RETURNING col1 -- on success return col1
)
SELECT
    'redirect' AS component,
    CASE
        WHEN EXISTS (SELECT 1 FROM ins) THEN 'success_page'
        ELSE 'error_page'
    END AS link;

https://sqlite.org/lang_conflict.html

https://www.postgresql.org/docs/current/sql-insert.html