Guide: Write to a SQL database with JavaScript

In this example, we'll use JavaScript and node-postgres to write a task to make an update to a PostgreSQL database. To follow along, you'll need a DB you can connect to and the airplane CLI (see Install the Airplane CLI).

Example database
In our example below, we'll assume there's a PostgreSQL database we can connect to that has the following table of users:
sql
Copied
1
CREATE TABLE users (
2
    id           TEXT                     NOT NULL,
3
    email        TEXT                     NOT NULL,
4
    is_suspended BOOLEAN                  NOT NULL DEFAULT false,
5
    suspended_at TIMESTAMP WITH TIME ZONE
6
);
We'll write a task that accepts a user as input and updates the DB to suspend or un-suspend them.

Create a new task
To start, make a new JavaScript task and give it a name ("Suspend User"):

Parameters
Let's add parameters for "User Email" (short text) and "Suspended" (boolean):
These parameters will get passed to our JavaScript inside a JSON object—more on that later.
Select Create task to finish.

Initialize a new script
The task page shows instructions on how to initialize your task. You'll need the slug, which is referenced in the commands for you:
Run the init command to create a script at a path of your choice:
bash
Copied
1
$ airplane tasks init --from suspend_user
2
? Where should the script be created? suspend_user.js
This should create a new file for you:
javascript
Copied
1
// suspend_user.js
2
// Linked to https://app.airplane.dev/t/suspend_user [do not edit this line]
3


4
export default async function (params) {
5
  console.log("parameters:", params);
6
}

Install dependencies
We will use the node-postgres package to query our PostgreSQL database. Go ahead and install it as a dependency:
bash
Copied
1
npm install pg
Make sure you're in the directory with the package.json file. Running npm install will have created or updated a package-lock.json file and updated package.json to include pg:
javascript
Copied
1
// package.json
2
{
3
  "dependencies": {
4
    "pg": "^8.7.1"
5
  }
6
}

Write a script
Next, let's fill in suspend_user.js (or suspend_user.ts if TypeScript) with our actual update logic:
javascript
Copied
1
// suspend_user.js
2
import { Client } from "pg";
3


4
export default async function ({ user_email, suspended }) {
5
  // Connect using DATABASE_URL (which we'll set later)
6
  const client = new Client({
7
    connectionString: process.env.DATABASE_URL,
8
  });
9
  await client.connect();
10


11
  // Run the UPDATE query
12
  const res = await client.query(
13
    `UPDATE users
14
    SET
15
      is_suspended = $1,
16
      suspended_at = (CASE WHEN is_suspended THEN NOW() ELSE NULL END)
17
    WHERE email = $2 RETURNING id`,
18
    [suspended, user_email]
19
  );
20
  const updatedID = res.rows[0].id;
21


22
  // Close the database connection.
23
  await client.end();
24


25
  return {
26
    userID: updatedID,
27
    userEmail: user_email,
28
    message: `Set suspended=${suspended}`,
29
  };
30
}
As you can see, this is not too different from a basic script you'd run outside of Airplane.
There are two main differences:
  • We define a default function export that takes in the parameters as a JSON object. Each parameter is keyed by its slug.
  • We return Output from our function. This results in a nicer, user-friendly table for users to see.

Configure an environment variable
Before we deploy this, we need to set process.env.DATABASE_URL, which our script above references.
To do this, let's first create a new config variable by opening a new tab, going to the Config Variables page, and selecting "New." Enter a name and value (e.g. postgresql://username:password@rds.amazonaws.com:5432/database):
From your task page, select Edit task and, under Advanced, add DATABASE_URL as an environment variable from config:
Select Update task to save.

Deploy the task
Now you're ready to deploy your code. All you have to do is run:
bash
Copied
1
airplane deploy ./suspend_user.js
This will package, build, and deploy the task to Airplane.
The task page now shows a form that teammates can run.
Note how the console.log in the script above produces a structured output section:

Reference
For a full reference to writing JavaScript tasks, see the JavaScript docs.
PrivacyTermsSupport