Tutorial: Build a Template with Postgres and Redis (Stack)

"use strict";

const fs = require("node:fs");

// Reads the three client-certificate files the platform wrote for a database.
const tls = (prefix) => ({
  ca: fs.readFileSync(process.env[`${prefix}_CA_FILE`]),
  cert: fs.readFileSync(process.env[`${prefix}_CERT_FILE`]),
  key: fs.readFileSync(process.env[`${prefix}_KEY_FILE`]),
});

const UPSERT = "insert into counter (id, n) values (1, 1) on conflict (id) do update set n = counter.n + 1 returning n";

async function openStorage() {
  if (process.env.STORAGE === "sqlite") {
    const Database = require("better-sqlite3");
    const db = new Database(process.env.SQLITE_PATH);
    db.exec("create table if not exists counter (id integer primary key, n integer)");
    return { increment: async () => db.prepare(UPSERT).get().n };
  }
  const { Pool } = require("pg");
  const pool = new Pool({
    host: process.env.DB_HOST,
    port: Number(process.env.DB_PORT),
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    ssl: { ...tls("DB"), rejectUnauthorized: true },
  });
  await pool.query("create table if not exists counter (id integer primary key, n integer)");
  return { increment: async () => (await pool.query(UPSERT)).rows[0].n };
}

// Redis only exists when the user turned the cache on: no REDIS_HOST, no cache.
function openCache() {
  if (!process.env.REDIS_HOST) return null;
  const Redis = require("ioredis");
  return new Redis({
    host: process.env.REDIS_HOST,
    port: Number(process.env.REDIS_PORT),
    username: process.env.REDIS_USER,
    password: process.env.REDIS_PASSWORD,
    tls: { servername: process.env.REDIS_HOST, ...tls("REDIS") },
  });
}

module.exports = { openStorage, openCache, ttl: Number(process.env.CACHE_TTL || 30) };

id: visit-stack
version: 1.0.0
name:
  pt-br: Contador com banco
  en: Counter with a database
description:
  pt-br: App com Postgres ou SQLite e cache Redis opcional. Exemplo de stack.
  en: An app with Postgres or SQLite and an optional Redis cache. A stack example.
category: starters
tags: [starter, postgres, redis]

inputs:
  - id: storage
    type: select
    label:
      pt-br: Onde guardar os dados?
      en: Where should the data live?
    help:
      pt-br: "Postgres gerenciado usa mais memória do plano. SQLite grava no disco do app."
      en: "Managed Postgres uses more of the plan's memory. SQLite writes to the app disk."
    default: postgres
    options:
      - value: postgres
        label:
          pt-br: Postgres gerenciado (recomendado)
          en: Managed Postgres (recommended)
        sets:
          services: { db: "on" }
          env: { app: { STORAGE: postgres } }
      - value: sqlite
        label:
          pt-br: SQLite no disco do app
          en: SQLite on the app disk
        sets:
          services: { db: "off" }
          env: { app: { STORAGE: sqlite } }
  - id: cache
    type: boolean
    label:
      pt-br: Usar cache Redis?
      en: Use a Redis cache?
    default: false
  - id: cache_ttl
    type: number
    label:
      pt-br: Validade do cache (segundos)
      en: Cache lifetime (seconds)
    default: 30
    validation: { min: 1, max: 3600 }
    when: { input: cache, equals: true }

services:
  app:
    source: { type: code, path: src }
    language: javascript
    runtime_version: recommended
    main: server.js
    start: node server.js
    ram: { min: 512, recommended: 512 }
    web: required
    env:
      SQLITE_PATH: /app/data/app.db
      CACHE_TTL: "{{inputs.cache_ttl}}"
      DB_HOST: "{{services.db.host}}"
      DB_PORT: "{{services.db.port}}"
      DB_NAME: "{{services.db.database}}"
      DB_USER: "{{services.db.user}}"
      DB_PASSWORD: "{{services.db.password}}"
      DB_CA_FILE: "{{services.db.ssl.ca_path}}"
      DB_CERT_FILE: "{{services.db.ssl.cert_path}}"
      DB_KEY_FILE: "{{services.db.ssl.key_path}}"
      REDIS_HOST: "{{services.cache.host}}"
      REDIS_PORT: "{{services.cache.port}}"
      REDIS_USER: "{{services.cache.user}}"
      REDIS_PASSWORD: "{{services.cache.password}}"
      REDIS_CA_FILE: "{{services.cache.ssl.ca_path}}"
      REDIS_CERT_FILE: "{{services.cache.ssl.cert_path}}"
      REDIS_KEY_FILE: "{{services.cache.ssl.key_path}}"
    healthcheck: { path: /healthz, timeout: 120 }
    deploy:
      auto_rollback: { default: true }
      rollback: { with_data: optional }
  db:
    database: postgres
    ram: { min: 1024, recommended: 1024 }
  cache:
    database: redis
    ram: { min: 512, recommended: 512 }
    when: { input: cache, equals: true }

permissions:
  ram: { change: true, below_min: false, above_max: true }

outputs:
  - label:
      pt-br: Endereço
      en: Address
    value: "{{app.url}}"
  - label:
      pt-br: Armazenamento
      en: Storage
    value: "{{inputs.storage}}"