Build a form backed by a database #

View this page as Markdown

Prompt:

Build a public guestbook on Turn Zero Cloud. Keep the entries in PostgreSQL. Include a form and routes to read, change and delete entries. Use the Free plan.

This complete application uses Node.js and the pg driver, without the Database package. The page lists the latest 100 entries. Anyone can add an entry through its form, or read, change and delete an entry through its JSON routes.

All routes are public. For private records, add sign-in and access checks.

What your AI tool does #

Your AI tool uses the plan named in your request and asks if none is named. It asks before any check that writes to production. With no shell, it uses the platform's tools and skips local commands and tests.

Before your AI starts #

For the AI tool: Use a connected, signed-in Cloud tool.

The platform supplies APP_DATABASE_URL and APP_DATABASE_CONNECTION_LIMIT. Keep those settings out of the files and never put their values in chat. The deployed server listens on PORT.

Steps #

1. Write the files #

These six files form the whole application. With a shell, write them in the application's folder. Without a shell, keep each path and its complete text for deploy_files.

Package settings #

package.json goes at the root. The build installs pg, and npm start runs the entry file:

{
  "name": "database-guestbook",
  "private": true,
  "type": "module",
  "scripts": {
    "start": "node src/main.mjs"
  },
  "dependencies": {
    "pg": "^8.22.0"
  }
}

The manifest #

manifest.json declares a database and a public audience. It uses no library packages:

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

The first migration #

migrations/0001_entries.sql creates the table. The migration runner supplies the transaction:

-- migrations/0001_entries.sql
CREATE TABLE entries (
  id bigserial PRIMARY KEY,
  message text NOT NULL CHECK (char_length(message) BETWEEN 1 AND 1000)
);

The migration runner #

src/migrate.mjs applies the files in name order. It holds one connection's advisory lock, so a second starting copy waits. Each file and its row in schema_migrations commit together:

// src/migrate.mjs: applies each file of migrations/ once, in name order, under a lock, on the pg driver alone
import { readdir, readFile } from 'node:fs/promises';
import { join } from 'node:path';

// The Database package's ledger and lock, so its applyMigrations can take over later.
const LEDGER = 'schema_migrations';
const LOCK = "hashtext('mark_one_database_migrations')";

export async function migrate(client, folder = 'migrations') {
  // One client, not a pool: an advisory lock belongs to the connection that takes it.
  await client.query(`SELECT pg_advisory_lock(${LOCK})`);
  try {
    await client.query(`CREATE TABLE IF NOT EXISTS ${LEDGER} (name text PRIMARY KEY, applied timestamptz NOT NULL DEFAULT now())`);
    const { rows } = await client.query(`SELECT name FROM ${LEDGER}`);
    const applied = new Set(rows.map((row) => row.name));
    const names = (await readdir(folder)).filter((name) => name.endsWith('.sql')).sort();
    for (const name of names) {
      if (applied.has(name)) continue;
      // A leading byte-order mark is dropped, as the package drops it: the server refuses it.
      const sql = (await readFile(join(folder, name), 'utf8')).replace(/^\uFEFF/, '');
      // Each file and its ledger row in one transaction, so a failed file records nothing.
      await client.query('BEGIN');
      try {
        await client.query(sql);
        await client.query(`INSERT INTO ${LEDGER} (name) VALUES ($1)`, [name]);
        await client.query('COMMIT');
      } catch (error) {
        await client.query('ROLLBACK');
        throw error;
      }
    }
  } finally {
    await client.query(`SELECT pg_advisory_unlock(${LOCK})`);
  }
}

This is the runner from Keep the schema as migrations. It records file names, not content hashes. Once a database has applied a file, leave it unchanged and add the next change as 0002_...sql.

The server #

src/server.mjs runs those migrations before listening. It keeps the form body within 16 KiB, checks messages and escapes them before writing HTML. SQL values are passed separately from the query text:

// src/server.mjs: a public form, database health check and entry routes
import { createServer } from 'node:http';
import { fileURLToPath } from 'node:url';
import { migrate } from './migrate.mjs';

const MIGRATIONS = fileURLToPath(new URL('../migrations/', import.meta.url));
const BODY_LIMIT = 16 * 1024;
const LOST = ['ECONNREFUSED', 'ECONNRESET', 'ETIMEDOUT', 'EAI_AGAIN', '57P01', '57P03', '08006'];
const escape = (text) => String(text).replace(/[&<>"']/g, (c) => `&#${c.charCodeAt(0)};`);
const refuse = (status, code) => Object.assign(new Error(code), { status, code });

async function prepare(pool) {
  const deadline = Date.now() + 120_000;
  for (;;) {
    let client;
    try {
      client = await pool.connect();
      await migrate(client, MIGRATIONS);
      client.release();
      return;
    } catch (error) {
      if (client) client.release(error);
      const lost = LOST.includes(error.code) || /timeout|terminated/i.test(error.message);
      if (!lost || Date.now() + 1_000 > deadline) throw error;
      await new Promise((resolve) => setTimeout(resolve, 1_000));
    }
  }
}

async function readBody(request) {
  let size = 0;
  const chunks = [];
  for await (const chunk of request) {
    size += chunk.length;
    if (size <= BODY_LIMIT) chunks.push(chunk);
  }
  if (size > BODY_LIMIT) throw refuse(413, 'body_too_large');
  return Buffer.concat(chunks).toString('utf8');
}

function messageOf(value) {
  if (typeof value !== 'string') throw refuse(400, 'message_required');
  const message = value.trim();
  if (!message || message.length > 1000) throw refuse(400, 'message_length');
  return message;
}

function pageOf(rows) {
  return `<!doctype html>
<html lang="en">
<head><meta charset="utf-8"><title>Guestbook</title></head>
<body>
  <h1>Guestbook</h1>
  <form method="post" action="/">
    <label>Message <input name="message" maxlength="1000" required></label>
    <button>Post</button>
  </form>
  <ul>${rows.map((row) => `<li>#${row.id}: ${escape(row.message)}</li>`).join('')}</ul>
</body>
</html>`;
}

async function reachable(pool) {
  let timer;
  try {
    await Promise.race([
      pool.query('SELECT 1'),
      new Promise((_, reject) => {
        timer = setTimeout(() => reject(new Error('health read timed out')), 2_000);
      }),
    ]);
    return true;
  } catch {
    return false;
  } finally {
    clearTimeout(timer);
  }
}

async function handle(pool, request, response) {
  request.signal?.addEventListener('abort', () => response.destroy(), { once: true });
  const send = (status, body, type = 'application/json', headers = {}) => {
    if (response.destroyed) return;
    response.writeHead(status, { 'content-type': type, ...headers });
    response.end(type === 'application/json' ? JSON.stringify(body) : body);
  };
  const { pathname } = new URL(request.url, 'http://localhost');
  const type = String(request.headers['content-type'] ?? '').split(';')[0].trim().toLowerCase();
  try {
    if (pathname === '/health' && request.method === 'GET') {
      const ok = await reachable(pool);
      return send(ok ? 200 : 503, { ok });
    }
    if (pathname === '/' && request.method === 'GET') {
      const { rows } = await pool.query('SELECT id, message FROM entries ORDER BY id DESC LIMIT 100');
      return send(200, pageOf(rows), 'text/html; charset=utf-8');
    }
    if (pathname === '/' && request.method === 'POST') {
      if (type !== 'application/x-www-form-urlencoded') throw refuse(415, 'form_required');
      const form = new URLSearchParams(await readBody(request));
      await pool.query('INSERT INTO entries (message) VALUES ($1)', [messageOf(form.get('message'))]);
      return send(303, '', 'text/plain', { location: '/' });
    }
    const match = /^\/entries\/([1-9][0-9]{0,18})$/.exec(pathname);
    if (!match || BigInt(match[1]) > 9223372036854775807n) return send(404, { error: 'not_found' });
    const id = match[1];
    if (request.method === 'GET') {
      const { rows } = await pool.query('SELECT id, message FROM entries WHERE id = $1', [id]);
      return rows.length ? send(200, rows[0]) : send(404, { error: 'not_found' });
    }
    if (request.method === 'PATCH') {
      if (type !== 'application/json') throw refuse(415, 'json_required');
      let body;
      try { body = JSON.parse(await readBody(request)); }
      catch (error) {
        if (error.status) throw error;
        throw refuse(400, 'invalid_json');
      }
      const { rows } = await pool.query(
        'UPDATE entries SET message = $1 WHERE id = $2 RETURNING id, message',
        [messageOf(body?.message), id],
      );
      return rows.length ? send(200, rows[0]) : send(404, { error: 'not_found' });
    }
    if (request.method === 'DELETE') {
      const { rowCount } = await pool.query('DELETE FROM entries WHERE id = $1', [id]);
      return rowCount ? send(200, { deleted: true }) : send(404, { error: 'not_found' });
    }
    return send(405, { error: 'method_not_allowed' }, 'application/json', { allow: 'GET, PATCH, DELETE' });
  } catch (error) {
    if (!error.status) console.error(error);
    return send(error.status ?? 500, { error: error.status ? error.code : 'internal' });
  }
}

export async function startServer(pool, port = Number(process.env.PORT ?? 8080)) {
  await prepare(pool);
  const server = createServer((request, response) => {
    handle(pool, request, response).catch((error) => {
      console.error(error);
      if (!response.destroyed) response.writeHead(500).end();
    });
  });
  await new Promise((resolve, reject) => {
    server.once('error', reject);
    server.listen(port, resolve);
  });
  return server;
}

The startup loop retries a lost database connection for up to 120 seconds. Other migration failures stop startup. The health route makes one short database read and answers 503 if it fails or takes more than two seconds.

The process entry #

src/main.mjs opens the live pool. The injected connection string already carries its TLS settings, so add no ssl override:

// src/main.mjs: open the live pool, then start the application
import pg from 'pg';
import { startServer } from './server.mjs';

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;
const pool = new pg.Pool({ connectionString: url, max, connectionTimeoutMillis: 5_000 });
pool.on('error', (error) => console.warn('database session ended:', error.message));

try {
  await startServer(pool);
} catch (error) {
  console.error(error);
  await pool.end();
  process.exitCode = 1;
}

The pool shares the plan's connection limit across all routes. Free permits one connection per process, so the migration lock uses that same connection and releases it before the server starts.

2. Declare and check the application #

Call create_application on the chosen plan and keep its identifier. Call submit_manifest with that identifier as application and the complete manifest.json object as manifest.

Then call check_deploy before the first deploy and after each fix. With a shell, also run the check line it returns. Without a shell, use its without_command call for the file check in step 3. Deploy only once nothing is refused.

3. Deploy through your available route #

With a shell: call deploy with the application and the folder as local_path. Run its returned command, or command_windows in Windows, exactly as given. Deploy from your computer covers the full route.

With no shell: call deploy_files with the application and all six files as {path, content} entries. Keep package.json and manifest.json at the root, and include the migrations/ file. First send check_only: true and correct every refusal.

Once the answer is files_checked, call deploy_files without check_only to start the deploy. List all six files using the path and sha256 returned in kept. Do not calculate hashes yourself or resend unchanged content.

Each file must fit within 512 KiB, the list within 200 files, and the decoded total within 4 MiB. These six text files fit those limits. Do not send node_modules; the build installs pg from package.json. Deploy from a chat explains the file list and its limits.

A new application has production alone, so this deploy reaches production. To try writes away from production first, turn on development before deploying, and use that environment for each check.

4. Check the result #

Call read_status until the deploy ends. If it fails, read its recorded error and container logs before changing the files or retrying.

Once deployed, use request_route on GET /health and GET / in the environment you deployed to. Expect status 200 and a guestbook form. A browser can use the form without signing in.

With approval for a production write, or using approved development test data, post a message to / as application/x-www-form-urlencoded. Expect a 303 redirect to /, then read the page again. It shows the entry's identifier beside its escaped message.

Use that identifier in the JSON routes:

Request Result
GET /entries/<id> The saved id and message.
PATCH /entries/<id> with Content-Type: application/json and {"message":"Updated"} The changed entry.
DELETE /entries/<id> {"deleted":true}. A later read returns 404.

Changing and deleting production entries need the person's approval for those checks too. Do not create test data in production just to prove persistence.

Expected result #

Visitors can use the form without signing in. Entries remain in PostgreSQL when the server restarts. The JSON routes return the saved entry, its changed value, or confirmation that it was deleted.

Data and local runs #

The form stores entries in PostgreSQL, not in the server's memory or disk. Durability and backups explains what survives and how an operator restore works during the beta.

For a local run before the first deploy, follow Run against your own PostgreSQL. To use the platform's development database instead, follow Develop locally.

Refusals #

The table lists only the example application's own responses. For platform refusals, read the refusals reference.

Refusal Cause Remedy
body_too_large 413: The form or JSON body exceeds 16 KiB. Send a smaller body.
form_required, json_required 415: A form post is not application/x-www-form-urlencoded, or an update is not application/json. Use the content type required by that route.
message_required, message_length 400: The message is missing, empty after trimming, or longer than 1,000 characters. Send a nonempty message within the limit.
invalid_json 400: The update body is not valid JSON. Send a JSON object with a string message.
not_found 404: The route or entry does not exist, or the identifier is invalid. Check the path and use an identifier shown on the page.
method_not_allowed 405: An entry route received a method other than GET, PATCH or DELETE. Use one of the methods in its Allow header.
internal 500: A route met an unexpected error, such as a failed database query. Read the application logs and correct the error before retrying.

GET /health returns status 503 with {"ok":false} if the database read fails or takes more than two seconds. Read the database and application status; retry once the connection works.

If a migration cannot complete, startup stops before the server listens, so there is no HTTP response. Read the startup error. Correct an unapplied migration, or add a new file for a change to an applied schema. See Keep the schema as migrations.

For platform refusals during declaration or deployment, follow Add a database and Deploys.