SELECT statement

Parameter is way to denote value replacements in a SQL query. Each database uses different placeholder symbol:

databaseplaceholder symbolExample
MySQL?SELECT * FROM items WHERE points > ?
PostgreSQL$1 $2 $3 ... $nSELECT * 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