Forge SQL tutorial

At present, you can test how your app can query and update an SQL database provisioned on your developer site. You’ll also be able to test some observability features we are planning to enhance over the next few months.

For more information about Forge SQL’s planned implementation, see Forge SQL.

Limitations

During this EAP, the following limitations will apply:

• You’ll need to define triggers to provision and associate an SQL database instance for your app upon installation. In a future update, Forge SQL will automatically do this for you.

• SQL databases can only be provisioned this way for app installations in your development environment. At the end of the EAP period, Atlassian will delete all provisioned SQL databases (along with all data they contain).

• Forge SQL accepts SQL statements for defining and updating your provisioned database’s schema. You’ll need to manually execute each SQL migration (using web triggers).

For a complete list of limitations, see the Forge SQL overview.

Before you begin

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` ;

What we're building

This tutorial will provide code examples for a library app that lets users:

1. Create users
2. Create books
3. Allow users to borrow books
4. List books
5. List borrowed books

To accomplish this, our app needs three tables:

Step 1: Define provisioning function

To provision an SQL database for your app, you’ll need to define a trigger for it. This will allow your app to invoke the database provisioning process automatically upon installation.

Start by defining a trigger module in your manifest invoked by the app installation event (specifically, avi:forge:installed:app).

In the example to the right, we create the trigger provision-sql-db-installer and map it to a database provisioning function named provisionSchema:

manifest.yml

1
2
modules:
  trigger:
    - key: provision-sql-db-installer
      function: provisionSchema
      events:
        - avi:forge:installed:app
  function:
    - key: provisionSchema
      handler: index.provisionSchema

Afterwards, create the database provisioning function named provisionSchema. In the following examples, we define what the function does in provision.ts and sql.ts:

provision.ts

1
2
import { createSchema } from './sql';

export const provisionSchema = async () => {
  await createSchema();

  return {
    statusCode: 200,
    headers: [],
    body: 'Schema created',
  };
};

sql.ts

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

export const createSchema = async () => {
  console.log(`Creating SQL schema on install`);
  await sql._provision();
  console.log('database created!');
};

Step 2: Create SQL migration script

Use SQL migration scripts to create tables and otherwise update the schema of your provisioned database.

You’ll need to define a web trigger to execute these scripts. Start by defining the webtrigger module in your manifest:

manifest.yml

1
2
modules:
...
  webtrigger:
    - key: run-migration
      function: runMigration
  function:
    - key: resolver
      handler: index.handler
    - key: runMigration
      handler: index.runMigration

Once the database has been provisioned, you can define the table structure that your app requires. To do so, first define the function for running SQL migrations:

src/resolvers/migration.ts

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

export const CREATE_USERS_TABLE = `CREATE TABLE IF NOT EXISTS Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)`;

export const CREATE_BOOKS_TABLE = `CREATE TABLE IF NOT EXISTS Books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100) NOT NULL,
    published_date DATE
)`;

export const CREATE_LOANS_TABLE = `CREATE TABLE IF NOT EXISTS Loans (
    loan_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    book_id INT,
    loan_date DATE,
    return_date DATE
)`;

export const CREATE_INDEX_ON_BOOKS = `
    CREATE INDEX IF NOT EXISTS idx_books_title_author ON Books (title, author);
`;

export const ALTER_USER_TABLE = `ALTER TABLE Users ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT TRUE`;

const migrationsRunner = migrationRunner
  .enqueue('v001_create_users_table', CREATE_USERS_TABLE)
  .enqueue('v002_create_books_table', CREATE_BOOKS_TABLE)
  .enqueue('v003_create_loans_table', CREATE_LOANS_TABLE)
  .enqueue('v004_create_index_title_author_on_books', CREATE_INDEX_ON_BOOKS)
  .enqueue('v005_alter_user_table_add_active', ALTER_USER_TABLE);

function getResponse<Body>(statusCode: number, body: Body) {
  let statusText = '';
  if (statusCode === 200) {
    statusText = 'Ok';
  } else if (statusCode === 404) {
    statusText = 'Not Found';
  } else {
    statusText = 'Bad Request';
  }

  return {
    headers: { 'Content-Type': ['application/json'] },
    statusCode,
    statusText,
    body,
  };
}

export const runMigration = async () => {
  try {
    await applyMigrations();
    return getResponse(200, 'Migrations successfully executed');
  } catch (e) {
    console.error('Error while executing migration', e);
    return getResponse(500, 'Error while executing migrations');
  }
};

export const applyMigrations = async () => {
  const migrationsRun = await migrationsRunner.run();
  console.log('Migrations run:', migrationsRun);

  console.log('Migrations checkpoint [after running migrations]:');
  await migrationRunner.list()
    .then((migration) =>
      migration.map((y) => console.log(`${y.name} migrated at ${y.migratedAt.toUTCString()}`)));
};

Step 3: Execute SQL migration script

Now that you’ve defined a web trigger for running SQL migrations, you can execute it. You can do this after installing your app on any development site.

Each app installation will have a unique Installation ID, which you'll need to execute your web trigger. Use forge install list to get this:

1
2
Showing all the current installations of your app:
┌──────────────────────────────────────┬─────────────┬────────────────────────────┬────────────┬─────────┬───────────────┐
│ Installation ID                      │ Environment │ Site                       │ Product    │ Version │ Major Version │
├──────────────────────────────────────┼─────────────┼────────────────────────────┼────────────┼─────────┼───────────────┤
│ bf9d8915-cc1a-xxxxxxxxxxxxxxxxxxxxxx │ development │ xxxxxxxxxxxx.atlassian.net │ Confluence │ Latest  │ 2             │
└──────────────────────────────────────┴─────────────┴────────────────────────────┴────────────┴─────────┴───────────────┘

Then, run forge webtrigger <Installation ID>. This will generate a web trigger URL, which you can use to invoke the run-migration web trigger. For example, to execute the migrations, run:

curl --header "Content-Type: application/json" --request POST --data '' <web-trigger-url>

Step 4: Write your SQL operations

For now, Forge SQL accepts prepared statements with positional parameters. We plan to expand parameter support later on based on capacity and EAP feedback.

Use the following method signature for defining your SQL statements:

1
2
sql
  .prepare<DataType>(query: string)
  .bindParams(...args: SqlParameters): Promise<Result<DataType>>;

For example, to create a function for saving data to the Users table (defined in the SQL migration script from Step 2):

interactors/userInteractor.ts

1
2
const CREATE_USER = `INSERT INTO Users (name, email) VALUES (?, ?);`;

export const createUser = async (name: string, email: string) => {
  try {
    return await sql.prepare(CREATE_USER).bindParams(name, email).execute();
  } catch (error) {
    console.error('Error creating user', JSON.stringify(error));
    // @ts-expect-error - Ignore error unknown
    // eslint-disable-next-line @typescript-eslint/no-unsafe-member-access
    if (error.code === errorCodes.SQL_EXECUTION_ERROR && error.debug.code === 'ER_DUP_ENTRY') {
      throw new Error('A user with this email already exists.');
    }
    throw error;
  }
};

Inserting Date values

Forge SQL lets you store multiple Date objects based on data types supported by ANSI SQL. Ensure that each date type adheres to the specified input format.

The following example uses moment to format dates accordingly:

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


export const insertLoginDetails = async () => {
  await sql
  .executeRaw(`CREATE TABLE IF NOT EXISTS LoginDetails (
      id INT PRIMARY KEY,
      CreatedAt DATE,
      LastLoginTime TIME,
      LastLoginTimestamp TIMESTAMP);`
  );
  await sql
  .prepare(`INSERT INTO LoginDetails
      (id, CreatedAt, LastLoginTime, LastLoginTimestamp)
    VALUES
      (?, ?, ?, ?);`)
  .bindParams(
    1,
    moment().format("YYYY-MM-DD"),
    moment().format("HH:mm:ss.SSS"),
    moment().format("YYYY-MM-DDTHH:mm:ss.SSS")
  );
};

Tunnelling

Whenever you open a tunnel, Forge will redirect local app code changes to all installations of your app in a specified environment. This provides you with a faster turnaround for testing changes to your app.

SQL operations executed while tunnelling will be directed to your app’s provisioned database. This means that changes to the database from other users will be reflected on any SQL queries performed while tunnelling.

Step 5: Submit feedback

After testing Forge SQL, feel free to provide us with your feedback. You can do so through the the Forge SQL EAP category on the Atlassian Developer Community. Only EAP participants will be able access this category.