Guide: Write to a SQL database with Node

In this example, we'll use Node.js 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 Node.js 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 init --slug 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
return {
23
userID: updatedID,
24
userEmail: user_email,
25
message: `Set suspended=${suspended}`,
26
};
27
}

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 Node.js tasks, see the Node.js docs.