DocsIntegrationsAppsTutorial: Single-click app

5: Add a real database

In the tutorial so far, storage of user and credential information has been handled in a local SQLite database, for the sake of a dependency-free setup. For real production readiness, the app needs to interface with a more robust database service capable of supporting a serverless application where the filesystem is ephemeral.

In this exercise, you’ll build a second storage driver for Postgres. This database solution was chosen because of the ease of configuring a Postgres integration on Vercel, and we’ll also cover the basic steps for deploying to this hosting provider.

For reference, you can note the following vars/values in .env.example to prepare for this exercise.

Unless you’re going to connect to a real Postgres database in your local development environment, you should not set these vars in .env.local. The Vercel deployment step will include a reminder for setting these vars in the deployed environment.

1CREDENTIALS_STORE_DRIVER=POSTGRES
2
3# Example: DATABASE_URL=postgres://user:password@host/dbname?sslmode=verify-full
4# On Vercel, provisioned automatically when a Postgres database is connected.
5DATABASE_URL=<postgres-connection-string>
6
7# Example: postgres://user:password@host/dbname?sslmode=verify-full
8* Unpooled connection string, used by migration script
9DATABASE_URL_UNPOOLED=<postgres-connection-string>
10
11# Optional
12# DATABASE_POOL_MAX=10

Build the Postgres credentials store driver

The first step is the heavy grunt work of defining the driver, with its read/write operations conforming to your existing CredentialsStore interface. This is all unremarkable SQL operations very similar to the SQLite driver.

One area that differs from SQLite is the need for a connection “pool”, handled with the pg libary’s Pool constructor.

1

Create the connection pool helper

Replace the contents of src/lib/credentials-store/postgres-driver/get-pool.ts.

postgres-driver/get-pool.ts
import { Pool } from "pg";
import { logError } from "@/lib/errors/logger";
function getConnectionString(): string {
  const connectionString = process.env.DATABASE_URL;
  if (!connectionString) {
    throw new Error("DATABASE_URL must be set to use the POSTGRES credentials store driver.");
  }
  return connectionString;
}
function getPoolMax(): number | undefined {
  const configuredMax = process.env.DATABASE_POOL_MAX;
  if (!configuredMax) {
    return undefined;
  }
  const parsedMax = Number(configuredMax);
  if (!Number.isInteger(parsedMax) || parsedMax <= 0) {
    throw new Error(`DATABASE_POOL_MAX must be a positive integer, got "${configuredMax}".`);
  }
  return parsedMax;
}
let pool: Pool | undefined;
export function getPool(): Pool {
  if (!pool) {
    pool = new Pool({ connectionString: getConnectionString(), max: getPoolMax() });
    pool.on("error", (error) => {
      logError("Postgres pool: unexpected error on idle client", error);
    });
  }
  return pool;
}

DATABASE_POOL_MAX matters most if you’re pointing this at a plain, unpooled Postgres server rather than a pooled endpoint like Neon’s. node-postgres’s own default (10 connections) is safe against a pooler, which fans many concurrent queries in below that limit. Against a server with no pooler in front of it, lower it to somewhere in the 2–5 range.

2

Implement the Postgres driver

Replace the contents of src/lib/credentials-store/postgres-driver/postgres-credentials-store.ts.

postgres-credentials-store.ts
import { PoolClient } from "pg";
import { getPool } from "@/lib/credentials-store/postgres-driver/get-pool";
import { decrypt, encrypt } from "@/lib/credentials-store/encryption";
import { CredentialsStore, StoreRecord, StoreUserRecord, UserRecord } from "@/lib/credentials-store/types";
import { AppError } from "@/lib/errors/app-error";
import { logError } from "@/lib/errors/logger";
async function withDatabaseErrorHandling<T>(context: string, run: () => Promise<T>): Promise<T> {
  try {
    return await run();
  } catch (error) {
    logError(`PostgresCredentialsStore: ${context}`, error);
    throw new AppError("DATABASE", "A database error occurred.", { cause: error });
  }
}
interface StoreTokenRow {
  access_token: string;
}
interface UserIdRow {
  user_id: number;
}
export class PostgresCredentialsStore implements CredentialsStore {
  async setStore(store: StoreRecord): Promise<void> {
    await withDatabaseErrorHandling("setStore", async () => {
      const pool = getPool();
      await pool.query(
        `INSERT INTO stores (store_hash, access_token, scope, admin_user_id)
         VALUES ($1, $2, $3, $4)
         ON CONFLICT (store_hash) DO UPDATE SET
           access_token = excluded.access_token,
           scope = excluded.scope,
           admin_user_id = excluded.admin_user_id`,
        [store.storeHash, encrypt(store.accessToken), store.scope, store.adminUserId],
      );
    });
  }
  async setUser(user: UserRecord): Promise<void> {
    await withDatabaseErrorHandling("setUser", async () => {
      const pool = getPool();
      await pool.query(
        `INSERT INTO users (user_id, email)
         VALUES ($1, $2)
         ON CONFLICT (user_id) DO UPDATE SET
           email = excluded.email`,
        [user.userId, user.email],
      );
    });
  }
  async setStoreUser(storeUser: StoreUserRecord): Promise<void> {
    await withDatabaseErrorHandling("setStoreUser", async () => {
      const pool = getPool();
      await pool.query(
        `INSERT INTO store_users (store_hash, user_id)
         VALUES ($1, $2)
         ON CONFLICT (store_hash, user_id) DO NOTHING`,
        [storeUser.storeHash, storeUser.userId],
      );
    });
  }
  async getStoreToken(storeHash: string): Promise<string | undefined> {
    return withDatabaseErrorHandling("getStoreToken", async () => {
      const pool = getPool();
      const result = await pool.query<StoreTokenRow>("SELECT access_token FROM stores WHERE store_hash = $1", [storeHash]);
      const row = result.rows[0];
      return row ? decrypt(row.access_token) : undefined;
    });
  }
  async isStoreUserLinked(storeHash: string, userId: number): Promise<boolean> {
    return withDatabaseErrorHandling("isStoreUserLinked", async () => {
      const pool = getPool();
      const result = await pool.query(
        "SELECT 1 FROM store_users WHERE store_hash = $1 AND user_id = $2",
        [storeHash, userId],
      );
      return result.rowCount !== null && result.rowCount > 0;
    });
  }
  async deleteStore(storeHash: string): Promise<void> {
    await withDatabaseErrorHandling("deleteStore", async () => {
      const pool = getPool();
      const client = await pool.connect();
      try {
        await client.query("BEGIN");
        const affectedUserIds = (
          await client.query<UserIdRow>("SELECT user_id FROM store_users WHERE store_hash = $1", [storeHash])
        ).rows.map((row) => row.user_id);
        await client.query("DELETE FROM store_users WHERE store_hash = $1", [storeHash]);
        await client.query("DELETE FROM stores WHERE store_hash = $1", [storeHash]);
        await deleteUsersWithNoRemainingStores(client, affectedUserIds);
        await client.query("COMMIT");
      } catch (error) {
        await client.query("ROLLBACK");
        throw error;
      } finally {
        client.release();
      }
    });
  }
  async deleteUser(storeHash: string, userId: number): Promise<void> {
    await withDatabaseErrorHandling("deleteUser", async () => {
      const pool = getPool();
      const client = await pool.connect();
      try {
        await client.query("BEGIN");
        await client.query("DELETE FROM store_users WHERE store_hash = $1 AND user_id = $2", [storeHash, userId]);
        await deleteUsersWithNoRemainingStores(client, [userId]);
        await client.query("COMMIT");
      } catch (error) {
        await client.query("ROLLBACK");
        throw error;
      } finally {
        client.release();
      }
    });
  }
}
async function deleteUsersWithNoRemainingStores(client: PoolClient, userIds: number[]): Promise<void> {
  await client.query(
    `DELETE FROM users
     WHERE user_id = ANY($1)
       AND NOT EXISTS (SELECT 1 FROM store_users su WHERE su.user_id = users.user_id)`,
    [userIds],
  );
}

PostgresCredentialsStore implements the exact same CredentialsStore interface as SqliteCredentialsStore from the previous exercise. That shared interface is the seam: nothing else in the app — the install callback, the launch callback, the authorization checks — knows or cares which driver is actually storing data. Once CREDENTIALS_STORE_DRIVER selects POSTGRES (in subsequent steps below), everything upstream of get-credentials-store.ts keeps working unchanged.

Access tokens are still encrypted at rest via CREDENTIALS_ENCRYPTION_KEY, exactly as with the SQLite driver — setStore encrypts before writing, getStoreToken decrypts after reading. The Postgres driver introduces no new handling for this; it reuses the same encrypt/decrypt helpers.

Example code

Build the Postgres driver loader indirection

This step isn’t strictly necessary if Postgres will always be the supporting storage solution in your deployed environment. However, in this app the goal is to keep hosting target flexible, including support for multiple database drivers. If the app is deployed to an environment where Postgres is not the configured storage, the dependency imports in postgres-credentials-store.ts could cause a build problem.

The specifics have to do with the optional dependency pg-cloudflare, missing from the app’s default dependencies but assumed at runtime in a Cloudflare Workers deployment. Even if Postgres is not the intended storage solution in a Cloudflare-deployed scenario, directly importing postgres-credentials-store.ts (with its own chain of dependency imports) will confuse the bundling process into an error.

To keep the app flexible for non-Postgres environments, this step introduces an indirection technique creating a different execution path in those scenarios.

1

Create the stable loader specifier

Instead of directly importing postgres-credentials-store.ts, the runtime “switcher” that chooses a driver based on CREDENTIALS_STORE_DRIVER will use this thin loader.

Replace the contents of src/lib/credentials-store/postgres-driver-loader.ts with a simple re-export of PostgresCredentialsStore.

postgres-driver-loader.ts
export { PostgresCredentialsStore } from "@/lib/credentials-store/postgres-driver/postgres-credentials-store";
2

Create the unavailable stub

Create an alternate version of the loader with no actual Postgres imports/capability.

Replace the contents of src/lib/credentials-store/postgres-driver-loader.unavailable.ts.

postgres-driver-loader.unavailable.ts
import {
  CredentialsStore,
  StoreRecord,
  StoreUserRecord,
  UserRecord,
} from "@/lib/credentials-store/types";
export class PostgresCredentialsStore implements CredentialsStore {
  async setStore(_store: StoreRecord): Promise<void> {
    throw unavailableError();
  }
  async setUser(_user: UserRecord): Promise<void> {
    throw unavailableError();
  }
  async setStoreUser(_storeUser: StoreUserRecord): Promise<void> {
    throw unavailableError();
  }
  async getStoreToken(_storeHash: string): Promise<string | undefined> {
    throw unavailableError();
  }
  async isStoreUserLinked(_storeHash: string, _userId: number): Promise<boolean> {
    throw unavailableError();
  }
  async deleteStore(_storeHash: string): Promise<void> {
    throw unavailableError();
  }
  async deleteUser(_storeHash: string, _userId: number): Promise<void> {
    throw unavailableError();
  }
}
function unavailableError(): Error {
  return new Error(
    "The POSTGRES credentials store driver is not available in this deployment target's build " +
      "(see next.config.ts's turbopack.resolveAlias) — CREDENTIALS_STORE_DRIVER must not be set " +
      "to POSTGRES here.",
  );
}

We’ll shortly be setting up an alias for this “unavailable” version of the loader. When CREDENTIALS_STORE_DRIVER isn’t POSTGRES, the alias points that specifier at this unavailable stub instead of postgres-driver-loader.ts, and pg never enters the module graph for that build. Every method here throws rather than silently no-opping — if this class is ever actually instantiated, the alias and the runtime driver selection have drifted out of sync.

3

Alias the driver at build time

Add the appropriate build-time alias to next.config.ts.

next.config.ts
const nextConfig: NextConfig = {
  turbopack: {
    resolveAlias:
      process.env.CREDENTIALS_STORE_DRIVER !== "POSTGRES"
        ? {
            "@/lib/credentials-store/postgres-driver-loader":
              "@/lib/credentials-store/postgres-driver-loader.unavailable",
          }
        : {},
  },
  ...
};

Example code

Write the initial Postgres migration and runner

The SQLite driver relies on running CREATE TABLE IF NOT EXISTS on each connection. In a real production environment, you need a more optimized strategy. In this step, you’ll create a Postgres “migration” script that runs on each deployment to keep your database schema up to date.

1

Write the initial schema migration

Create src/lib/credentials-store/postgres-driver/migrations/0001_initial_schema.sql with the following contents.

Note that is a rare case in the tutorial in which the file does not already exist in the project boilerplate.

0001_initial_schema.sql
CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);
CREATE TABLE stores (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  store_hash TEXT NOT NULL UNIQUE,
  access_token TEXT NOT NULL,
  scope TEXT NOT NULL,
  admin_user_id INTEGER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE
);
CREATE TABLE store_users (
  store_hash TEXT NOT NULL REFERENCES stores (store_hash) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE,
  PRIMARY KEY (store_hash, user_id)
);
2

Create the migration runner script

Replace the contents of scripts/postgres/migrate.mjs.

postgres/migrate.mjs
#!/usr/bin/env node
import { execFileSync } from "node:child_process";
import { fileURLToPath } from "node:url";
const connectionString = process.env.DATABASE_URL_UNPOOLED ?? process.env.DATABASE_URL;
if (!connectionString) {
  console.log("[migrate] DATABASE_URL_UNPOOLED/DATABASE_URL not set — skipping Postgres migrations.");
  process.exit(0);
}
const nodePgMigrateBin = fileURLToPath(import.meta.resolve("node-pg-migrate/bin/node-pg-migrate"));
execFileSync(
  process.execPath,
  [
    nodePgMigrateBin,
    "up",
    "--migrations-dir",
    "src/lib/credentials-store/postgres-driver/migrations",
    "--migration-file-language",
    "sql",
  ],
  {
    stdio: "inherit",
    env: { ...process.env, DATABASE_URL: connectionString },
  },
);

This short command-line script uses the node-pg-migrate library to handle running all pending Postgres migrations.

3

Wire the migration into the build

Modify package.json to add the migration script and a vercel-build script that runs it before every build.

package.json
  "scripts": {
    ...
    "db:postgres:migrate": "node scripts/postgres/migrate.mjs",
    "vercel-build": "pnpm db:postgres:migrate && next build"
  },

You’re building these scripts directly into package.json, assuming Vercel as the deployment target.

In keeping with the aim of hosting provider flexibility, the final version of this app instead includes a scaffold script that will modify package.json on demand when Vercel is the target platform. See the post-tutorial enhancements.

Example code

Add the POSTGRES driver-select branch

The final step is to update getCredentialsStore to select the Postgres driver when CREDENTIALS_STORE_DRIVER is POSTGRES.

1

Add the branch to the driver selector

Update src/lib/credentials-store/get-credentials-store.ts to remove the default line and add the conditional branch for Postgres.

get-credentials-store.ts
...
import { PostgresCredentialsStore } from "@/lib/credentials-store/postgres-driver-loader";
const VALID_DRIVERS: CredentialsStoreDriver[] = ["SQLITE", "POSTGRES"];
const DEFAULT_DRIVER: CredentialsStoreDriver = "SQLITE";
...
function getConfiguredCredentialsStore(): CredentialsStore {
  switch (getConfiguredDriver()) {
    case "SQLITE":
      return new SqliteCredentialsStore();
    case "POSTGRES":
      return new PostgresCredentialsStore();
  }
}

Your app is now ready for an environment with Postgres connected.

Example code

Full step code

Full diff

Deploy to Vercel with Postgres

The starter app is designed to support Vercel as a default hosting target. See Deploying to Vercel in the repo documentation for a full walkthrough of the deployment process, but below are the basic steps.

Note that the “Scaffold the Vercel Tooling” step in the project guide is not necessary for your tutorial codebase, where the db:postgres:migrate and vercel-build scripts have already been baked into package.json.

Prerequisites

Steps

Once you create your Vercel project: If BigCommerce needs to reach a preview or branch deployment rather than production, disable or scope Vercel’s Deployment Protection for it under Project Settings > Deployment Protection. Otherwise BigCommerce’s server-to-server callbacks hit Vercel’s SSO gate instead of your app, and installs fail in a way that looks like an app bug rather than a deployment-configuration one.

  1. Commit your current code to Git and push to a remote repository on your chosen Git provider.
  2. Create a new project in Vercel and import from your Git provider. If any environment variables are auto-populated from the project, delete them. You initially just need one: Set the environment variable DATA_MODE to MOCK for an initial deployment.
  3. Verify the deployment renders in MOCK mode and capture the deployed base URL.
  4. Create a Neon Postgres database from the project’s Storage tab, which automatically provisions DATABASE_URL and DATABASE_URL_UNPOOLED values in the project.
  5. Create a new app in the Developer Portal and set the callback URLs using the Vercel production URL. (See the previous tutorial step or the guide linked above for other required settings.)
  6. Update/set the remaining vars in the Vercel project’s Environment Variables tab. See the table below.
  7. Redeploy.
Environment VariableValue/Source
DATA_MODEMULTITENANT
APP_ORIGINYour Vercel production URL
CREDENTIALS_STORE_DRIVERPOSTGRES
CREDENTIALS_ENCRYPTION_KEY and SESSION_SECRETGenerate new values
BIGCOMMERCE_CLIENT_ID and BIGCOMMERCE_CLIENT_SECRETYour new Developer Portal app
DEVELOPER_NAME, SUPPORT_EMAIL, SUPPORT_URL, SUPPORT_PHONE, DEVELOPER_LOGO_FILENAMECopy from .env.local

Your Vercel project has a permanent domain that remains unchanged across deployments and a deployment domain with a deployment-specific hash. Make sure to use the permanent domain for APP_ORIGIN and in your Developer Portal callback URLs.

You should now have a Vercel-hosted app ready to install in your store’s control panel (Apps -> Develop)!

Next steps