Execute SQL operations

This page lists the different methods for interacting with the Forge SQL service.

To start using Forge SQL’s capabilities (including provisioning), you’ll need to install its package in your project:

1
2
npm install @forge/sql

To import the package into your app:

1
2
import sql from '@forge/sql';

Prepared statements

Allows you to prepare a SQL statement, to be later passed parameters, and executed.

1
2
sql.prepare<DataType>(query: string): SqlStatement<Result<DataType>>;

This returns a SqlStatement instance, which provides two methods:

• .bindParams(...params): this: Binds parameters to the query, one for each ? in your query.
• .execute(): Promise<Result<DataType>>: Runs the SQL statement against the database

Our responses are JSON encoded and decoded, and we do not currently do any translation back to a specific field type. You will need to handle this in your code. For example, Dates will be returned as strings.

Passing Parameters

To prevent SQL injection attacks, your query should use a ? in place of values which will be substituted in order, when you use bindParams(param1, param2).

1
2
SELECT * FROM cities WHERE name = ?

Example

This shows an example of how to use sql.prepare to insert data into a table. The use of supplying UpdateQueryResponse is optional here, and you can use it if you want to inspect the results of the query execution.

1
2
import sql, { UpdateQueryResponse } from '@forge/sql';

const results = await sql
                  .prepare<UpdateQueryResponse>(`INSERT INTO cities VALUES (?, ?)`)
                  .bindParams('New york', 'USA')
                  .execute();

And this example shows how to use sql.prepare to query data from a table:

1
2
import sql from '@forge/sql';

interface City {
  name: string;
  state?: string;
  country: string;
};

const results = await sql
                  .prepare<City>(`SELECT * FROM cities WHERE name = ?`)
                  .bindParams('New York')
                  .execute();

console.log(results.rows[0].country); // USA

Execute statement

Executes the desired SQL query against your database. You can execute any ANSI SQL dialect statement through this method. This does not accept any parameters and will execute the query immediately (this is a shorthand for sql.prepare(query).execute()).

1
2
sql.executeRaw<DataType>(query: string): Promise<Result<DataType>>;

Provision database

Manually provisions a database instance for an installation. Running it again will not result in additional instances if one already exists.

1
2
client._provision(): Promise<void>;

Provision Example

1
2
import sql from '@forge/sql';

await client._provision();

Typescript Support

The SDK supports Typescript Generics for the result type, as indicated by <DataType>. This assumes the response will be an array format Array<DataType>, and does not do any validation of the type you supply matches the response. Additionally, all types returned from our API are normalised via JSON, and will need to be converted to a specific object type.

If you are making a Data Modification (DML) / Data Definition (DDL) query, you can supply UpdateQueryResponse as the generic type.

These types are exported and available from the main import:

1
2
import sql, { UpdateQueryResponse, Result } from '@forge/sql'

The types as defined in our code are:

1
2
type SqlParameters = any[];

/** Returned when result set is part of a DDL / DML query */
export interface UpdateQueryResponse {
  /** The number of rows affected by the query */
  affectedRows: number;
  /** The number of fields in the result set */
  fieldCount: number;
  /** The information message from the query */
  info: string;
  /** The ID generated for an AUTO_INCREMENT column by the previous query */
  insertId: number;
  /** The server status */
  serverStatus: number;
  /** The warning status */
  warningStatus: number;
}

interface Result<DataType = any> {
  rows: DataType extends UpdateQueryResponse ? UpdateQueryResponse : DataType[];
  metadata?: Record<string, any>;
}