Ideas

Raw

To allow insertion of dynamic raw parts of an SQL query we can add support of the following syntax:

SELECT * FROM users ORDER BY $raw:orderBy(name|age)

The orderBy becomes one of the parameters with a type [#name | #age]. Under the hood we send both versions of the query to the DB server, and make sure both versions have the same input/output types.

The predefined options solve two problems:

1. we get example values that we can use to send a query for the analysis;
2. we don't have to worry about SQL injection as only predefined values can be used.

Issue: what to do with the weird characters in the options, like whitespaces or quotes?

Batch insert

To support the case where user want to have a query that can insert arbitrary number of rows into a table we can add support of the following syntax:

INSERT INTO users (age, name, email) VALUES $batch:users((20, $name, $email))

The type of parameters for such a query will be {users: array<{name: string, email: string}>}.

If the parameters would be the following:

{users: [{name: "Bob", email: "[email protected]"}, {name: "Alice", email: "[email protected]"}]}

The query we send to the server will become:

INSERT INTO users (age, name, email) VALUES (20, $1, $2), (20, $3, $4)

Under the hood to analyze the query we assume that the parameter array has one element, and send the corresponding query to get the types:

INSERT INTO users (age, name, email) VALUES (20, $1, $2)