PostgreSQL
Execute queries against a PostgreSQL database.
Hypermode’s PostgreSQL API allows you to run queries against PostgreSQL or any PostgreSQL compatible database platform, such as Neon. After defining a host for the PostgreSQL endpoint in your project’s manifest, you can use the following APIs to interact with the data.
Example project
For your reference, A complete example for using the GraphQL APIs is available on GitHub in the
hypermodeinc/functions-as repository, at /examples/postgresql.
Import from the SDK
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 build partners. Let’s chat about what would make the Functions SDK even more powerful for your next use case!
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 (
hostName: string,
statement: string,
params?: Params
): Response
Name of the host, as defined in the manifest.
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.
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>(
hostName: string,
statement: string,
params?: Params,
): QueryResponse<T>
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 project’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.
Name of the host, as defined in the manifest.
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.
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> (
hostName: string,
statement: string,
params?: Params
): ScalarResponse<T>
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.
Name of the host, as defined in the manifest.
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.
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 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.
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>.
An error message, if an error occurred during the operation. Otherwise, this
field is null.
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.
An error message, if an error occurred during the operation. Otherwise, this
field is null.
The number of rows affected by the operation.
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.
An error message, if an error occurred during the operation. Otherwise, this
field is null.
The number of rows affected by the operation.
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.
The x coordinate of the point.
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.
The longitude coordinate of the location, in degrees.
The latitude coordinate of the location, in degrees.