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;

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;
    inspect?: boolean;
}

Param	Interfaces	Description
desc?	-	Applicable to DDL operations; an optional commit description. Required if the Linked DB's config.require_commit_descs is active.
inspect?	-	An optional directive to log details of the query to the console.

└ QueryResult

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

Type	Interfaces	Description
Array<object>	-	An array of objects—the standard result type for DQL operations (SELECT), and DML operations (INSERT, UPDATE, DELETE) with a RETURNING clause.
number	-	A number indicating total number of rows processed by the query for DML operations (INSERT, UPDATE, DELETE) without a RETURNING clause.
DDLResult	DDLResult	The result type for DDL operations (CREATE, ALTER, DROP, RENAME).
Null	-	The default result type for all other types of query.

└ DDLResult

type DDLResult = boolean | SchemaInterface | Savepoint | null;

Type	Interfaces	Description
boolean	-	The boolean true-the default result type for DDL operations without a RETURNING clause.
SchemaInterface	-	For an operation with a RETURNING clause set to SCHEMA-the corresponding schema instance of the DB object manipulated by the operation.
Savepoint | null	Savepoint	For an operation with a RETURNING clause set to SAVEPOINT-the Savepoint instance associated with the DDL operation; null when savepoint creation has been disabled at the server level.

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
    RETURNING SAVEPOINT`
);
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