database-service example #

turnzero scaffold --template database-service

node_modules/
.env
.env.*

# This service runs on Turn Zero Cloud

The service in this folder is hosted on Turn Zero Cloud. It started from the platform's `database-service` template: a Node.js service that keeps its data in the platform's database. This file describes the template the service started from: keep it current as the service changes.

## Where the rules are

Turn Zero Cloud serves its rules to a coding tool through its MCP server, `turnzero-cloud`, and they change with the platform, so none is repeated here.

- Where the server is not connected, connect it first. The platform's page "Connect your AI tool" says how.
- Before you change or deploy this service, read the server's `deploy` skill.

## What you run here

- The tests: `npm test`, in this folder.
- A local run and a deploy: the `deploy` skill gives each step and the line to run, in this order: `submit_manifest`, the local run, `deploy`.

{
  "manifest_version": 1,
  "services": [{ "kind": "database" }],
  "health": "/health",
  "region": "usa",
  "egress": [],
  "audience": { "kind": "public" },
  "packages": []
}

-- migrations/0001_notes.sql
CREATE TABLE "notes" (
  id bigserial PRIMARY KEY,
  title text NOT NULL CHECK (title <> '')
);

{
  "name": "notes-service",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node src/main.mjs",
    "test": "node --test"
  },
  "dependencies": {
    "@turnzero/database": "file:lib/database",
    "pg": "^8.22.0"
  },
  "devDependencies": {
    "@electric-sql/pglite": "0.5.8"
  }
}

// src/db.mjs
import { fileURLToPath } from 'node:url';
import pg from 'pg';
import { bootWalk, createFailoverPool, migrationsFrom } from '@turnzero/database';

const MIGRATIONS = fileURLToPath(new URL('../migrations/', import.meta.url));
const HEALTH_READ_MS = 2_000;

// The live pool, which only the process entry opens: the package's failover
// pool over a pg pool. Its maximum is the plan's connection_limit, which the
// platform injects and the turnzero command's provision writes; one
// connection where the setting is absent.
export function openLivePool() {
  const url = process.env.APP_DATABASE_URL;
  if (!url) throw new Error('APP_DATABASE_URL is not set');
  const limit = process.env.APP_DATABASE_CONNECTION_LIMIT ?? '';
  const max = /^[1-9][0-9]*$/.test(limit) ? Number(limit) : 1;
  return createFailoverPool({
    // settings is { max, connectionTimeoutMillis }: the maximum and the five-second connect limit.
    connect: (settings) => new pg.Pool({ connectionString: url, ...settings }),
    max,
    onConnectionLoss: (error) => console.warn('database session ended:', error.message),
  });
}

// The migrations through the package's boot walk: a lost connection retried
// every second for up to 120 seconds, and any other failure ends the start.
export function migrate(pool) {
  return bootWalk(pool, migrationsFrom(MIGRATIONS), {
    onRetry: (error, attempt) => {
      if (attempt === 1) console.warn('database unreachable at start; retrying:', error.message);
    },
  });
}

// The data layer over any pool with the pg.Pool shape: the live one, or a test double's.
export function createDataLayer(pool) {
  return {
    // One read over the connection, bounded so a probe never waits on a lost database.
    async reachable() {
      let timer;
      const late = new Promise((resolve) => { timer = setTimeout(resolve, HEALTH_READ_MS, 'late'); });
      try {
        return (await Promise.race([pool.query('SELECT 1'), late])) !== 'late';
      } catch {
        return false;
      } finally {
        clearTimeout(timer);
      }
    },
    async addNote(title) {
      const { rows } = await pool.query('INSERT INTO "notes" (title) VALUES ($1) RETURNING id, title', [title]);
      return rows[0];
    },
    async listNotes() {
      const { rows } = await pool.query('SELECT id, title FROM "notes" ORDER BY id');
      return rows;
    },
  };
}

// src/main.mjs, the process entry the start script runs
import { createDataLayer, migrate, openLivePool } from './db.mjs';
import { createAppServer } from './server.mjs';

const pool = openLivePool();
const applied = await migrate(pool);
console.log(applied.length > 0 ? `migrations applied at this start: ${applied.join(', ')}` : 'migrations: none pending at this start');
const port = Number(process.env.PORT ?? 8080);
createAppServer(createDataLayer(pool)).listen(port, () => console.log(`listening on ${port}`));

// src/server.mjs
import { createServer } from 'node:http';

const readBody = async (request) => {
  // One decoder for the whole body: a character whose bytes arrive in two chunks is read whole.
  request.setEncoding('utf8');
  let text = '';
  for await (const chunk of request) text += chunk;
  return text;
};

export function createAppServer(db) {
  return createServer(async (request, response) => {
    // The deployed runtime's abort signal: end the response when it fires.
    request.signal?.addEventListener('abort', () => response.destroy(), { once: true });
    const send = (status, body) => {
      if (response.destroyed) return;
      response.writeHead(status, { 'content-type': 'application/json' });
      response.end(JSON.stringify(body));
    };
    try {
      // A target that does not parse as a URL names no route, so it is answered 404 below.
      let pathname = null;
      try {
        pathname = new URL(request.url, 'http://localhost').pathname;
      } catch {}
      if (request.method === 'GET' && pathname === '/health') {
        return (await db.reachable()) ? send(200, { ok: true }) : send(503, { ok: false });
      }
      if (request.method === 'GET' && pathname === '/notes') return send(200, await db.listNotes());
      if (request.method === 'POST' && pathname === '/notes') {
        const posted = JSON.parse(await readBody(request));
        return send(201, await db.addNote(String(posted?.title ?? '')));
      }
      send(404, { error: 'not_found' });
    } catch (error) {
      const refused = error instanceof SyntaxError ? 'invalid_json' : error?.code === '23514' ? 'invalid_note' : null;
      if (refused === null) console.error(error);
      send(refused === null ? 500 : 400, { error: refused ?? 'internal' });
    }
  });
}

// test/notes.test.mjs
import assert from 'node:assert/strict';
import { after, before, beforeEach, test } from 'node:test';
import { fileURLToPath } from 'node:url';
import { PGlite } from '@electric-sql/pglite';
import { createDatabaseDouble, migrationsFrom } from '@turnzero/database/testing';
import { createDataLayer } from '../src/db.mjs';
import { createAppServer } from '../src/server.mjs';

const MIGRATIONS = fileURLToPath(new URL('../migrations/', import.meta.url));

// One test double for the file: a PostgreSQL engine inside this process, with
// the migrations applied, and the server over it on a port of this machine.
let double;
let db;
let server;
let base;

before(async () => {
  double = await createDatabaseDouble({ engine: new PGlite(), files: migrationsFrom(MIGRATIONS) });
  db = createDataLayer(double.pool);
  server = createAppServer(db);
  await new Promise((resolve) => server.listen(0, '127.0.0.1', resolve));
  base = `http://127.0.0.1:${server.address().port}`;
});

// Every table emptied between tests, so each test starts from empty tables.
beforeEach(() => double.reset());

after(async () => {
  await new Promise((resolve) => server.close(resolve));
  await double.close();
});

test('the migration applies', () => {
  assert.deepEqual(double.applied, ['0001_notes.sql']);
});

test('addNote adds a row and listNotes lists it', async () => {
  assert.deepEqual(await db.addNote('first'), { id: '1', title: 'first' });
  assert.deepEqual(await db.listNotes(), [{ id: '1', title: 'first' }]);
});

test('GET /health reads the database and returns 200', async () => {
  const response = await fetch(`${base}/health`);
  assert.equal(response.status, 200);
  assert.deepEqual(await response.json(), { ok: true });
});

test('POST /notes keeps a title with characters outside ASCII, and GET /notes returns it', async () => {
  const title = 'café ☕ 東京';
  const response = await fetch(`${base}/notes`, {
    method: 'POST',
    headers: { 'content-type': 'application/json' },
    body: JSON.stringify({ title }),
  });
  assert.equal(response.status, 201);
  assert.equal((await response.json()).title, title);
  const listed = await fetch(`${base}/notes`);
  assert.equal(listed.status, 200);
  assert.deepEqual((await listed.json()).map((row) => row.title), [title]);
});

test('POST /notes refuses an empty title, and GET /notes returns no row', async () => {
  const response = await fetch(`${base}/notes`, {
    method: 'POST',
    headers: { 'content-type': 'application/json' },
    body: JSON.stringify({ title: '' }),
  });
  assert.equal(response.status, 400);
  assert.deepEqual(await response.json(), { error: 'invalid_note' });
  assert.deepEqual(await (await fetch(`${base}/notes`)).json(), []);
});

test('POST /notes answers 400 invalid_json for a body that is not JSON, and GET /notes returns no row', async () => {
  const response = await fetch(`${base}/notes`, {
    method: 'POST',
    headers: { 'content-type': 'application/json' },
    body: '{',
  });
  assert.equal(response.status, 400);
  assert.deepEqual(await response.json(), { error: 'invalid_json' });
  assert.deepEqual(await (await fetch(`${base}/notes`)).json(), []);
});