SELECT statement
Parameter is way to denote value replacements in a SQL query. Each database uses different placeholder symbol:
database | placeholder symbol | Example |
---|---|---|
MySQL | ? | SELECT * FROM items WHERE points > ? |
PostgreSQL | $1 $2 $3 ... $n | SELECT * FROM items WHERE points > $1 |
Based on your target database, you should use its known placeholder symbol. sqlx-ts is able to handle both MySQL and PostgreSQL placeholder syntax.
Example database
Throughout this section of the guide, we will be using the following database ERD
Binary operations
If you have the follow MySQL query in typescript
// example
const someQuery = sql`
SELECT *
FROM items
WHERE points > ?;
would generate
export type SomeQueryParams = []
export interface ISomeQueryResult {
id: string
points: number
}
export interface ISomeQueryQuery {
params: SomeQueryParams;
result: ISomeQueryResult;
}
IN list query
If you have the following MySQL query
// example
const someQuery = sql`
SELECT *
FROM items
WHERE id IN (?);
would generate following typescript types
export type SomeQueryParams = [Array<number>]
export interface ISomeQueryResult {
id: string
points: number
}
export interface ISomeQueryQuery {
params: SomeQueryParams;
result: ISomeQueryResult;
}
Subqueries
Query params within subqueries are interpreted as well. If you have the following MySQL query
const someQuery = sql`
SELECT id, points
FROM items
WHERE id IN (
SELECT id FROM items
WHERE
points > ?
AND id IN (SELECT id FROM items WHERE food_type = ?)
)
AND points < ?
`
would generate following type definitions
export type SomeQueryParams = [number, string, number]
export interface ISomeQueryResult {
id: string
points: number
}
export interface ISomeQueryQuery {
params: SomeQueryParams;
result: ISomeQueryResult;
}
Note that QueryParams
array respects the order of params present in the query above