SELECT IN Queries Failing

[drveresh]

The following is failing to get records despite the same query works in the Console.

Endpoint:
../productGetByCountries?cx=JP,DE

EdgeFunction Code:

    const cx = decodeURIComponent(request.params.cx);
    const arrayWithSingleQuotes = cx.split(',').map(item => `'${item}'`);
    const outputString = arrayWithSingleQuotes.join(',');

    console.log("query: " + `SELECT * FROM products WHERE country IN (${outputString}) LIMIT 5`);
    console.log("outputString: " + `${outputString}`);
    result = await connection.sql`SELECT * FROM products WHERE country IN (${outputString}) LIMIT 5`;

Log Console:

query: SELECT * FROM products WHERE country IN ('DE','JP') LIMIT 5
outputString: 'DE','JP'

Response:
"result": []

[marcobambini]

Thanks, @drveresh. We'll get it fixed asap.
@TizianoT can you please take a look at this issue?

[drveresh]

Thanks and let me know once done.

Please never launch anything without comprehensive documentation, examples, and definitely not with this kind of bugs. I wasted almost few days analyzing these technical issues. In the interim, I almost got a new SQLite database instance up and running on AWS.

Just wondering, why these basic queries are never tested? Your documentation just contains one super basic example of selecting records based on one field (integer) comparison, and I really doubt that I would end up wasting additional time and efforts testing other type of queries.

In sum, I have a requirement to support almost 100M queries per month, and would like to try your service again unless you support necessary query capabilities, as mentioned here - https://github.com/mevdschee/php-crud-api

[drveresh]

@marcobambini
Also, please make below query statement or structure more coding and developer friendly.
result = await connection.sql'SELECT * FROM products WHERE country IN (${outputString}) LIMIT 5;

I mean, why the query is forced to be attached or formed along with 'await connection.sql''`? I should be able to create a query separately and pass it as a parameter. As of now, it is failing, and ideally I expect below format of code structure.

const query = 'SELECT * FROM products WHERE country IN (${outputString}) LIMIT 5';
result = await connection.sql(query);
//or
result = await connection.sql('${query}');

Note: Had to use" ' " instead of " ` " as it is breaking Github's code formatting.

[drveresh]

When I tried the below code, even the empty `` is getting converted as 'null' and showed in Console logs, it is really strange.
result = await connection.sql(''+${query});

[marcobambini]

@drveresh I think that the right way to pass two values, like in your statement, is to use two different parameters in your query. So it should look like:

../productGetByCountries?p1=JP&p2=DE

result = await connection.sql`SELECT * FROM products WHERE country IN (${p1}, ${p2}) LIMIT 5`;

[drveresh]

@marcobambini I agree, but it is neither a good design nor a scalable solution. Both the endpoint and query should be dynamic, so the custom logic can be handled within EdgeFunction (EF) code itself.

The input value to one query param could have dynamic(1 or 50 CSV) values, so it doesn't make sense to restrict the EF logic to have a specific set of values in IN queries. The same issue is similar to having multiple WHERE conditions.

In sum, though EFs give the option to have more custom and complex logic, but I really hope that the entire SQLite database should be able to get exposed as REST endpoint, as per https://github.com/mevdschee/php-crud-api. This should adhere to the No-Code Foundation Principle - write no or less code, via HTTP REST API mechanism. We just need a simple and faster way to go production and have flexible options as API integrations.

[TizianoT]

@drveresh
I've created a test database with a products table to simulate your situation:

CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
)

Then I added some rows:

Execute query as string
Then I've created this edge function:

const cx = decodeURIComponent(request.params.cx);
const arrayWithSingleQuotes = cx.split(',').map(item => `'${item}'`);
const outputString = arrayWithSingleQuotes.join(',');

const query = `SELECT * FROM products WHERE country IN (${outputString}) LIMIT 5`;
const resultA = await connection.sql(query);
const resultB = await connection.sql(`${query}`);

return {
    resultA,
    resultB
};

If you try to execute it with a different set of query parameters, it works correctly using the resultA or the resultB way to pass the statement.

• https://claqfuebsz.sandbox.sqlite.cloud:8090/v2/functions/productGetByCountries?cx=IT
• https://claqfuebsz.sandbox.sqlite.cloud:8090/v2/functions/productGetByCountries?cx=IT,DE
• https://claqfuebsz.sandbox.sqlite.cloud:8090/v2/functions/productGetByCountries?cx=CO,JP,MX

Execute query as Prepared Statements
When you want to execute the query as a Prepared Statements, it is impossible to pass multiple parameters (?cx=JP,DE) using only one variable.

result = await connection.sql`SELECT * FROM products WHERE country IN (${outputString}) LIMIT 5`;

The replacement of ${outputString} takes place inside the connection.sql and not before the execution like you were expecting with the

console.log("query: " + `SELECT * FROM products WHERE country IN (${outputString}) LIMIT 5`);

Inside the connection.sql the outputString is properly serialized and escaped so what you obtain at the end is the following query:

SELECT * FROM products WHERE country IN ("'JP','DE'") LIMIT 5

Conclusion
So, if you want more flexibility and scalability you could opt for the first approach, otherwise if you want a more safely approach you could opt for the second one