--- title: Build a form backed by a database description: Build and deploy a public guestbook with PostgreSQL, ordered migrations, a database health check, and routes to read, change and delete entries. slug: /guides/build-a-form-backed-by-a-database order: 41 visibility: public redirects_from: [] --- # Build a form backed by a database 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](/cloud/guides/add-sign-in-to-your-app/). ## 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](/cloud/getting-started/connect-your-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: ```json { "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: ```json { "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: ```sql -- 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: ```js // 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](/cloud/guides/add-a-database/#4-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: ```js // 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 ` Guestbook

Guestbook

`; } 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: ```js // 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](/cloud/getting-started/deploy-an-application/) 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](/cloud/getting-started/build-an-app-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](/cloud/guides/turn-on-development-and-promote/) 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/` | The saved `id` and `message`. | | `PATCH /entries/` with `Content-Type: application/json` and `{"message":"Updated"}` | The changed entry. | | `DELETE /entries/` | `{"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](/cloud/guides/add-a-database/#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](/cloud/guides/run-your-application-on-your-machine/#run-against-your-own-postgresql). To use the platform's development database instead, follow [Develop locally](/cloud/guides/add-a-database/#5-develop-locally). ## Refusals The table lists only the example application's own responses. For platform refusals, read the [refusals reference](/cloud/reference/refusals/). | 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](/cloud/guides/add-a-database/#4-keep-the-schema-as-migrations). For platform refusals during declaration or deployment, follow [Add a database](/cloud/guides/add-a-database/#refusals) and [Deploys](/cloud/troubleshooting/deploys/). ## Related - [Add a database](/cloud/guides/add-a-database/) covers connection limits, credentials, migrations and backups. - [Serve a web client](/cloud/guides/serve-a-web-client/) explains forms, redirects and server-built pages. - [Test your application locally](/cloud/guides/test-your-application-locally/) shows how to test through a pool without a platform credential.