Architectural choices that scale.
Tags: postgres, tutorial, productivity, typescript
Published: 2023-06-06 15:31:26

TL;DR: By leveraging the auto-scaling capabilities of a correctly configured API Gateway in AWS, GCP, or Azure, along with a Neon Postgres db, we can easily create auto-scaling apps. 

When technologies work harmoniously towards a single goal it makes sense to combine them. Let's create a simple application that leverages scale using Neon, API Gateways, and Nitric.


## Setup your Neon Postgres DB

If you do not have one already, create a [Neon project](https://neon.tech).

Navigate to the Projects page in the Neon Console -> Click New Project.

Then you can create your table using the SQL Editor. The table is fairly basic. We'll set it up with a primary key, a piece of text describing the task, and a completed status.

```sql
CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  description TEXT NOT NULL,
  completed BOOLEAN
);
```

Head back to the dashboard and take note of the connection string. We'll want the pooled address here, it should look something like this.

```
postgres://username:password@*********.us-east-2.aws.neon.tech/neondb
```

## Building your task list API

We're going to use the Nitric framework to automatically wire up your API gateway and containerize your images using docker.

### Pre-requisites

- Node.js
- The [Nitric CLI](https://nitric.io/docs/installation)
- An AWS, GCP, or Azure account (your choice)

### Scaffold your project

Let's start with a new nitric project and select the JavaScript starter template:

```bash
nitric new task-list
? Choose a template:  [Use arrows to move, type to filter]
> official/TypeScript - Starter
  official/JavaScript - Starter
```

### Install dependencies

```bash
cd task-list
yarn install
```

###  Review your project

The scaffolded project should have the following structure:

```bash
TypeScript
Python
+--functions/
|  +-- hello.ts
+--node_modules/
|  ...
+--nitric.yaml
+--package.json
+--README.md
```

### Create your environments file 

In the root directory, create an .env file and update it with the neon pooled database url.

```
DATABASE_URL=postgres://username:password@******-pooler.us-east-2.aws.neon.tech/neondb``
```

### Set up your database connection 

Let's establish a connection to a PostgreSQL database using the pg package, which is a PostgreSQL client for Node.js. We're going to be using "pool" which allows us to manage multiple connections to the database.

Create a folder named resources with a file named 'db.js' containing the following snippet.

```javascript 
import pg from "pg"

const { Pool } = pg;

const { DATABASE_URL } = process.env;
export const conn = new Pool({
    connectionString: DATABASE_URL,
    ssl: {
      rejectUnauthorized: false,
    },
});
```

### Create the tasklist api

Remove the 'hello.js' file from the functions folder and replace it with 'task-api.js'

First define your API and import your database connection pool.

```javascript 
import { api } from "@nitric/sdk";
import { conn } from "../resources/db.js";

const tasks = api('tasks');
```

Next, we'll need some API methods to access our task list:

#### Get all tasks
```javascript
tasks.get('/tasks',  async (ctx) => {
  try {
    const tasks = await conn.query('SELECT * FROM tasks');
    ctx.res.json(tasks.rows);
  } catch (err) {
    ctx.res.status = 500;
    ctx.res.json({ response: 'An error occurred fetching your tasks'});
  } 
});
```

#### Create one task
```javascript
tasks.post('/tasks',  async (ctx) => {
  try {
    const { description } = ctx.req.json();
    await conn.query('INSERT INTO tasks (description, completed) VALUES ($1, $2)', [
      description,
      false,
    ]);
    ctx.res.status = 200
    ctx.res.json({ response: 'Task created successfully'});
  } catch (err) {
    ctx.res.status = 500;
    ctx.res.json({ response: `An error occured creating your task ${err}`});
  } 
});
```

#### Update task completion

```javascript
tasks.put('/tasks/:id',  async (ctx) => {
  try {
    const { id } = ctx.req.params;
    const { completed } = ctx.req.json();
    await conn.query('UPDATE tasks SET completed=$1 WHERE id=$2', [
      completed,
      id,
    ]);
    ctx.res.status = 200
  } catch (err) {
    ctx.res.status = 500;
    ctx.res.json({ response: 'An error occurred updating your task'});
  } 
});
```

#### Delete item

```javascript 
tasks.delete('/tasks/:id', async (ctx) => {
  try {
    const { id } = ctx.req.params;
    await conn.query('DELETE FROM tasks WHERE id=$1', [id]);
    ctx.res.status = 200
  } catch (err) {
    ctx.res.status = 500;
    ctx.res.json({ response: 'An error occurred deleting your task'});
  }
});
```

### Testing locally

Now we've got the API established, let's test it out locally.

```bash
yarn run dev
```

Try out the Nitric local dashboard, your URL should look something like this: http://localhost:49153/ 

> Note: Take note of the port because it might vary. 

Try fetching and creating new tasks here, the body for creating a new task will look like this 

```json
{
  "description": "run a mile"
}
```

### Deploy to the cloud

If you're ready, you can deploy this project to AWS, Azure or Google Cloud. For this example, we'll show the steps for AWS, but they're essentially the same in all cases.

Start by defining a stack. Stacks are essentially named deployment targets, which represent instances of your application running in the cloud.

To create a new stack, run nitric stack new and follow the prompts. For this example, we'll name the stack 'awsdev', select 'aws' as the target cloud, and 'us-east-1' as the target region.

```bash
nitric stack new
? What do you want to call your new stack? awsdev
? Which Cloud do you wish to deploy to? aws
? select the region us-east-1
```

Finally, run the up command to deploy the stack and push your code to the cloud:

```bash
nitric up
```

### Recap

When correctly configured API Gateways and Neon automatically scale to handle the volume of traffic your API receives without throttling your backend operations. Consequently, by choosing technologies that scale by default, all you have to do is write code.

Nitric automates the process of creating an open API specifically, containerizing your handler functions into images, and configuring and deploying your API Gateway in whichever cloud you want AWS, GCP, or Azure - all ready to scale to your usage demands.