client.query()

Run an arbitrary query.

Syntax

client.query(
    query: string | Statement,
    values?: any[],
    options?: Options
): Promise<QueryResult>;

client.query(
    query: string | Statement, 
    arg: {
        values?: any[];
    } & Options
): Promise<QueryResult>;

client.query(
    arg: {
        query: string | Statement;
        values?: any[];
    } & Options
): Promise<QueryResult>;

type Statement = 
    | SelectStatement 
    | InsertStatement 
    | UpsertStatement 
    | UpdateStatement 
    | DeleteStatement 
    | CreateDatabase 
    | RenameDatabase 
    | AlterDatabase 
    | DropDatabase
    | CreateTable 
    | RenameTable 
    | AlterTable 
    | DropTable;

interface Options {
    [key: string]: any;
}

type QueryResult = Savepoint | Array<object> | number | null;

Param	Type	Description
query	string or Statement	An SQL query or a Statement instance.
values	Array	For non-DDL operations, values for parameters in the query.
options	Options	Extra parameters for the query.

Return Value

• Array: the query result set - when it's a DQL operation (SELECT) or when it's a DML operation (INSERT, UPDATE, DELETE) with a RETURNING clause
• Number: a number indicating number of rows processed by the query - when it's a DML operation without a RETURNING clause
• Savepoint: a Savepoint instance - when it's a DDL operation (CREATE, ALTER, DROP, RENAME) (See ➞ Savepoint)
• Null - in all other cases

Usage

Run a DML operation (CREATE, ALTER, DROP, RENAME) and get back a reference to the savepoint associated with it (See ➞ Automatic-Schema-Versioning):

const savepoint = await client.query(
    `ALTER TABLE users
    RENAME TO accounts`
);
console.log(savepoint.versionTag()); // number

await savepoint.rollback(); // true

or a DQL operation (SELECT), and get back a result set:

const rows = await client.query(
    `SELECT * FROM users
    WHERE id = 4`
);
console.log(rows.length); // 1

or a DML operation (INSERT, UPDATE, DELETE) with a RETURNING clause, and get back a result set:

const rows = await client.query(
    `INSERT INTO users
    SET name = 'John Doe'
    RETURNING id`
);
console.log(rows.length); // 1

or same DML operation without a RETURNING clause, and get back a number indicating the number of rows processed by the query:

const rowCount = await client.query(
    `INSERT INTO users
    SET name = 'John Doe'`
);
console.log(rowCount); // 1

Using options

Param	Type	Applicable to	Description
desc	string	DDL operations	the commit description.
noCreateSavepoint	Boolean	DDL operations	a disable savepoint creation.
inspect	Boolean	All operations	Log details of the query to the console.

// Descripted DDL operation
const rows = await client.query(
    `ALTER TABLE users 
    MODIFY COLUMN id int`,
    { desc: 'Query description' }
);

// Parameterized query
// With parser-level details logged to the console
const rows = await client.query(
    `SELECT * FROM users 
    WHERE id = $1`,
    [4],
    { inspect: true }
);