Incorrect casting of url parameters

[YoelisA]

Introduction

When no type is specified, the casting of the url parameters passed to a query fails with DELETE FROM <table> WHERE id = CAST($1 AS TEXT) RETURNING 'resource deleted'. DB is postgres hosted on supabase.

To Reproduce

-- Create todo table
CREATE TABLE
  public.todos (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    task TEXT
  );

-- Insert sample todos
INSERT INTO
  public.todos (task)
VALUES
  ('Complete project proposal');

INSERT INTO
  public.todos (task)
VALUES
  ('Prepare presentation slides');

create delete_todo.sql with

DELETE FROM public.todo
WHERE id = $id
RETURNING
   'resource deleted';

http://localhost:8080/delete_todo.sql?id=1

Actual behavior

Failed to execute SQL statement:
DELETE FROM public.todos WHERE Id = CAST($1 AS TEXT) RETURNING 'resource deleted'
                                     ⬆️
line 1, character 37

Backtrace says :

error returned from database: operator does not exist: bigint = text

operator does not exist: bigint = text

Screenshots

If applicable, add screenshots to help explain your problem.

Expected behavior

I expected a deletion of the resource without the need the specify the id's type. If it's needed for some reason, maybe we should find a caveat in the documentation ?

Version information

• OS: Pop!_OS 22.04 LTS
• Database [e.g. SQLite, Postgres]: Postgres hosted on supabase
• SQLPage Version [found when hovering the default footer of pages]: 0.24.0

Additional context

To fix this error, I had to specify the id's type with $id::int

[lovasoa]

Hello and welcome to SQLPage !

What you found is a common caveat with postgres. Other databases such as SQLite cast the values automatically. Unfortunately, I don't think there is much more to do than better documentation and better error messages. HTTP URL parameters are not typed, and there is no easy way to automatically guess that $id should be an integer in this context...

Do you have a suggestion concerning the documentation ?

[YoelisA]

Thank you :)

I didn't understand that it was postgres that is responsible for type checking the url parameters. I tried to do the query with an int and text from psql and postgres was able to delete the resource so I assumed it was something with sqlpage.

I'm not sure for the documentation, maybe the examples could be categorized with the database behind ?

[lovasoa]

Yes, I would like to improve https://sql.ophir.dev/your-first-sql-website/

What could we have written that would have made it obvious to you how URL parameters binding work ? Can we change something in the existing tutorial or docs to make them less confusing ?

If you have suggestions, feel free to open a pr on https://github.com/lovasoa/SQLpage/blob/main/examples/official-site/your-first-sql-website/tutorial.md

And in general, I would love to know how you approached discovering sqlpage, getting started, and using the different features. It would help prioritize the work on the docs.