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.

└ 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.

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

Call patterns

Three-parameter call pattern:

// Each parameter passed distinctly
await client.query(
    `SELECT * FROM users WHERE name = $1`,
    ['John'],
    options
);

Two-parameter call pattern:

// Values passed via second parameter
await client.query(
    `SELECT * FROM users WHERE name = $1`,
    { values: ['John'], ...options }
);

Single-parameter call pattern:

// Everything in an object
await client.query({
    query: `SELECT * FROM users WHERE name = $1`,
    values: ['John'],
    ...options
});

The Options object

Pass relevant additional options to a query:

// Inspect query in the console
const rows = await client.query(
    `ALTER TABLE users 
    MODIFY COLUMN id int`,
    { desc: 'Query description', inspect: true }
);

Return types

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

// Savepoint as return type
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:

// Array as return type
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:

// Array as return type
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:

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