The Modus PostgreSQL API allows you to run queries against PostgreSQL or any PostgreSQL-compatible database platform.

​
Import

To begin, import the postgresql namespace from the SDK:

​
PostgreSQL APIs

The APIs in the postgresql namespace are below, organized by category.

We’re constantly introducing new APIs through ongoing development with early users. Open an issue if you have ideas on what would make Modus even more powerful for your next app!

​
Functions

​
execute

Execute a SQL statement against a PostgreSQL database, without any data returned. Use this for insert, update, or delete operations, or for other SQL statements that don’t return data.

The execute function is for operations that don’t return data. However, some insert/update/delete operations may still return data, such as an identifier for a newly inserted row. In these cases, you can use the queryScalar function instead (for a single row’s identifier), or the query function (for multiple rows).

function postgresql.execute (
  connectionName: string,
  statement: string,
  params?: Params
): Response
​
connectionName
string
required

Name of the connection, as defined in the manifest.

​
statement
string
required

SQL statement containing the query or mutation operation to execute.

While it’s possible to directly include parameter values into your SQL statement, it’s highly recommended to pass a Params object instead. This can help to prevent against injection attacks and other security vulnerabilities.

​
params
Params

Optional parameters to include with the query.

See the details of the Params object for more information.

​
query

Execute a SQL statement against a PostgreSQL database, returning a set of rows. In the results, each row converts to an object of type T, with fields matching the column names.

function postgresql.query<T>(
  connectionName: string,
  statement: string,
  params?: Params,
): QueryResponse<T>
​
T
Type
required

Type of object to use for the data returned from the query. This can be any type, including a custom type defined in your project. It should match the shape of the row returned from the SQL query.

Define custom types in the app’s source code. In AssemblyScript, create classes decorated with @json.

All types, including classes, base classes, and field types must be JSON serializable. You can also use built-in types such as strings, numbers, arrays, and maps.

If working with PostgreSQL’s point data type, you can use a Point or Location object to represent the data.

​
connectionName
string
required

Name of the connection, as defined in the manifest.

​
statement
string
required

SQL statement containing the query or mutation operation to execute.

While it’s possible to directly include parameter values into your SQL statement, it’s highly recommended to pass a Params object instead. This can help to prevent against injection attacks and other security vulnerabilities.

​
params
Params

Optional parameters to include with the query.

See the details of the Params object for more information.

​
queryScalar

Execute a SQL statement against a PostgreSQL database, returning a single scalar value. For example, the result could be a count, sum, or average, or it could be an identifier.

function postgresql.queryScalar<T> (
  connectionName: string,
  statement: string,
  params?: Params
): ScalarResponse<T>
​
T
Type
required

Type of object to use for the data returned from the query. This should generally be a scalar data type, such as a number or string. It should match the type of the data returned from the SQL query.

​
connectionName
string
required

Name of the connection, as defined in the manifest.

​
statement
string
required

SQL statement containing the query or mutation operation to execute.

While it’s possible to directly include parameter values into your SQL statement, it’s highly recommended to pass a Params object instead. This can help to prevent against injection attacks and other security vulnerabilities.

​
params
Params

Optional parameters to include with the query.

See the details of the Params object for more information.

​
Objects

​
Params

class Params {
  public push(value: any): void;
  public toJSON(): string;
}

A container for parameters to include with a SQL operation.

To use this feature, create a new Params object and call the push method for each parameter you want to include. Then pass the object to the execute, query, or queryScalar function along with your SQL statement.

​
push(value)

Push a parameter value into the list included with the SQL operation. The sequence of calls to push determines the order of the parameters in the SQL statement. This corresponds to the order of the ? placeholders or $1, $2, etc.

​
value
any
required

The value of the parameter to include in the SQL operation.

The value can be of any type that’s JSON serializable, including strings, numbers, boolean values, arrays, maps, and custom objects decorated with @json, as long as the database supports it.

If working with PostgreSQL’s Point data type, you can either pass separate parameters for the coordinates and use a point() function in the SQL statement, or you can pass a Point or Location object as a single parameter.

​
toJSON()
string

Serializes the parameters to a JSON string for inclusion in the SQL operation. The SDK functions call this automatically when you pass a Params object. You typically don’t need to call it directly.

​
Response

class Response {
  error: string | null;
  rowsAffected: u32;
}

Represents the response from an execute operation. Also serves as the base class for QueryResponse<T> and ScalarResponse<T>.

​
error
string | null

An error message, if an error occurred during the operation. Otherwise, this field is null.

​
rowsAffected
u32

The number of rows affected by the operation.

​
QueryResponse

class QueryResponse<T> {
  error: string | null;
  rowsAffected: u32;
  rows: T[];
}

Represents the response from a query operation.

​
error
string | null

An error message, if an error occurred during the operation. Otherwise, this field is null.

​
rowsAffected
u32

The number of rows affected by the operation.

​
rows
T[]

An array of objects, each representing a row returned from the query. Each object has fields corresponding to the columns in the result set.

​
ScalarResponse

class ScalarResponse<T> {
  error: string | null;
  rowsAffected: u32;
  value: T;
}

Represents the response from a queryScalar operation.

​
error
string | null

An error message, if an error occurred during the operation. Otherwise, this field is null.

​
rowsAffected
u32

The number of rows affected by the operation.

​
value
T

The scalar value returned from the query.

​
Point

class Point {
 x: f64,
 y: f64,
}

Represents a point in 2D space, having x and y coordinates. Correctly serializes to and from PostgreSQL’s point type, in (x, y) order.

This class is identical to the Location class, but uses different field names.

​
x
f64
required

The x coordinate of the point.

​
y
f64
required

The y coordinate of the point.

​
Location

class Location {
 longitude: f64,
 latitude: f64,
}

Represents a location on Earth, having longitude and latitude coordinates.

Correctly serializes to and from PostgreSQL’s point type, in (longitude, latitude) order.

This class is identical to the Point class, but uses different field names.

​
longitude
f64
required

The longitude coordinate of the location, in degrees.

​
latitude
f64
required

The latitude coordinate of the location, in degrees.

Was this page helpful?

ModelsModus CLI