Skip to main content

Writing SQL

In the editor you write standard SQL for your chosen database — MySQL/MariaDB, PostgreSQL, or MSSQL. Kvery sends it to the database largely as-is, while adding a few conveniences for parameters, error handling, and result formatting.

Read vs. write queries​

Kvery looks at the leading keyword of your SQL to decide how the query behaves:

  • Read queries begin with SELECT or EXEC (and WITH for CTEs). They can be run safely and exposed over GET.
  • Write queries begin with INSERT, UPDATE, DELETE, REPLACE, ALTER, or CREATE. They change data and, when published, are exposed over write methods (POST/PUT/PATCH/DELETE).

This drives the run UI and the Public Query API method mapping. You do not configure it manually.

Parameters (form variables)​

Use a $-prefixed name to introduce a parameter:

SELECT *
FROM invoices
WHERE customer_id = '$customer_id'
  AND issued_at >= '$from_date';

Kvery renders a form field for each $-prefixed parameter and fills it from the request when the query runs. Apostrophes in the values are escaped, so put text and dates in quotes. Full details — field types, defaults, validation, and special inputs (checkbox/radio/select) — are in Form variables.

Internal variables​

Kvery also exposes internal variables you can reference in SQL, such as contextual values that Kvery fills in at run time. See Internal variables.

Multiple statements and CTEs​

You can use CTEs (WITH ...) in read queries. When combining statements, keep in mind the read/write classification is based on the leading keyword.

Keeping queries fast​

  • Add LIMIT (or the database equivalent) while developing.
  • Index the columns you filter and join on.
  • Remember the query timeout (100 seconds by default).

Next​