My First REST API » Connect to Postgres as a Service

My First REST API » Connect to Postgres as a Service

Connect to Postgres as a Service

Let's step up the game a little bit and try to use a real database.

In this tutorial we will create a service that connects to a PostgreSQL database, and offers an interface towards the App's features to run custom queries.

Why a service?

I'm glad you asked 😎!

Setting up a connection to a db is not really a "business logic". It's more of a infrastructural responsibility that may be easily shared across different Apps. A service is the best match for this kind of stuff.

Get a Database

You can get a free PostgreSQL database on ElephantSQL by following this tutorial. Once you set it up, you need to get the full connection string that looks a lot like:

postgres://ghsxxny:jGTHd9Xf9KsGgg9fifSCjdl4fw1G-g@tai.db.elephantsql.com/ghsxxny

👉 Then you should add it in the Sandbox's Environmental Variables as PGSTRING.

Barebone Codebase

If we had to "just get it done", without ForrestJS, and without thinking in terms of modular composition, we could just open up pg's documentation and write the following code:

// Create a connection pool:
const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.PGSTRING });

// Run a query:
const result = await pool.query(`SELECT NOW() AS "current_time"`);
console.log(result.rows[0]);

👉 In the beginning, you can simply replace process.env.PGSTRING with the hard coded ElephantSQL connection string. Then try to refactor your code so to learn how to use environmental variables.

💻 Live on CodeSandbox:
https://codesandbox.io/s/050-postgres-connect-osqg3?file=/src/index.js:231-483

But that is not our goal, isn't it? We're here to package this simple logic into a reusable service that makes it easy for one or more Features to deal with a PostgreSQL database.

Features should not bother with the infrastructural challenges, they focus on business logic!

The service that we are going to build implements 2 main responsibilities:

1. Setup a connection towards the database, offering a degree of service customization and validating a successful connection
2. Offer to new features (and existing services) a simple API to run queries.

Scaffold your Service

A Service has the very same internal structure of a Feature. Basically, it just uses a different set of Lifecycle hooks, and gets registered before any Feature.

Moreover, a Service will likely perform some slightly more complex logic compared to a Feature. For this reason, I usually scaffold my services using the Functional Manifest definition.

Create the /pg/index.js Service manifest:

// Functional Manifest Definition
// (yes, it's just another JS function)
const pg = ({ registerAction }) => {};

module.exports = pg;

And don't forget to register it in your App's manifest into the services list!

Settings Propagation

Services often need some form of configuration.

In our case, the pg service will need to know the PostgreSQL Connection String and the maximum amount of concurent clients that we want to keep alive and connected to our db. Stuff like that are usually done by reading an environmental variable. But accessing environmental variables froum inside our services or features may lead to some nasty bugs when you misspell stuff.

I strongly recommend you checkout all your environmental variables in your App's manifest, and propagate them using the App's settings and the getConfig() API. Also, you should consider validating your environment using stuff like envalid:

const envalid = require('envalid');

const env = envalid.cleanEnv(process.env, {
  PGSTRING: envalid.url(),
  PG_MAX: envalid.num({ default: 1 }),
});

forrest.run({
  settings: {
    pg: {
      connectionString: env.PGSTRING,
      maxConnections: env.PGMAX,
    },
  },
});

💻 Live on CodeSandbox:
https://codesandbox.io/s/050-postgres-service-pw36m?file=/src/index.js:1251-1338

With the correct environment, and the settings correctly set up, we can finally use the getConfig() API to setup our connection pool.

Service Initialization

First thing, we register an Action into the $INIT_SERVICE Lifecycle Extension where we create the pool and share it with the rest of the app via Context API:

registerAction({
  target: '$INIT_SERVICE',
  handler: ({ getConfig, setContext }) => {
    // 1. Get the configuration:
    const connectionString = getConfig('pg.connectionString');
    const max = getConfig('pg.maxConnections');

    // 2. Create the pool:
    const pool = new Pool({
      connectionString,
      max,
    });

    // 3. Share it:
    setContext('pg.pool', pool);
  },
});

💻 Live on CodeSandbox:
https://codesandbox.io/s/050-postgres-service-pw36m?file=/src/pg/index.js:179-644

Service Start

When it comes to PG, creating the pool does NOT check out that your connection flows correctly. It just creates the objects and internal wiring for you, but the first query will actually guarantee the correct behavior.

But we don't want other Features to find out the hard way about an incorrect setup. We want to make sure they receive a fully working pool to use in their business logic.

We can simply achieve this goal by extending $SERVICE_START and checking out the PostgreSQL local time:

registerAction({
  target: '$START_SERVICE',
  handler: async ({ getContext }) => {
    const pool = getContext('pg.pool');

    try {
      const res = await pool.query(`SELECT now() AS "pgtime"`);
      console.log(`Successfully connected to Postgres`);
      console.log(`pgtime: ${res.rows[0].pgtime}`);
    } catch (err) {
      throw new Error(`Could not connect to PostgreSQL`);
    }
  },
});

💻 Live on CodeSandbox:
https://codesandbox.io/s/050-postgres-service-pw36m?file=/src/pg/index.js:648-1204

Here we use console.log for simplicity, but I suggest you also check out the Service Logger that provides a better way to stream your logs to the console or to whatever logging gateway you may choose.

Features Integration

One of the most important behaviors of a Service is how does it facilitates Features.
Basically:

what API does your Service provide?

Witht the code we wrote so far, any feature could run a query after service initialization using the Context API:

registerAction({
  target: '$START_FEATURE',
  handler: async ({ getContext }) => {
    const pool = getContext('pg.pool');
    await pool.query('SELECT ...');
  },
});

💻 Live on CodeSandbox:
https://codesandbox.io/s/050-postgres-service-pw36m?file=/src/index.js:452-777