client.query()

DOCS • API • Client API

---

Run an arbitrary query.

Syntax

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

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

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

Param	Interfaces	Description
query	Statement	An SQL statement (string) or a query instance.
values?	-	For non-DDL operations, optional values for parameters in the query.
options?	QueryOptions	Optional extra parameters for the query.
arg2?	-	Optional argument for a two-parameter call pattern.
arg?	Statement	Optional argument for a single-parameter call pattern.

└ Statement

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

➞ Description

Interface	Description
SelectStatement	A SELECT statement interface.
InsertStatement	An INSERT statement interface.
UpsertStatement	An UPSERT statement interface.
UpdateStatement	An UPDATE statement interface.
DeleteStatement	A DELETE statement interface.
CreateDatabase	A CREATE DATABASE interface.
RenameDatabase	A RENAME DATABASE interface.
AlterDatabase	An ALTER DATABASE interface.
DropDatabase	A DROP DATABASE interface.
CreateTable	A CREATE TABLE interface.
RenameTable	A RENAME TABLE interface.
AlterTable	An ALTER TABLE interface.
DropTable	A DROP TABLE interface.

└ QueryOptions

interface QueryOptions {
    desc?: string;
    ref?: string;
    noCreateSavepoint?: boolean;
    inspect?: boolean;
}

Param	Interfaces	Description
desc	-	Applicable to DDL operations; the commit description.
desc	-	Applicable to DDL operations; the commit reference.
noCreateSavepoint	-	Applicable to DDL operations; a directive to disable savepoint creation.
inspect	-	A directive to log details of the query to the console.

└ QueryResult

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

Type	Interfaces	Description
Array<object>	-	An array of objects—the standard result type 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 total number of rows processed by the query when it's a DML operation without a RETURNING clause.
Savepoint | boolean	Savepoint	The default result type when it's a DDL operation (CREATE, ALTER, DROP, RENAME); the boolean true when savepoint creation has been disabled via options.noCreateSavepoint
Null	-	The default result type for all other types of query.

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 QueryOptions 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